Default Date value in a form 1

[edioguardi]

I have a main form which allows the user to enter and change parameters in queries which will populate tables in the database. For the majority of it's uses we use the same date range. When I try to set the default values dor the text box to these dates it returns a completely different date.

Example (from Properties)
StartDate- Default: 12/01/2000
Format: Short Date
End Date- Default: 11/30/2001
Format: Short Date

In Form View
Start Date returns:12/30/1899
End Date returns:12/30/1899

When I click in Start Date it switches to:12:08:38AM
When I click in End Date it switches to: 8:48:00 AM

Any Ideas?

 

[RickSpr]

Access is interpreting "12/01/2000" in the Default property as an expression involving 2 divisions. If you change it to "#12/01/2000#" it will work.

In general, date/time constants should be surrounded by # signs anywhere that a VBA expression can be coded. This is true for the Default property, in VBA code of course, and in SQL statements. Rick Sprague

 

[edioguardi]

Thanks for clearing that up Rick. I had assumed that Access was reading it as an expression (although I still don't understand the results it gave me). When I had added the # signs in previous attempts, I simply saved and switched to form view to view the results. Turns out all I had to do was close and then reopen the form after making the change.

I added a few more problems with the calendar you helped me make to the thread, have any suggestions?

Thanks again,
Elizabeth

 

[RickSpr]

Let me explain the results you got. Date/time values are floating point numbers that give the number of days since December 30, 1899. The integer part is the number of whole days, so it represents the date. The fractional part is a partial day, and represents the time of day.

The fraction can be converted to hours by multiplying by 24, or to minutes by multiplying by 24*60, or to seconds by multiplying by 24*60*60. 12/01/2000, as a division, yields 0.006. This was the value stored in the date/time field. Multiplying 0.006 by 24*60 gives 8.64, which is 8.64 hours after midnight. Subtracting the 8 hours, we have a remainder of .64 hours. Multiplying by 60 we have 38.4 minutes. As a time, that gives you 12:08:38.4. Your format didn't include seconds, so this was rounded to 12:08:38, and since the integer part was 0, it represented 12/30/1899.

The string shown in your field, then, was "12/30/1899 12:08:38 AM". The field wasn't big enough to show all this, so at first you just saw 12/30/1899. When you clicked in the field, the cursor went to the end, so all you saw was 12:08:38 AM.

I'll check your other post and see if I have anything else to offer. Rick Sprague