423,818 Members | 2,266 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,818 IT Pros & Developers. It's quick & easy.

Date Range in Access not working

P: 5
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!!!
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM OAUSER_ITEM LEFT JOIN OAUSER_ITEM_HISTORY ON OAUSER_ITEM.ITEM_NUMBER = OAUSER_ITEM_HISTORY.ITEM_NUMBER
  3. WHERE (((OAUSER_ITEM.QTY_ONHAND)<>0) AND ((OAUSER_ITEM_HISTORY.LAST_PURCH_DATE)>="#06/30/07#"));
Jan 21 '08 #1
Share this Question
Share on Google+
10 Replies


MindBender77
100+
P: 234
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#"
Jan 21 '08 #2

P: 5
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?????
Jan 21 '08 #3

jaxjagfan
Expert 100+
P: 254
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.
Jan 21 '08 #4

P: 5
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???
Jan 21 '08 #5

MindBender77
100+
P: 234


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
Jan 21 '08 #6

P: 5
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!
Jan 21 '08 #7

jaxjagfan
Expert 100+
P: 254
Try this:
If LAST_PURCH_DATE is in a viable date/string format then CDate function will convert it to a true date format.
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM OAUSER_ITEM LEFT JOIN OAUSER_ITEM_HISTORY ON OAUSER_ITEM.ITEM_NUMBER = OAUSER_ITEM_HISTORY.ITEM_NUMBER
  3. WHERE (OAUSER_ITEM.QTY_ONHAND)<>0) AND (CDate(OAUSER_ITEM_HISTORY.LAST_PURCH_DATE)>=#06/30/2007#);
Jan 21 '08 #8

P: 5
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...
Jan 22 '08 #9

jaxjagfan
Expert 100+
P: 254
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.
Jan 22 '08 #10

NeoPa
Expert Mod 15k+
P: 31,112
...
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.
Jan 22 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.