SQL problem (Access03)

Topic

New Reply

I had a query that was working properly just using the grid. I had to add a extra feature in so I needed to create an IF statement so I brought the query into VBA as an SQL statement. I may have more than one problem with how this is written, but the point I can’t get by is in the Else Where statement it gives me a compile/syntax error and highlights RN on the First line of the Where clause. Since I have a very basic understanding of SQL I would appreciate any and all assistance.

The situation is this I have four main credential groupings RN, LPN, Assistant, UC. I now need to account for Agency RNs. Data is entered as Credentials which is the RN, LPN, etc. Status is things like parttime, inhouse agency, agency, or fulltime etc. So if someone’s status is Agency then the ReguiredAgencyRN should be selected. But if they are not agency the report should show the appropriate credentials.

If Reports.rptOrientationChecklist.txtStatus.Value = “Agency” Then
‘ SQL to obtain records for agency nurses
strSQL = ” SELECT tblComponents.Name, tblComponents.PresentationType, ” & _
“tblComponentDept.RequiredAgencyRN, tblComponentDept.Department” & _
“FROM tblComponents INNER JOIN tblComponentDept ” & _
“ON tblComponents.ComponentID = tblComponentDept.ComponentID ” & _
“WHERE (((tblComponentDept.RequiredAgencyRN)=-1)” & strWhere1
Else
‘ SQL to obtain records for regular staff
strSQL = ” SELECT tblComponents.Name, tblComponents.PresentationType, ” & _
“tblComponentDept.RequiredRN, tblComponentDept.RequiredLPN, ” & _
“tblComponentDept.RequiredAide, tblComponentDept.RequiredUC, ” & _
“tblComponentDept.Department” & _
“FROM tblComponents INNER JOIN tblComponentDept ” & _
“ON tblComponents.ComponentID = tblComponentDept.ComponentID ” & _
“WHERE (((tblComponentDept.RequiredRN) Like IIf([Reports]![rptOrientationChecklist]![txtCredential]=”RN”,”-1″,”*”))” & _
“AND ((tblComponentDept.RequiredLPN) Like IIf([Reports]![rptOrientationChecklist]![txtCredential]=”LPN”,”-1″,”*”))” & _
“AND ((tblComponentDept.RequiredAide) Like IIf([Reports]![rptOrientationChecklist]![txtCredential]=”Nursing Assistant”,”-1″,”*”))” & _
“AND ((tblComponentDept.RequiredUC) Like IIf([Reports]![rptOrientationChecklist]![txtCredential]=”Unit Clerk”,”-1″,”*”)))” & strWhere1
End If

Do I need this DoCmd line. I think Hans said I only needed it when creating tables. But I wouldn’t bank anything on my memory.

‘ Execute it
DoCmd.RunSQL strSQL

stDocName = “rptOrientationChecklist”
DoCmd.OpenReport stDocName, acPreview, , strWhere1

PS this code is associated with the click event of a button. Should it be associated with the on open even of the form?

Thank you for your help.

Fay

Reply | Quote

Replies

DoCmd.RunSQL is only for SQL statements that correspond to action queries (i.e. make table, update and delete queries), not for selection queries.

It is not clear to me what you want to do with this SQL. Is it supposed to be the record source for the form, or for the report you are about to open, or what?

Note: your second SQL statement contains quoted strings within the SQL string. You must either use single quotes within the SQL string, or double the double quotes, otherwise VBA gets confused over where the string actually ends. For example:

"WHERE (((tblComponentDept.RequiredRN) Like IIf([Reports]![rptOrientationChecklist]![txtCredential]='RN','-1','*'))" & _

or

"WHERE (((tblComponentDept.RequiredRN) Like IIf([Reports]![rptOrientationChecklist]![txtCredential]=""RN"",""-1"",""*""))" & _

Reply | Quote

I thought I didn’t need the DoCmd statement. But wanted to verify. Thanks

The code is for the report not the form.

I put in single quotes and not get the message: Object doesn’t support the property or method. I should also say that the SQL is for the subreport not the main report.

Reply | Quote

The code you have now won’t work, but it is not really clear to me what you want to accomplish. Could try to explain more precisely what you want? Thanks.

Reply | Quote

The main report, rptOrientationChecklist, pulls the staff members name, credentials, status (Full time, Part-Time, Agency, etc), start date, and department.

There are two subreports only one is an issue.

The subreport, rsubOrientationChecklistComponents, is to report only the components that match the person’s department and credentials for example RN in ICU. That is based on a query I did on the grid here is the SQL version.

SELECT tblComponents.Name, tblComponents.PresentationType, tblComponentDept.RequiredRN, tblComponentDept.RequiredLPN, tblComponentDept.RequiredAide, tblComponentDept.RequiredUC, tblComponentDept.Department
FROM tblComponents INNER JOIN tblComponentDept ON tblComponents.ComponentID = tblComponentDept.ComponentID
WHERE (((tblComponentDept.RequiredLPN) Like IIf([Reports]![rptOrientationChecklist]![txtCredential]=”LPN”,”-1″,”*”)) AND ((tblComponentDept.RequiredAide) Like IIf([Reports]![rptOrientationChecklist]![txtCredential]=”Nursing Assistant”,”-1″,”*”)) AND ((tblComponentDept.RequiredUC) Like IIf([Reports]![rptOrientationChecklist]![txtCredential]=”Unit Clerk”,”-1″,”*”)));

I needed to modify the components table to hold the requirements for Agency RNs based on units. The query above is a straight forward match RN to RN, UC to UC, etc. I can’t add Agency RN to the credential listing it needs to be straight RN, LPN etc. So the only way to pull the agency requirements is to create an IF Then statement. At least that was my thinking.

If rptOrientationChecklist.txtStatus = Agency Then
give me the material from the tblComponentDept.RequiredAgencyRN
Else
give me the above Select results
End if

Would it be more appropriate to create a second report that is tied to a query that only reported just the Agency RN with a seond button to activate it?

Hans you said what I had would cut the mustard. Where do I go from here? Thank you.

Reply | Quote

I can’t seem to get my head around your description. Unless somebody else comes up with an suggestion, it’s probably best to go with different versions of the report.

Reply | Quote

Fay,
Are you clicking a button on a form and, at that time, creating a sql based upon a report that the button opens, and upon which the sql of the underlying report on that opened report is based?
I’m not sure how much help I can give, but I did think of this: you might want to surround whatever resolves from the “iif” statement with quotes, as in:
” WHERE ReqdRN) Like ” & chr$(34) & iif(parent![txtCredential]=”RN”,”-1″,”*”) & chr$(34) & _etc
Also, your problem made me remember some earlier issues I had with the iif, as in “Access must resolve everything you are giving it to pass it to the IIF function,” (I’ll find the author of that quote shortly), and the fact that the grid is a lot more forgiving.
thx
Pat

Reply | Quote

It was Mark Liquorman response in my thread starting with msg # 66215 , “Curiosity”. I’m sorry, I don’t know how to embed the link. You might want to take a look at that thread although I don’t know if you will find anything helpful in it.
thx
Pat

Reply | Quote

Including a link to a post is very simple: use the mouse to select the post number including the square brackets around it, in this case [Post: 66,215]. Press Ctrl+C to copy to the clipboard. When composing your post/reply, press Ctrl+V to paste the contents of the clipboard. The Lounge software will convert the bracketed text to a hyperlink: post 66,215.

Reply | Quote

Perfect — thanks, Hans! I had copied the whole http:// etc thing, and it looked like text, so I gave up. Now I know!
Pat

Reply | Quote