Solved

Query criteria base on form string

Posted on 2003-11-04
10
323 Views
Last Modified: 2012-06-27
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
Comment
Question by:LisaPrice
  • 5
  • 5
10 Comments
 
LVL 46

Expert Comment

by:tbsgadi
Comment Utility
Try Like & Forms![frm Allocate]![AllStates]  

0
 

Author Comment

by:LisaPrice
Comment Utility
I tried that and got Invalid use of null.  I didn't choose TX and thats where it bombed out.
0
 
LVL 46

Expert Comment

by:tbsgadi
Comment Utility
How about Like & nz(Forms![frm Allocate]![AllStates])
0
 

Author Comment

by:LisaPrice
Comment Utility
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
 

Author Comment

by:LisaPrice
Comment Utility
Now I get "The expression you entered has an invalid .(dot) or ! operator or invalid parenthesis.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:LisaPrice
Comment Utility
Don't laugh but what does nz mean?
0
 
LVL 46

Expert Comment

by:tbsgadi
Comment Utility
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
 
LVL 46

Accepted Solution

by:
tbsgadi earned 95 total points
Comment Utility
instead of  "AR" Or "LA" Or "TX"
try  [AR] Or [LA] Or [TX]
0
 

Author Comment

by:LisaPrice
Comment Utility
It's working.  I looked up nz.  Alot simpler that IIf IsNull.  Learn something new everyday in this job.
0
 
LVL 46

Expert Comment

by:tbsgadi
Comment Utility
Glad to be of help :>
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now