Access not recognizing Date Format

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

 

[Duane Hookom]

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.

 

[ecwhite]

Hello Duane,

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

Thanks.

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

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.

 

[Duane Hookom]

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

Hello Duane,

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

Thanks.

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

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.