Query with variable criteria

[marronhar]

I need to be able to adjust query criteria depending what is chosen on a
form. The field in question is a numeric field (long). I have attempted to
get this to work by using a public function in the criteria window to return
a string that contains what I am trying to restrict my query results to. If
I return a single number, say 12345, I get results for that number. If I
return something like 12345 Or 67890 I will get a datatype mismatch.
I have tried formatting the string like [tablename].[fieldname]=12345 but
datatype mismatch seems to prevail. I have tried changing the datatype of
the public function as a string and as a variant with no change in the
outcome.
Any suggestions?

 

[Ken Snell \(MVP\)]

When matching to a string value, the syntax is to delimit the string value
with either ' or " characters:

[tablename].[fieldname]="12345"

or

[tablename].[fieldname]='12345'

Assuming that you're building an SQL statement via VBA code, it's best to
use the ' delimiters.

 

[John W. Vinson]

I need to be able to adjust query criteria depending what is chosen on a
form. The field in question is a numeric field (long). I have attempted to
get this to work by using a public function in the criteria window to return
a string that contains what I am trying to restrict my query results to. If
I return a single number, say 12345, I get results for that number. If I
return something like 12345 Or 67890 I will get a datatype mismatch.
I have tried formatting the string like [tablename].[fieldname]=12345 but
datatype mismatch seems to prevail. I have tried changing the datatype of
the public function as a string and as a variant with no change in the
outcome.
Any suggestions?

In addition to Ken's suggestions, be aware that you cannot pass an operator
such as OR or IN() as a parameter; you must actually build the complete SQL
string including the WHERE clause.

Criteria for Text fields must be delimited by either ' or " characters;
criteria for a Number field must have no delimiters at all. Perhaps you could
post your code and explain how you're using it!

 

[marronhar]

Thanks for the responses. Here is some more detail on what I am trying to do.

First the Globals:

Public DepSearch As String

Public Function DepCriteria() As String

DepCriteria = DepSearch

End Function

From a combo box (Combo7), different values are selected and concentrated
into a string.

Option Compare Database
Dim DepLu As String

Private Sub Combo7_Click()
'DepLu is used to display the actual list of names selected in a text box
(Text5)

DepLu = DLookup("dname", "tbldept", "[deptnu]=" & Combo7)

‘Text5 displays the names of what has been selected. Multiple values can be
chosen and displayed. DepSearch contains a string with the actual values
being searched.

If Text5 = "" Then
Text5 = DepLu
DepSearch = Combo7
Else
Text5 = Text5 & Chr(13) & Chr(10) & DepLu
DepSearch = DepSearch & " Or " & Combo7
End If
End Sub

DepCriteria() is placed on the criteria area (query design view) in the Dept
field (long).

I have tried several variations of this. But nothing has worked except when
DepSearch contains a single value. It was said that an operator like “Orâ€
can’t be passed in this manner. Building an SQL statement is something I can
do but I am not certain on how to apply it to the query I am opening.

I need to be able to adjust query criteria depending what is chosen on a
form. The field in question is a numeric field (long). I have attempted to
get this to work by using a public function in the criteria window to return
a string that contains what I am trying to restrict my query results to. If
I return a single number, say 12345, I get results for that number. If I
return something like 12345 Or 67890 I will get a datatype mismatch.
I have tried formatting the string like [tablename].[fieldname]=12345 but
datatype mismatch seems to prevail. I have tried changing the datatype of
the public function as a string and as a variant with no change in the
outcome.
Any suggestions?

In addition to Ken's suggestions, be aware that you cannot pass an operator
such as OR or IN() as a parameter; you must actually build the complete SQL
string including the WHERE clause.

Criteria for Text fields must be delimited by either ' or " characters;
criteria for a Number field must have no delimiters at all. Perhaps you could
post your code and explain how you're using it!

 

[John W. Vinson]

Thanks for the responses. Here is some more detail on what I am trying to do.

First the Globals:

