VBA Public function used to return Criterion to Query - return "*"

[alexhatzisavas]

Hi all.

I'm calling a Public VBA Function (defined in a VBA module) from the
Criterion field of a Query in order to control the criterion of the query.

I'm having a problem making the VBA Function return an expression (such as:
Like "*") that will enable the Query to return all the values for the field
where the Criterion exists.
That is, i want the VBA function to evaluate to an expression such as " Like
"*" ", that will make the Query return all records.

Here are the specifics:

Suppose the Query field is "Customer".
For field "Customer", i have written the following Criterion:
Filter([Forms]![FormName]![FormControl])

"Filter" is the Public VBA Function.
It accepts [Forms]![FormName]![FormControl] as an argument (this is the
value of a control -FormControl- on Form "FormName").
The query is activated via a CommandButton on Form "FormName".

So, on Form "FormName", the user clicks on the CommandButton that activates
the Query.

Depending on whether the user *has* or *has not* selected a value from the
control "FormControl" (which is a ComboBox), i want to achieve the following
functionality:

Case 1 (this I HAVE accomplished...)
- The user *has* selected a value from the ComboBox control (e.g. "ABC"):
Thus, [Forms]![FormName]![FormControl]="ABC"
In the VBA module, the VBA Function (Filter) becomes: Filter="ABC"
In the Query, the Criterion ( Filter([Forms]![FormName]![FormControl] ) now
equals: "ABC"
Thus, field "Customer" is properly filtered, and only records with "ABC"
appear.
This I HAVE accomplished.

Case 2 (this I HAVE NOT accomplished...)
- The user *has not* selected a value from the control:
Thus, [Forms]![FormName]![FormControl]=""
In the VBA module, the VBA Function (Filter) SHOULD evaluate to something
that enables ALL records of the field with the Criterion to be returned.
So, in the VBA code, i'd be lloking for something like:
Filter=" Like "*" " (this doesn't work of course)

That is, i want to have the VBA function (Filter) return such a value that
the criterion becomes Like "*" and all records appear.

Any help would be greatly appreciated.

Thanks,
Alex

 

[Wayne Morgan]

Does the combo box return a Null or have you added a record to it to return
a value for All Records? Whatever that value is (Null or one you've created)
use it in the criteria as follows, no function is needed.

=[Forms]![FormName]![FormControl] or [Forms]![FormName]![FormControl] Is
Null

If the value of the combo box is Null, then the second half of the
expression returns True. If you use True as the criteria, you'll get all
records.

 

[Kevin K. Sullivan]

Alex,

Typically, the way I do this is to use the Like operator explicitly in
all cases. I don't use a function, I just specify the Criterion of the
query to be:

Like [Forms]![FilteringForm]![cboCrit]

The trick is make cboCrit report an asterisk instead of Null or
zero-length string. What I do is make the RowSource of the Combo Box a
UNION query with the original Rowsource, a la

SELECT "*" AS CompanyName UNION SELECT CompanyName FROM Companies ORDER
BY CompanyName

for a single column Combo box or

SELECT "*" AS CompanyID, "(All)" AS CompanyNameUNION SELECT CompanyID,
CompanyName FROM Companies ORDER BY CompanyName

for a two-column (ColumnWidths = 0 combo box.

Set the default value of the combo to "*" and LimitToList = True in
either case.

HTH,

Kevin

Hi all.

I'm calling a Public VBA Function (defined in a VBA module) from the
Criterion field of a Query in order to control the criterion of the query.

I'm having a problem making the VBA Function return an expression (such as:
Like "*") that will enable the Query to return all the values for the field
where the Criterion exists.
That is, i want the VBA function to evaluate to an expression such as " Like
"*" ", that will make the Query return all records.

Here are the specifics:

Suppose the Query field is "Customer".
For field "Customer", i have written the following Criterion:
Filter([Forms]![FormName]![FormControl])

"Filter" is the Public VBA Function.
It accepts [Forms]![FormName]![FormControl] as an argument (this is the
value of a control -FormControl- on Form "FormName").
The query is activated via a CommandButton on Form "FormName".

So, on Form "FormName", the user clicks on the CommandButton that activates
the Query.

Depending on whether the user *has* or *has not* selected a value from the
control "FormControl" (which is a ComboBox), i want to achieve the following
functionality:

Case 1 (this I HAVE accomplished...)
- The user *has* selected a value from the ComboBox control (e.g. "ABC"):
Thus, [Forms]![FormName]![FormControl]="ABC"
In the VBA module, the VBA Function (Filter) becomes: Filter="ABC"
In the Query, the Criterion ( Filter([Forms]![FormName]![FormControl] ) now
equals: "ABC"
Thus, field "Customer" is properly filtered, and only records with "ABC"
appear.
This I HAVE accomplished.

Case 2 (this I HAVE NOT accomplished...)
- The user *has not* selected a value from the control:
Thus, [Forms]![FormName]![FormControl]=""
In the VBA module, the VBA Function (Filter) SHOULD evaluate to something
that enables ALL records of the field with the Criterion to be returned.
So, in the VBA code, i'd be lloking for something like:
Filter=" Like "*" " (this doesn't work of course)

That is, i want to have the VBA function (Filter) return such a value that
the criterion becomes Like "*" and all records appear.

Any help would be greatly appreciated.

Thanks,
Alex

 

[alex_hatzisavas]

Does the combo box return a Null or have you added a record to it to return
a value for All Records? Whatever that value is (Null or one you've created)
use it in the criteria as follows, no function is needed.

=[Forms]![FormName]![FormControl] or [Forms]![FormName]![FormControl] Is
Null

If the value of the combo box is Null, then the second half of the
expression returns True. If you use True as the criteria, you'll get all
records.

Thank you Wayne.
I think where i'm missing your point is the phrase:
"If you use True as the criteria, you'll get all records."
How do i do that please? Should i use another criteria line?

Using the expression:
[Forms]![FormName]![FormControl] or [Forms]![FormName]![FormControl] Is Null
as the criterion didn't work out, probably because i didn't understand that
"True as criteria" part.

Anyway, i think i have a wrong approach.
I should probably write an SQL string that takes into account which Controls
are active on the Form.

The functionality i'm going for is to have a set of filters (Controls on the
Form).
The user selects any number of them, and clicks on a CommandButton that runs
a Query, which runs on a Master Table where all the data resides.

I know how to construct the SQL string, but i'm not sure how to 'connect' it
to the Query (eg Query 1).
After searching here for a while, i came up with the following expression.
Could you please confirm it's correct?
(Final goal: run Query 1 on a Table using an SQL string to define criteria
etc.)

Dim strDocName as String, strSQL as String

strSQL="SELECT * FROM ... WHERE ..." ' etc.
CurrentDb.QueryDefs("Query1").SQL = strSQL
strDocName = "Query1"
DoCmd.OpenQuery strDocName, acNormal, acEdit

Hi all.

I'm calling a Public VBA Function (defined in a VBA module) from the
Criterion field of a Query in order to control the criterion of the query.

I'm having a problem making the VBA Function return an expression (such
as:
Like "*") that will enable the Query to return all the values for the
field
where the Criterion exists.
That is, i want the VBA function to evaluate to an expression such as "
Like
"*" ", that will make the Query return all records.

Here are the specifics:

Suppose the Query field is "Customer".
For field "Customer", i have written the following Criterion:
Filter([Forms]![FormName]![FormControl])

"Filter" is the Public VBA Function.
It accepts [Forms]![FormName]![FormControl] as an argument (this is the
value of a control -FormControl- on Form "FormName").
The query is activated via a CommandButton on Form "FormName".

So, on Form "FormName", the user clicks on the CommandButton that
activates
the Query.

Depending on whether the user *has* or *has not* selected a value from the
control "FormControl" (which is a ComboBox), i want to achieve the
following
functionality:

Case 1 (this I HAVE accomplished...)
- The user *has* selected a value from the ComboBox control (e.g. "ABC"):
Thus, [Forms]![FormName]![FormControl]="ABC"
In the VBA module, the VBA Function (Filter) becomes: Filter="ABC"
In the Query, the Criterion ( Filter([Forms]![FormName]![FormControl] )
now
equals: "ABC"
Thus, field "Customer" is properly filtered, and only records with "ABC"
appear.
This I HAVE accomplished.

Case 2 (this I HAVE NOT accomplished...)
- The user *has not* selected a value from the control:
Thus, [Forms]![FormName]![FormControl]=""
In the VBA module, the VBA Function (Filter) SHOULD evaluate to something
that enables ALL records of the field with the Criterion to be returned.
So, in the VBA code, i'd be lloking for something like:
Filter=" Like "*" " (this doesn't work of course)

That is, i want to have the VBA function (Filter) return such a value that
the criterion becomes Like "*" and all records appear.

Any help would be greatly appreciated.

Thanks,
Alex

 

[Wayne Morgan]

The way criteria works is that it looks for items that will cause the
criteria to return True. If you set the criteria to =3, then if the value in
the field is 3 you get 3=3, which is True and the value is returned by the
query. You can "trick" the query by simply typing True in for the criteria
and, since the result is always True, you'll get all records returned
(assuming no other fields limit which records are returned).

The second half of the criteria I mentioned will return True if the form
control's value is Null. While the first part of the Or statement fails, the
second part returns True. "False Or True" results in True. As mentioned
above, if the criteria is simply set to True, all records are returned.