Help with using text boxes to enter query criteria, running the query and reports

It would be simpler to put your query in code something like..........

dim sql as string
sql = "Select " & Nz([Forms]![frmPEERmenuA]![txtFY],"") & Nz([Forms]![frmPEERmenuA]![txtFmtNo],"")  &  Nz([Forms]![frmPEERmenuA]![txtJt],"")                      and so on. Be Careful tho I have not tested this.

Bob

IIf([Forms]![frmPEERmenuA]![txtFY] Is Not Null,[Forms]![frmPEERmenuA]![txtFY],"*")
should be:
IIf(IsNull([Forms]![frmPEERmenuA]![txtFY]), "*", [Forms]![frmPEERmenuA]![txtFY])
when imbedded in an IIf statement, use the VBA IsNull() function instead of the SQL Is Null.
Using a combobox would actually be easier bu using ALL and *.  This link will show you how to add "ALL" to a combobox:
http://www.mvps.org/access/forms/frm0043.htm
Change the "Null as AllChoice" to "* as AllChoice" in SELECT CustomerID, CompanyName FROM Customers UNION Select Null as AllChoice , "(All)" as Bogus From Customers ORDER BY CustomerID;

You don't need the line:
  If txtFY <> "" Or txtFmtNo <> "" Or txtJt <> "" Or txtSRG <> "" Or txtLicTy <> "" Or TxtSR <> ""

1) Do I need to set up each textbox with instructions to Requery?
No
2) Or does just opening the reports re-run the query and so uses the most recent text entries?
Yes
3) If I need to code the textboxes to requery, where would I put the code?  On Enter?
N/A
4) What code would I use?
N/A

>when I open a report, the report shows no records<
That's because "... then run the query, it returns no records."

thenelson,
OK,
I've deleted this line and the command button works fine.  Thanks.
If txtFY <> "" Or txtFmtNo <> "" Or txtJt <> "" Or txtSRG <> "" Or txtLicTy <> "" Or TxtSR <> ""

I've used your code for the criteria line in the query
IIf(IsNull([Forms]![frmPEERmenuA]![txtFY]), "*", [Forms]![frmPEERmenuA]![txtFY])

But when I have the form open and then run the query,it still returns zero records when it should return 3.

BobLamberson,
I'd like to try your code.  Not sure where it goes.  Do I paste it into the SQL view of the query?  Or does it go with the form?  Can you be really specific?

Thanks.

Another comment for thenelson:

I don't think adding an "all" choice for the combo box would solve my problem with the variety of answers available.

For example, the user might choose
1 or 7 or 8 or 9 for txtFrmNo     OR 1 or 2 or 4 or 5
and 1 or 2 or 3 for txtLicTy        OR 1 or 3
2003 or 2004 for txtFY               OR 2005

Then when I run the query, the code ends up being split up into 15-20 criteria lines under the various fields and then when I run the 'clear text boxes', the query retains all of those lines.  Don't know how to get rid of that.

You could put a button on your form, put the code in the button click event. Click the button when you are ready to create the query and this would create the sql query string. Use the query string to create a recordset and what ever else you are doing with the records.


Bob

BobLamberson
I'm needing more instruction - I get how to put the button on the form and the sql query string in the button click event.  But how does that populate the criteria in my query?

BobLamberson,
I copied your code into the onclick of a new button
dim sql as string
sql = "Select " & Nz([Forms]![frmPEERmenuA]![txtFY],"") & Nz([Forms]![frmPEERmenuA]![txtFmtNo],"")  &  Nz([Forms]![frmPEERmenuA]![txtJt],"")

I get a 'compile error syntax error' messagebox

and it highlights the first and third lines:
Private Sub Command96_Click()
Dim sql As String
sql = "Select " &

I've got to log off.  I'll check in early tomorrow morning.  Am hoping for more responses.
Thanks.

Use a multiselect list box.  In a new module, put code for each list box like this:
Public Function txtFrmNoGetString() As String
Dim vItm As Variant

With Me.txtFrmNo
    If .ItemsSelected.Count > 0 Then
       For Each vItm In .ItemsSelected
           txtFrmNoGetString = txtFrmNoGetString & .ItemData(vItm) & ","
       Next
   Else
       txtFrmNoGetString = ""
       Exit Function
   End If
