Related Topics

Date Calculations

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?


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.


Adding Calculations
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


SQL VBA Table/Query update- Possible use of cursors
I'm new to this forum and access programming. I was more of a COBOL programmer with a fairly good understanding of SQL.

I have table1 with say 4 fields with 100's of values. I want to create a new table/Query with the values from table 1, Add new columns to the query/table whose data involves calculations based on the fields from table 1 and other tables as well.

The calculations involves addition, subtraction, log, exponential etc., So I couldn't handle it on the SQL.

Also I want these calculations to happen automatically without any user interaction. Also for each Row(value) on table 1, I want a corresponding values to be calculated in thequery/Table.

Ideally for each data being coming into the table1 with 4 fields, I need to make some calculations and look like calculations are done for the additional columns are being added.

The data for table 1 comes from a external file, We are saying them we want the data in the format we want in table 1 with 4 fields.


Calculations
Let me start by saying I do not know how to use SQL or VB so all I am doing is within Access itself.

I have a Membership Database created in Access. The main table, Members Table, includes a field for Next Renewal. The Next Renewal Date should be one year from the last membership update. To date, the end user has been putting the Next Renewal in manually. What I want to do is to have it auto-entered by the program by writing a calculation that basically says =([LastUpdate]+365 and automatically posts the answer into the Next Renewal field.

Now, I know I cannot do calculations in a table, but I can in a form. So I wrote the calculation in the form, but when entries are made on the form that should update in the table, the Next Renewal field shows blank in the table.

I need that field in the table because I have to run a query based on the Next Renewal and I can't run a query based on a form field.

Can what I wish to do be done? How would I go about it


Calculated Fields in Entry Form
Table Pricing
Pricing Query

I am building a data entry form - when they enter their date I want it to save to the pricing table. The pricing query has calculations built in - can I use the pricing query to build theform - and show the calculated fields on the form - refresh before saving to the pricing table? Will the data be saved to the Table? The calculations will not be saved to the table.

Or should I build the form on the pricing table and create fields in the form to show the calculations?


Date/Time Issue
I am having a issue with some data I imported from a CSV file. First of all the Date/Time field came over as 'Text' data type instead of a 'Date/Time' data type. I tried to change the settings to a Date/Time format while importing but then it would give me an error and the date/time would not load at all.

I need Access to see this as Date/Time for calculations and such, but I am having a hard time converting it.

I have tried in a query using Format and CDate with no luck. Here is what the date/time looks like in the DATA_DATE field.

09-OCT-09 12.00.00.000000000 AM

This time format is new to me and I would rather it be 09/OCT/2009 12:00:00 AM as well as be a date/time datatype. I was thinking I will probably have toparse this "String" of text out using function like Len, Right, Left etc.

Using Access 2010


Creating a Dynamic Query
Firstly I know very little about microsoft access. I know how to create basic queries.
I need to create two queries
One with a criteria which needs to be entered everytime accessed. E.g. Date is variable according what you need to find.
I want to make it so that the user can type in a date that they wish to look for and it will be displayed with all the relationships.

Two
A A query which does calculations similar to excel. Uses data from the first query such as the date entered and calculates new data from it.
E.g. A addition of all results if they were a number in a row


Creating a Dynamic Query
Firstly I know very little about microsoft access. I know how to create basic queries.
I need to create two queries
One with a criteria which needs to be entered everytime accessed. E.g. Date is variable according what you need to find.
I want to make it so that the user can type in a date that they wish to look for and it will be displayed with the relationships.

Two
A A query which does calculations similar to excel. Uses data from the first query such as the date entered and calculates new data from it.
E.g. A addition of all results if they were a number in a row.


Changing date format for optional field
I need to do some date calculations on an optional date field that is current in YYYYMMDD text format.

I can get the date to format by using:
DateValue(Format(CLng([Date]),"0000-00-00"))
However, it returns an #Error if the field is blank.

I'm trying to write in the builder to leave the field blank if it is currently blank:
iif(IsNull[Date],"",(DateValue(Format(CLng([Date]),"0000-00-00")))


Data Going Weird on Different Computer
I have a database I want to install on another computer. I originally did it on my original computer and want to put it on another computer for others to use.

The problem lies that when I move over the database to another computer, running the same software, operating system my date calculations go weird on me.

Originally this is how things are setup

[Delivery Date] + 5 - Set to Short Date/Time

Rather than add 5 days as it normally does on the original computer I programmed this on, it adds +5 (and more) to the month. It also reformats the date to some weird format 19th month?!

For the life of me, I don't know what is going on. Can anyone help me identify the problem?


Best data type & length for currency calculations that involve percentages
I have a database that I'm building off of a process that is currently done in excel. I want my percentages calculations to match what I get in excel but I'm finding the numbers to be off by as much as .4%. I'm pretty sure they issue has to do with the precision of its calculations but I need some advise on what the best settings are.

My percent numbers are currently set to the field size "Double" with a percent formatting. My currency numbers are set to the format Currency and decimal places on auto.


Queries vs Reports/Forms
Where is the best place to do formatting and/or calculations? In the underlying query, or the report/form that calls it?

When I say best, I mean that in two senses - the best for the program, and the best for the person maintaining the database (eventually it will be handed off to someone else).

None of the calculations that are done require grouping etc that would be dependent on sub-forms/reports etc.

I am using Access 2003.


Form calculations in query
Is it possible to use calculations created in unbound text boxs on a form in a query? If so; how