Parameter crosstab and wildcard (Access 2000)

Topic

New Reply

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[/b] 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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

PARAMETERS   [SELECT Value] Text(255);

and the criteria would be something like

WHERE MyTable.TextValue Like [SELECT Value] & "*"

Reply | Quote

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;”

Reply | Quote

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

Reply | Quote

Excellent! It sounds like the problem is solved.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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….

Reply | Quote