Prev: Query: how to count up data from different fields
Next: New Record
From: tina on 10 May 2010 10:51
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
From: Douglas J. Steele on 10 May 2010 11:03
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" <tina(a)discussions.microsoft.com> wrote in message
news:9C66AE9A-B1C9-4B7A-9185-C37780AAD5FA(a)microsoft.com...
> 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


From: ghetto_banjo on 10 May 2010 11:07
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])


From: Şehla on 9 May 2010 12:41

"tina" <tina(a)discussions.microsoft.com>, iletisinde şunu yazdı,
news:9C66AE9A-B1C9-4B7A-9185-C37780AAD5FA(a)microsoft.com...
> 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

 | 
Pages: 1
Prev: Query: how to count up data from different fields
Next: New Record