getting dates from text

[Dave Cullen]

I'm creating a query to extract records between two dates, with start
and end dates entered by the user. But the date field is stored as plain
text in the form "MM/DD/YYYY" and the results are not what I expect.

How do I change this criteria so the math compare works?

Between [Start Date MM/DD/YYY] And [End Date MM/DD/YYYY]

Thanks

 

[Allen Browne]

Change the field to a Date/Time field.

Then declare your parameters as Date/Time types (Parameters on Query menu,
in query design view).

You will not be able to get any sense from text dates in that format.

 

[Dave Cullen]

Change the field to a Date/Time field.

I can't. It's a linked table that I don't have ownership of, and the
compiled application that stores the data saves it as text.

You will not be able to get any sense from text dates in that format.

Isn't there an Access function to create a date data type from text?

 

[John Vinson]

I'm creating a query to extract records between two dates, with start
and end dates entered by the user. But the date field is stored as plain
text in the form "MM/DD/YYYY" and the results are not what I expect.

How do I change this criteria so the math compare works?

Between [Start Date MM/DD/YYY] And [End Date MM/DD/YYYY]

If you're storing the data as Text then Access will treat it as Text.
The text string "9/12/2004" sorts after the text string "12/14/2005" -
Access has no way to guess that you *intend* it to be interpreted as a
date/time value.

Either, as suggested, store dates properly in a Date/Time field, or
use a calculated field

CDate([TextDateField])

and apply your date range criteria to that calculated field.

John W. Vinson[MVP]

 

[Rick Brandt]

I can't. It's a linked table that I don't have ownership of, and the
compiled application that stores the data saves it as text.


Isn't there an Access function to create a date data type from text?

Assuming a text format of "MM/DD/YYYY" then...

RealDate: DateSerial(Right([FieldName], 4), Left([FieldName], 2),
Mid([FieldName], 4, 2))

CDate([FieldName]) would also work, but I don't like to use that because it
is dependent on the Regional Settings of the PC whereas DateSerial() is not
so affected.

 

[Dave Cullen]

Thak you. CDate() does indeed work, but with limits.

The date field is called SESSIONDATE. I create a column
Test Date: CDate([SESSIONDATE])

If I manually enter criteria for a date range using
Between #01/01/2004# And #12/31/2004#
The query works fine.

However, I want Access to prompt the user for the date range. This
criteria:
Between [Start Date] And [End Date]
without the # signs, doesn't work as expected. And Access won't let me
insert # signs within the prompt brackets.

Any solution to this?

Thanks

I can't. It's a linked table that I don't have ownership of, and the
compiled application that stores the data saves it as text.


Isn't there an Access function to create a date data type from text?

Assuming a text format of "MM/DD/YYYY" then...

RealDate: DateSerial(Right([FieldName], 4), Left([FieldName], 2),
Mid([FieldName], 4, 2))

CDate([FieldName]) would also work, but I don't like to use that because it
is dependent on the Regional Settings of the PC whereas DateSerial() is not
so affected.

 

[Douglas J. Steele]

As John Vinson pointed out, use Between CDate([Start Date]) And CDate([End
Date]) as your criteria.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thak you. CDate() does indeed work, but with limits.

The date field is called SESSIONDATE. I create a column
Test Date: CDate([SESSIONDATE])

If I manually enter criteria for a date range using
Between #01/01/2004# And #12/31/2004#
The query works fine.

However, I want Access to prompt the user for the date range. This
criteria:
Between [Start Date] And [End Date]
without the # signs, doesn't work as expected. And Access won't let me
insert # signs within the prompt brackets.

Any solution to this?

Thanks

Allen Browne wrote:

Change the field to a Date/Time field.

I can't. It's a linked table that I don't have ownership of, and the
compiled application that stores the data saves it as text.

You will not be able to get any sense from text dates in that format.

Isn't there an Access function to create a date data type from text?

Assuming a text format of "MM/DD/YYYY" then...

RealDate: DateSerial(Right([FieldName], 4), Left([FieldName], 2),
Mid([FieldName], 4, 2))

CDate([FieldName]) would also work, but I don't like to use that because
it
is dependent on the Regional Settings of the PC whereas DateSerial() is
not
so affected.

 

[Allen Browne]

Okay: if you are stuck you can use CDate() or CVDate() around the text
field, and declare the parameters.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thak you. CDate() does indeed work, but with limits.

The date field is called SESSIONDATE. I create a column
Test Date: CDate([SESSIONDATE])

If I manually enter criteria for a date range using
Between #01/01/2004# And #12/31/2004#
The query works fine.

However, I want Access to prompt the user for the date range. This
criteria:
Between [Start Date] And [End Date]
without the # signs, doesn't work as expected. And Access won't let me
insert # signs within the prompt brackets.

Any solution to this?

Thanks

Allen Browne wrote:

Change the field to a Date/Time field.

I can't. It's a linked table that I don't have ownership of, and the
compiled application that stores the data saves it as text.

You will not be able to get any sense from text dates in that format.

Isn't there an Access function to create a date data type from text?

Assuming a text format of "MM/DD/YYYY" then...

RealDate: DateSerial(Right([FieldName], 4), Left([FieldName], 2),
Mid([FieldName], 4, 2))

CDate([FieldName]) would also work, but I don't like to use that because
it
is dependent on the Regional Settings of the PC whereas DateSerial() is
not
so affected.