getting dates from text

  • Thread starter Thread starter Dave Cullen
  • Start date Start date
D

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
 
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.
 
Allen said:
Change the field to a Date/Time field.
Click to expand...

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.
Click to expand...

Isn't there an Access function to create a date data type from text?
 
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]
Click to expand...

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]
 
Dave said:
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?
Click to expand...

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.
 
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



Rick said:
Dave said:
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?
Click to expand...

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.
Click to expand...
 
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!)



Dave Cullen said:
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



Rick said:
Dave said:
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?
Click to expand...

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.
Click to expand...
Click to expand...
 
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.

Dave Cullen said:
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



Rick said:
Dave said:
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?
Click to expand...

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.
Click to expand...
Click to expand...
 
Back
Top