Solved

Query criteria base on form string

Posted on 2003-11-04
10
331 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

730 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