Date/Time Field Problem

[rsilberfarb]

I have a field that stores both date and time and I am having a very hard time using it in any queries.

I basically just want pull data from a specific range, this month, or the past 7 days, but it does not work.

The same query works on fields that are just date, but on the date/time fields they do not work.

Any help is appreciated.

Thanks

Ross

[OTWarrior]

Format(YourDateTime, "dd/mm/yy")
or
Format(YourDateTime, "hh:mm:ss AMPM")

[DarkSprout]

Or...

dteTableDate = Format(DLookup("dteDateField", "tbl_TableName", "[anAutoNumberField] = " & Criteria), "dd\/mm\/yyyy;;;\N\u\l\l")

[X-BRichard-X]

You SHOULD NEVER have multiple data elements contained within a data field. That is your first mistake. The reason for this universally adopted rule in database design is to prevent the exact scenario that forms the basis of your question.

The first think you need to do is separate out the time and date into (2) separate fields. This is achieved with a basic parsing algorithm created in a calculated query such as ParseDate: format([DateTimeField], "dd-mmm-yyyy") for the date field then parse out another calculated field for the time such as ParseTime: format([DateTimeField], "hh:mm AMPM")

You may have to create a separate table because you will have limited use of the calculated fields but if this is not the case, then you would use the search parameter under the appropriate field for you to access ONLY the date contents of the query (not the time data).