Date Calculation Fomula??

[RelapseWizard]

Hope someone can help so here goes, I have a data quality database I run on a daily basis, however I keep having to go into the database query to amend the date everyday my object is to try automate this a much as I can i have tried this.

"ADMISSION_DATE_TIME" is the field
">=TODAY() And <TODAY()+7" in criteria



I think It would work if I used a varieable in a module but still new to that VBA area, I use access 2000 any help is much appreciated.

 

[rainman89]

check out the dateadd function

>=date() And < dateadd("d",7,date())

should give you the upperbound for your criteria

 

[namliam]

DATE() is the only thing you need though... Perhaps combined with a Between... instead of the > and <= combination. Though between can be a headache....

Between DATE() And DATE()+7

>=DATE() And <DATE()+7

Dateadd is a nice function if your moving months or years, for days its not needed and just the simple +7 will work just fine.

 

[Brianwarnock]

I notice that your field is ADMISSION_DATE_TIME, which suggests a time element. Be aware that Date() defaults to a time of 00:00:00 and therefore you wont get your last day.
You can either use Datevalue(ADMISSION_DATE_TIME) in the comparison or if no activity takes plae a midnight add 8 instead.

Brian

 

[RelapseWizard]

Thanks for the quick reply

I triedthis one >=date() And < dateadd("d",7,date()) but an error message came up saying unidentified function...same with this suggestion Between DATE() And DATE()+7 I think the query is expecting a function to look up the values of "date()"
I'm sure this can be set up in VB Modules....thanks

 

[Brianwarnock]

Date() and Dateadd are standard ACCESS functions s o they should be available to you. can you post the full SQL?

Brian

 

[RelapseWizard]

The Access database I use is on a "update query" In the criteria is where I'm placing the the date spec and fomula so when I run an update query thats when the message comes I'll see if I can upload my database here.

 

[namliam]

1) You have a module called "Date"
Open Module1
Press CTRL+R to show the Project viewer
Find the module "Date", click on it
Press F4 to show the Properties window
Rename that module to "mdlDate"

NEVER EVER use reserved words!

2) Parameter
You have entered [>=Date() And <DateAdd("d",7,Date())] as beeing a parameter...
A parameter is to be asked from the user, you dont want this... Remove the parameter

Your query now runs

Though IMHO a "period" like you have here is a calculated field, and should not be stored in the table at all..

 

[RelapseWizard]

Thanks namliam it worked, I'm just playing around with it now as I've run the initial query I use, and then run the modfied query with the DateAdd criteria, Its pulled out the same figures based on the links I've put in place but for some reason its not picking up 3 or 4 records here and there....nice one.

 

[namliam]

Take into account Brian's comment about your field potentialy having a time in it... Date does not have a time in it... or as far as it does... its Midnight... Thus will not pick up anything beyond midnight...

 

[Brianwarnock]

Take into account Brian's comment about your field potentialy having a time in it... Date does not have a time in it... or as far as it does... its Midnight... Thus will not pick up anything beyond midnight...

And just to reinforce this, it's midnight first thing not midnight last thing ie 00:00:00 not 24:00:00

Brian

 

[ChrisLayfield]

I tried to use what was extolled here to build my own query and have hit a bit of a roadblock.

Field:
ExpirationDate: DateAdd("d",Val([tbl_TrainingCourses]![CourseExp]),[jtbl_EmployeeTraining]![CompletionDate])

Criteria: <Date()+30

My goal was to have only the records which will expire within the next 30 days show on the query, but all records are shown. What has me confused is that the calculated expiration date is correct on the querry. Just to be clear the CourseExp is a text unit because there are some training courses with no expiration date which are denoted as "N/A" for the expiration date.

 

[boblarson]

I've found that many times when creating this calculated field, you need to take this query and then use it in another query where you can apply criteria to it.

 

[namliam]

You might need > date() and <= Date() +30 as using only the <Date+30 will also show all that have expired already

The combination of DateAdd and the >date() doesnt/shouldnt pose any problems in and on itself. Problesm might be comming from your Val() and /or the completiondate not actually being a 'real' date field and/or the >date() thing I mentioned.
Also keep in mind any ORs that may exist in the query that may circumvent this limitation.

 

[ChrisLayfield]

Thank you both for the assistance. I got it to work and the solution was to put the calculation in ()...<=(Date()+30)