End With
txtFrmNoGetString = Left(txtFrmNoGetString, Len(strList)-1)       'remove last comma
txtFrmNoGetString = "In (" & strList & ")"                    'put strList in parentheses

in the criteria row of the query you would then put:
txtFrmNoGetString()

The query would call the txtFrmNoGetString function which would return "In (1,7,8,9)" or "In (1,2,3,4)" or a zero length string for no criteria.

The standard way to build a criteria is to use BuildCriteria().

I have created a form with the six text boxes and two buttons: cmdClear and cmdQuery. In the second one, I create the query in six steps, making silly assumptions about the field names and field types. For instance, I treat txtLicTy to be meant for a date, called dtmLicTy, in the table tblMyTable that is going to be queried. You will need to adjust the field names and field types accordingly.

The procedure shows the criteria and then offers some suggestions as what to do with it ;)

When testing this, notice that you can type anything that would be accepted in the QBE grid:
    1 or 5 or 6
    Geneva Or Milano
    3 Oct
    Between 4 and 10
    Pal*
    >= 5.5 and < 6.5

With a little training and a few examples, the users will love it. Here is  the entire module:

------------------------------------------------------
Option Compare Database
Option Explicit

Private Sub cmdClear_Click()
   txtFY = Null
   txtFmtNo = Null
   txtJt = Null
   txtSRG = Null
   txtLicTy = Null
   txtSR = Null
   txtFY.SetFocus
End Sub

Private Sub cmdQuery_Click()

    Dim varCriteria As Variant

    varCriteria = Null
    If Not IsNull(txtFY) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("intFY", dbInteger, txtFY)
    If Not IsNull(txtFmtNo) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("bytfmtno", dbByte, txtFmtNo)
    If Not IsNull(txtJt) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("strJt", dbText, txtJt)
    If Not IsNull(txtSRG) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("ysnSRG", dbBoolean, txtSRG)
    If Not IsNull(txtLicTy) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("dtmLicTy", dbDate, txtLicTy)
    If Not IsNull(txtSR) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("dblSR", dbDouble, txtSR)

    MsgBox Nz(varCriteria, "no criteria")

    Exit Sub   ' samples below...

    ' store into a query definition for later use:
    CurrentDb.QueryDefs("qselTemp").SQL = "SELECT * FROM tblMyTable" & " WHERE " + varCriteria & ";"
   
    ' open a report:
    DoCmd.OpenReport "rptMyReport", acViewPreview, WhereCondition:=Nz(varCriteria)

    ' filter an open form:
    Forms!frmMyForm.Filter = Nz(varCriteria)
    Forms!frmMyForm.FilterOn = Not IsNull(varCriteria)

End Sub


Have Fun!

I forgot Null and Functions! Also accepted:
    Not Null
    Null or > Date()
    in(3,5,10) or > 10
etc...

thenelson,
A multiselect listbox!  That would be fantastic!  Never done that.  How do I create one???!!!

I copied and pasted the code txtFrmNoGetString() into the criteria line in the query

I copied and pasted the code into a new module, and now when I run the query, I get a message "Compile error: invalid use of the Me keyword"

Waiting for more instructions.



harfang,
I don't want to create a new query; I didn't mention that these are only 6 of MANY fields in the query, many of which perform calculations.  So I need to work with the query I've already created.

thenelson,
just to be sure, I've copied and pasted the code in my module back here:  (I changed your 'txtFrmNo' to 'txtFmtNo' to match my field name.

Public Function txtFmtNoGetString() As String
Dim vItm As Variant

With Me.txtFmtNo
    If .ItemsSelected.Count > 0 Then
       For Each vItm In .ItemsSelected
           txtFmtNoGetString = txtFmtNoGetString & .ItemData(vItm) & ","
       Next
   Else
       txtFmtNoGetString = ""
       Exit Function
   End If

txtFmtNoGetString = Left(txtFmtNoGetString, Len(strList) - 1)     'remove last comma
txtFmtNoGetString = "In (" & strList & ")"                    'put strList in parentheses

harfang,
In this code, what does the 'txtFY.SetFocus' do?  My code does everything up to that point, and the command button seems to clear the textboxes ok.

