Problems with setting record source by code

B

BruceM

I have a database for keeping track of various statistics related to
manufacturing. The departments are either Plating or Painting. qryDept
contains the calculations, and is used as the recordsource for a form on
which the user can filter by month and department. I am using VBA code to
build a recordsource string according to choices in list boxes. I have used
debug.print to view the following recordsource code. [JobMonth] is obtained
by formatting the JobDate, which is a standard date field.

I can select for one or more months with no selections for department, or
one or more departments with no selection for month, or one department and
one month. In any of those cases the WHERE includes either AND or OR. When
I attempt to combine the two, I run into problems. I can't sort out what I
need to change. If I know the syntax I can probably figure out how to
assemble the string, but the trouble is that I can't get the syntax to do
what I need.

SELECT * FROM qryDept WHERE [JobMonth] = "02" OR [JobMonth] = "03" AND
[ProcDept] = "Painting"
Intended to show Painting jobs only for February and March, but shows
Plating and Painting

SELECT * FROM qryDept WHERE [JobMonth] = "02" AND [ProcDept] = "Plating" OR
[ProcDept] = "Painting"
Intended to show Plating or Painting for February only, but shows all months

The asterisk is for development purposes. Actual fields will be substituted
later.
 
M

Marshall Barton

BruceM said:
I have a database for keeping track of various statistics related to
manufacturing. The departments are either Plating or Painting. qryDept
contains the calculations, and is used as the recordsource for a form on
which the user can filter by month and department. I am using VBA code to
build a recordsource string according to choices in list boxes. I have used
debug.print to view the following recordsource code. [JobMonth] is obtained
by formatting the JobDate, which is a standard date field.

I can select for one or more months with no selections for department, or
one or more departments with no selection for month, or one department and
one month. In any of those cases the WHERE includes either AND or OR. When
I attempt to combine the two, I run into problems. I can't sort out what I
need to change. If I know the syntax I can probably figure out how to
assemble the string, but the trouble is that I can't get the syntax to do
what I need.

SELECT * FROM qryDept WHERE [JobMonth] = "02" OR [JobMonth] = "03" AND
[ProcDept] = "Painting"
Intended to show Painting jobs only for February and March, but shows
Plating and Painting

SELECT * FROM qryDept WHERE [JobMonth] = "02" AND [ProcDept] = "Plating" OR
[ProcDept] = "Painting"
Intended to show Plating or Painting for February only, but shows all months
Click to expand...


You need to use parenthesis to keep the Or groups together.
Without the parenthesis, the And is done before the Or.

SELECT * FROM qryDept WHERE ( JobMonth = "02" )
AND ( ProcDept = "Plating" OR ProcDept = "Painting" )

A different syntax using the IN operator will be shorter and
make this a little clearer:

SELECT * FROM qryDept WHERE JobMonth IN("02", "03")
AND IN("Plating", "Painting")

Are you sure the JobMonth calculation is adequate?
Shouldn't it include the year along with the month?
 
B

BruceM

Thanks for the reply. I thought I had tried that, but I guess I missed
something. I substituted the literal code for the constructed string,
except that I added the parentheses, and it worked as expected. Now I need
to reconstruct the string, which may take a bit of doing, but at least I
know what will work. Thanks for the tip about IN. I assume I can use that
with a single condition. Also, I assume that this:
SELECT * FROM qryDept WHERE JobMonth IN("02", "03") AND IN("Plating",
"Painting")
needs ProcDept before the second IN, just as JobMonth is used for the first
IN.
Youare correct that JobMonth will not be adequate by itself. There will
either be a filter for JobYear in the same way as for JobMonth, or maybe
they will want to see the past six months or twelve months or whatever.
Until there are some decisions from the powers that be I will just be
thankful that the project is starting fairly early in the year so that I
don't need to worry about that for a while.
I adpated the code for assembling the string from something on Allen
Browne's web site for filtering a report based on a multi-select list box
(http://allenbrowne.com/ser-50.html). In my case I am filtering a form, and
I am using multiple criteria (Month and Dept, for instance). The list boxes
are in the form's header. I decided (for reasons I cannot recall at the
moment) to assemble a recordsouce string rather than a filter string. Is
there a reason to prefer one over the other in this case?
I will set to work on reassembling the recordsource string. I will post
back and let you know how that went. Thanks again.

