Results 1 to 10 of 10
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Can you see the typo? (Access 2003/SP2)

    I'm trying to add the following criteria to my query, yet am having no luck:
    =IIf(IsNull([Forms]![frm_PerformanceHistory]![Supervisor]),"Like '*'",[Forms]![frm_PerformanceHistory]![Supervisor])

    If I replace the Like "*" with a supervisor's name (in double quotes) the query functions as desired, so I know I'm on the right track.


    any suggestions?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Can you see the typo? (Access 2003/SP2)

    Are you trying to substitute "Like '*" for the first parameter? You have only one ' in there, is that correct?

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Can you see the typo? (Access 2003/SP2)

    Nope, I had nested single and double quotes.
    I think that the Like is not evaluating properly.
    Here is the SQL view of the query:
    SELECT Projects.[Job #], Projects.DESCRIPTION, Projects.[CUSTOMER CONTACT] AS Inspector, Projects.LOCATION, Projects.[CONTACT] AS Supervisor, Projects.COMPANY AS Customer, Projects.[START DATE], Projects.COMPLETEDATE, Projects.CLOSED
    FROM Projects
    WHERE (((Projects.[CUSTOMER CONTACT])=[Forms]![frm_PerformanceHistory]![Inspector]) AND ((Projects.[CONTACT])=IIf(IsNull([Forms]![frm_PerformanceHistory]![Supervisor]),(Projects.[CONTACT]) Like "*",[Forms]![frm_PerformanceHistory]![Supervisor])));

    Now normally the WHERE would look like this (((Projects.[CONTACT]) Like "*"))
    I think that my IIf statement is returning a string rather than selecting everthing in Projects.Contact
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Can you see the typo? (Access 2003/SP2)

    You can't use iif like that.
    What you have to do is setup a column containing Forms]![frm_PerformanceHistory]![Supervisor] and have 2 criteria lines. The first criteria line would be Is Null for Forms]![frm_PerformanceHistory]![Supervisor].
    The second criteria for Forms]![frm_PerformanceHistory]![Supervisor] would be Is Not Null and the criteria for Projects.[CONTACT] would be Forms]![frm_PerformanceHistory]![Supervisor].

    Here is the amended query:
    SELECT Projects.[Job #], Projects.DESCRIPTION, Projects.[CUSTOMER CONTACT] AS Inspector, Projects.LOCATION, Projects.CONTACT AS Supervisor, Projects.COMPANY AS Customer, Projects.[START DATE], Projects.COMPLETEDATE, Projects.CLOSED, [Forms]![frm_PerformanceHistory]![Inspector] AS Expr1
    FROM Projects
    WHERE (((Projects.[CUSTOMER CONTACT])=[Forms]![frm_PerformanceHistory]![Inspector]) AND ((Projects.CONTACT)=[Forms]![frm_PerformanceHistory]![Supervisor]) AND (([Forms]![frm_PerformanceHistory]![Inspector]) Is Not Null)) OR (((Projects.[CUSTOMER CONTACT])=[Forms]![frm_PerformanceHistory]![Inspector]) AND (([Forms]![frm_PerformanceHistory]![Inspector]) Is Null));

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Can you see the typo? (Access 2003/SP2)

    But interestingly enough - if I don't pick an supervisor and an inspector, then nothing is returned.

    I think I'm headed totally in the wrong direction here.

    The scenario is an unbound form providing 3 dropdown lists, these fields provide the criteria for the query (which in turn feeds into a report).
    The user should be able to select any combination of Supervisor, Inspector and Company to winnow down the list of projects fed into the report.

    I was sure there was a method for setting up the query, without resorting to code.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Can you see the typo? (Access 2003/SP2)

    Use this in the criteria line:

    [Forms]![frm_PerformanceHistory]![Supervisor] Or [Forms]![frm_PerformanceHistory]![Supervisor] Is Null

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Can you see the typo? (Access 2003/SP2)

    Bingo!

    I thought Is Null would have to precede the form field in the query.

    Thanks
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Can you see the typo? (Access 2003/SP2)

    Ah, now that I reopen the query - I understand what you meant about creating the column and criteria lines. I've never done that before and I'll have to remember this.

    Thanks
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Can you see the typo? (Access 2003/SP2)

    The best way to do this is Hans way, because once the design of the query is closed and reopened again it displays all the criteria lines exploded.

    Otherwise you can get very confused (unless you are a genius like Hans) by all the criteria lines.

    It's good to see the exploded criteria lines that Access has generated.

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

    Re: Can you see the typo? (Access 2003/SP2)

    I get confused too - I often end up deleting all criteria and recreating them from scratch. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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