I will work with your other suggestions also (thanks), but if it means creating a new query, that seems like more work than I want to do to get this problem fixed.  I'm almost done with this database, its' been a huge amount of work, and I want to be finished!

Private Sub cmdClear_Click()
   txtFY = Null
   txtFmtNo = Null
   txtJt = Null
   txtSRG = Null
   txtLicTy = Null
   txtSR = Null
   txtFY.SetFocus
End Sub

thenelson,
OK, I looked up 'creating multiselection list boxes' in my Access Reference and I'll try it.  It does say "To use the multiple selections, however, you must define a program by using Visual Basic for Applications to capture the selections"
I'm hoping your code above does that.
I'll be back with more comments

K.S. Schneider,

The instruction "txtFY.SetFocus" simply puts the focus into the text box. This seemed a nice thing to do: after clearing the criteria, the user wants to write a new one...

> "I don't want to create a new query"

That is not a problem. Let's say that your query is called "qryAllMyData", and you don't want to include all that SQL into the VB coding (which I understand). In that case, you can create _another_ query, based on the first one, just for the filtering.

For example, create a dummy query (any query at all) called "qselUserSelection". Then the following line will rewrite it each time with the needed WHERE clause for filtering:

    ' store into a query definition for later use:
    CurrentDb.QueryDefs("qselTemp").SQL = "qselUserSelection * FROM qryAllMyData" & " WHERE " + varCriteria & ";"
 
Naturally, the original query needs to output the six fields used in the filtering.

Furthermore, thenelson's suggestion also produces just a criteria. You will have the same problem: how to incorporate the resulting criteria string into your query. The query by itself can only use text boxes in a crude fashion, and not at all multi-select list boxes, so you *will* need something like what I suggest.

Please try the solution using BuildCriteria. Create a command button, and paste the code above. Adjust for your true field names for each text box and for their data type (Long, Integer, Text, Date, etc). I'm sure you'll like it.

Good Luck!

thenelson:

I've created a multi-select listbox for Fiscal Year called FYlist
It gets it's list from the FYID (an autonumber field) from the FiscalYear table

The form is unbound.

I've copied the code that I have in a module below

When I run the query, first I get a message box that says
  Enter Parameter Value - FiscalYear.FiscalYear_ID
If I click through (OK), then I get another message box that says:
 Microsoft Visual Basic: Compile error: Invalid use of Me keyword

Here's the module code
  Public Function FYlistGetString() As String

  Dim vItm As Variant

  With Me.FYlist
    If .ItemsSelected.Count > 0 Then
       For Each vItm In .ItemsSelected
           FYlistGetString = FYlistGetString & .ItemData(vItm) & ","
       Next
   Else
       FYlistGetString = ""
       Exit Function
   End If

  FYlistGetString = Left(txtFmtNoGetString, Len(strList) - 1)     'remove last comma
  FYlistNoGetString = "In (" & strList & ")"                    'put strList in parentheses

Thanks again, I really want this to work!

Stepping in for thenelson about "Compile error: invalid use of the Me keyword"

This is because this code only works on the form, not from a standalone module. You can put the function in the form's module. If you then need it from elswhere, use something like this:

    strInClause = Forms!frmNameOfForm.txtFmtNoGetString()

It is also possible to change the line:
    With Me.txtFmtNo
to
    With Forms!frmNameOfTheForm.txtFmtNo
and put this in a global module, say as FmtNoGetString()

However, you cannot use the function from a query. if you place the call to the function into the QBE grid, you will generate something like this:

    WHERE (intFmtNo = FmtNoGetString())

The function will happily provide something like this "In (3,7,10)", and JetEngine will then evaluate this:

    WHERE (intFmtNo = "In (3,7,10")

