Date Conversion (1 Viewer)

[HJAMES]

I have a time stamp field from an Oracle database that I want to convert to a regular date field in my Access query so that I can pull data from the table base on start and end date.
The time stamp field is formatted as: 09/19/2006 03:16:00 PM

In my query I have tried the following formatting:

1. DateRcvd: Format((Left([time_stamp],10)),"mm/dd/yy")
or
2. DateRcvd: Format([time_stamp],"Short Date")

Without criteria I get all the records in the following format:

1. 09/19/06
2. 9/19/06

Dates in the table are from 06/01/06 to current date 07.

Using the following criteria - I get varing results but never what I want. For example, using format #1, if I enter 09/16/06 and 01/10/07 I get everything for 07 and nothing for 06
If I use format #2, I get everything for 9/16/06 (no other records) and everything for 07

Criteria:
Between [Forms]![frmDateRange]![StartDate] And [Forms]![frmDateRange]![EndDate]


Any suggestions?

 

[namliam]

how did you get the records into access? Or are they still in oracle and are you doing a passthrough query?

 

[KeithG]

Try

DateValue(Format([time_stamp],"MM/DD/YYYY"))

 

[HJAMES]

The records are still in Oracle - linked to access

DateValue(Format([time_stamp],"MM/DD/YYYY")) - returns only 2007 records

Thanks for the suggestion.

 

[Bat17]

I would try running it without any criteria so that you can check that the date conversions are working as expected.
You can also use Cdate to turn the date stamp into an Access date.

Peter

 

[HJAMES]

Found the solution and now I feel really silly.

The Criteria:
Between [Forms]![frmDateRange]![StartDate] And [Forms]![frmDateRange]![EndDate]

pulls the dates form 2 unbound fields. I discovered that the although the Input Mask was set up for date input, the Format was left blank. Once I changed the Format to Short Date the query pulled all the requested data.

Thank you all for your quick responses.

Helen