date query criteria prompt

"tina" , iletisinde şunu yazdı,
...
hi
I would like to add a prompt to a query allowing user to enter month and
year desired and show all records where matches criteria
the date is in format dd/mm/yy
so if users enters 04/10 at prompt they would get all data for april 2010
or do I need to have start date and stop date
thanks
tina

hi
I would like to add a prompt to a query allowing user to enter month and
year desired and show all records where matches criteria
the date is in format dd/mm/yy
so if users enters 04/10 at prompt they would get all data for april 2010
or do I need to have start date and stop date
thanks
tina

Realistically, you need a start date and stop date although in your case you
should be able to derive the stop date from the start date. If you're going
to have them input a single value representing mm/yy, you should be able to
use

BETWEEN DateSerial(CInt(Mid([NameOfPrompt], InStr([NameOfPrompt], "/") +
1)), CInt(Left([NameOfPrompt], InStr([NameOfPrompt], "/") - 1)), 1) AND
DateSerial(CInt(Mid([NameOfPrompt], InStr([NameOfPrompt], "/") + 1)),
CInt(Left([NameOfPrompt], InStr([NameOfPrompt], "/") - 1)) + 1, 0)

Just to clear up a misconception, though, the dates are NOT stored in
dd/mm/yy format. Dates in Access are stored as 8 byte floating point
numbers, where the integer portion represents the date as the number of days
relative to 30 Dec, 1899 and the decimal portion represents the time as a
fraction of a day. You might find it useful to read what Allen Browne has at
http://www.allenbrowne.com/ser-36.html or what I wrote in my September, 2003
"Access Answers" column in Pinnacle Publication's "Smart Access" (you can
read it for free at http://www.vb123.com/kb/200309_ds_aa.htm )

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"tina" wrote in message
...
hi
I would like to add a prompt to a query allowing user to enter month and
year desired and show all records where matches criteria
the date is in format dd/mm/yy
so if users enters 04/10 at prompt they would get all data for april 2010
or do I need to have start date and stop date
thanks
tina

You can modify the WHERE clause of the SQL statement to include
something like this:


WHERE Format([datefield], "mm/yy") = [Please Enter Month/Year]




Or similarly in the Query Design view you add a field like:

myeah: Format([datefield], "mm/yy")


With criteria: [Please Enter Month/Year])