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
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
Try Like & Forms![frm Allocate]![AllStates]
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])
ASKER
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"
ASKER
Now I get "The expression you entered has an invalid .(dot) or ! operator or invalid parenthesis.
ASKER
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It's working. I looked up nz. Alot simpler that IIf IsNull. Learn something new everyday in this job.
Glad to be of help :>