which will never be true, of course. What you really meant was:

    WHERE (intFmtNo In(3, 7, 10)

which will be true for the relevant records.


To sum it up: my suggestion builds a wide range or criteria for the six fields. thenelson's suggestion builds a "In()" criteria for one field based on a multi-select list. In both cases, you will have to do something with the resulting string, as the query can't do anything with it.


Good Luck!

> FYlistGetString()

Again, same problems: it cannot work in a global module (replace Me. with a referenct to the form like Forms!frmFormName); it cannot be used by a query, only in another piece of VB to produce a filter, a criteria or a new query.

Cheers

OK, harfang, I will try what you're suggesting.  It may take awhile.  I'm not able to work on this again until this evening or tomorrow.  Please check back and see if I'm successful!
Thanks.

harfang,
how does one "store into a query definition for later use"?  is this a module
Thanks

The idea is this:

A query is basically a text string with a question written in SQL, such as "SELECT datYear FROM tblMyData WHERE bytType = '3';", with some additional display settings. In this simple sample, there are three "clauses": SELECT, FROM and WHERE. In this thread, we are concerned by this last bit: the WHERE clause.

A query can thus exist as a simple string in another object's propert, such as the form's .RecordSource property (no need for the additional display options in that case). A query can also be created through VB. In this thread, we have been building strings to append after the reserved word "WHERE".

A stored query, one that you can see in the database window, can be altered through VB code. So, if you have a string variable containing a valid SQL string, like:
    strMySQL = "SELECT strName & ', '+strFirstName FROM tblPeople WHERE snFriends"
You can write this tring into the existing query, e.g. "qselFriends", like this:
    CurrentDb.QueryDefs!qselFriends.SQL = strMySQL

The next time you open the query, it will use the new SQL string, which you can also observe in the query's SQL view. (Using the button or the menu View/SQL View.)

Does that clarify things?

For the sake of completeness, Access often uses criteria, defined as "a WHERE clause without the word WHERE". So this is a valid criteria (if the field names are meaningful, of course):
    strCriteria = "strCity Like 'Gen*' Or strCountry = 'CA'"
Such criteria can be used when opening some objects (forms and reports) and to filter an open form.


Good Luck!

Thanks, I'll post something tomorrow.

More on SQL query language:
If you open your query in the query builder (desgn view with the criteria row) and go to queries> SQL view, you will see the SQL statement for that query.  When you build a query in design view, Access writes the SQL for you in the background, saves the SQL and runs the query from the SQL.  That's why sometimes when you close the query builder and reopen it, some of the items are rearranged.

So with that, if you paste the SQL for your query here, we can help your further.

OK, harfang, I’m hoping you have the patience to continue to walk me through this:

My original query is qryallpeer
I’ve created a new one called qselUserSelection

QUESTION:
Should my reports now be based on my original one or the new UserSelection one?  I’m using the new one.

I’ve created one text box called “txtFY” –a number (1 = 2003, 2=2004) because the field is an autonumber field

In the query qselUserSelection grid, the criteria is [Forms]![frmPeerMenu]![txtFY]

I created a command query button and entered the code (see below):

If I enter a ‘2’ in the txtFY box, and click a button to open a report based on the qselUser, I get a messagebox asking to Enter Parameter Value – FiscalYear_ID
QUESTION: why?

If I click on my command query button, I get a ‘Microsoft Office Access’ message box saying intFY=2
QUESTION: What is this button for?

If I open the report from the report window, first I get a ‘Enter Parameter Value FiscalYear_ID’
QUESTION: why

The SQL code for my qselUserSelection is:
Select  *
FROM qryallpeer
WHERE (((qryallpeer.FiscalYr_ID)=[Forms]![frmPeerMenu]![txtFY]));

QUESTION
I still don’t quite understand where/how to “store into a query definition for later use” (this is from one of your earlier comments)
CurrentDB.QueryDefs(“qselTem”).SQL = “qselUserSelection * FROM qryallpeer” & “WHERE” + varCriteria & “;”

QUESTION: Do I copy and paste this into the SQL view of either the qselUserSelection or the QRYALLPEER (if so, does it go AFTER the WHERE?)

COMMAND BUTTON CODE (I think I’m missing something)
Private Sub cmdQuery_Click()

    Dim varCriteria As Variant

    varCriteria = Null
    If Not IsNull(txtFY) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("intFY", dbInteger, txtFY)
   
    MsgBox Nz(varCriteria, "no criteria")

    Exit Sub
   
     End Sub

> Should my reports now be based on my original one or the new UserSelection one?  I’m using the new one.
correct

> In the query qselUserSelection grid, the criteria is [Forms]![frmPeerMenu]![txtFY]
This is irrelevant. We are going to rewrite that query each time! (see below)

> I get a messagebox asking to Enter Parameter Value – FiscalYear_ID
This means that the query uses something called FiscalYear_ID, but that this is not the exact name of any field in the source.
I'm guessing this is due either to a spelling mistake or to the fact that qryallpeer does _not_ expose that field, or exposes it _twice_.
Check in the list of fields above the query grid to see if that field exists and is spelled like this.

> QUESTION: What is this button for?
It will be used to _write_ the query qselUserSelection ...

Private Sub cmdQuery_Click()

    Dim varCriteria As Variant

    varCriteria = Null
    If Not IsNull(txtFY) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("FiscalYr_ID", dbLong, txtFY)   ' <--- note field name and type!
   
    ' this is just a check, remove when everything works...
    MsgBox Nz(varCriteria, "no criteria")

    ' now (re-)write the query qselUserSelection
    CurrentDb.QueryDefs!qselUserSelection.SQL = "SELECT * FROM qryallpeer" & " WHERE " + varCriteria & ";"

    ' open the query from here if you like:
    DoCmd.OpenQuery "qselUserSelection"
 
End Sub

The two lines I added will write a new SQL string into the query and then open it for inspection.
Note that it will not work before you solved the missing FiscalYr_ID problem...

More in the next posting... ;)

