Need Between not UpTo (not including)

B

Bonnie

Hi there. Using A02 on XP. Not a programmer but have used
it long enough that this should not be a surprise to me
but holy, moly! Have a number of reports and forms using
queries as control source. Many have parameters such
as 'From what date to what date?', 'Start Date' and 'End
Date', etc. Most reports are quarter or annual periods. A
Supe decided to analyze more detail and ordered a YTD
report (I use criteria forms to fill in my parameter data)
and keyed into parameter inquiry the weekly period dates.
Jan-Mar record count is 72. Using parameter:

Between [Forms]![fCriteriaFromTo]![BeginDate] And [Forms]!
[fCriteriaFromTo]![EndDate]

If I ran each separate month, my totals were not matching
up. I discovered that in Feb, there were 4 contracts set
up on the 28th. They show on the Jan-Mar but not on the
Feb only data. The dates keyed in were 02/01/2005 and
02/28/2005. UGH! Is Between not the right expression to
use? What should I use?

Can someone point me in a direction to read up on this and
how to work with it? Thanks in advance for any and all
help and advice!
 
R

Rick B

I would try...
[Forms]![fCriteriaFromTo]![BeginDate] And
<[Forms]![fCriteriaFromTo]![EndDate]




Bonnie said:
Hi there. Using A02 on XP. Not a programmer but have used
it long enough that this should not be a surprise to me
but holy, moly! Have a number of reports and forms using
queries as control source. Many have parameters such
as 'From what date to what date?', 'Start Date' and 'End
Date', etc. Most reports are quarter or annual periods. A
Supe decided to analyze more detail and ordered a YTD
report (I use criteria forms to fill in my parameter data)
and keyed into parameter inquiry the weekly period dates.
Jan-Mar record count is 72. Using parameter:

Between [Forms]![fCriteriaFromTo]![BeginDate] And [Forms]!
[fCriteriaFromTo]![EndDate]

If I ran each separate month, my totals were not matching
up. I discovered that in Feb, there were 4 contracts set
up on the 28th. They show on the Jan-Mar but not on the
Feb only data. The dates keyed in were 02/01/2005 and
02/28/2005. UGH! Is Between not the right expression to
use? What should I use?

Can someone point me in a direction to read up on this and
how to work with it? Thanks in advance for any and all
help and advice!
 
A

Allen Browne

Several possible issues.

1. If the text boxes on your form are unbound, set their Format property to
Short Date. This will help Access interpret them correctly, and will prevent
users entering bad dates, such as 2/29/2005.

2. Declare the parameters in the query.
In query design view, choose Parameters on the Query menu.
Enter two rows into the narrow dialog, i.e.:
[Forms]![fCriteriaFromTo]![BeginDate] Date/Time
[Forms]![fCriteriaFromTo]![EndDate] Date/Time
This will help the query interpret the dates correctly.

3. If you are applying these criteria on a calculate date field (not a
date/time field in a table), wrap the calculation in CVDate() so Access
interprets it correctly. More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

