469,926 Members | 2,490 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,926 developers. It's quick & easy.

Query on a calculated date field

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
Nov 13 '05 #1
2 2103
"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
Nov 13 '05 #2
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

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Steve | last post: by
7 posts views Thread by Arild Larsen | last post: by
5 posts views Thread by Javier | last post: by
2 posts views Thread by Julie Wardlow | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.