SQL Question

[dixxy]

Hello,

I am trying to create a dynamic report that can come from two different record source.

One the open event of the report I have this SQL stament, it's giving me errors 'Excpected end of statment'

this is it:

me.RecordSource = "SELECT from tblJobs.JobNumber, tblRooms.RoomName, tblRooms.RoomID, tblJobs.FirstName, tblJobs.LastName, tblClients.Address, tblJobs.Telephone, tblJobs.POnumber, tblRooms.UpperDoor, tblRooms.Finish, tblRooms.CounterStyle, tblRooms.CounterColor, tblJobs.RequiredDate, tblJobs.ShipAddress, tblJobs.ShipPhone, tblRooms.Handle1, tblRooms.Handle2, tblRooms.Handle3, tblRooms.Handle4, tblRooms.Edge, tblJobs.Fax, tblRooms.LowerDoor, tblRooms.Drawer, tblJobs.ShipVia, tblJobs.Terms, tblJobs.Contact, tblJobs.OnDelivery, tblJobs.WONotes, tblClients.City, tblClients.Province, tblClients.PostalCode, tblRooms.Print, tblJobs.ShipProvince, tblJobs.ShipPostal, tblJobs.ShipCity, tblJobs.ShipNotes" _
FROM ((tblClients
INNER JOIN tblJobs _
ON tblClients.ID = tblJobs.ID) _
INNER JOIN tblRooms ON tblJobs.JobNumber = tblRooms.JobNumber) _
WHERE (((tblJobs.JobNumber)=[Forms]![frmJobs]![JobNumber]) AND ((tblRooms.Print)=True))"

Thanks,

Sylvain

 

[dhookom]

Add more quotes:
tblJobs.ShipPostal, tblJobs.ShipCity, tblJobs.ShipNotes" _
" FROM ((tblClients INNER JOIN tblJobs " & _
" ON tblClients.ID = tblJobs.ID) " & _
" INNER JOIN tblRooms ON tblJobs.JobNumber = tblRooms.JobNumber) " & _
"WHERE (((tblJobs.JobNumber)=[Forms]![frmJobs]![JobNumber]) AND ((tblRooms.Print)=True))"



Duane
MS Access MVP

 

[dixxy]

Duane,

I'm having a really hard time with this.

I am trying to create one report that I can use for different purposes. I am thinking (maybe it's wrong I don't know)instead of making two different queries to populate this report I could use an SQL Statment in VBA. Am I going about this wrong.

This report will draw from more than one table, and I am thinking of running it from two different form. So I could use something from the forms to make the report decide which SQL Statment to run. This is what I am trying but it keep giving me th error 'expected end of Statment' and it points to the FROM all the time. This is how I have it set up...

Private_Sub Report Open(Cancel as Integer)
Dim SQL As String

SQL = "SELECT tblJobs.JobNumber, tblRooms.RoomName, tblRooms.RoomID, tblJobs.FirstName, tblJobs.LastName, tblClients.Address, tblJobs.Telephone, tblJobs.POnumber, tblRooms.UpperDoor, tblRooms.Finish, tblRooms.CounterStyle, tblRooms.CounterColor, tblJobs.RequiredDate, tblJobs.ShipAddress, tblJobs.ShipPhone, tblRooms.Handle1, tblRooms.Handle2, tblRooms.Handle3, tblRooms.Handle4, tblRooms.Edge, tblJobs.Fax, tblRooms.LowerDoor, tblRooms.Drawer, tblJobs.ShipVia, tblJobs.Terms, tblJobs.Contact, tblJobs.OnDelivery, tblJobs.WONotes, tblClients.City, tblClients.Province, tblClients.PostalCode, tblRooms.Print, tblJobs.ShipProvince, tblJobs.ShipPostal, tblJobs.ShipCity, tblJobs.ShipNotes" _
" FROM ((tblClients INNER JOIN tblJobs " & _
" ON tblClients.ID = tblJobs.ID)" & _
" INNER JOIN tblRooms ON tblJobs.JobNumber = tblRooms.JobNumber)" & _
"WHERE (((tblJobs.JobNumber)=[Forms]![frmJobs]![JobNumber]) AND ((tblRooms.Print)=True))"

Me.RecordSource = SQL

Now I'm thinking of putting an If at the top so something like this:

If form1.txttext1 = whatever then
run that SQL statment
end if

if form2.txttext2 = whatever then 
run a different SQL Statment
end if

In your expertise I'm I wasting my time playing with this or could this eventually work?



Thanks,

Sylvain

 

[dhookom]

I would create and save two queries that would be the record sources of the report. Assuming they are "queryA" and "queryB". Don't add any criteria to either one. Then create a third query "queryC" that is simply "SELECT * from queryA". Base your report on queryC.

Then in code on any form change the SQL of the query (not the report).

Dim db As DAO.Database
Dim strWhere as String
Set db = Currentdb
If Me.txtText = "SomeValue"
db.QueryDefs("queryC&quot.SQL = "SELECT * from queryA"
Else
db.QueryDefs("queryC&quot.SQL = "SELECT * from queryB"
End If
Set db = Nothing
strWhere = "JobNumber=" & Me.[JobNumber]
'or if JobNumber is text use the following line
'strWhere = "JobNumber=""" & Me.[JobNumber] & """"
DoCmd.OpenReport "rptMyReport", acviewpreview, , strWhere


Duane
MS Access MVP

 

[CosmoKramer]

Duane,

Apparently dixxy has an aversion to using multiple queries (thread703-648923)

Hoc nomen meum verum non est.

 

[OhioSteve]

Your approach may eventually work, but there are alternatives. You could just do two queries. Set one as the report's record source (let's assumes its called query1). Then, in the "on open" property of the report, write

if x=y then
me.recordsource=query2
end if

Access will bind the second query to the report, and ignore the first.

This approach would work best if the fields of the two queries have the same name. If that is not the case, you will need to work on the control sources for the fields in the report...they will need to include some conditional logic.

 

[RoyVidar]

Mr Sylvain

I agree with the advice of using stored queries or querydefs. If you still like the SQL/VBA approach, consider Mr. Duane's advice about more quotes.

I think the reason for the "expected end" thingy, might be that you do not concatinate the "From" statement with the previous:

...tblJobs.ShipCity, tblJobs.ShipNotes" _
" FROM ((tblClients INNER JOIN tblJobs " & _

should instead read

...tblJobs.ShipCity, tblJobs.ShipNotes" & _
" FROM ((tblClients INNER JOIN tblJobs " & _

To extract values from a form, also needs another set of concatinations (your "Where clause&quot:

...WHERE (((tblJobs.JobNumber)= " & [Forms]![frmJobs]![JobNumber] & &quot AND ((tblRooms.Print)=True))"

HTH Roy-Vidar

 

[Eupher]

dixxy, RoyVidar is right, your SQL string isn't concatenated properly. I know it's confusing! But the compiler will bark every time if something is out of place. And that's the good news! Worse is when the code syntax is correct, but the logic is wrong. You won't get an error message, but neither will you get the results you want.

To add to RoyVidar's post, depending on the type of field [JobNumber] is, you likely will need a set of single quotes, like so (single quotes are in red):

...WHERE (((tblJobs.JobNumber)= '" & [Forms]![frmJobs]![JobNumber] & "') AND ((tblRooms.Print)=True))"

Ken S.

 

[Eupher]

dixxy, addendum to my previous post...

If [JobNumber] is a number field, you will NOT need single quotes. If it is a text field, you WILL need single quotes.

Ken S.

p.s. FWIW, I agree with the others that it's probably easier (and faster!) to use multiple saved queries instead of multiple SQL strings.