Query expression criteria- Access can't recognize column as dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a query and limiting the data with an expression for a column
containing dates. The problem seems to be that Access doesn't recognize the
column in my table as containing only dates, because when I input the
expression:
Between #1/1/1993# And #1/1/2004#
Access automatically puts quotes around the expression like this:
Between "#1/1/1993#" and "#1/1/2004#"
and it doesn't work.

I have tried other expressions, like:
<#1/1/2004# and <'1/1/2004'
and Access always puts quotes on it, I assume because it is reading the
column as text?

I'm not terribly familiar with Access, but I assume I have to do something
to re-format the column as a date column. I have already changed all the
dates so they appear in the same format, ie. 1/1/2004. There is no text in
any of the fields.
I know that the problem is not with my table, because I can use a date
expression with other columns and the expression works fine.

Please help!!
 
What is the Data Type of the field? Sounds like it's Text. So ACCESS expects
your entries to be text strings, which is why it puts " characters around
your values.

You could use a calculated field in a query to convert the text string to a
date/time value:
ItsADate: CDate([YourCurrentField])

Then use your criterion expression on this field:
Between #1/1/1993# And #1/1/2004#

Otherwise, you'll need to create a new field in the table, and set its Data
Type to Date/Time. You then will need to run an update query to copy the
data from the text field into the date/time field. Then you'll need to
delete the text field. Then you can use your criterion expression on the new
date/time field.
 
Thank you so much for your help! Just wondering if you can clarify for me-
What do you mean exactly by "calculated field"? Where do I enter that
command? I entered it in the 'criteria' box in the design view of the query
and it didn't work.

Ken Snell said:
What is the Data Type of the field? Sounds like it's Text. So ACCESS expects
your entries to be text strings, which is why it puts " characters around
your values.

You could use a calculated field in a query to convert the text string to a
date/time value:
ItsADate: CDate([YourCurrentField])

Then use your criterion expression on this field:
Between #1/1/1993# And #1/1/2004#

Otherwise, you'll need to create a new field in the table, and set its Data
Type to Date/Time. You then will need to run an update query to copy the
data from the text field into the date/time field. Then you'll need to
delete the text field. Then you can use your criterion expression on the new
date/time field.

--

Ken Snell
<MS ACCESS MVP>


LisaB said:
I am creating a query and limiting the data with an expression for a column
containing dates. The problem seems to be that Access doesn't recognize
the
column in my table as containing only dates, because when I input the
expression:
Between #1/1/1993# And #1/1/2004#
Access automatically puts quotes around the expression like this:
Between "#1/1/1993#" and "#1/1/2004#"
and it doesn't work.

I have tried other expressions, like:
<#1/1/2004# and <'1/1/2004'
and Access always puts quotes on it, I assume because it is reading the
column as text?

I'm not terribly familiar with Access, but I assume I have to do something
to re-format the column as a date column. I have already changed all the
dates so they appear in the same format, ie. 1/1/2004. There is no text
in
any of the fields.
I know that the problem is not with my table, because I can use a date
expression with other columns and the expression works fine.

Please help!!
Click to expand...
Click to expand...
 
A calculated field is added in the "Field" cell of the "design" view of a
query.

--

Ken Snell
<MS ACCESS MVP>

LisaB said:
Thank you so much for your help! Just wondering if you can clarify for
me-
What do you mean exactly by "calculated field"? Where do I enter that
command? I entered it in the 'criteria' box in the design view of the
query
and it didn't work.

Ken Snell said:
What is the Data Type of the field? Sounds like it's Text. So ACCESS
expects
your entries to be text strings, which is why it puts " characters around
your values.

You could use a calculated field in a query to convert the text string to
a
date/time value:
ItsADate: CDate([YourCurrentField])

Then use your criterion expression on this field:
Between #1/1/1993# And #1/1/2004#

Otherwise, you'll need to create a new field in the table, and set its
Data
Type to Date/Time. You then will need to run an update query to copy the
data from the text field into the date/time field. Then you'll need to
delete the text field. Then you can use your criterion expression on the
new
date/time field.

--

Ken Snell
<MS ACCESS MVP>


LisaB said:
I am creating a query and limiting the data with an expression for a
column
containing dates. The problem seems to be that Access doesn't
recognize
the
column in my table as containing only dates, because when I input the
expression:
Between #1/1/1993# And #1/1/2004#
Access automatically puts quotes around the expression like this:
Between "#1/1/1993#" and "#1/1/2004#"
and it doesn't work.

I have tried other expressions, like:
<#1/1/2004# and <'1/1/2004'
and Access always puts quotes on it, I assume because it is reading the
column as text?

I'm not terribly familiar with Access, but I assume I have to do
something
to re-format the column as a date column. I have already changed all
the
dates so they appear in the same format, ie. 1/1/2004. There is no
text
in
any of the fields.
I know that the problem is not with my table, because I can use a date
expression with other columns and the expression works fine.

Please help!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top