Link to home
Start Free TrialLog in
Avatar of LisaPrice
LisaPrice

asked on

Query criteria base on form string

Need help, should be easy - Access Xp:  I want criteria in a query's field to come from a value in a text box on a form.  For the control source of the text box I use code:

Public Sub AddToCriteria(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)
    'Create Control Source for AllStates TextBox
    If FieldValue <> "" Then
        'Add OR if other criteria exists
        If ArgCount > 0 Then
            MyCriteria = MyCriteria & " Or "
        End If
        'Append criteria to existing criteria
        'Enclose Fieldvalue in quotation marks
        MyCriteria = (MyCriteria & Chr(34) & FieldValue & Chr(34))

        'Increase argument count
        ArgCount = ArgCount + 1
    End If
End Sub

'This is a button I am using just for test until it works.
Private Sub GetString_Click()
Dim MyCriteria As String, ArgCount As Integer
'Initialize argument count
ArgCount = 0
MyCriteria = ""
'Use values in state boxes to create criteria
    AddToCriteria [AR], "[AR]", MyCriteria, ArgCount
    AddToCriteria [KY], "[KY]", MyCriteria, ArgCount
    AddToCriteria [LA], "[LA]", MyCriteria, ArgCount
    AddToCriteria [SD], "[SD]", MyCriteria, ArgCount
    AddToCriteria [TX], "[TX]", MyCriteria, ArgCount
    AddToCriteria [PR], "[PR]", MyCriteria, ArgCount

Me![AllStates] = MyCriteria
End Sub

This all comes from check boxes.  If ckAR is checked then textbox AR = 'AR' and so on...  The string part works, If i check AR & LA & TX I get  "AR" Or "LA" Or "TX"  in the [AllStates]text box.  Now I want the query that runs when a button is clicked to use this as criteria for the State field.  If I use
=Forms![frm Allocate]![AllStates]  
in the criteria I get no records.  How can I make this work?? Thanks
Lisa
Avatar of tbsgadi
tbsgadi
Flag of Israel image

Try Like & Forms![frm Allocate]![AllStates]  

Avatar of LisaPrice
LisaPrice

ASKER

I tried that and got Invalid use of null.  I didn't choose TX and thats where it bombed out.
How about Like & nz(Forms![frm Allocate]![AllStates])
I was trying new things yesterday and changed FieldValue As Varient to String.  When I changed that back to Variant I didn't bomb on the form but when I open the query to see what data is in it I get " Data type mismatch in criteria expression"
Now I get "The expression you entered has an invalid .(dot) or ! operator or invalid parenthesis.
Don't laugh but what does nz mean?
I wouldn't laugh at you...

Nz is New Zealand

What has that got to do with anything?

Not a lot.

NZ is also Non_Zero..It stops errors when you have a null function
nz(a,3) means everytime you have a null put 3 in place.
Also not alot of use.
nz(a,0) is more useful!
ASKER CERTIFIED SOLUTION
Avatar of tbsgadi
tbsgadi
Flag of Israel image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It's working.  I looked up nz.  Alot simpler that IIf IsNull.  Learn something new everyday in this job.
Glad to be of help :>