Wrong data returned on "Between" "And" parameter in simple query

G

Guest

In a simple query on a data table (design view) a Between[Enter Start Date:]
And [Enter End Date:] is placed on the criteria line of the field
"first_attendance" which is formatted in medium date form. When the query is
run with for example Start Date entered as 01/01/01 and End date as 30/12/02
it returns data with "first_attendance" dates in the years 2000, 2002, 2003,
2005 as well as the required year of 2001. What am I doing wrong?
 
A

Allen Browne

In query design view, choose Parameters from the Query menu, and declare two
parameters of type Date/Time:
[Enter Start Date:] Date/Time
[Enter End Date:] Date/Time
This ensures Access knows what type these parameters are, does not accept
invalid entries, and helps it interpret them correctly.

Since you use d/m/y formatting, this article explains 3 cases where Access
tends to misunderstand our dates:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html
 
G

Guest

Thanks Allen for your help -

I have declared the two parameters and checked out the article you have
recommended. I have checked the format at table level and field level (on
the input subform) and all are set at medium date with input in dd/mm/yy
format. On running my query I still get erroneous data return regardless of
the format of data entry (ie dd/mm/yy or dd-mmm-yy etc).

Here is the SQL for the whole query - perhaps you might be able to see
something I'm missing.

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT [Patient Treatments Table].mb_service_number, [Patient Treatments
Table].mb_surname, [Patient Treatments Table].mb_given_names, [Patient
Treatments Table].body_part, [Patient Treatments Table].diagnosis, [Patient
Treatments Table].history, [Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1, [Patient Treatments
Table].treatment_provided_2, [Patient Treatments Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
FROM [Patient Treatments Table]
GROUP BY [Patient Treatments Table].mb_service_number, [Patient Treatments
Table].mb_surname, [Patient Treatments Table].mb_given_names, [Patient
Treatments Table].body_part, [Patient Treatments Table].diagnosis, [Patient
Treatments Table].history, [Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1, [Patient Treatments
Table].treatment_provided_2, [Patient Treatments Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
HAVING ((([Patient Treatments Table].first_attendance) Between [Enter Start
Date:] And [Enter End Date:]) AND (([Patient Treatments
Table].treatment_provided_1)="boots" Or ([Patient Treatments
Table].treatment_provided_1)="lace-up boots")) OR ((([Patient Treatments
Table].treatment_provided_2)="boots")) OR ((([Patient Treatments
Table].treatment_provided_2)="lace-up boots")) OR ((([Patient Treatments
Table].treatment_provided_3)="boots")) OR ((([Patient Treatments
Table].treatment_provided_3)="lace-up boots")) OR ((([Patient Treatments
Table].treatment_provided_4)="boots")) OR ((([Patient Treatments
Table].treatment_provided_4)="lace-up boots"))
ORDER BY [Patient Treatments Table].mb_service_number, [Patient Treatments
Table].body_part, [Patient Treatments Table].diagnosis;

The query is attempting to determine how often a specific item of equipment
was provided during treatment in a specific time period. Treatments are
recorded in 4 separate fields to allow for multiple treatment types to ensure
standardisation of data input.

Hope you can help.

Allen Browne said:
In query design view, choose Parameters from the Query menu, and declare two
parameters of type Date/Time:
[Enter Start Date:] Date/Time
[Enter End Date:] Date/Time
This ensures Access knows what type these parameters are, does not accept
invalid entries, and helps it interpret them correctly.

Since you use d/m/y formatting, this article explains 3 cases where Access
tends to misunderstand our dates:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html

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

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

Joe said:
In a simple query on a data table (design view) a Between[Enter Start
Date:]
And [Enter End Date:] is placed on the criteria line of the field
"first_attendance" which is formatted in medium date form. When the query
is
run with for example Start Date entered as 01/01/01 and End date as
30/12/02
it returns data with "first_attendance" dates in the years 2000, 2002,
2003,
2005 as well as the required year of 2001. What am I doing wrong?
Click to expand...
Click to expand...
 
A

Allen Browne

Joe, I think your bracketing is not give you what you intended.

You have asked for the records where first_attendance is between the dates
and treatment_provided_1 is the boots, plus all the records where
treatment_provided_2, 3, or 4 is the boots regardless of the date.

Is this what you meant?

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].mb_surname,
[Patient Treatments Table].mb_given_names,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis,
[Patient Treatments Table].history,
[Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1,
[Patient Treatments Table].treatment_provided_2,
[Patient Treatments Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
FROM [Patient Treatments Table]
GROUP BY [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].mb_surname,
[Patient Treatments Table].mb_given_names,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis,
[Patient Treatments Table].history,
[Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1,
[Patient Treatments Table].treatment_provided_2,
[Patient Treatments Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
HAVING ([Patient Treatments Table].first_attendance
Between [Enter Start Date:] And [Enter End Date:])
AND (([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots")))
ORDER BY [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis;

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

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

Joe said:
Thanks Allen for your help -

I have declared the two parameters and checked out the article you have
recommended. I have checked the format at table level and field level (on
the input subform) and all are set at medium date with input in dd/mm/yy
format. On running my query I still get erroneous data return regardless
of
the format of data entry (ie dd/mm/yy or dd-mmm-yy etc).

Here is the SQL for the whole query - perhaps you might be able to see
something I'm missing.

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT [Patient Treatments Table].mb_service_number, [Patient Treatments
Table].mb_surname, [Patient Treatments Table].mb_given_names, [Patient
Treatments Table].body_part, [Patient Treatments Table].diagnosis,
[Patient
Treatments Table].history, [Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1, [Patient Treatments
Table].treatment_provided_2, [Patient Treatments
Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
FROM [Patient Treatments Table]
GROUP BY [Patient Treatments Table].mb_service_number, [Patient Treatments
Table].mb_surname, [Patient Treatments Table].mb_given_names, [Patient
Treatments Table].body_part, [Patient Treatments Table].diagnosis,
[Patient
Treatments Table].history, [Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1, [Patient Treatments
Table].treatment_provided_2, [Patient Treatments
Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
HAVING ((([Patient Treatments Table].first_attendance) Between [Enter
Start
Date:] And [Enter End Date:]) AND (([Patient Treatments
Table].treatment_provided_1)="boots" Or ([Patient Treatments
Table].treatment_provided_1)="lace-up boots")) OR ((([Patient Treatments
Table].treatment_provided_2)="boots")) OR ((([Patient Treatments
Table].treatment_provided_2)="lace-up boots")) OR ((([Patient Treatments
Table].treatment_provided_3)="boots")) OR ((([Patient Treatments
Table].treatment_provided_3)="lace-up boots")) OR ((([Patient Treatments
Table].treatment_provided_4)="boots")) OR ((([Patient Treatments
Table].treatment_provided_4)="lace-up boots"))
ORDER BY [Patient Treatments Table].mb_service_number, [Patient Treatments
Table].body_part, [Patient Treatments Table].diagnosis;

The query is attempting to determine how often a specific item of
equipment
was provided during treatment in a specific time period. Treatments are
recorded in 4 separate fields to allow for multiple treatment types to
ensure
standardisation of data input.

Hope you can help.

Allen Browne said:
In query design view, choose Parameters from the Query menu, and declare
two
parameters of type Date/Time:
[Enter Start Date:] Date/Time
[Enter End Date:] Date/Time
This ensures Access knows what type these parameters are, does not accept
invalid entries, and helps it interpret them correctly.

Since you use d/m/y formatting, this article explains 3 cases where
Access
tends to misunderstand our dates:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html

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

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

Joe said:
In a simple query on a data table (design view) a Between[Enter Start
Date:]
And [Enter End Date:] is placed on the criteria line of the field
"first_attendance" which is formatted in medium date form. When the
query
is
run with for example Start Date entered as 01/01/01 and End date as
30/12/02
it returns data with "first_attendance" dates in the years 2000, 2002,
2003,
2005 as well as the required year of 2001. What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks for your prompt reply Allen.
No I had intended that 'boots' from treatment_provided_ 2,_3 or _4 be from
the same date range as for treatment_provided_1. Please could you indicate
how I might fix this? Am I to understand that the 'OR' statements should
really be 'AND' statments?

Allen Browne said:
Joe, I think your bracketing is not give you what you intended.

You have asked for the records where first_attendance is between the dates
and treatment_provided_1 is the boots, plus all the records where
treatment_provided_2, 3, or 4 is the boots regardless of the date.

Is this what you meant?

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].mb_surname,
[Patient Treatments Table].mb_given_names,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis,
[Patient Treatments Table].history,
[Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1,
[Patient Treatments Table].treatment_provided_2,
[Patient Treatments Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
FROM [Patient Treatments Table]
GROUP BY [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].mb_surname,
[Patient Treatments Table].mb_given_names,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis,
[Patient Treatments Table].history,
[Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1,
[Patient Treatments Table].treatment_provided_2,
[Patient Treatments Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
HAVING ([Patient Treatments Table].first_attendance
Between [Enter Start Date:] And [Enter End Date:])
AND (([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots")))
ORDER BY [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis;

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

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

Joe said:
Thanks Allen for your help -

I have declared the two parameters and checked out the article you have
recommended. I have checked the format at table level and field level (on
the input subform) and all are set at medium date with input in dd/mm/yy
format. On running my query I still get erroneous data return regardless
of
the format of data entry (ie dd/mm/yy or dd-mmm-yy etc).

Here is the SQL for the whole query - perhaps you might be able to see
something I'm missing.

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT [Patient Treatments Table].mb_service_number, [Patient Treatments
Table].mb_surname, [Patient Treatments Table].mb_given_names, [Patient
Treatments Table].body_part, [Patient Treatments Table].diagnosis,
[Patient
Treatments Table].history, [Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1, [Patient Treatments
Table].treatment_provided_2, [Patient Treatments
Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
FROM [Patient Treatments Table]
GROUP BY [Patient Treatments Table].mb_service_number, [Patient Treatments
Table].mb_surname, [Patient Treatments Table].mb_given_names, [Patient
Treatments Table].body_part, [Patient Treatments Table].diagnosis,
[Patient
Treatments Table].history, [Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1, [Patient Treatments
Table].treatment_provided_2, [Patient Treatments
Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
HAVING ((([Patient Treatments Table].first_attendance) Between [Enter
Start
Date:] And [Enter End Date:]) AND (([Patient Treatments
Table].treatment_provided_1)="boots" Or ([Patient Treatments
Table].treatment_provided_1)="lace-up boots")) OR ((([Patient Treatments
Table].treatment_provided_2)="boots")) OR ((([Patient Treatments
Table].treatment_provided_2)="lace-up boots")) OR ((([Patient Treatments
Table].treatment_provided_3)="boots")) OR ((([Patient Treatments
Table].treatment_provided_3)="lace-up boots")) OR ((([Patient Treatments
Table].treatment_provided_4)="boots")) OR ((([Patient Treatments
Table].treatment_provided_4)="lace-up boots"))
ORDER BY [Patient Treatments Table].mb_service_number, [Patient Treatments
Table].body_part, [Patient Treatments Table].diagnosis;

The query is attempting to determine how often a specific item of
equipment
was provided during treatment in a specific time period. Treatments are
recorded in 4 separate fields to allow for multiple treatment types to
ensure
standardisation of data input.

Hope you can help.

Allen Browne said:
In query design view, choose Parameters from the Query menu, and declare
two
parameters of type Date/Time:
[Enter Start Date:] Date/Time
[Enter End Date:] Date/Time
This ensures Access knows what type these parameters are, does not accept
invalid entries, and helps it interpret them correctly.

Since you use d/m/y formatting, this article explains 3 cases where
Access
tends to misunderstand our dates:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html

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

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

In a simple query on a data table (design view) a Between[Enter Start
Date:]
And [Enter End Date:] is placed on the criteria line of the field
"first_attendance" which is formatted in medium date form. When the
query
is
run with for example Start Date entered as 01/01/01 and End date as
30/12/02
it returns data with "first_attendance" dates in the years 2000, 2002,
2003,
2005 as well as the required year of 2001. What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Try the statement from my last reply, and see if it does what you want.

If you do mix ANDs and ORs, be aware that you get different results from:
(a AND b) OR c
a AND (b OR c)
where a, b, an c represent phrases such as "City = ""New York""".

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

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

Joe said:
Thanks for your prompt reply Allen.
No I had intended that 'boots' from treatment_provided_ 2,_3 or _4 be from
the same date range as for treatment_provided_1. Please could you
indicate
how I might fix this? Am I to understand that the 'OR' statements should
really be 'AND' statments?

Allen Browne said:
Joe, I think your bracketing is not give you what you intended.

You have asked for the records where first_attendance is between the
dates
and treatment_provided_1 is the boots, plus all the records where
treatment_provided_2, 3, or 4 is the boots regardless of the date.

Is this what you meant?

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].mb_surname,
[Patient Treatments Table].mb_given_names,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis,
[Patient Treatments Table].history,
[Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1,
[Patient Treatments Table].treatment_provided_2,
[Patient Treatments Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
FROM [Patient Treatments Table]
GROUP BY [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].mb_surname,
[Patient Treatments Table].mb_given_names,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis,
[Patient Treatments Table].history,
[Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1,
[Patient Treatments Table].treatment_provided_2,
[Patient Treatments Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
HAVING ([Patient Treatments Table].first_attendance
Between [Enter Start Date:] And [Enter End Date:])
AND (([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots")))
ORDER BY [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis;

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

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

Joe said:
Thanks Allen for your help -

I have declared the two parameters and checked out the article you have
recommended. I have checked the format at table level and field level
(on
the input subform) and all are set at medium date with input in
dd/mm/yy
format. On running my query I still get erroneous data return
regardless
of
the format of data entry (ie dd/mm/yy or dd-mmm-yy etc).

Here is the SQL for the whole query - perhaps you might be able to see
something I'm missing.

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT [Patient Treatments Table].mb_service_number, [Patient
Treatments
Table].mb_surname, [Patient Treatments Table].mb_given_names, [Patient
Treatments Table].body_part, [Patient Treatments Table].diagnosis,
[Patient
Treatments Table].history, [Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1, [Patient Treatments
Table].treatment_provided_2, [Patient Treatments
Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
FROM [Patient Treatments Table]
GROUP BY [Patient Treatments Table].mb_service_number, [Patient
Treatments
Table].mb_surname, [Patient Treatments Table].mb_given_names, [Patient
Treatments Table].body_part, [Patient Treatments Table].diagnosis,
[Patient
Treatments Table].history, [Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1, [Patient Treatments
Table].treatment_provided_2, [Patient Treatments
Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
HAVING ((([Patient Treatments Table].first_attendance) Between [Enter
Start
Date:] And [Enter End Date:]) AND (([Patient Treatments
Table].treatment_provided_1)="boots" Or ([Patient Treatments
Table].treatment_provided_1)="lace-up boots")) OR ((([Patient
Treatments
Table].treatment_provided_2)="boots")) OR ((([Patient Treatments
Table].treatment_provided_2)="lace-up boots")) OR ((([Patient
Treatments
Table].treatment_provided_3)="boots")) OR ((([Patient Treatments
Table].treatment_provided_3)="lace-up boots")) OR ((([Patient
Treatments
Table].treatment_provided_4)="boots")) OR ((([Patient Treatments
Table].treatment_provided_4)="lace-up boots"))
ORDER BY [Patient Treatments Table].mb_service_number, [Patient
Treatments
Table].body_part, [Patient Treatments Table].diagnosis;

The query is attempting to determine how often a specific item of
equipment
was provided during treatment in a specific time period. Treatments are
recorded in 4 separate fields to allow for multiple treatment types to
ensure
standardisation of data input.

Hope you can help.

:

In query design view, choose Parameters from the Query menu, and
declare
two
parameters of type Date/Time:
[Enter Start Date:] Date/Time
[Enter End Date:] Date/Time
This ensures Access knows what type these parameters are, does not
accept
invalid entries, and helps it interpret them correctly.

Since you use d/m/y formatting, this article explains 3 cases where
Access
tends to misunderstand our dates:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html

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

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

In a simple query on a data table (design view) a Between[Enter
Start
Date:]
And [Enter End Date:] is placed on the criteria line of the field
"first_attendance" which is formatted in medium date form. When the
query
is
run with for example Start Date entered as 01/01/01 and End date as
30/12/02
it returns data with "first_attendance" dates in the years 2000,
2002,
2003,
2005 as well as the required year of 2001. What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Allen.

I'm away from my workplace for a couple of days and will implement your
suggestion on my return. Thanks for the guidance on the "And" and "OR's".

Allen Browne said:
Try the statement from my last reply, and see if it does what you want.

If you do mix ANDs and ORs, be aware that you get different results from:
(a AND b) OR c
a AND (b OR c)
where a, b, an c represent phrases such as "City = ""New York""".

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

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

Joe said:
Thanks for your prompt reply Allen.
No I had intended that 'boots' from treatment_provided_ 2,_3 or _4 be from
the same date range as for treatment_provided_1. Please could you
indicate
how I might fix this? Am I to understand that the 'OR' statements should
really be 'AND' statments?

Allen Browne said:
Joe, I think your bracketing is not give you what you intended.

You have asked for the records where first_attendance is between the
dates
and treatment_provided_1 is the boots, plus all the records where
treatment_provided_2, 3, or 4 is the boots regardless of the date.

Is this what you meant?

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].mb_surname,
[Patient Treatments Table].mb_given_names,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis,
[Patient Treatments Table].history,
[Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1,
[Patient Treatments Table].treatment_provided_2,
[Patient Treatments Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
FROM [Patient Treatments Table]
GROUP BY [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].mb_surname,
[Patient Treatments Table].mb_given_names,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis,
[Patient Treatments Table].history,
[Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1,
[Patient Treatments Table].treatment_provided_2,
[Patient Treatments Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
HAVING ([Patient Treatments Table].first_attendance
Between [Enter Start Date:] And [Enter End Date:])
AND (([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots")))
ORDER BY [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis;

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

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

Thanks Allen for your help -

I have declared the two parameters and checked out the article you have
recommended. I have checked the format at table level and field level
(on
the input subform) and all are set at medium date with input in
dd/mm/yy
format. On running my query I still get erroneous data return
regardless
of
the format of data entry (ie dd/mm/yy or dd-mmm-yy etc).

Here is the SQL for the whole query - perhaps you might be able to see
something I'm missing.

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT [Patient Treatments Table].mb_service_number, [Patient
Treatments
Table].mb_surname, [Patient Treatments Table].mb_given_names, [Patient
Treatments Table].body_part, [Patient Treatments Table].diagnosis,
[Patient
Treatments Table].history, [Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1, [Patient Treatments
Table].treatment_provided_2, [Patient Treatments
Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
FROM [Patient Treatments Table]
GROUP BY [Patient Treatments Table].mb_service_number, [Patient
Treatments
Table].mb_surname, [Patient Treatments Table].mb_given_names, [Patient
Treatments Table].body_part, [Patient Treatments Table].diagnosis,
[Patient
Treatments Table].history, [Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1, [Patient Treatments
Table].treatment_provided_2, [Patient Treatments
Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
HAVING ((([Patient Treatments Table].first_attendance) Between [Enter
Start
Date:] And [Enter End Date:]) AND (([Patient Treatments
Table].treatment_provided_1)="boots" Or ([Patient Treatments
Table].treatment_provided_1)="lace-up boots")) OR ((([Patient
Treatments
Table].treatment_provided_2)="boots")) OR ((([Patient Treatments
Table].treatment_provided_2)="lace-up boots")) OR ((([Patient
Treatments
Table].treatment_provided_3)="boots")) OR ((([Patient Treatments
Table].treatment_provided_3)="lace-up boots")) OR ((([Patient
Treatments
Table].treatment_provided_4)="boots")) OR ((([Patient Treatments
Table].treatment_provided_4)="lace-up boots"))
ORDER BY [Patient Treatments Table].mb_service_number, [Patient
Treatments
Table].body_part, [Patient Treatments Table].diagnosis;

The query is attempting to determine how often a specific item of
equipment
was provided during treatment in a specific time period. Treatments are
recorded in 4 separate fields to allow for multiple treatment types to
ensure
standardisation of data input.

Hope you can help.

:

In query design view, choose Parameters from the Query menu, and
declare
two
parameters of type Date/Time:
[Enter Start Date:] Date/Time
[Enter End Date:] Date/Time
This ensures Access knows what type these parameters are, does not
accept
invalid entries, and helps it interpret them correctly.

Since you use d/m/y formatting, this article explains 3 cases where
Access
tends to misunderstand our dates:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html

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

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

In a simple query on a data table (design view) a Between[Enter
Start
Date:]
And [Enter End Date:] is placed on the criteria line of the field
"first_attendance" which is formatted in medium date form. When the
query
is
run with for example Start Date entered as 01/01/01 and End date as
30/12/02
it returns data with "first_attendance" dates in the years 2000,
2002,
2003,
2005 as well as the required year of 2001. What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks very much for the help Allen, your suggestion worked a treat. I did,
however, need to change
"OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1"

to

"OR ([Patient Treatments Table].treatment_provided_2
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_3
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_4

for all records to be returned for all fields.

Thanks again for all your help.



Allen Browne said:
Try the statement from my last reply, and see if it does what you want.

If you do mix ANDs and ORs, be aware that you get different results from:
(a AND b) OR c
a AND (b OR c)
where a, b, an c represent phrases such as "City = ""New York""".

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

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

Joe said:
Thanks for your prompt reply Allen.
No I had intended that 'boots' from treatment_provided_ 2,_3 or _4 be from
the same date range as for treatment_provided_1. Please could you
indicate
how I might fix this? Am I to understand that the 'OR' statements should
really be 'AND' statments?

Allen Browne said:
Joe, I think your bracketing is not give you what you intended.

You have asked for the records where first_attendance is between the
dates
and treatment_provided_1 is the boots, plus all the records where
treatment_provided_2, 3, or 4 is the boots regardless of the date.

Is this what you meant?

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].mb_surname,
[Patient Treatments Table].mb_given_names,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis,
[Patient Treatments Table].history,
[Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1,
[Patient Treatments Table].treatment_provided_2,
[Patient Treatments Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
FROM [Patient Treatments Table]
GROUP BY [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].mb_surname,
[Patient Treatments Table].mb_given_names,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis,
[Patient Treatments Table].history,
[Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1,
[Patient Treatments Table].treatment_provided_2,
[Patient Treatments Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
HAVING ([Patient Treatments Table].first_attendance
Between [Enter Start Date:] And [Enter End Date:])
AND (([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots"))
OR ([Patient Treatments Table].treatment_provided_1
IN ("boots", "lace-up boots")))
ORDER BY [Patient Treatments Table].mb_service_number,
[Patient Treatments Table].body_part,
[Patient Treatments Table].diagnosis;

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

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

Thanks Allen for your help -

I have declared the two parameters and checked out the article you have
recommended. I have checked the format at table level and field level
(on
the input subform) and all are set at medium date with input in
dd/mm/yy
format. On running my query I still get erroneous data return
regardless
of
the format of data entry (ie dd/mm/yy or dd-mmm-yy etc).

Here is the SQL for the whole query - perhaps you might be able to see
something I'm missing.

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT [Patient Treatments Table].mb_service_number, [Patient
Treatments
Table].mb_surname, [Patient Treatments Table].mb_given_names, [Patient
Treatments Table].body_part, [Patient Treatments Table].diagnosis,
[Patient
Treatments Table].history, [Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1, [Patient Treatments
Table].treatment_provided_2, [Patient Treatments
Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
FROM [Patient Treatments Table]
GROUP BY [Patient Treatments Table].mb_service_number, [Patient
Treatments
Table].mb_surname, [Patient Treatments Table].mb_given_names, [Patient
Treatments Table].body_part, [Patient Treatments Table].diagnosis,
[Patient
Treatments Table].history, [Patient Treatments Table].first_attendance,
[Patient Treatments Table].treatment_provided_1, [Patient Treatments
Table].treatment_provided_2, [Patient Treatments
Table].treatment_provided_3,
[Patient Treatments Table].treatment_provided_4
HAVING ((([Patient Treatments Table].first_attendance) Between [Enter
Start
Date:] And [Enter End Date:]) AND (([Patient Treatments
Table].treatment_provided_1)="boots" Or ([Patient Treatments
Table].treatment_provided_1)="lace-up boots")) OR ((([Patient
Treatments
Table].treatment_provided_2)="boots")) OR ((([Patient Treatments
Table].treatment_provided_2)="lace-up boots")) OR ((([Patient
Treatments
Table].treatment_provided_3)="boots")) OR ((([Patient Treatments
Table].treatment_provided_3)="lace-up boots")) OR ((([Patient
Treatments
Table].treatment_provided_4)="boots")) OR ((([Patient Treatments
Table].treatment_provided_4)="lace-up boots"))
ORDER BY [Patient Treatments Table].mb_service_number, [Patient
Treatments
Table].body_part, [Patient Treatments Table].diagnosis;

The query is attempting to determine how often a specific item of
equipment
was provided during treatment in a specific time period. Treatments are
recorded in 4 separate fields to allow for multiple treatment types to
ensure
standardisation of data input.

Hope you can help.

:

In query design view, choose Parameters from the Query menu, and
declare
two
parameters of type Date/Time:
[Enter Start Date:] Date/Time
[Enter End Date:] Date/Time
This ensures Access knows what type these parameters are, does not
accept
invalid entries, and helps it interpret them correctly.

Since you use d/m/y formatting, this article explains 3 cases where
Access
tends to misunderstand our dates:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html

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

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

In a simple query on a data table (design view) a Between[Enter
Start
Date:]
And [Enter End Date:] is placed on the criteria line of the field
"first_attendance" which is formatted in medium date form. When the
query
is
run with for example Start Date entered as 01/01/01 and End date as
30/12/02
it returns data with "first_attendance" dates in the years 2000,
2002,
2003,
2005 as well as the required year of 2001. What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Parameter Query using date ranges 2
Parameter Query with DSum 1
Converted from LINUX time ok, but having issues with the between date. 4
Between [start date] and [end date] 7
Parameter query issue in Access 2003 6
Date Range in Query using DateAdd 4
Create List of Dates that fall between 2 Dates 2
Limiting multiple results in query 7

Top