Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL problem (Access03)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL problem (Access03)

    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:
    <code>
    "WHERE (((tblComponentDept.RequiredRN) Like IIf([Reports]![rptOrientationChecklist]![txtCredential]='RN','-1','*'))" & _
    </code>
    or
    <code>
    "WHERE (((tblComponentDept.RequiredRN) Like IIf([Reports]![rptOrientationChecklist]![txtCredential]=""RN"",""-1"",""*""))" & _</code>

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL problem (Access03)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL problem (Access03)

    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.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL problem (Access03)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL problem (Access03)

    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.

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL problem (Access03)

    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

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL problem (Access03)

    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL problem (Access03)

    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 <!t>[Post: 66,215]<!/t>. 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>post 66,215</post:>.

  10. #10
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL problem (Access03)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •