Calculated Date Field 1

[3063]

I am working on a database to track employees training. Some of the training that is tracked is required every 12 months, some every 2 or 3 years. What I would like to do is have a field in my table, for instance called CIMS, with a date field and have it automatically calculate, for instance 24 months into another date field in my table, called for instance "due date." The reason I would like to due that is so I can create reports based on the due dates fields to see who is due for training. I have read this is not a good idea to do this, but not sure of a better way. I have tried creating a query and adding a field called due date in the query, using the DateAdd function and it works, but when I set the criteria (in the new field) in the query, to get data for a certain time period for my reports, it uses the dates in the field I based by query on (CIMS), and not on my new field that has been calculated out. If anyone can assist me, or has a better way, it would greatly be appreciated. I have learned sooo much from reading posts on this site.

 

[Eupher]

Hi, 3063,

but when I set the criteria (in the new field) in the query, to get data for a certain time period for my reports, it uses the dates in the field I based by query on (CIMS), and not on my new field that has been calculated out

Are you sure you are using your calculated field as your criteria? Can you post the SQL of the query that is the recordsource for your report?

Ken S.

 

[3063]

Here's the SQL for for new field in my query: SELECT TblStaff.IdStaff, TblStaff.LastName, TblStaff.FirstName, TblCIMS.CIMS, TblCIMS.CIMSDueDate, TblCIMS.IDC, TblCIMS.IDCDueDate, DateAdd("m",36,[CIMS]) AS Due FROM TblStaff INNER JOIN TblCIMS ON TblStaff.IdStaff = TblCIMS.CtrlCIMS;
It works fine, but when I use the parameter function in the query on the new field(Between [begin date ] and [ending date])it will not filter for the dates I need and that is what I would base my report on.

 

[Eupher]

Can you show me the SQL with the parameters included?

Ken S.

 

[TheAceMan1]

How are ya 3063 . . . . .

[blue]For form or report, all you should really need is an unbound textbox[/blue] (this would alleviate the ambiguity with query) as the DueDate. You already have a [blue]BaseDate[/blue] field. How you assign the [purple]DueDate[/purple] (directly in the controlsource or thru a function) depends on where your getting the [purple]interval[/purple] (days, months . . .).

So where is the [purple]interval[/purple] coming from (another field in the same record . . . what)?

If you don't have a field for interval, I highly recommend adding one, unless in can be computed . . .

See Ya! . . . . . .

 

[3063]

Here it is: SELECT TblStaff.IdStaff, TblStaff.LastName, TblStaff.FirstName, TblCIMS.CIMS, TblCIMS.CIMSDueDate, TblCIMS.IDC, TblCIMS.IDCDueDate, DateAdd("m",36,[CIMS]) AS Due
FROM TblStaff INNER JOIN TblCIMS ON TblStaff.IdStaff = TblCIMS.CtrlCIMS
WHERE (((DateAdd("m",36,[CIMS])) Between [Begin Date] And [Ending Date]));

 

[Eupher]

3063,

Where are you getting [Begin Date] and [Ending Date]? Are they fields in your table? Controls on your form?

Ken S.

 

[3063]

Neither. It's in the query. In the query I created, I created a new field called "Due Date" with this function: Due Date: DateAdd("m",36,[CIMS]), which works. It calculates out 36 months in the new field(Due Date). Underneath my new field "Due Date" in my query, where you can select a criteria, I used the parameter function "Between [] and [] function. This is the function that I cannot get to work. I'm wondering if it's because it is not really a field in table. It's just producing calculated data that's not permanent.

 

[PHV]

And what about this ?
PARAMETERS [Begin Date] DateTime, [Ending Date] DateTime;
SELECT TblStaff.IdStaff, TblStaff.LastName, TblStaff.FirstName, TblCIMS.CIMS, TblCIMS.CIMSDueDate, TblCIMS.IDC, TblCIMS.IDCDueDate, DateAdd("m",36,[CIMS]) AS Due
FROM TblStaff INNER JOIN TblCIMS ON TblStaff.IdStaff = TblCIMS.CtrlCIMS
WHERE DateAdd("m",36,[CIMS]) Between [Begin Date] And [Ending Date];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[3063]

