Query criteria base on textbox string

[Eprice]

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 <> &quot;&quot; Then
'Add OR if other criteria exists
If ArgCount > 0 Then
MyCriteria = MyCriteria & &quot; Or &quot;
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 = &quot;&quot;
'Use values in state boxes to create criteria
AddToCriteria [AR], &quot;[AR]&quot;, MyCriteria, ArgCount
AddToCriteria [KY], &quot;[KY]&quot;, MyCriteria, ArgCount
AddToCriteria [LA], &quot;[LA]&quot;, MyCriteria, ArgCount
AddToCriteria [SD], &quot;[SD]&quot;, MyCriteria, ArgCount
AddToCriteria [TX], &quot;[TX]&quot;, MyCriteria, ArgCount
AddToCriteria [PR], &quot;[PR]&quot;, 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 &quot;AR&quot; Or &quot;LA&quot; Or &quot;TX&quot; 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

 

[Eupher]

I don't think this will yield a SQL WHERE clause that your query can recognize. Try this instead:

Public Sub AddToCriteria(FieldValue As Boolean, 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 & "State = '" & Mid(FieldName, 2, 2) & "'")

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

Private Sub GetString_Click()
Dim MyCriteria As String, ArgCount As Integer
Dim CurDB As Database
Dim qdefMyQuery As QueryDef
Dim SQLStmt As String

'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     'this is now unnecessary
Set CurDB = CurrentDb()
Set qdefMyQuery = CurDB.QueryDefs("qryAllocate")
SQLStmt = "SELECT * FROM tblCriteriaTest WHERE " & MyCriteria & " ORDER BY State;"
qdefMyQuery.SQL = SQLStmt
'Debug.Print qdefMyQuery.SQL
DoCmd.OpenQuery ("qryAllocate")

Set qdefMyQuery = Nothing
Set CurDB = Nothing
End Sub

In this example I'm assuming a table named &quot;tblCriteriaTest&quot; and a query called &quot;qryAllocate&quot;. Change the references as appropriate for your DB.

HTH...

Ken S.

 

[Eprice]

This gives me a Type mismatch Run-time error 13 and takes me to
AddToCriteria [AR], &quot;[AR]&quot;, MyCriteria, ArgCount

The FieldValue is a text value so I changed it to
Dim FieldValue As String and used
If FieldValue <> &quot;&quot; Then

Now I get runtime error 94 Invalid use of null.

Got any ideas.
Lisa

 

[Eupher]

Sorry, I skipped a step, I failed to see that the checkbox was writing a value to a textbox. The code should work if you make the FieldValue argument Boolean, and replace [AR], [KY], etc., in the the AddToCriteria lines with Me!ckAR, Me!ckKY, etc. Then the code will test for a true or false value in the checkboxes, and assemble the WHERE clause string accordingly.

Ken S.

 

[Eprice]

OK, it works using Dim FieldValue As String and
If FieldValue <> &quot;&quot; Then ...
as long as I surround tbl Allocations with brackets. Thanks for all your help.