> QUESTION:
> Should my reports now be based on my original one or the new UserSelection one?  I’m using the new one.

Do not read this now, wait until the previous works, it will make things clearer.

The difference  between qryallpeers and qselUserSelection is little. In fact, qselUserSelection only adds a WHERE clause. So that you have two solutions:

1) Base reports on qselUserSelection. This will be rewritten each time the button cmdQuery is pressed, and the next time the report will apply the specific WHERE clause it contains.

Advantage: You can inspect qselUserSelection in case of problems. You can even write code that will test the validity of the query before using it for something else.

2) Base reports on qryallpeers. When opening a report from VB, it is possible to add a WHERE clause from the "outside". This is done using the parameter WhereCondition, as in:

    DoCmd.OpenReport "rptAllPeers", acViewPreview, WhereCondition:=varCriteria

Advantage: This does not use a separate query, and it also works for queries not stored in the database window (the report's .RecordSource can contain a full SQL string, not available from the outside...).


We will talk more about that once you have the first part running.

Finally (for the time being):

> I’ve created one text box called “txtFY” –a number (1 = 2003, 2=2004) because the field is an autonumber field

I don't see the point. I would define the fiscal year like this:
    Name: intFiscaYr
    Type: Number
    Size: Integer
    Default Value: Null

If you want that as key field, just select it and press the "key" button from the toolbar (use context-menu / primary key). This way, you can get rid of a meaningless autonumber and display the true year instead of a code. As an integer takes even less room that a Long, I see only advantages.

But maybe this is not the right time to change the design of the data model. Just keep it in mind for the future.

Cheers!

You are so helpful!
Do I finally get it?  The Command Query Button is actually running a query written behind the 'on click'
So I don't need to be pasting that info anywhere else, e.g. in the qryallpeer SQL view?

I redid my field to take your recommendation about not using the autonumber - my new field name is intFY

So I now have the textbox, txtFY and
the command button with the code be;pw:

When I enter 2003, and click the command query button, I'm getting a Visual Basic error messagebox that highlights ".SQL" in the line that starts "Currentdb....."
and says "Compile error: Invalid use of property"

Here's the code:
Private Sub cmdQuery_Click()

    Dim varCriteria As Variant

    varCriteria = Null
    If Not IsNull(txtFY) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("intFY", dbInteger, txtFY)
   
    MsgBox Nz(varCriteria, "no criteria")
   
    CurrentDb.QueryDefs!qselUserSelection.SQL -"SELECT * FROM qryallpeer" & " WHERE " + vaarCriteria & ";"
   
    DoCmd.OpenQuery "qselUserSelection"
   
         End Sub

Need to leave for a few hours, will check back later.
Thanks

   CurrentDb.QueryDefs!qselUserSelection.SQL -"SELECT * FROM qryallpeer" & " WHERE " + vaarCriteria & ";"
                                           -------------------^

This needs an equal sign.
Also, vaarCriteria is not spelled correctly ;)

