Full Version: Number of Days between dates
UtterAccess Forums > Microsoft Access > Access Forms
dzajicek
I have a table that includes a Start Date field and a Tag field (among other fields). I am using a form to enter the data and I need to know the Days Between Each Start Date for each specific Tag. I have a calculation to do this but the dates must be entered sequencially. If you enter a date prior to the last date entered against the tag you get a negative number. Is there a way you can have the calculation done after data entry where the dates can be entered in any order?
strive4peace
if you put ABS (Absolute Value) in your equation for a calculated control, then the order of the dates won't matter
dzajicek
If you don't have the days even with ABS you still have problems for example:
You enter the following dates for one piece of equipment:
02/25/05
01/30/05
02/28/05
03/05/05
02/08/05
If you don't have the dates in order the days between each date will not be correct.
The employees entered the data may not enter the information each day, then when they do enter the data they may not enter it in the order of each occurence. I have the form calculating the date when the form closes but it is only sees the last record for that equipment. I need to be able to sort the table by equipment and date and then do the calculation for the # of days between each date but I am not sure how to do this since the dates are in the same field.
strive4peace
in a query, you can put the same recordset up multiple times, then link on your key field(s) and do MAX on the date of the the second recordset using criteria that it is <= date on current record...
dzajicek
Thanks for your response but I don't quite understand. Do I only put the Start_Date field in the query (twice) with Max in the Total and the <=[Forms]!etc. in the criteria field? If I do this how do I subtract the Start_Date from each other and when do I run the query?
strive4peace
please post your db and I'll give you an example. Tell me the table names and field names you are using.
dzajicek
The table is T_Findings. The technician enters data in the F_Findings Form. The Days Since Last Charge is the field that I need to calculate the number of days since last charge was added. Right now I have the days calculating on the form by using a sub form but this is where I am having problems because the dates may not be entered in order. I sure appreciate your help, thank you.
strive4peace
Tell me the table names and field names you are using ...
On looking at your T_Findings table, which is the date you are interested in, you have 3:
Start_Date
Complete_Date
Trigger_Date
strive4peace
btw, it is a good idea to put an autonumber field in your t_findings table
_findID, autonumber
on your form:
Name --> Days_Since_Last_Charge
ControlSource -->= [Start_Date] - dMax("Start_Date","T_findings", "Start_Date < #" & [Start_Date] & "#")
and this calculated value should NOT be stored...
in a query:
field--> Days_Since_Last_Charge: [Start_Date] - dMax("Start_Date","T_findings", "Start_Date < #" & [Start_Date] & "#")
dzajicek
If you can't store the calculated value how do you get the # of days into the T_Findings table? Also what is the query for and when does the query run?
strive4peace
You calculate the value whenever you need it
Is for the query -- just showing you how to do a calculated field in a query in case you have a need to do that sometime -- like for a report since you should not store calculated values
dzajicek
Oh, very good then. This is working great on the form but since the calculation is the control source on my form it doesn't save the # of days to my table!! Another thing can you explain to me in normal words how this is working? Again, thank you so much for taking the time to help me with this.
dzajicek
Now I need this to calculate based on only the TAG selected on the form. The tag is the number for each piece of equipment that refrigerant can be added to. The # of days between charge should be based on the tag number's Start_Date. Can this calculation you gave me be changed so the calculation is only looking at the Tag number's dates?
strive4peace
you need to quit thinking in terms of controls on your form... and start thinking about your FIELDS in your TABLES ... forms are simply used to edit, display, and view data in tables or for menus.
will help you with the next step if you:
tell me the data structure (tables, fields) that are applicable and what the conditions are
dzajicek
The table is the T_Findings and the fields are Tag, Start Date, and Days Since Last Charge. As the technician enters data in the form he will select a Tag, then enter the Start Date. I need the Days Since Last Charge to be calculated for ONLY that Tag by using the Start Dates. Since the technician may not enter these findings until later the data may not be entered in date order and that is where my problem is. Again thank you for all your help.
strive4peace
on your form:
ame --> Days_Since_Last_Charge
ControlSource -->= [Start_Date] - dMax("Start_Date","T_findings", "Start_Date < #" & [Start_Date] & "# AND Tag='" & [tag] & "'")
assuming:
you have controls on your form with the Name property set to Start_Date and Tag
and Tag is text
in a query:
field--> Days_Since_Last_Charge: [Start_Date] - dMax("Start_Date","T_findings", "Start_Date < #" & [Start_Date] & "# AND Tag='" & [tag] & "'")
dzajicek
I entered ControlSource -->= [Start_Date] - dMax("Start_Date","T_findings", "Start_Date < #" & [Start_Date] & "# AND Tag='" & [tag] & "'") and now there the Days-Since_Last_Charge remains blank on the form as I enter data.
strive4peace
do a manual search to find out if there is a record matching those conditions
. write down Start_Date
2. write down Tag
and then make a query on your table
field --> Start_Date
criteria --> < #1/1/03#
(whatever date you wrote down with # for delimiters)
field --> Tag
criteria --> "your tag value"
see if any records are returned
dzajicek
All four record I entered were returned in the query.
strive4peace
post the SQL for the query that you made that returns records
post the ControlSource for the equation on your form
Is your form otherwise working fine?
dzajicek
Here is the SQL:
SELECT T_Findings.Start_Date, T_Findings.Tag
FROM T_Findings
WHERE (((T_Findings.Start_Date)>#1/1/2005#) AND ((T_Findings.Tag)="013-400"));
Here is the ControlSource:
=[Start_Date]-DMax("Start_Date","T_findings","Start_Date < #" & [Start_Date] & "# AND Tag='" & [Tag] & "'")
Yes my form is working fine except for not calculating the Days Since Last Charge
Again thank you for your help. I was looking at your profile and read that you just passed your 5000 post. Congratulations what an accomplishment, it is so great to have people like you to help us out.
strive4peace
Thank you, Diane -- nice to be recognized for something that I love to do anyway wink.gif
Can you post your db the way it is now?
I will test the form myself and figure out why it isn't working -- your equation looks right
dzajicek
Here it is.
strive4peace
"Tag" is a form property ... change the NAME property of your control called Tag to TagField
dzajicek
Looks great but I still need to resolve the issue of retaining the result from this calculation in my T_Findings table as this is used in the Q_Leak Rate calucation query that runs on the F_Findings closure.
strive4peace
you should not store calculated values...
You can get the value anytime in a query using the method I gave you above
dzajicek
When I try to run the query I am getting the following:
Syntax error in date in query expression 'Start_Date<##AND Tag='0"
I am using
[Start_Date] - dMax("Start_Date","T_findings", "Start_Date < #" & [Start_Date] & "# AND Tag='" & [tag] & "'")
in the Days_Since_Last_Charge
strive4peace
your [Start_Date] is, apparently, not filled out... is it in the fieldlist for your underlying recordset (query)?
dzajicek
Yes the Start_Date is filled out for all the records in the table. I have 146 records and the query only finds 95. Also now that this is working how do I use this calculated field for data I need in another calculated query? The following is the calculation I need to use the Days_Since_Last Charge calculation in.
Round(((T_Findings!Charge_Added/T_Equipment!Full_Charge)/(T_Findings!Days_Since_Last_Charge/365))*100,1)
strive4peace
check your joins -- right-click on link lines between fieldlists in the query and choose "Join properties" -- perhaps you need a LEFT join or a RIGHT join instead of the default, EQUIJOIN
T_Findings!Charge_Added is invalid syntax ... what are you trying to do and where is this equation? If is is in a query, you use:
T_Findings.Charge_Added
where
Charge_Added is a field in the T_Findings table
and the T_Findings table is one of your fieldlists for the query
... same with
T_Equipment!Full_Charge
and
T_Findings!Days_Since_Last_Charge
--> T_Equipment.Full_Charge
--> T_Findings.Days_Since_Last_Charge
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.