Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter crosstab and wildcard (Access 2000)

    Ok, I figured out how to write the SQL for a parameterized crosstab query, and how to force the column headings so the result can be used to create a report. What I have not been able to figure out is how to write the SQL to select records based on the parameter specified or all records regardless of parameter, just like you can in the criteria box in the query design grid -- example:

    Like [Select Criteria or "*"] & "*"

    How do you write the statement in SQL to include a wildcard function if you want to disregard the parameters?

    I have tried using the vertical bar and percent symbol (e.g., PARAMETERS [SELECT Value] | % Text in the first line of the statement, but I get an error message and it says that I cannot use the "|" or the "%" in this statement.

    So how do ya do it? I cannot imagine that the structure of Jet SQL limits the ability to use SQL wildcard characters. I have also tried using "*", but to the same effect....no worky worky.

    Please advise. Thanks.

    Captain D

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter crosstab and wildcard (Access 2000)

    That's a good question and I don't know the answer. However, why don't you just save the query and then use VB to run the query? I'm assuming you are creating the SQL in an Access database.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter crosstab and wildcard (Access 2000)

    I don't think you can use the pipe character like that in Access 2000. I know there are other places where you can't use it since the change from A97 to A2k.

    The Like expression has to go in the criteria, not the parameters. so the parameter portion would be something like <pre>PARAMETERS [SELECT Value] Text(255);</pre>

    and the criteria would be something like <pre>WHERE MyTable.TextValue Like [SELECT Value] & "*"</pre>

    Charlotte

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

    Re: Parameter crosstab and wildcard (Access 2000)

    If you create a query in the design grid, you can look at its SQL by switching to SQL view. It could look like this:

    PARAMETERS [Select Value] Text;
    SELECT *
    FROM tblSomething
    WHERE SomeField=[Select Value] OR [Select Value] Is Null;

    where tblSomething is the name of the table and SomeField is the name of the field. If you want to build the SQL string in code, you can do something like this:

    strSQL = "PARAMETERS [Select Value] Text; " & _
    "SELECT * FROM tblSomething " & _
    "WHERE SomeField=[Select Value] OR [Select Value] Is Null;"

  5. #5
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter crosstab and wildcard (Access 2000)

    dashiell, yes, I am creating the SQL in an Access db. I am not too savvy on VB, so that would be quite a challenge. I can write some rudimentary and simple code in VBA, but this may be a bridge too far for the time being. Could you give me an example of what it would look like in VBA?

    Thanks

    Captain D

  6. #6
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter crosstab and wildcard (Access 2000)

    Hans, my man!! Thanks a mil. Just like your other answers, this one seems to have worked great. For those others out there who are wondering how to do it, simply add this to your WHERE statement: "OR [SelectValue] IS NULL" (just without the quotes), as in the example below:

    WHERE [SELECT PUD]=[PUD] or [SELECT PUD] IS NULL

    This will allow you to simply press enter when prompted for the parameter value, returning all records regardless of the parameter value.

    Thanks again, Hans!

    Captain D

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter crosstab and wildcard (Access 2000)

    Excellent! It sounds like the problem is solved.

  8. #8
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter crosstab and wildcard (Access 2000)

    Yeah, it works great. Now the next challenge: write the PARAMETERS and WHERE statements for multiple parameter values. Now that I can query for specific records based on a parameter value or all records regardless of parameter value, how do I write the query to return records based on multiple values for the same parameter?

    You all have been great thus far and I really appreciate your help.

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

    Re: Parameter crosstab and wildcard (Access 2000)

    If by "return records based on multiple values for the same parameter" you mean that if the user enters "A, D, F" (without the quotes), all records for which the field is A or D or F are returned, you can use a one of the techniques described in ACC2000: How to Create a Parameter In() Statement. For example:

    WHERE InStr([Enter zero or more PUDs separated by commas], [PUD])>0 Or InStr([Enter zero or more PUDs separated by commas], [PUD]) Is Null

    If some of your values are entirely contained in others, this may return more records than you want. For example, entering "Finance" (without the quotes) as parameter will return all records for which the field contains part of this word, so "Fin" will be OK, or "nance", as well as "Finance". If you want to avoid this, you'll need to use a variation on the other solution mentioned in the MSKB article, involving a custom VBA function.

  10. #10
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter crosstab and wildcard (Access 2000)

    Again, thanks a million, Hans. I tried it and it works flawlessly. I will make sure I check out the link you posted as well.
    ....back to the grindstone....

Posting Permissions

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