Cheers!

harfang,
Thanks, I fixed the equal sign and the varCriteria
Now when I click the commandquery button, I get a MS Office Access messagebox " intFY=2004" and then when I click OK, it runs the query.

Then when I open the report based on qselUserSelection - the report is accurate.
This is like magic!

Is there a way to 'hide' the opening of the query?  I don't really want the user to view and then close the query.  Can I write the commandquery code into the instructions for each of my report buttons instead?  So that the query automatically executes behind the scenes and the user just sees the report?

I'll work on adding the other textboxes and code and let you know if I require more assistance.

Thank you so much for everything thus far!

OK, I'm working on adding additional textboxes
My code thus far is below

Here's what's happening:
If I enter 2003 for the FY and yes for StateReg,
I get a messagebox confirming intFY=2003 AND strStateReg="Yes"
Then I get a messagebox asking for a parameter value for strStateReg, and if I click through that, the query returns no records.
The StateRegional field is a text field (I'm using that instead of a yes/no because text data is imported as yes or no)

Another example:
If I enter 2004 for the FY and "1 Or 2 Or 3" for the Licensee Type, I get the messagebox confirming those values, but then a box asking for a parameter value for intLicTyNo

Same thing happens if e.g. I enter the year 2003 and "1 Or 4" for Format Number - confirming messagebox and then 'Enter Parameter Value intFmtNo'

Here's the code:

Private Sub cmdQuery_Click()

    Dim varCriteria As Variant

    varCriteria = Null
    If Not IsNull(txtFY) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("intFY", dbInteger, txtFY)
   
     If Not IsNull(txtLicTyNo) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("intLicTyNo", dbInteger, txtLicTyNo)
   
         If Not IsNull(txtFmtNo) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("intFmtNo", dbInteger, txtFmtNo)
   
           If Not IsNull(txtStReg) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("strStateReg", dbText, txtStReg)
   
    MsgBox Nz(varCriteria, "no criteria")
   
    CurrentDb.QueryDefs!qselUserSelection.SQL = "SELECT * FROM qryallpeer" & " WHERE " + varCriteria & ";"
   
    DoCmd.OpenQuery "qselUserSelection"
   
         End Sub

Now I've added a textbox for "StationID"; the field IS an autonumber; I know that's not the best way to do it, but I may have to keep this one.
Isn't an autonumber an integer?
This code doesn't work at all - I get into debugging, with the entire text highlighted:          

     If Not IsNull(txtStID) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("intStation_ID", dbInteger, txtStID)

Congratulations on that part. You have seen it working for one field, I'm sure you are motivated now ;)

> 'hide' the opening of the query?

Sure, just comment out the lines:
    ' MsgBox .....
and:
    ' DoCmd.OpenQuery .....

(or delete them if you no longer require them).
The important line is the writing of the query into qselUserSelection.

About the error messages, make sure you fully understand the syntax of this:

     If Not IsNull( <CONTROL> ) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("<FIELD>",  <FIELD TYPE>,  <CONTROL> )
 
<CONTROL> is the name of the text box where the user will enter the criteria
<FIELD> is the name of the field in qryallpeers for which the criteria is intended
<FIELD TYPE> is one of dbInteger, dbLong, dbDouble, dbText, dbBoolean, or dbDate (a few others, but this will do)

As I did not know the actual field names from your database, I invented them. You probably do not have a field "intLicTyNo" or "intFmtNo". Please adjust the lines so that they match the true field names (LicTyNo and FmtNo?).

Finally:

> This code doesn't work at all - I get into debugging, with the entire text highlighted:
I need to know the message that appears before you enter debugging.
(And an AutoNumber is a "long integer", but both dbLong and dbInteger can be used for BuildCriteria()...)

BTW: I assume that the module has the line "Option Explicit" near the top? And also that you select "Debug / Compile project" after each modification?


Good Luck with the rest!

You're right, I'm SUPER MOTIVATED.
I fixed the field names.  
Now the only thing that's happening when I click on the command query button is this VB message:
Run-time error '7952
You made an illegal function call.
And this part of the code comes up highlighted when I click on debug:
 varCriteria = varCriteria + " AND " & BuildCriteria("Station_ID", dbLong, txtStID)

I'll show the entire code below.

About your comment:
BTW: I assume that the module has the line "Option Explicit" near the top? And also that you select "Debug / Compile project" after each modification?
QUESTION
NO, my module doesn't say that - do you mean that I should add that code somewhere to the code below?
I'm working in the visual basic window that opens up after I click on the 'on click' section of the commandquery button properties.  Is this a module?  Or does it have to be something on the db module page ( tables / queries /forms etc)?
Also, in the VB window, I did click on debug but I don't see a 'Compile project' option, just 'compile db1'

Here's my code with all of the textboxes I've created:

Private Sub cmdQuery_Click()

    Dim varCriteria As Variant

    varCriteria = Null
    If Not IsNull(txtFY) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("intFY", dbInteger, txtFY)
   
       If Not IsNull(txtSRG) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("SRG", dbText, txtSRG)
   
      If Not IsNull(txtJT) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("Joint", dbText, txtJT)
   
           If Not IsNull(txtLoc) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("Location", dbText, txtLoc)
     
     If Not IsNull(txtLicTyNo) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("LicTyNo", dbInteger, txtLicTyNo)
   
         If Not IsNull(txtLH) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("LH", dbInteger, txtLH)
   
         If Not IsNull(txtFmtNo) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("FmtNo", dbInteger, txtFmtNo)
   
         If Not IsNull(txtMkRk) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("MarketRank", dbInteger, txtMkRk)
   
           If Not IsNull(txtStReg) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("StateReg", dbText, txtStReg)
       
               If Not IsNull(txtStID) Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("Station_ID", dbLong, txtStID)
   
    MsgBox Nz(varCriteria, "no criteria")
   
    CurrentDb.QueryDefs!qselUserSelection.SQL = "SELECT * FROM qryallpeer" & " WHERE " + varCriteria & ";"
   
    DoCmd.OpenQuery "qselUserSelection"
   
         End Sub

Thanks, Merci, Danke!

Hello,

> Runtime Error 8952

The only way I found to reproduce this error was to call BuildCriteria with a blank string:

    ? BuildCriteria("field", dbLong, "")

The problem is that I don't know how you managed to enter a blank string into the control txtStID... I could not reproduce that. Either the control has some special properties, or it's name is not exactly txtStID ;)

