Access not recognizing Date Format

  • Thread starter Thread starter ecwhite
  • Start date Start date
E

ecwhite

Hello,
I have a date field that I extracted from a main frame database and the date
field comes into my access database like 2005-01-05. My users when looking
for data that falls within a date range will like to look for it in
mm/dd/yyyy. I formatted the field to Format(([my_Date]),'mm/dd/yyyy'). When
they type in the begin and end date prompt 02/01/2009 and 02/28/2009. The
query result will bring back every record in February of all the years in the
database e.g. 02/10/2005, 02/1/2003 etc instead of only the February of 2009.

How do I make this date field so that Access can recognize the date type and
return the correct date range? I am not sure of the exact data type in the
main frame but it comes in as yyyy-mm-dd.

Thanks for your help.
 
Date comparisons should be performed with date data types, not strings. The
format function will convert to a string data type.

Use CDate() to convert your date field to an actual date. Then compare it
with other date values.
 
Hello Duane,

CDate(([my_Date])) gives me a datatype mismach error message.

Thanks.

Duane Hookom said:
Date comparisons should be performed with date data types, not strings. The
format function will convert to a string data type.

Use CDate() to convert your date field to an actual date. Then compare it
with other date values.

--
Duane Hookom
Microsoft Access MVP


ecwhite said:
Hello,
I have a date field that I extracted from a main frame database and the date
field comes into my access database like 2005-01-05. My users when looking
for data that falls within a date range will like to look for it in
mm/dd/yyyy. I formatted the field to Format(([my_Date]),'mm/dd/yyyy'). When
they type in the begin and end date prompt 02/01/2009 and 02/28/2009. The
query result will bring back every record in February of all the years in the
database e.g. 02/10/2005, 02/1/2003 etc instead of only the February of 2009.

How do I make this date field so that Access can recognize the date type and
return the correct date range? I am not sure of the exact data type in the
main frame but it comes in as yyyy-mm-dd.

Thanks for your help.
Click to expand...
Click to expand...
 
Could you provide us with the data type of your field? Please provide the SQL
view of your query.

If your field is actually a date data type then just get rid of the Format().

--
Duane Hookom
Microsoft Access MVP


ecwhite said:
Hello Duane,

CDate(([my_Date])) gives me a datatype mismach error message.

Thanks.

Duane Hookom said:
Date comparisons should be performed with date data types, not strings. The
format function will convert to a string data type.

Use CDate() to convert your date field to an actual date. Then compare it
with other date values.

--
Duane Hookom
Microsoft Access MVP


ecwhite said:
Hello,
I have a date field that I extracted from a main frame database and the date
field comes into my access database like 2005-01-05. My users when looking
for data that falls within a date range will like to look for it in
mm/dd/yyyy. I formatted the field to Format(([my_Date]),'mm/dd/yyyy'). When
they type in the begin and end date prompt 02/01/2009 and 02/28/2009. The
query result will bring back every record in February of all the years in the
database e.g. 02/10/2005, 02/1/2003 etc instead of only the February of 2009.

How do I make this date field so that Access can recognize the date type and
return the correct date range? I am not sure of the exact data type in the
main frame but it comes in as yyyy-mm-dd.

Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top