Public DepSearch As String

Public Function DepCriteria() As String

DepCriteria = DepSearch

End Function

From a combo box (Combo7), different values are selected and concentrated
into a string.

Option Compare Database
Dim DepLu As String

Private Sub Combo7_Click()
'DepLu is used to display the actual list of names selected in a text box
(Text5)

DepLu = DLookup("dname", "tbldept", "[deptnu]=" & Combo7)

‘Text5 displays the names of what has been selected. Multiple values can be
chosen and displayed. DepSearch contains a string with the actual values
being searched.

If Text5 = "" Then
Text5 = DepLu
DepSearch = Combo7
Else
Text5 = Text5 & Chr(13) & Chr(10) & DepLu
DepSearch = DepSearch & " Or " & Combo7
End If
End Sub

DepCriteria() is placed on the criteria area (query design view) in the Dept
field (long).

The ONLY thing that you can place in the criteria area in design view for a
Long Integer field is a single long integer. Access will not be able to parse
a partial WHERE clause.

What you'll need to do instead is *something like* setting strSQL to the
complete SQL string of your query, up through the word WHERE, and then
appending the result of your function. Then use this strSQL as the
Recordsource of a Form or Report.

 

[marronhar]

Thanks,
As I worked with this issue and tried different things, your suggestion
method had occured to me but I was hoping to find a way to do this within the
query so I could see the results in a datatable view.
Thanks again.

Thanks for the responses. Here is some more detail on what I am trying to do.

First the Globals:

Public DepSearch As String

Public Function DepCriteria() As String

DepCriteria = DepSearch

End Function

From a combo box (Combo7), different values are selected and concentrated
into a string.

Option Compare Database
Dim DepLu As String

Private Sub Combo7_Click()
'DepLu is used to display the actual list of names selected in a text box
(Text5)

DepLu = DLookup("dname", "tbldept", "[deptnu]=" & Combo7)

‘Text5 displays the names of what has been selected. Multiple values can be
chosen and displayed. DepSearch contains a string with the actual values
being searched.

If Text5 = "" Then
Text5 = DepLu
DepSearch = Combo7
Else
Text5 = Text5 & Chr(13) & Chr(10) & DepLu
DepSearch = DepSearch & " Or " & Combo7
End If
End Sub

DepCriteria() is placed on the criteria area (query design view) in the Dept
field (long).

The ONLY thing that you can place in the criteria area in design view for a
Long Integer field is a single long integer. Access will not be able to parse
a partial WHERE clause.

What you'll need to do instead is *something like* setting strSQL to the
complete SQL string of your query, up through the word WHERE, and then
appending the result of your function. Then use this strSQL as the
Recordsource of a Form or Report.

 

[David W. Fenton]

When matching to a string value, the syntax is to delimit the
string value with either ' or " characters:

[tablename].[fieldname]="12345"

or

[tablename].[fieldname]='12345'

Assuming that you're building an SQL statement via VBA code, it's
best to use the ' delimiters.

Why would you suggest that? I think it's better to use double
quotes, in fact, though it's a bit fussier to do it in code (though
pretty easy if you have a double quote constant declared).

 

[David W. Fenton]

What you'll need to do instead is *something like* setting strSQL
to the complete SQL string of your query, up through the word
WHERE, and then appending the result of your function. Then use
this strSQL as the Recordsource of a Form or Report.

It's also possible to have a function that returns a list and use
InStr() to test a particular value. It can be messy, though, as well
as potentially complex if some of the values can be substrings of
other values.

 

[John W. Vinson]

Thanks,
As I worked with this issue and tried different things, your suggestion
method had occured to me but I was hoping to find a way to do this within the
query so I could see the results in a datatable view.

You can still construct a SQL string and open a Datasheet view form, or even
use the CreateQuerydef() method to convert the SQL string to a Query and then
open that Query.

What you cannot do is pass a complex SQL string (anything more complex than a
single value) as a parameter in a parameter query. Sorry, you just can't.