Query on a calculated date field

[Norbert Lieckfeldt]

I am [still!] setting up a database for a friend who's an Optician,
using MS Access 2002. All seems to be working well, but I have hit a
snag.

There's a calculated field both in a form and a query which calculates
the date for the next appointment from a date field and the number of
months to the next appointment. That works fine.

For reminder letters, I need to have a query which allows me to select
a subsection of dates between dd/mm/yyyy and dd/mm/yyyy. However, when
I try to do this calculation using a popup form, it fails. I suspect
it's because I try to query on a field which is already calculated.
The formula I use is

Between [Forms]![SubformDates]![From:] And
[Forms]![SubformDates]![To:]

When I try to open the query, it asks me for these dates, but then
displays a subsection which bears no resemblance to the time span I
wanted. I would be grateful for any help!

Norbert

[Norbert Lieckfeldt]

"Norbert Lieckfeldt" <lc*****@yahoo.co.uk> wrote in message
news:fc**************************@posting.google.c om...

I am [still!] setting up a database for a friend who's an Optician,
using MS Access 2002. All seems to be working well, but I have hit a
snag.

There's a calculated field both in a form and a query which calculates
the date for the next appointment from a date field and the number of
months to the next appointment. That works fine.

For reminder letters, I need to have a query which allows me to select
a subsection of dates between dd/mm/yyyy and dd/mm/yyyy. However, when
I try to do this calculation using a popup form, it fails. I suspect
it's because I try to query on a field which is already calculated.
The formula I use is

Between [Forms]![SubformDates]![From:] And
[Forms]![SubformDates]![To:]

When I try to open the query, it asks me for these dates, but then
displays a subsection which bears no resemblance to the time span I
wanted. I would be grateful for any help!

What I have found is that the query returns different data sets. If I look
for between 1/1/2004 and 31/1/2004 I get about 1,200 data entries. If I ask
for data between 15/11/2003 and 15/12/2003 I get about 19, all dated 15
November, but in different years.

Am getting frustrated and desperate!

Norbert

[Douglas J. Steele]

Regardless of what your short date format has been set to through Regional
Settings, Access won't recognize dd/mm/yyyy dates in queries. Use the Format
function to reformat them as mm/dd/yyyy or yyyy-mm-dd.

For more information, check Allen Browne's "International Dates in Access"
at
http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have at
http://members.rogers.com/douglas.j....artAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Norbert Lieckfeldt" <lc*****@yahoo.co.uk> wrote in message
news:fc**************************@posting.google.c om...

I am [still!] setting up a database for a friend who's an Optician,
using MS Access 2002. All seems to be working well, but I have hit a
snag.

There's a calculated field both in a form and a query which calculates
the date for the next appointment from a date field and the number of
months to the next appointment. That works fine.

For reminder letters, I need to have a query which allows me to select
a subsection of dates between dd/mm/yyyy and dd/mm/yyyy. However, when
I try to do this calculation using a popup form, it fails. I suspect
it's because I try to query on a field which is already calculated.
The formula I use is

Between [Forms]![SubformDates]![From:] And
[Forms]![SubformDates]![To:]

When I try to open the query, it asks me for these dates, but then
displays a subsection which bears no resemblance to the time span I
wanted. I would be grateful for any help!

Norbert