PHV,

It worked! Thank you. My next question is can I setup another one up in the same query? I need to setup one for my IDC Field with a 24 month calculated field. I need them both for the same report. I tried copying the exact code and then changing information to match the IDC field. I get an error that says, "Characters found at end of SQL Statement.

 

[PHV]

Any chance you could post your erroneous SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[3063]

Here's the code: PARAMETERS [Begin Date] DateTime, [Ending Date] DateTime;
SELECT TblStaff.IdStaff, TblStaff.LastName, TblStaff.FirstName, TblCIMS.CIMS, TblCIMS.CIMSDueDate, TblCIMS.IDC, TblCIMS.IDCDueDate, DateAdd("m",36,[CIMS]) AS Due
FROM TblStaff INNER JOIN TblCIMS ON TblStaff.IdStaff=TblCIMS.CtrlCIMS
WHERE DateAdd("m",36,[CIMS]) Between [Begin Date] And [Ending Date];PARAMETERS [Begin Date] DateTime, [Ending Date] DateTime;
SELECT TblStaff.IdStaff, TblStaff.LastName, TblStaff.FirstName, TblCIMS.CIMS, TblCIMS.CIMSDueDate, TblCIMS.IDC, TblCIMS.IDCDueDate, DateAdd("m",24,[IDC]) AS Due
FROM TblStaff INNER JOIN TblCIMS ON TblStaff.IdStaff=TblCIMS.CtrlCIMS
WHERE DateAdd("m",24,[IDC]) Between [Begin Date] And [Ending Date];

 

[PHV]

And what about this ?
PARAMETERS [Begin Date] DateTime, [Ending Date] DateTime;
SELECT TblStaff.IdStaff, TblStaff.LastName, TblStaff.FirstName, TblCIMS.CIMS, TblCIMS.CIMSDueDate, TblCIMS.IDC, TblCIMS.IDCDueDate, DateAdd("m",36,[CIMS]) AS Due
FROM TblStaff INNER JOIN TblCIMS ON TblStaff.IdStaff=TblCIMS.CtrlCIMS
WHERE DateAdd("m",36,[CIMS]) Between [Begin Date] And [Ending Date]
UNION ALL SELECT TblStaff.IdStaff, TblStaff.LastName, TblStaff.FirstName, TblCIMS.CIMS, TblCIMS.CIMSDueDate, TblCIMS.IDC, TblCIMS.IDCDueDate, DateAdd("m",24,[IDC])
FROM TblStaff INNER JOIN TblCIMS ON TblStaff.IdStaff=TblCIMS.CtrlCIMS
WHERE DateAdd("m",24,[IDC]) Between [Begin Date] And [Ending Date];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[3063]

Ok. I think it worked, but is there a way that I can have a new field in there called IDC Due Date that shows the calculated 24 months like I have for the CIMS. That information is what I need to show on my report.

 

[PHV]

PARAMETERS [Begin Date] DateTime, [Ending Date] DateTime;
SELECT TblStaff.IdStaff, TblStaff.LastName, TblStaff.FirstName, TblCIMS.CIMS, TblCIMS.CIMSDueDate, TblCIMS.IDC, TblCIMS.IDCDueDate, DateAdd("m",36,[CIMS]) AS [CIMS Due Date], DateAdd("m",24,[IDC]) AS [IDC Due Date]
FROM TblStaff INNER JOIN TblCIMS ON TblStaff.IdStaff=TblCIMS.CtrlCIMS
WHERE DateAdd("m",36,[CIMS]) Between [Begin Date] And [Ending Date]
OR DateAdd("m",24,[IDC]) Between [Begin Date] And [Ending Date];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[3063]

PHV,

Thanks for all your help. Everything is working great. Any suggestions on how I can learn more about coding in Access. Would you recommend taking Visual Basic or SQL classes?