General Date to Short Date problem (1 Viewer)

[Steve C]

Field1 is General Date Format - I'd like to query using the Date() Function

But that doesn't work.

I tried creating Field2 in a Short Date Format with a Short Date Input Mask and used VBA

Field2 = Field1

Planning to use the Date() Function to query Field2 and sort Field1

But that doesn't work either because the time part of Field1 transfers right across to Field2

Any idea or pointers on how to tackle this would be much apreciated.

 

[jdraw]

Please post the SQL of the query that "doesn't work".

 

[Pat Hartman]

If you want only the date portion of a date, use the DateValue() function:

If DateValue(MyDate) = Date() Then

to get only the time, use TimeValue().

Dates are stored internally as double precision numbers with the number of days since Dec 30, 1899 being the integer portion and the time since midnight in the decimal places.

Once you format a date field, it looses its "date" properties and becomes a string so NEVER format a date field except for viewing/printing.

 

[Steve C]

Oh dear.

When creating my Date/Time Fields in the Table, I made the Format General Date. Was that wrong?

If yes, should I have used a Form, Text Box, Format General Date, to add the data to a Table?

I'll try your DateValue() Function Pat - Thank you very much.

Thank you too to jdraw. I'll post the SQL if (when) I get really stuck.

Best wishes

Steve