Marshall Barton said:
BruceM said:
I have a database for keeping track of various statistics related to
manufacturing. The departments are either Plating or Painting. qryDept
contains the calculations, and is used as the recordsource for a form on
which the user can filter by month and department. I am using VBA code to
build a recordsource string according to choices in list boxes. I have
used
debug.print to view the following recordsource code. [JobMonth] is
obtained
by formatting the JobDate, which is a standard date field.

I can select for one or more months with no selections for department, or
one or more departments with no selection for month, or one department and
one month. In any of those cases the WHERE includes either AND or OR.
When
I attempt to combine the two, I run into problems. I can't sort out what
I
need to change. If I know the syntax I can probably figure out how to
assemble the string, but the trouble is that I can't get the syntax to do
what I need.

SELECT * FROM qryDept WHERE [JobMonth] = "02" OR [JobMonth] = "03" AND
[ProcDept] = "Painting"
Intended to show Painting jobs only for February and March, but shows
Plating and Painting

SELECT * FROM qryDept WHERE [JobMonth] = "02" AND [ProcDept] = "Plating"
OR
[ProcDept] = "Painting"
Intended to show Plating or Painting for February only, but shows all
months
Click to expand...


You need to use parenthesis to keep the Or groups together.
Without the parenthesis, the And is done before the Or.

SELECT * FROM qryDept WHERE ( JobMonth = "02" )
AND ( ProcDept = "Plating" OR ProcDept = "Painting" )

A different syntax using the IN operator will be shorter and
make this a little clearer:

SELECT * FROM qryDept WHERE JobMonth IN("02", "03")
AND IN("Plating", "Painting")

Are you sure the JobMonth calculation is adequate?
Shouldn't it include the year along with the month?
Click to expand...
 
M

Marshall Barton

BruceM said:
Thanks for the reply. I thought I had tried that, but I guess I missed
something. I substituted the literal code for the constructed string,
except that I added the parentheses, and it worked as expected. Now I need
to reconstruct the string, which may take a bit of doing, but at least I
know what will work. Thanks for the tip about IN. I assume I can use that
with a single condition. Also, I assume that this:
SELECT * FROM qryDept WHERE JobMonth IN("02", "03") AND IN("Plating",
"Painting")
needs ProcDept before the second IN, just as JobMonth is used for the first
IN.
Youare correct that JobMonth will not be adequate by itself. There will
either be a filter for JobYear in the same way as for JobMonth, or maybe
they will want to see the past six months or twelve months or whatever.
Until there are some decisions from the powers that be I will just be
thankful that the project is starting fairly early in the year so that I
don't need to worry about that for a while.
I adpated the code for assembling the string from something on Allen
Browne's web site for filtering a report based on a multi-select list box
(http://allenbrowne.com/ser-50.html). In my case I am filtering a form, and
I am using multiple criteria (Month and Dept, for instance). The list boxes
are in the form's header. I decided (for reasons I cannot recall at the
moment) to assemble a recordsouce string rather than a filter string. Is
there a reason to prefer one over the other in this case?
I will set to work on reassembling the recordsource string. I will post
back and let you know how that went. Thanks again.
Click to expand...


Yes, ProcDept should have been before the second IN and you
can use IN with one or a large number of conditions.

The Record Source property is reliable while the Filter
property has numerous "issues". Allen discusses most of
them in the Flaws - Filtering section.
 
B

BruceM

It turned out to be pretty simple to make the changes to the code, and the
code is tidier as a result. For instance, I need to trim the comma plus
space after the last IN condition, which is now always two characters. It
all works just as intended, so thanks again for pointing toward what proved
to be a simple fix.
Thanks too for the observation about RecordSource vs. Filter. Building the
recordsource string works well, so I will go ahead and use it without
worrying that it will turn around and bite me some day.
 
Top