Date Calculations

Views: 1
I need some help please, on my form I have two boxes on is called "test date" (data type time and date) and aonther called "retest date" (data type time and date) what I want to happen is when a date is put into "test date" via date picker then "retest date" will show date in "test date" but will have 2 years added two it? please remember I am a newbie at this so an explination as to how your solution works would be a godsend to me understanding it,

More topics

Converting a Text Date String to a Date for Calculations

I'm just jumping into Access and have the following question. In my Purchase Order Table, I have a date field calcualated as text, i.e. "01/12/04". I need to convert this to a date format in my queries so I can do calculations, i.e. 01/12/04 - 01/05/04 = 7 days. Can someone help me with how to convert this text date to a date format

Date Calculations

I'm new to Access and having problems using dates in calculations.
I want to use these in a form :

Date Field: Date()
Date Of test: "Date Field"
Date of Next Test:"Date of Test" + 180 days
Days Overdue: Date() -"Date of Next Test"

I've tried to work it out using expressions, but don't think I'm grasping the syntax.I come from the simple world of the Approach database where you can create calculated fields in the table.

Calculations in report footer

I have created a report, based on a query to filter results by date range as user selected. The report footer is performing calculations (percentages) from the values returned. Sometimes some of these fields might be returned blank and when this happens my calculations return #num! or #div/0! How can I get these to display a 0 instead of the errors?

Examples of using dates as criteria in Access queries

Here are some common date criteria examples, ranging from simple date filters to more complex date range calculations. Some of the more complex examples use Access date functions to extract different parts of a date to help you get just the results you want.

MS Access Date Calculations

I am trying to return a value in a report based on comparing a date value to a start and end date range. For example, if 08/04/2010 falls between 08/01/2010 (start date) and 08/15/2010 (end date) return a value of "X".

This works OK until the end date exceeds 08/10/2010. The value isn't returned when the end date is 08/11/2010 or higher.

My goal is to create a representation of when certain dates have been selected in a timeline.

I am hoping someone can help me out. I have a table that logs information on weigh bills.
ie client number, Kg (which is the weight), date, etc.

I am trying to create a query that will let me select a date range (which I have the entry for) and then list each client with a total Kg for the date range entered.

How or where can I enter the sum function

Date calculations

I am trying to convert date data from Excell in the "days from 1900" format to (mm/dd/yyyy) in Access. Is there a function or procedure available for this?

Update method

I need some advice regarding the following method I'm using to update data:

I have a form with code on the On Current event that runs a function to do some calculations with the data in fields on the form and writing the results back into fields on the form.
The calculations relay on the date, so data of the diffrent records are always "out of date" until the user goes to a spesific record which means the On Current event procudure will then update the spesific record.
Now if the User want to view a list of all records with their respected up to date fields, it means he has to run through all the records to make sure they are up to date.
Now I have automated this to do a run-through before the query opens, but the result is slow, especially with Users who have say 500 records and where the BE is on their server.
With 500 records it may take up to 4 minutes

Aggregate calculations on calculated query fields?

I made a query that uses fields from multiple tables to calculate values for daily metrics. I need to present monthly totals of the data as sums and/or means. The tables are linked by the Date (mm/dd/y) field. I enter ‘between 03/01/2010 and 03/31/2010’ in the criteria for the date field. For the totals I enter ‘group by’ for the date and ‘sum’ or ‘avg’ for the calculated fields.

When the query is executed I receive an error that I ‘tried to execute a query that does not include the specified expression *** as part of an aggregate function. *** is the calculated field in the query. The help button reveals this is also known as Error 3122.

I thought it would be straightforward to select this data by a date range and perform the calculations.

Lookup expression (or something like it)

with Access 2007 expressions. I have one table of company cost allocations, and another table that needs to use these calculations to distribute costs.

Example - the split could be Company A - 25%, Company B - 25%, Company C - 50%. There are many different combinations of splits to choose from.

Total cost (to date) could be \$125,000.and I need to take the above percentages and calculate the cost distribution across the participating companies. Is this possible?

In my case - I will have many different split scenarios which need to be "looked up", percentages passed, and calculations done