Use global variable as query criteria (1 Viewer)

[arcticsponge]

Hi everyone.

First, please accept my apologies if this has been posted in the wrong section.

I am certain that there is a far more easier way to do this, I am open to suggestion but please humour me; its driving me potty!

My database is used for vehicle lookups:

main table: tblItems: fields [type; body; doors; transmission; fuel]
created as INTO from qrySearch: tblSearch: fields [as tblItems]
query: qrySearch: fields [as tblItems]; criteria [type]=key1(), [body]=key2(), [doors]=key3(), [transmission]=key4(), [fuel]=key5()

There will eventually be more fields but for the time being I am only playing with 5 fields / keys

Purpose, I have a listbox bound to tblSearch with OnDoubleClick set to goto record.

To aid filtering, I have a set of checkboxes linked to global code, the same code that runs the criterion of qrySearch:

5 global variables (only displaying 1 - you get the picture)

Public Function key2() As String

key2 = strKey2

End Function

with the code behind the submit button being:

Private Sub Command235_Click()
DoCmd.SetWarnings False

Dim strType As String
Dim strBody As String
Dim strDoors As String
Dim strTransmission As String
Dim strFuel As String


strKey1 = like "*"    'car / van
strKey2 = like "*"    'saloon / hatch / pickup
strKey3 = like "*"    '3 / 5
strKey4 = like "*"    'auto / semi / manual
strKey5 = like "*"    'Diesel / petrol / gas

If Me.Check1 = True Then strKey1 = "car"
If Me.Check2 = True Then strKey1 = "van"

If Me.Check3 = True Then strKey2 = "saloon or hatch or coupe"
If Me.Check4 = True Then strKey2 = "pickup or small or large"

If Me.Check5 = True Then strKey3 = 3
If Me.Check6 = True Then strKey3 = 5

If Me.Check7 = True Then strKey4 = "auto"
If Me.Check8 = True Then strKey4 = "semi"
If Me.Check9 = True Then strKey4 = "manual"

If Me.Check10 = True Then strKey5 = "Diesel or petrol"
If Me.Check11 = True Then strKey5 = "gas or electric"


DoCmd.OpenQuery "qrySearch", acViewNormal

DoCmd.SetWarnings True

Me.Requery
end sub

The problem: I cannot figure out the correct syntax to be passed to the global variable: such as using the wildcard: LIKE "*"; or where multiple selections are required such as "diesel" OR "petrol".

Apologies for the long post.

 

[vbaInet]

main table: tblItems: fields [type; body; doors; transmission; fuel]
created as INTO from qrySearch: tblSearch: fields [as tblItems]

Why are you needing to create a table? And why is the table created from a query?

 

[Guus2005]

You could use a parameter query:

Select query

    Set qdf = CurrentDb.QueryDefs("qryCalcElapsedTimeStatusFinal")
    qdf.Parameters("ParameterContactNummer") = lngConNr
    Set rst = qdf.OpenRecordset

or action query

    Set qdf = CurrentDb.QueryDefs("qryDeleteElapsedRecords")
    qdf.Parameters("ParameterContactNummer") = lngConNr
    qdf.Execute

Enjoy!

 

[arcticsponge]

Why are you needing to create a table? And why is the table created from a query?

To vbaInet: I am using a query so that I can add / remove or modify criteria at a later date (and to add a count function in if required); I am creating a table so as to ensure that the results are always available even if the form should be shut so as to prevent the user from needing to re-run the search.

Thanks Guus2005 for your suggestion, I must admit that your method is both far cleaner and less open to SQL injection from the use of public variables; I shall try this but in the meantime so as to aid my learning, I want to try to hammer down the passing of a global variable with multiple criteria to a query.

 

[DCrake]

Here is a Sample Database that uses global variables that are passed between forms/queries/reports.

Hope this helps you.

 

[arcticsponge]

Thanks David for your suggestion - the entry that has some bearing on my post is that of passing string in this instance strInvoice however, you are only passing one string through to the criteria eg "I0001" however, I would need the criteria to be something more along the lines of "I0001" OR "I0002" it is this part than I am having the problems with - within the query the criteria would be:

"diesel" OR "petrol"

However:

strKey1 = "diesel" OR "petrol"

will not parse neither will

strKey1 = ""diesel" OR "petrol""

neither will 

strKey1 = """diesel"" ""OR"" ""petrol"""

 

[vbaInet]

To vbaInet: I am using a query so that I can add / remove or modify criteria at a later date (and to add a count function in if required); I am creating a table so as to ensure that the results are always available even if the form should be shut so as to prevent the user from needing to re-run the search.

I still don't see the need to create a table. I would have thought the table should have already been created and all you do is insert into or update the table.

But you've got some solutions there so you're sorted.

 

[DCrake]

Instead of using OR try IN()

In("Deisal","Petrol")

If Me.Check3 = True Then strKey2 = "In('saloon','hatch','coupe')"

 

[arcticsponge]

If Me.Check3 = True Then strKey2 = "In('saloon','hatch','coupe')"

Tried that but it didn't work, neither did

"'saloon' OR 'hatch'"

 

[DCrake]

How are you using strKey2 in your query?

=strKey2()

or

strKey2()

The first option will fail

The second will not

 

[arcticsponge]

No equals sign; currently hardcoding the variable at source for test purposes so am referencing the the variable direct:

Public Function key1() As String


key1 = "IN('saloon','hatch')"
'key1 = strKey1

End Function

also, the query:

field: body
table: tblItems
Total: Group By
Sort:
Show:
Criteria: key1()
or:

 

[DCrake]

Add the field again and change it to Where instead of Group By

 

[arcticsponge]

Oh dear, slipped up there.

Interestingly, if I set the query criteria as: IN('Saloon','hatch')
the query runs perfectly, but, if a prepare it to be passed as a variable, the query fails.

key1()="hatch"

works

but 
key1() = "IN('hatch')"

fails

Any idea?

 

[Sampaio Avelino]

Hi

The error occurs because the query put the equal sign (=) in front of the clause "IN". The result looks like this:

field: body
table: tblItems
Total: Group By
Sort:
Show:
Criteria: = in ('hatch')
or:

One solution is to remove the equal sign in the query.

Put [x] in the criteria.

field: body
table: tblItems
Total: Group By
Sort:
Show:
Criteria: [x]
or:

Example:

Public Function fncRunQuery()
Dim qrf As DAO.QueryDef
Dim strSql As String
Set qrf = CurrentDb.QueryDefs("YourQuery")
strSql = qrf.SQL
strSql = Replace(strSql, "=[x]", key1)
qrf.SQL = strSql
Set qrf = Nothing
DoCmd.OpenQuery "YourQuery"
End FunctionFunction

 

[boblarson]

Check this thread for more answers:

http://social.answers.microsoft.com/Forums/en-US/addbuz/thread/e45dbc44-9ea2-46d4-a19a-acdbf050f432