Date Range in Access not working

[AsheboroAlli]

Hey... googled my problem and got this site...

I am new to SQL and pretty much "winging it" as far as learning...

I have set the format on my field to mm/dd/yy to match the data that is returned.
I am using >= #mm/dd/yy# in my criteria, being >= '#06/30/07#' because I only want to see things bought the last 6 months of the year.

everything seems to look right. However, it is returning a few items from 2005? I have tried just about everything i can think of... I have posted the SQL below for suggestions!!! Thanks in advance!!!

SELECT OAUSER_ITEM.ITEM_NUMBER, OAUSER_ITEM.ITEM_DESC, OAUSER_ITEM.ITEM_DESC_2, OAUSER_ITEM.CURRENT_COST, OAUSER_ITEM.QTY_ONHAND, OAUSER_ITEM_HISTORY.LAST_PURCH_DATE
FROM OAUSER_ITEM LEFT JOIN OAUSER_ITEM_HISTORY ON OAUSER_ITEM.ITEM_NUMBER = OAUSER_ITEM_HISTORY.ITEM_NUMBER
WHERE (((OAUSER_ITEM.QTY_ONHAND)<>0) AND ((OAUSER_ITEM_HISTORY.LAST_PURCH_DATE)>="#06/30/07#"));

[MindBender77]

Hey... googled my problem and got this site...

I am new to SQL and pretty much "winging it" as far as learning...

I have set the format on my field to mm/dd/yy to match the data that is returned.
I am using >= #mm/dd/yy# in my criteria, being >= '#06/30/07#' because I only want to see things bought the last 6 months of the year.

everything seems to look right. However, it is returning a few items from 2005? I have tried just about everything i can think of... I have posted the SQL below for suggestions!!! Thanks in advance!!!

SELECT OAUSER_ITEM.ITEM_NUMBER, OAUSER_ITEM.ITEM_DESC, OAUSER_ITEM.ITEM_DESC_2, OAUSER_ITEM.CURRENT_COST, OAUSER_ITEM.QTY_ONHAND, OAUSER_ITEM_HISTORY.LAST_PURCH_DATE
FROM OAUSER_ITEM LEFT JOIN OAUSER_ITEM_HISTORY ON OAUSER_ITEM.ITEM_NUMBER = OAUSER_ITEM_HISTORY.ITEM_NUMBER
WHERE (((OAUSER_ITEM.QTY_ONHAND)<>0) AND ((OAUSER_ITEM_HISTORY.LAST_PURCH_DATE)>="#06/30/07#"));

I believe it could be one of two things:

First, the OAUSER_ITEM_HISTORY.LAST_PUCH_DATE might not be a set under the correct date format. Second, try removing the quotes from "#06/30/07#"

[AsheboroAlli]

I believe it could be one of two things:

First, the OAUSER_ITEM_HISTORY.LAST_PUCH_DATE might not be a set under the correct date format. Second, try removing the quotes from "#06/30/07#"

how do you change the date format for OAUSER_ITEM_HISTORY.LAST_PUCH_DATE?????

[jaxjagfan]

Hey... googled my problem and got this site...

I am new to SQL and pretty much "winging it" as far as learning...

I have set the format on my field to mm/dd/yy to match the data that is returned.
I am using >= #mm/dd/yy# in my criteria, being >= '#06/30/07#' because I only want to see things bought the last 6 months of the year.

everything seems to look right. However, it is returning a few items from 2005? I have tried just about everything i can think of... I have posted the SQL below for suggestions!!! Thanks in advance!!!

SELECT OAUSER_ITEM.ITEM_NUMBER, OAUSER_ITEM.ITEM_DESC, OAUSER_ITEM.ITEM_DESC_2, OAUSER_ITEM.CURRENT_COST, OAUSER_ITEM.QTY_ONHAND, OAUSER_ITEM_HISTORY.LAST_PURCH_DATE
FROM OAUSER_ITEM LEFT JOIN OAUSER_ITEM_HISTORY ON OAUSER_ITEM.ITEM_NUMBER = OAUSER_ITEM_HISTORY.ITEM_NUMBER
WHERE (((OAUSER_ITEM.QTY_ONHAND)<>0) AND ((OAUSER_ITEM_HISTORY.LAST_PURCH_DATE)>="#06/30/07#"));

