Use of "Between/And" Statement in Query

[Guest]

I am using Access 2003

I am trying to develop a preventative maintenance database for all of our
plant machines. In my database, I have a query for a form with a calculated
date (PM Due Date) field. It takes the previous "PM date" and adds the
"frequency" (# of Days) to it and comes up with the next "PM Due Date".
When I add the following to the query criteria for this field - "Between
[Enter Beg Date] and [Enter End Date]" - I get no response. But when I add -
">#7/1/06# and
<#8/1/06#" I get all of the correct records. Does anyone have any idea why
my "between" statement is not working???? Does the "Between/And" statement
not work in a calculated field.

The maintenance man (who does not know Access) needs to enter the dates of
when the next PM's are due so that he can print them and plan his month.

I looked on MS for a template for this type of database but couldn't find
one.
Does anyone know where I could get a template for a Preventative Maintenance
Program?

I would appreciate any help you can give me - Aurora

 

[SusanV]

Hi Aurora,

In your working SQL you have the date enclosed in hash marks - but your
non-working SQL is missing these marks try adding them before and after your
prompts:

"Between #[Enter Beg Date]# and #[Enter End Date]#"

 

[Guest]

Susan: That did not work also. I have never had a problem using the
Between/And statement. Do you think it is because of the field being a
calculated field?

Aurora

Hi Aurora,

In your working SQL you have the date enclosed in hash marks - but your
non-working SQL is missing these marks try adding them before and after your
prompts:

"Between #[Enter Beg Date]# and #[Enter End Date]#"
--
hth,
SusanV

I am using Access 2003

I am trying to develop a preventative maintenance database for all of our
plant machines. In my database, I have a query for a form with a
calculated
date (PM Due Date) field. It takes the previous "PM date" and adds the
"frequency" (# of Days) to it and comes up with the next "PM Due Date".
When I add the following to the query criteria for this field - "Between
[Enter Beg Date] and [Enter End Date]" - I get no response. But when I
add -
">#7/1/06# and
<#8/1/06#" I get all of the correct records. Does anyone have any idea why
my "between" statement is not working???? Does the "Between/And"
statement
not work in a calculated field.

The maintenance man (who does not know Access) needs to enter the dates of
when the next PM's are due so that he can print them and plan his month.

I looked on MS for a template for this type of database but couldn't find
one.
Does anyone know where I could get a template for a Preventative
Maintenance
Program?

I would appreciate any help you can give me - Aurora

 

[fredg]

Susan: That did not work also. I have never had a problem using the
Between/And statement. Do you think it is because of the field being a
calculated field?

Aurora

Hi Aurora,

In your working SQL you have the date enclosed in hash marks - but your
non-working SQL is missing these marks try adding them before and after your
prompts:

"Between #[Enter Beg Date]# and #[Enter End Date]#"
--
hth,
SusanV

I am using Access 2003

I am trying to develop a preventative maintenance database for all of our
plant machines. In my database, I have a query for a form with a
calculated
date (PM Due Date) field. It takes the previous "PM date" and adds the
"frequency" (# of Days) to it and comes up with the next "PM Due Date".
When I add the following to the query criteria for this field - "Between
[Enter Beg Date] and [Enter End Date]" - I get no response. But when I
add -
">#7/1/06# and
<#8/1/06#" I get all of the correct records. Does anyone have any idea why
my "between" statement is not working???? Does the "Between/And"
statement
not work in a calculated field.

The maintenance man (who does not know Access) needs to enter the dates of
when the next PM's are due so that he can print them and plan his month.

I looked on MS for a template for this type of database but couldn't find
one.
Does anyone know where I could get a template for a Preventative
Maintenance
Program?

I would appreciate any help you can give me - Aurora

Copy and paste the entire query SQL into a message so we can see what
else is going on.

 

[Guest]

Hi Aurora,
I tested your problem and agree that using a BETWEEN...AND expression may
not work in a calculated field. Actually, the expression did work for me
when I used BETWEEN...AND, but my display showed all years in my queried
table, not just 2006 even though I entered dates for 2006. However, the "Due
Dates" did display as expected.

In my test, I used two expressions: (1) PMDueDate:=DateAdd("m",1,[PMDate])
and (2) PMDueDate2:= DateAdd("d",15,[PMDate]). Note the colon ) in the
expression. Your expression, using either months ("m") or days ("d") at your
discretion, goes on a blank query column. Your expression, "BETWEEN [Enter
beg date] AND [Enter End Date]" goes on, of course, the criteria line.

To make the outcome display as desired, I needed to add the expression, Like
"*2006" on the criteria line of the actual PMDate field in the query. (Note
use of the asterisk (*) wildcard). Since you are using a form for your PM
person to work from, filtering the query to the year 2006 may not work for
you. However, in the query itself, it corrected my problem, resulting in the
correct display of due dates within the date range I specified --- filtering
out prior years to 2006.

Using the expression ">=[Enter Beg Date] and <=[Enter End Date]" produces
the same results.

Perhaps the easiest solution might be to use your BETWEEN...AND expression
in the PMDate field in your query, instead of the calculated field. I am
assuming your PM person manually enters the updated PM date in such a field
when he/she performs the updated preventive maintainence on the machine.
Therefore, if the PM person has performed PM in the month of July (with the
machine's latest PM date recorded as such) and the schedule calls for PM
every 60 days, the query requiring him/her to enter a date range of 7-1-06 to
7-31-06, for example, for work already performed in that month, will display
(either as a datasheet or as a form depending on how you have it set up for
the person) preventive maintenance to be performed 60 days from the last PM
date of each machine in the specified date range.

I hope this information helps in coming up with a good workaround solution.