building a query with variables

[Afrosheen]

I have a combo box that is based on a group box. Here's the code
120 Select Case grpDept.Value
Case 1
130 strDept = "Administrative"
140 Case 2
150 strDept = "Food Service"
160 Case 3
170 strDept = "Maintenance"
180 Case 4
190 strDept = "Ops. A-Days"
200 Case 5
210 strDept = "Ops. A-Nights"
220 Case 6
230 strDept = "Ops. B-Days"
240 Case 7
250 strcnt = "Ops. B-Nights"
260 Case 8
270 strDept = "Programs"
280 Case 9
290 strDept = "Programs/Chaplin"
end select

Dim flag As String
flag = "true"
490 cboloc.RowSource = "Select qrySupervisor2.tblmain.[lname] & ', '&
qrySupervisor2.tblmain.[fname], qrySupervisor2.tblmain., [email1]" & _
"From qrySupervisor2 " & _
"Where qrySupervisor2.location = '" & strDept & "' " & _
"Order by qrySupervisor2.Location;"

Line 490 is the line that builds the information for the combo box. The
problem I'm having is that it displays all information. I want it to display
just the information where [email1] = true.

I've tried using "Where qrySupervisor2.email1 = True" plus the rest of the
location statement. all I keep getting is errors no matter how I put them in.

SELECT [lname]+ ', '+[fname], FROM qrysupervisor2, where [email1]=true;
ORDER BY [Lname];

And that didn't work. Right now any help would be much appreciated.

 

[Jeff Boyce]

A group box with 9 choices? That must take up a lot of room on your form!

Consider another alternative ... use a combobox to list those, let the user
select one, then proceed with what you're trying to do.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Dorian]

Try:

490 cboloc.RowSource = "Select qrySupervisor2.tblmain.[lname] & ', '&
qrySupervisor2.tblmain.[fname], qrySupervisor2.tblmain.,
[email1]" & _
"From qrySupervisor2 " & _
"Where qrySupervisor2.location = '" & strDept & "' " & _
" AND Email1 = TRUE " & _
" Order by qrySupervisor2.Location;"


The ';' must be at the very end. Actually its optional, you dont need it at
all. You also dont need the [...] around column names unless they are
reserved words or contain special characters.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Douglas J. Steele]

You're missing a space between [email1] and the keyword From.