Vba DAO Recordset Parameter Query

[SteveTyco]

I am going to the well again.

I have a parameter query that I would like open a DAO recordset in VB. I
would like to make the Parameter "X" if something, else "Like *". How do I
do that ? I am trying...

Set qd = CurrentDb.QueryDefs(stDocName)
If IsNull([Forms]![1_GlobalVariables]![str_g]) Then
qd![Forms!1_GlobalVariables!str_g] = "Like ' * '"
Else
qd![Forms!1_GlobalVariables!str_g] = [Forms]![1_GlobalVariables]![str_g]
End If

But, it does not run the "Like *" properly. It does work if
([Forms]![1_GlobalVariables]![G]) is not null.
thanks in advance.

 

[david epsom dot com dot au]

No, you can't use "Like *" as a parameter. You are
getting this:
WHERE [g] = "Like ' * '"
You wanted:
WHERE [g] Like " * "

You can use something like this:
WHERE [g] Like [strParam]
and

If IsNull([Forms]![1_GlobalVariables]![str_g]) Then
qd![Forms!1_GlobalVariables!str_g] = " * "
else
qd![Forms!1_GlobalVariables!str_g] =
[Forms]![1_GlobalVariables]![str_g]
End If

or you can change the query even more and get rid of
the If/Then/Else statement:

WHERE ([g] = [Forms]![1_GlobalVariables]![str_g])
OR ([Forms]![1_GlobalVariables]![str_g] IS NULL)

(david)

 

[SteveTyco]

I must not have explained my self well enough. Let me try again. I have a
parameter query where the parameters are on a Hidden Global Variable Form.
If the parameter is set on the Global Variable Form (not null), I want that
value to be the parameter. If no value is set (null) on the Global Variable
Form, then I want to return all values from the query. The logic in the
criteria section on the field [str_GPL] of the parameter query is:
IIf([Forms]![1_GlobalVariables]![str_g] Is
Null,[str_GPL],[Forms]![1_GlobalVariables]![str_g]). When I run the query,
it works properly outside of VBA. To open this query as a DAO recordset, you
need to set the parameters in the querydef before you qd.openrecordset(). I
do this:

Set qd = CurrentDb.QueryDefs(strQueryName)
If IsNull([Forms]![1_GlobalVariables]![str_g]) Then
qd![Forms!1_GlobalVariables!str_g] = "*"
Else
qd![Forms!1_GlobalVariables!str_g] = [Forms]![1_GlobalVariables]![str_g]
End If
Set rs = qd.OpenRecordset()

But, the Like * does not work. I cannot make it = [str_GPL] like I do in
the query logic because there is no variable [str_GPL] until the query is
opened.

Thanks.

No, you can't use "Like *" as a parameter. You are
getting this:
WHERE [g] = "Like ' * '"
You wanted:
WHERE [g] Like " * "

You can use something like this:
WHERE [g] Like [strParam]
and

If IsNull([Forms]![1_GlobalVariables]![str_g]) Then
qd![Forms!1_GlobalVariables!str_g] = " * "
else
qd![Forms!1_GlobalVariables!str_g] =
[Forms]![1_GlobalVariables]![str_g]
End If

or you can change the query even more and get rid of
the If/Then/Else statement:

WHERE ([g] = [Forms]![1_GlobalVariables]![str_g])
OR ([Forms]![1_GlobalVariables]![str_g] IS NULL)

(david)

I am going to the well again.

I have a parameter query that I would like open a DAO recordset in VB. I
would like to make the Parameter "X" if something, else "Like *". How do I
do that ? I am trying...

Set qd = CurrentDb.QueryDefs(stDocName)
If IsNull([Forms]![1_GlobalVariables]![str_g]) Then
qd![Forms!1_GlobalVariables!str_g] = "Like ' * '"
Else
qd![Forms!1_GlobalVariables!str_g] = [Forms]![1_GlobalVariables]![str_g]
End If

But, it does not run the "Like *" properly. It does work if
([Forms]![1_GlobalVariables]![G]) is not null.
thanks in advance.

 

[SteveTyco]

I forgot to say the parameter query is 5 querys deep in the chain. I open
the last query in the chain to get the data.

I must not have explained my self well enough. Let me try again. I have a
parameter query where the parameters are on a Hidden Global Variable Form.
If the parameter is set on the Global Variable Form (not null), I want that
value to be the parameter. If no value is set (null) on the Global Variable
Form, then I want to return all values from the query. The logic in the
criteria section on the field [str_GPL] of the parameter query is:
IIf([Forms]![1_GlobalVariables]![str_g] Is
Null,[str_GPL],[Forms]![1_GlobalVariables]![str_g]). When I run the query,
it works properly outside of VBA. To open this query as a DAO recordset, you
need to set the parameters in the querydef before you qd.openrecordset(). I
do this:

Set qd = CurrentDb.QueryDefs(strQueryName)
If IsNull([Forms]![1_GlobalVariables]![str_g]) Then
qd![Forms!1_GlobalVariables!str_g] = "*"
Else
qd![Forms!1_GlobalVariables!str_g] = [Forms]![1_GlobalVariables]![str_g]
End If
Set rs = qd.OpenRecordset()

But, the Like * does not work. I cannot make it = [str_GPL] like I do in
the query logic because there is no variable [str_GPL] until the query is
opened.

Thanks.

No, you can't use "Like *" as a parameter. You are
getting this:
WHERE [g] = "Like ' * '"
You wanted:
WHERE [g] Like " * "

You can use something like this:
WHERE [g] Like [strParam]
and

If IsNull([Forms]![1_GlobalVariables]![str_g]) Then
qd![Forms!1_GlobalVariables!str_g] = " * "
else
qd![Forms!1_GlobalVariables!str_g] =
[Forms]![1_GlobalVariables]![str_g]
End If

or you can change the query even more and get rid of
the If/Then/Else statement:

WHERE ([g] = [Forms]![1_GlobalVariables]![str_g])
OR ([Forms]![1_GlobalVariables]![str_g] IS NULL)

(david)

I am going to the well again.

I have a parameter query that I would like open a DAO recordset in VB. I
would like to make the Parameter "X" if something, else "Like *". How do I
do that ? I am trying...

Set qd = CurrentDb.QueryDefs(stDocName)
If IsNull([Forms]![1_GlobalVariables]![str_g]) Then
qd![Forms!1_GlobalVariables!str_g] = "Like ' * '"
Else
qd![Forms!1_GlobalVariables!str_g] = [Forms]![1_GlobalVariables]![str_g]
End If

But, it does not run the "Like *" properly. It does work if
([Forms]![1_GlobalVariables]![G]) is not null.
thanks in advance.