• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

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
0
LisaPrice
Asked:
LisaPrice
  • 5
  • 5
1 Solution
 
tbsgadiCommented:
Try Like & Forms![frm Allocate]![AllStates]  

0
 
LisaPriceAuthor Commented:
I tried that and got Invalid use of null.  I didn't choose TX and thats where it bombed out.
0
 
tbsgadiCommented:
How about Like & nz(Forms![frm Allocate]![AllStates])
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LisaPriceAuthor Commented:
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"
0
 
LisaPriceAuthor Commented:
Now I get "The expression you entered has an invalid .(dot) or ! operator or invalid parenthesis.
0
 
LisaPriceAuthor Commented:
Don't laugh but what does nz mean?
0
 
tbsgadiCommented:
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!
0
 
tbsgadiCommented:
instead of  "AR" Or "LA" Or "TX"
try  [AR] Or [LA] Or [TX]
0
 
LisaPriceAuthor Commented:
It's working.  I looked up nz.  Alot simpler that IIf IsNull.  Learn something new everyday in this job.
0
 
tbsgadiCommented:
Glad to be of help :>
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now