Don't put (") quotes around your date.

((OAUSER_ITEM_HISTORY.LAST_PURCH_DATE)>=#06/30/07#));

If you are working in the query builder in Access you don't need to include the # signs, Access will include automatically if it needs.

[AsheboroAlli]

If I take out just the ' marks, if I take out just the #;s, and if I take out both it doesn't return anything for any option???

[MindBender77]

how do you change the date format for OAUSER_ITEM_HISTORY.LAST_PUCH_DATE?????

You have to view the table in "design view". This is where each column of the table is assigned a data type.

NOTE: I would not change table settings for it might cause you greater problems.

I would first start with removing the double quotes first. If they reappear after you remove them.

This would indicate that the OAUSER_ITEM_HISTORY.LAST_PUCH_DATE is set to a Text data type in the table design. It order to get the results you want, the data type this field must be set to date and formatted to be mm/dd/yy

JS

[AsheboroAlli]

The data type in Design View under properties is set to mm/dd/yy to match the info returned by Query...

is this what you mean... if not how would I change it from text to mm/dd/yy???

again, thank you for all of your help!

[jaxjagfan]

Try this:
If LAST_PURCH_DATE is in a viable date/string format then CDate function will convert it to a true date format.

SELECT OAUSER_ITEM.ITEM_NUMBER, OAUSER_ITEM.ITEM_DESC, OAUSER_ITEM.ITEM_DESC_2, OAUSER_ITEM.CURRENT_COST, OAUSER_ITEM.QTY_ONHAND, OAUSER_ITEM_HISTORY.LAST_PURCH_DATE
FROM OAUSER_ITEM LEFT JOIN OAUSER_ITEM_HISTORY ON OAUSER_ITEM.ITEM_NUMBER = OAUSER_ITEM_HISTORY.ITEM_NUMBER
WHERE (OAUSER_ITEM.QTY_ONHAND)<>0) AND (CDate(OAUSER_ITEM_HISTORY.LAST_PURCH_DATE)>=#06/30/2007#);

[AsheboroAlli]

Try this:
If LAST_PURCH_DATE is in a viable date/string format then CDate function will convert it to a true date format.

SELECT OAUSER_ITEM.ITEM_NUMBER, OAUSER_ITEM.ITEM_DESC, OAUSER_ITEM.ITEM_DESC_2, OAUSER_ITEM.CURRENT_COST, OAUSER_ITEM.QTY_ONHAND, OAUSER_ITEM_HISTORY.LAST_PURCH_DATE
FROM OAUSER_ITEM LEFT JOIN OAUSER_ITEM_HISTORY ON OAUSER_ITEM.ITEM_NUMBER = OAUSER_ITEM_HISTORY.ITEM_NUMBER
WHERE (OAUSER_ITEM.QTY_ONHAND)<>0) AND (CDate(OAUSER_ITEM_HISTORY.LAST_PURCH_DATE)>=#06/30/2007#);

It says that there is a data type mismatch in criteria expression error? ...

This is ridiculous... I cannot figure this out to save my life...

sheesh...

[jaxjagfan]

It says that there is a data type mismatch in criteria expression error? ...

This is ridiculous... I cannot figure this out to save my life...

sheesh...

With the Mismatch error then the "actual data" is not in a recognizable date format. This may not be true for all records tho. Are some of the date values Null, some other value or multiple input variations (I.E. "Missing Date", "Error", 2008/01/15, 010108, etc,)? This can cause the calculations to fail.

Sort the data by that date column in ascending order and look at the dates and then in descending order and lokk at dates. You may find some records with erroneous entries or formats the may need correcting first.

[NeoPa]

...
This would indicate that the OAUSER_ITEM_HISTORY.LAST_PUCH_DATE is set to a Text data type in the table design. It order to get the results you want, the data type this field must be set to date and formatted to be mm/dd/yy

JS

Not entirely true as any date format will be irrelevant (just being picky for the sake of full accuracy you understand - No criticism intended).
Any date is matched on m/d/y format in SQL (In GB we use d/m/y as standard - doesn't matter - have to use m/d/y in SQL).

See Literal DateTimes and Their Delimiters (#) for full explanation.