code wild card value for form variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i use a check box in an option group to generate a specific value for a
variable.
using the following code

If Forms![fee sched form]![select region] = 2 Then
Forms![fee sched form]![region var] = "Roch"
End If
If Forms![fee sched form]![select region] = 3 Then
Forms![fee sched form]![region var] = "CNY"
End If

this variable is hidden on the form and value is used a query.

how do i code this, so i can put "*" in the variable and thereby get all
values in the query?

the query code generates like "*" but i do not know how to code via the
forms command

thanks for your help
 
You can set the text box to Null, and then in the query add the criteria

Select * From TableName Where FieldName = Forms![fee sched form]![region
var] Or Forms![fee sched form]![region var] Is Null

So if the value is null the query will return all records

=============
Also, instead of using a hidden object in the form with code you can set the
criteria in the query

Select * From TableName Where FieldName Like IIf(Forms![fee sched
form]![select region]=2,"Roch",IIf(Forms![fee sched form]![select
region]=3,"CNY","*"))
 
jnewl said:
i use a check box in an option group to generate a specific value for a
variable.
using the following code

If Forms![fee sched form]![select region] = 2 Then
Forms![fee sched form]![region var] = "Roch"
End If
If Forms![fee sched form]![select region] = 3 Then
Forms![fee sched form]![region var] = "CNY"
End If

this variable is hidden on the form and value is used a query.

how do i code this, so i can put "*" in the variable and thereby get all
values in the query?

the query code generates like "*" but i do not know how to code via the
forms command
Click to expand...


You didn'r say what condition(s?) indicates that * should be
used. Whatever it is, just use the same kind of thing that
you already have:

If somecondition Then
Forms![fee sched form]![region var] = "*"
End If

Set the field's criteria to:
Like Forms![fee sched form]![region var]
 
tried that before sending. what happens the quote marks get dropped, when
the value is stored in the variable. so instead of "*", you get *. the query
does not understand like *, so as a result get nothing.

so tried using a variable called quote and set that equal to "

then did region var = "like " & quote & "*" & quote.

got the same result of like *



Marshall Barton said:
jnewl said:
i use a check box in an option group to generate a specific value for a
variable.
using the following code

If Forms![fee sched form]![select region] = 2 Then
Forms![fee sched form]![region var] = "Roch"
End If
If Forms![fee sched form]![select region] = 3 Then
Forms![fee sched form]![region var] = "CNY"
End If

this variable is hidden on the form and value is used a query.

how do i code this, so i can put "*" in the variable and thereby get all
values in the query?

the query code generates like "*" but i do not know how to code via the
forms command
Click to expand...


You didn'r say what condition(s?) indicates that * should be
used. Whatever it is, just use the same kind of thing that
you already have:

If somecondition Then
Forms![fee sched form]![region var] = "*"
End If

Set the field's criteria to:
Like Forms![fee sched form]![region var]
Click to expand...
 
My mistake. Let's try setting the query criteria to

Like """" & Forms![fee sched form]![region var] & """"
--
Marsh
MVP [MS Access]

tried that before sending. what happens the quote marks get dropped, when
the value is stored in the variable. so instead of "*", you get *. the query
does not understand like *, so as a result get nothing.


Marshall Barton said:
jnewl said:
i use a check box in an option group to generate a specific value for a
variable.
using the following code

If Forms![fee sched form]![select region] = 2 Then
Forms![fee sched form]![region var] = "Roch"
End If
If Forms![fee sched form]![select region] = 3 Then
Forms![fee sched form]![region var] = "CNY"
End If

this variable is hidden on the form and value is used a query.

how do i code this, so i can put "*" in the variable and thereby get all
values in the query?

the query code generates like "*" but i do not know how to code via the
forms command
Click to expand...


You didn'r say what condition(s?) indicates that * should be
used. Whatever it is, just use the same kind of thing that
you already have:

If somecondition Then
Forms![fee sched form]![region var] = "*"
End If

Set the field's criteria to:
Like Forms![fee sched form]![region var]
Click to expand...
Click to expand...
 
Back
Top