Access Query help

[Anubis]

for whatever reason i cant get this to work, our DB stores alot of run and batch data for samples such,

i have a query that pulls data based on Lot # that works fine, however sometimes lots get scanned in at different times or are split up so its easier to pull by date, however i cant get this to work

our date is stored the general way DD/MM/YYYY 12H time format so say today would be 10/15/2007 9:15:00 AM

if i set the query to pull by specified date i get nothing no matter what date i input, so im left opening and copying out of the DB manually which sucks

i have the stupid access book but its not helping

 

[PhatoseAlpha]

I'm fairly sure access requires #'s around dates. Can we see your exact syntax?

 

[Anubis]

just been useing brackets [] (Parameter search) to prompt the yser to enter a date they want, this works fine for Lot # but not the date, if you modify the query criteria to just pull 10/15/2007 it works but then every time you wanta diff date you have to mod the query and not just run it again

and access automaticialls adds the # around dates when you put them in

 

[Anubis]

people ive talked to think it has to do with the fact that the date and time are both stored in the same cell

the Date Measured Field contains them both as i stated above, no one seems to know how to make it work, and i cant find anything on the google. and FWIW if you do the search for a date and specific time it still does not work, even it you just copy it from the DB and use it as your parameter

 

[KLin]

Originally posted by: Anubis
for whatever reason i cant get this to work, our DB stores alot of run and batch data for samples such,

i have a query that pulls data based on Lot # that works fine, however sometimes lots get scanned in at different times or are split up so its easier to pull by date, however i cant get this to work

our date is stored the general way DD/MM/YYYY 12H time format so say today would be 10/15/2007 9:15:00 AM

if i set the query to pull by specified date i get nothing no matter what date i input, so im left opening and copying out of the DB manually which sucks

i have the stupid access book but its not helping

When you want to look at a specific day, but the data is stored as a timestamp, you need to use a range criteria. So if you wanted to look at data for today, you would need to say in the criteria field of a query below:

BETWEEN #10/16/2007 12:00 AM# AND #10/16/2007 11:59:59 PM#

If it was just the date being stored you could just say #10/16/2007#. hope this helps.

 

[KLin]

It would be even better to have a form with text boxes that allow a user to type in a start date and end date, then have them click a button that opens the queries. You can reference textboxes from a form within a query's critera. PM me for more details.

 

[Anubis]

Originally posted by: KLin

Originally posted by: Anubis
for whatever reason i cant get this to work, our DB stores alot of run and batch data for samples such,

i have a query that pulls data based on Lot # that works fine, however sometimes lots get scanned in at different times or are split up so its easier to pull by date, however i cant get this to work

our date is stored the general way DD/MM/YYYY 12H time format so say today would be 10/15/2007 9:15:00 AM

if i set the query to pull by specified date i get nothing no matter what date i input, so im left opening and copying out of the DB manually which sucks

i have the stupid access book but its not helping

When you want to look at a specific day, but the data is stored as a timestamp, you need to use a range criteria. So if you wanted to look at data for today, you would need to say in the criteria field of a query below:

BETWEEN #10/16/2007 12:00 AM# AND #10/16/2007 11:59:59 PM#

If it was just the date being stored you could just say #10/16/2007#. hope this helps.

that definitly does help, works actually, tho is alot to type for just wanting 1 day, i could reformat the DB to shart time i guess, however someone prob would get pissed

 

[Anubis]

Originally posted by: KLin
It would be even better to have a form with text boxes that allow a user to type in a start date and end date, then have them click a button that opens the queries. You can reference textboxes from a form within a query's critera. PM me for more details.

the way i have it it does promp the user for input, i just had they syntax wrong because of the time stamp

when i run it now i put in the start and end date like you first posted,

 

[KLin]

Originally posted by: Anubis

Originally posted by: KLin

Originally posted by: Anubis
for whatever reason i cant get this to work, our DB stores alot of run and batch data for samples such,

i have a query that pulls data based on Lot # that works fine, however sometimes lots get scanned in at different times or are split up so its easier to pull by date, however i cant get this to work

our date is stored the general way DD/MM/YYYY 12H time format so say today would be 10/15/2007 9:15:00 AM

if i set the query to pull by specified date i get nothing no matter what date i input, so im left opening and copying out of the DB manually which sucks

i have the stupid access book but its not helping

When you want to look at a specific day, but the data is stored as a timestamp, you need to use a range criteria. So if you wanted to look at data for today, you would need to say in the criteria field of a query below:

BETWEEN #10/16/2007 12:00 AM# AND #10/16/2007 11:59:59 PM#

If it was just the date being stored you could just say #10/16/2007#. hope this helps.

that definitly does help, works actually, tho is alot to type for just wanting 1 day, i could reformat the DB to shart time i guess, however someone prob would get pissed

Try this in the criteria field:

BETWEEN Cdate([Enter Start Date] & " 12:00 AM") AND Cdate([Enter End Date] & " 11:59:59 PM")

I tested it and it works. In access, another shortcut is to just enter MM/DD. Access will automatically use the current year in the date. But if you need to query previous years, entering MM/DD/YYYY is required

 

[Anubis]

Originally posted by: KLin

Originally posted by: Anubis

Originally posted by: KLin

Originally posted by: Anubis
for whatever reason i cant get this to work, our DB stores alot of run and batch data for samples such,

i have a query that pulls data based on Lot # that works fine, however sometimes lots get scanned in at different times or are split up so its easier to pull by date, however i cant get this to work

our date is stored the general way DD/MM/YYYY 12H time format so say today would be 10/15/2007 9:15:00 AM

if i set the query to pull by specified date i get nothing no matter what date i input, so im left opening and copying out of the DB manually which sucks

i have the stupid access book but its not helping

When you want to look at a specific day, but the data is stored as a timestamp, you need to use a range criteria. So if you wanted to look at data for today, you would need to say in the criteria field of a query below:

BETWEEN #10/16/2007 12:00 AM# AND #10/16/2007 11:59:59 PM#

If it was just the date being stored you could just say #10/16/2007#. hope this helps.

that definitly does help, works actually, tho is alot to type for just wanting 1 day, i could reformat the DB to shart time i guess, however someone prob would get pissed

Try this in the criteria field:

BETWEEN Cdate([Enter Start Date] & " 12:00 AM") AND Cdate([Enter End Date] & " 11:59:59 PM")

I tested it and it works. In access, another shortcut is to just enter MM/DD. Access will automatically use the current year in the date. But if you need to query previous years, entering MM/DD/YYYY is required

WOW thanks that works perfectly