DateAdd

[Forrest]

I have a form with a pop up calendar that fills cboDate1 and it works great. When I click the Save button, I run a query checking to see if a record was added yesterday Date()-"1". The query works great.

If a record is not found for yesterday, I display a message box saying a record was not entered on DateAdd("d", -1, Date). The problem is that Date in DateAdd takes on the value of whatever is in cboDate1, not today's date.

First, is DateAdd("d", -1, Date) what I want to use to display yesterday's date in my message box?

If so, how do I make Date = today's date regardless of what is in cboDate1?

Thanks,
Forrest

 

[Rabbie]

Firstly Date is a reserved word in access and refers to the function Date() which always returns todays date.

Date()-1 will always give you yesterday's date.

HTH

 

[Forrest]

I had cboDate1 feed a table column titled Date. Whether I used DateAdd or Date - 1, Date returned a value of whatever was in cboDate1. Thinking that the table column Date may be confusing the issue, I renamed it as Date Inspected, made the appropriate adjustments and now Access is telling me that it can't find the field 'Date' referred to in my expression.

Everything on the form does what I want it to other than not thinking that Date is today. In the query, I use Date() - 1 and it works as advertised. Just not on the form.

'Save and check if results were entered yesterday.
DoCmd.Save
strTeam = cboTeam.Value
strCrane = cboCrane.Value
strName = txtName.Value
strShift = cboShift.Value
nRecords = DCount("*", "queryOneDayAgo")
If nRecords = 0 Then
If MsgBox("The inspection results for this crane were not entered for" _
& Date - 1 & " (yesterday). Would you like to enter inspection results now?", vbYesNo, _
"Information needed.") = vbYes Then
DoCmd.GoToRecord acDataForm, "frmInspection", acNewRec
cboTeam.Value = strTeam
cboCrane.Value = strCrane
txtName.Value = strName
cboShift.Value = strShift
cboDate1 = Date - 1
Else
DoCmd.Close
End If
End If

I'm an Access novice and I'm Forrest Gumping my way through this. Any suggestions?

Thanks,
Forrest

 

[missinglinq]

This is probably why you're getting flaky results! You should never, ever name an Access object Date! It is a Reserved Word in Access, and a very dangerous one at that!

For instance, having a field named Date you might think

Date = #12/31/2007#

would place the New Year's Eve in the field, but what it can actually do is set the date in your PC's system to 12/31/2007!

Change the name of that field/column and see what happens! Any change to it will do! txtDate or even what I call "stuttering nomenclature," DDate!

 

[boblarson]

If necessary download V-Tools (free) from

http://www.skrol29.com/dev/en_vtools.htm

and use the Total Deep Search feature to find all possible locations where Date has been used.

 

[Forrest]

Thanks very much for all your help. I did everything you told me to, even running a deep search and got rid of everything that, as far as I could tell, caused a problem with Date. Still got the same result. Ended up rebuilding the form, copying the controls and code from the old to the new and everything worked as it should. Oh, well.

Thanks again,
Forrest

 

[boblarson]

yeah, Access is that way sometimes. If you go and use a reserved word for something it can haunt you badly even though you take it away again.

Here's a few lists of reserved words:
http://support.microsoft.com/kb/q286335/
http://support.microsoft.com/?id=321266

and special characters to avoid in names:
http://support.microsoft.com/?id=826763