Date problem

[jrh]

I have a form with a subform. The Dates I use on the form are saved in the appropriate table in the format I want (medium date). However, on my subform, linked to a different table, I want he medium date format, but it saves it with a time. The problem is when I try to get the report based on a query by date, an error appears because of the time (I think). The query works if the time part is removed manually, but I don't think I need to do this extra step if I can set it up properly. I do not want the time saved with the date. Help!

Thanks
JRH

 

[jimbrooking]

jrh-

Dates are the work of the devil.

First, when you set a date in the form field, are you using Now() or Date()? Date() would be preferred. Second, be sure the "Medium Date" format is specified in all tables that will contain the date in question, and in all controls bound to that date in forms or reports. Third, if you are setting up a date comparison, be sure you are comparing using a Date() function, not a Now() function.

I keep running into similar problems, and don't have any foolproof solutions. I did once get frustrated enough that I stored the date as a string, and used CDate() whenever I needed to compare with a date. Not elegant, but it worked in that instance.

Jim

 

[raskew]

If you are saving dates in date/time data type (versus strings), they
ARE NOT "…saved in the appropriate table in the format I want
(medium date)".

Instead, they are saved as a double-precision, floating-point
number (up to 15 decimal places). - The integer portion of
the double-precision number representing the date -
the decimal portion representing the time.

To illustrate, try this from the debug window:

MyDate = date()
? cdbl(MyDate)
37575

MyDateTime = now()
? cdbl(MyDateTime)
37575.7996296296

From this point on formatting, in one form or another,
is used to return the date/time as you want to see it, e.g.:

? format(myDate, "medium date")
15-Nov-02

? format(myDate, "short date")
11/15/02

? format(myDate, "yyyymmdd")
20021115

? format(myDate, "mmm yyyy")
Nov 2002

…and similarly:

? format(myDateTime, "General Date")
11/15/02 7:11:28 PM

? format(myDateTime, "Medium Date") & " " format(myDateTime, "hh:nn")
15-Nov-02 19:11

? format(myDateTime, "Medium Date")
15-Nov-02

Here are some things you can do at home to manipulate dates:

'use the cdbl() function to show date/time as a number
MyNaturalDateTime = cdbl(MyDateTime)
? MyNaturalDateTime
37575.7996296296

'use the int() function to show just the whole number
MyNaturalDate = int(cdbl(MyDateTime))
? MyNaturalDate
37575

'subtract the Int() portion from the whole number to leave just the time portion
MyNaturalTime = MyNaturalDateTime - MyNaturalDate
? MyNaturalTime 0.7996296296


Hopefully, the above will provide some insight towards solving your
date-related woes.