Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi use of query (A2K)

    In order not to create numerous queries which are off-shoots of one query, I've tried the following:

    Premise as I understand it:

    Data Source: qry CN
    Fields Used: F00_Function and F01_RA_Service

    I want to use various combinations of these two fields and define them when I'm selecting records for a generic report.

    Doing this will allow me to use one query and one generic report – hopefully

    Attempted Code cobbled from previous code:

    Private Sub prvPS01_Click()
    Dim strCombine As String
    Dim strFunction As String
    Dim strService As String
    strFunction = "F00_Function Like '11'"
    strService = "F01_RA_Service Like 'NER*'"
    strCombine = strFunction & strService

    On Error GoTo Err_Click
    DoCmd.OpenReport ReportName:="rpt Generic", View:=acViewPreview, WhereCondition:=strCombine
    DoCmd.RunCommand acCmdZoom100
    Exit Sub
    Err_Click:
    If Err <> 2501 Then
    MsgBox Err.Description, vbExclamation
    End If

    End Sub

    And the first of what will probably be many error messages was:

    Syntax error (missing operator) in query expression '(F00_Function Like '11'F01_Service Like 'NER*')'.

    As always, thanks in advance for any help with my attempted coding,
    Cheers,
    Andy

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Multi use of query (A2K)

    You must, of course, put something between the conditions to tell Access how you want to combine them, such as AND or OR. I assume that you want AND here, as in many of your previous questions. Change the line
    <code>
    strCombine = strFunction & strService
    </code>
    to
    <code>
    strCombine = strFunction & " AND " & strService
    </code>
    BTW you don't use a wildcard * or ? in
    <code>
    strFunction = "F00_Function Like '11'"
    </code>
    so you might as well use
    <code>
    strFunction = "F00_Function = '11'"</code>

  4. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi use of query (A2K)

    Thanks Hans,

    Worked perfectly, naturally. And I realize that some of my questions/your answers appear repetitive and I apologize for that. When I get caught up in a specific problem, sometimes the trees disappear into the forest.

    With respect to the use of the Wildcard *, I believe that I only used it in the NER example where it was appropriate and not in the Function where I knew it was not,
    Cheers,
    Andy

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

    Re: Multi use of query (A2K)

    You did indeed only use a wildcard for the F01_RA_Service field, not for the F00_Function field. But without a wildcard, there is no point in using the Like operator - Like is intended to be used with wildcards.

  6. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi use of query (A2K)

    Hans,

    If it wasn't for the trees, there'd be no forest. I, once more, stand corrected and smarter for it.

    Thanks for your patience,
    Cheers,
    Andy

Posting Permissions

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