Solved

Query criteria base on form string

Posted on 2003-11-04
10
328 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
ID: 9685650
Try Like & Forms![frm Allocate]![AllStates]  

0
 

Author Comment

by:LisaPrice
ID: 9686433
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
ID: 9686468
How about Like & nz(Forms![frm Allocate]![AllStates])
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:LisaPrice
ID: 9686533
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
ID: 9686555
Now I get "The expression you entered has an invalid .(dot) or ! operator or invalid parenthesis.
0
 

Author Comment

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

Expert Comment

by:tbsgadi
ID: 9686996
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
ID: 9687009
instead of  "AR" Or "LA" Or "TX"
try  [AR] Or [LA] Or [TX]
0
 

Author Comment

by:LisaPrice
ID: 9687126
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
ID: 9687183
Glad to be of help :>
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

785 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