If the value "" is a possible value of the control, we need to test for that as well, as in:

    If Not IsNull(txtStID) Then If txtStID <> "" Then _
        varCriteria = varCriteria + " AND " & BuildCriteria("Station_ID", dbLong, txtStID)

But I doubt that this is the problem. For the time being, comment the line out (add a single quote before it - the line should turn green), so as to test the  rest.

> Option Explicit

Once you are in the module (yes, that is where all the code above resides, in the "class module" behind the form), scroll up, or use Ctrl+Up until you reach the very first lines of the module. Normally, there  are:

Option Compare Database
Option Explicit

The second, the "option explicit" ensures that all vairables and control names used in the code exist. Thus, once you select "Debug / Compile <project name>", you will be notified if you made any spelling  mistakes. If you do not have the line "Option Explicit", please add it.
Note: The name of your project seems to be "db1".

> Here's my code

I found nothing wrong with it. It compiles and runs fine, provided all controls exist on the form with that spelling...

However, I found a logical problem when combining conditions with OR. In fact, each line should be slightly altered to avoid locigal problems in some cases like this:

    If Not IsNull(<CONTROL>) Then _
        varCriteria = varCriteria + " AND " & "(" & BuildCriteria("<FIELD>", <TYPE>, <CONTROL>) & ")"

Notice the added opening and closing brackets around each criteria. I should have thought of it from the start...

Good Luck

Hello harfang,

I did everything you instruct above, and it's working without a hitch.  Sorry about the StID textbox name, I didn't have it exactly right, so that one is working fine now as well.

And thanks for thinking of and solving the logical problem with combining conditions.

This is beyond my wildest hopes!  And now I'll be getting out my textbooks on Visual Basic and studying!

Thank you SO MUCH!

I just wanted to check again with you to be sure there isn't anything else you want to add before I close this question.

Once you check back in, I'll close out and award your points.

Will Do!

Can't thank you enough!

You are welcome!