4. Date fields with a time component.
If you set the Default Value of a field using Now(), Access stores the time
as well as the date. The value is then *after* the pure date. For example,
if the field contains:
2/28/2005 1:00:00am
that value after 2/28/2005, and so if your parameters request values up to
2/28/2005, this record will not be included. To avoid that problem, use this
criteria:
=[Forms]![fCriteriaFromTo]![BeginDate] And <
([Forms]![fCriteriaFromTo]![EndDate] + 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bonnie said:
Hi there. Using A02 on XP. Not a programmer but have used
it long enough that this should not be a surprise to me
but holy, moly! Have a number of reports and forms using
queries as control source. Many have parameters such
as 'From what date to what date?', 'Start Date' and 'End
Date', etc. Most reports are quarter or annual periods. A
Supe decided to analyze more detail and ordered a YTD
report (I use criteria forms to fill in my parameter data)
and keyed into parameter inquiry the weekly period dates.
Jan-Mar record count is 72. Using parameter:

Between [Forms]![fCriteriaFromTo]![BeginDate] And [Forms]!
[fCriteriaFromTo]![EndDate]

If I ran each separate month, my totals were not matching
up. I discovered that in Feb, there were 4 contracts set
up on the 28th. They show on the Jan-Mar but not on the
Feb only data. The dates keyed in were 02/01/2005 and
02/28/2005. UGH! Is Between not the right expression to
use? What should I use?

Can someone point me in a direction to read up on this and
how to work with it? Thanks in advance for any and all
help and advice!
 
B

Bonnie

Rick, thank you VERY much for the info. I will use your
suggestion below but will add 'or equal to' so when I ask
for 4/1 thru 4/7 that is what I'll get.

Thanks again!
-----Original Message-----
I would try...
[Forms]![fCriteriaFromTo]![BeginDate] And
<[Forms]![fCriteriaFromTo]![EndDate]




Hi there. Using A02 on XP. Not a programmer but have used
it long enough that this should not be a surprise to me
but holy, moly! Have a number of reports and forms using
queries as control source. Many have parameters such
as 'From what date to what date?', 'Start Date' and 'End
Date', etc. Most reports are quarter or annual periods. A
Supe decided to analyze more detail and ordered a YTD
report (I use criteria forms to fill in my parameter data)
and keyed into parameter inquiry the weekly period dates.
Jan-Mar record count is 72. Using parameter:

Between [Forms]![fCriteriaFromTo]![BeginDate] And [Forms]!
[fCriteriaFromTo]![EndDate]

If I ran each separate month, my totals were not matching
up. I discovered that in Feb, there were 4 contracts set
up on the 28th. They show on the Jan-Mar but not on the
Feb only data. The dates keyed in were 02/01/2005 and
02/28/2005. UGH! Is Between not the right expression to
use? What should I use?

Can someone point me in a direction to read up on this and
how to work with it? Thanks in advance for any and all
help and advice!


.
 
B

Bonnie

Allen, thanks VERY much for the info. Item 4 below is my
situation. Date with time. Used your suggestion. Works
great when using a form but a direct parameter gives me
the 'couldn't parse the expression, too complex, etc'.

Thanks! B
-----Original Message-----
Several possible issues.

1. If the text boxes on your form are unbound, set their Format property to
Short Date. This will help Access interpret them correctly, and will prevent
users entering bad dates, such as 2/29/2005.

2. Declare the parameters in the query.
In query design view, choose Parameters on the Query menu.
Enter two rows into the narrow dialog, i.e.:
[Forms]![fCriteriaFromTo]![BeginDate] Date/Time
[Forms]![fCriteriaFromTo]![EndDate] Date/Time
This will help the query interpret the dates correctly.

3. If you are applying these criteria on a calculate date field (not a
date/time field in a table), wrap the calculation in CVDate() so Access
interprets it correctly. More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

4. Date fields with a time component.
If you set the Default Value of a field using Now(), Access stores the time
as well as the date. The value is then *after* the pure date. For example,
if the field contains:
2/28/2005 1:00:00am
that value after 2/28/2005, and so if your parameters request values up to
2/28/2005, this record will not be included. To avoid that problem, use this
criteria:
=[Forms]![fCriteriaFromTo]![BeginDate] And <
([Forms]![fCriteriaFromTo]![EndDate] + 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi there. Using A02 on XP. Not a programmer but have used
it long enough that this should not be a surprise to me
but holy, moly! Have a number of reports and forms using
queries as control source. Many have parameters such
as 'From what date to what date?', 'Start Date' and 'End
Date', etc. Most reports are quarter or annual periods. A
Supe decided to analyze more detail and ordered a YTD
report (I use criteria forms to fill in my parameter data)
and keyed into parameter inquiry the weekly period dates.
Jan-Mar record count is 72. Using parameter:

Between [Forms]![fCriteriaFromTo]![BeginDate] And [Forms]!
[fCriteriaFromTo]![EndDate]

If I ran each separate month, my totals were not matching
up. I discovered that in Feb, there were 4 contracts set
up on the 28th. They show on the Jan-Mar but not on the
Feb only data. The dates keyed in were 02/01/2005 and
02/28/2005. UGH! Is Between not the right expression to
use? What should I use?

Can someone point me in a direction to read up on this and
how to work with it? Thanks in advance for any and all
help and advice!


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top