Select query on based on date help

[KenshiroUK]

Hi all, I have a question on how to run a select query based on date for purchases.

Currently my date includes 2012-09-22T12:38:00+00:00, which when ran in query prompts the user for YY-MM-DD. I just want my user to be able to input the date as 2012-11-22, or 20121122, which will select orders from that day. When I run it tomorrow and put in the date it'll be tomorrows orders etc etc. I have tried 'like' and * but still returns nothing.

The row I'm using is purchase-date.

 

[nathansav]

Hi

You will need to add two columns, not shown, to your query.

These will be something like

Format(DATE,"yyyy-mm-dd") and have your criteria in there
OR
Like "*" & Format(DATE,"yyyy-mm-dd") & "*" , just using the date output as is 2012-09-22T12:38:00+00:00

so either criteria will work.

Let me know if you want anymore help

 

[nathansav]

SELECT tblTest.EndDate, Format(Left([enddate],10),"yyyy-mm-dd") AS Expr1, Format(Left([enddate],10),"yyyymmdd") AS Expr2
FROM tblTest;

 

[jdraw]

Ken,

Is this a linked Oracle table?
Dates in Access are delimited by # eg, #3/24/2012#

You may need to use a Between date_time1 AND DateTime2 since you seem to have Times stored with the Date, so the = will only work if you have the exact date and Time.

 

[KenshiroUK]

Okay I'm a still a bit stuck what do I need to post where? And no Jdraw the orders table is just linked to a delimited text file that is downloaded from Amazon.

 

[nathansav]

Hi Ken,

Did you get any joy with my note?

 

[jdraw]

Ken,
The date format is typical of Oracle (may be others also). So if that is how your text is formatted, you will have to proceed along the lines that nathansav has posted.
If you drop the Time portion, then your Date compares should be OK.

 

[KenshiroUK]

Hi Ken,

Did you get any joy with my note?

Hi, I didn't get any joy not sure if I entered it correctly though.

Would it be Field - purchase-date
Criteria - Like "*" & Format(DATE,"2012-11-21") & "*"

 

[nathansav]

would be

SELECT * from tblTest
FROM tblTest
WHERE cstr(Format(Left([DATE_FIELD],10),"yyyy-mm-dd))= "2012-11-21"