SQL Guru

[Afrosheen via AccessMonster.com]

Thanks for reading this. Could you please check out this SQL statement. I
copied and pasted it in to a routine I'm trying to do. When I checked it out
IN the query it worked ok starting with the Select. In the positions of
tblTaps.Workplan= is a variable string that the user has to enter by combo
box on a form. The tblMain.Supervisor= is another variable string from a
combo box. The ultimate goal is to produce a report. On the query its self
when I put in January for the WorkPlan and Tom for the supervisor it works
great. When I try the input method it comes up with a syntax error. So
probably I'm missing something or something is in the wrong location.

strWhere = "SELECT tblTaps.StaffId, tblMain.Lname, tblMain.Fname, tblTaps.
WorkPlan, tblTaps.WorkRec, tblTaps.IntDue, tblTaps.IntRec, tblTaps.FinalDue,
tblTaps.FinalRec, tblTaps.RatingCyc, tblTaps.FiscalYr, tblTaps.Rating,
tblTaps.Notes, tblTaps.Archive, tblTaps.DateAch, tblMain.IdPict, tblMain.
Supervisor, tblMain.Location" _
& "FROM tblMain INNER JOIN tblTaps ON tblMain.StaffId = tblTaps.StaffId" _
& "WHERE (((tblTaps.WorkPlan)="""& me.combo149&""") AND ((tblTaps.Archive)
=False) AND ((tblMain.Supervisor)="""&me.cboloc&"""))"

Thanks for helping me out.

 

[John Spencer]

Hopefully this will help you get a working SQL statement.

strWhere = "SELECT tblTaps.StaffId, tblMain.Lname" & _
", tblMain.Fname, tblTaps.WorkPlan, tblTaps.WorkRec" & _
", tblTaps.IntDue, tblTaps.IntRec, tblTaps.FinalDue" & _
", tblTaps.FinalRec, tblTaps.RatingCyc, tblTaps.FiscalYr" & _
", tblTaps.Rating, tblTaps.Notes, tblTaps.Archive" & _
", tblTaps.DateAch, tblMain.IdPict, tblMain.Supervisor" & _
", tblMain.Location"

'Need a space before the word FROM
strWhere = StrWhere & _
" FROM tblMain INNER JOIN tblTaps ON tblMain.StaffId = tblTaps.StaffId"

'Need a space before the word WHERE, removed unneeded parentheses,
'and added spaces around the ampersands
strWhere = strWhere & _
" WHERE tblTaps.WorkPlan=""" & me.combo149 & _
""" AND tblTaps.Archive=False" & _
" AND tblMain.Supervisor=""" & me.cboloc & """"

I often add a line to print out the string and then copy the string from
the VBA immediate window into a blank query to see what errors are
generated when I attempt to run the query.

Debug.Print strWhere 'Print the SQL string to the VBA immediate window.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[egerds]

Also if you are moving the SQL statement to a different location other
then the form [me!controlname] would reference to where you moved the
sql statement to. You would need to replace the instances of [Me!
controlname] with Forms.Item(formnamehere).Controls.Item
(controlnamehere)

 

[Afrosheen via AccessMonster.com]

What I'm trying to do is print a report with a sub report. The Report 1 is a
form letter. The sub report will do the SQL statement some where. So the
report will have the supervisor name and the sub report will have all the
information based on WorkPlan = combo149 and the Supervisor = cboloc. I'm
hoping to get this thing worked out.. There is another post called I'm in a
delema that I posted this morning.

I hope this works out. Thanks for your reply.

Also if you are moving the SQL statement to a different location other
then the form [me!controlname] would reference to where you moved the
sql statement to. You would need to replace the instances of [Me!
controlname] with Forms.Item(formnamehere).Controls.Item
(controlnamehere)