Multi-parameter Select Query (1 Viewer)

[Noob1972]

Hello everyone - hoping someone can help a newbie who has zero VBA experience and very limited SQL knowledge.

I created a form that has 3 multiple drop-down selections that is supposed to select records from [TableName_1] based on those selections. I initially created the query using the query designer with actual "hard coded" selections to make sure it worked. When it gave me the expected records, I changed the criteria from the "hard coded" option to the input form's control reference [Forms]![FormName]![ComboName]. I did this one at a time and tested after each change.

After the first change, I got exactly what I expected. YAY

After the second change, it seemed okay. YAY (note: I put the criteria on
the first criteria line in the query designer, not on the second line)

After the third change, AAAAAAAARRRRRGGGGHHHHHH!

I get the dreaded: The expression is typed incorrectly or is too complex to be evaluated....

Here is the SQL:

SELECT M_Lending_Institution.InstitutionName, M_Lending_Institution.GeoRegionID, M_Lending_Institution.SpecialtyID, M_Lending_Institution.SBA
FROM (SELECT M_Lending_Institution.InstitutionName, M_Lending_Institution.GeoRegionID, M_Lending_Institution.SpecialtyID, M_Lending_Institution.SBA FROM M_Lending_Institution) AS LenderSearchQuery
WHERE (((LenderSearchQuery.GeoRegionID.Value)=[Forms]![LoanSearch]![CmbPrefGeo]) AND ((LenderSearchQuery.SpecialtyID)=[Forms]![LoanSearch]![CmbSpecialtyArea]) AND ((LenderSearchQuery.SBA)=[Forms]![LoanSearch]![CmbSBA]));

------
And once this is sorted out, yes I want to be able to allow for all records to be returned if any of those fields is NULL. I've read a LOT of posts around this subject, but I can't seem to find anything that answers my question without jumping into advanced VBA code.

I'm not afraid to use VBA but I haven't a clue how to begin. These forum posts often assume that we newbies know where to type this information. I've opened the VBA designer, but it's intimidating when you don't know where you're putting stuff or what the commands do.

Thanks in advance for any advice or information you can give.

 

[Noob1972]

Another weirdism with the query - if i change what line the criteria are on, I get different results - and often not correct ones. I'm sure that has something to do with AND vs. OR, but I couldn't figure it out. I can explain what I want to have this do, but I can't seem to figure out the step-by-step logic. (Not a computer science pro.)

 

[JHB]

Without your database and some sample data, it is hard to say what is wrong!

 

[Noob1972]

I've attached the database - all of this is "dummy data" anyway.

The Queries I'm struggling with are:
LenderSearchStruggle - has filters I'm struggling with
LenderSearchQuery - simple query with NO filters

The form I use (or want to use) to input the parameters is:
LoanSearch

I have another search on this same form and it works fine - pulls a totally different query.

Anyway... I feel like this SHOULD be simple - but I'm just not getting it.

Also - note - once I get the query to pull the correct records with ALL form fields filled in, I ultimately want to get the query to disregard any fields that aren't filled in and return all those records.

For example: If I select a geographical region (Northeast) but I don't fill out a specialty or other criteria, I ONLY want to filter by geographical region. As it is now, I am forced to enter in all criteria or I get empty results.

 

[JHB]

...
I have another search on this same form and it works fine - pulls a totally different query.

Anyway... I feel like this SHOULD be simple - but I'm just not getting it.
...

Sorry, but you have fallen into the trap of using lookup field types in your tables.

Yes you've another search, but you haven't any criteria for the fields which have the lookup field type!

 

[JHB]

Your database was in a way corrupt, I imported it into a new database and change your query, now it works!

 

[Noob1972]

Thank you so much! This helped with the Query - and I can see now how to continue to add more parameters to the query going forward. Can you tell me what you did to "fix" the query - and also - what did you mean by "corrupt in a way."

A few questions on the new database you posted... it seems to have "broken" some of the other tables and forms. I tried to use some of my other forms and they don't work the way they used to. I don't know which way to turn now...

I really appreciate what you've done so far

 

[JHB]

..Can you tell me what you did to "fix" the query - and also - what did you mean by "corrupt in a way."

You couldn't refer to a control on the form from the query, (normally you're able to do that), so something was wrong - Corrupt.

A few questions on the new database you posted... it seems to have "broken" some of the other tables and forms. I tried to use some of my other forms and they don't work the way they used to. I don't know which way to turn now...

Now I've imported the working form and the query into your old database, so everything is as you send it, except for the two changes.

 

[Noob1972]

This is GREAT, thank you!

Question for you... I see the logic to add more and more parameters to the query - though the number of criteria lines increases exponentially with each new parameter. Is there a limit as to how many I can have? I thought I recalled reading that the SQL tops out at some point.

With 3 parameters = 2^3 = 8
With 4 parameters = 2^4 = 16
With 5 parameters = 2^5 = 32

How far can I go?

 

[JHB]

Below is a link to the limits of MS-Access.
http://www.databasedev.co.uk/access_specifications.html

 

[Noob1972]

More Parameter Woes...

Thanks to you, JNB, I've managed to develop on this theme and have working parameter queries. Now, I've stumbled onto a NEW bug (still in the parameter query vein) - not sure if this should be a new thread. Please pardon my newbie way of trying to explain it - I attached my database as well.

I added ONE more parameter to a query with logical expressions:

enter values X Y and Z
where X >=[M_Loan_Parameters]![LoanMinimum]
and Y >=[M_Loan_Parameters]![CompanyEBITDAMin]
and Z >=[M_Loan_Parameters]![LoanMinimum] And <=[M_Loan_Parameters]![LoanMaximum]
​

This worked great UNTIL I tried to add a new option from a dropdown menu selection:

enter values X Y and Z and now Q
where X >=[M_Loan_Parameters]![LoanMinimum]
and Y >=[M_Loan_Parameters]![CompanyEBITDAMin]
and Z >=[M_Loan_Parameters]![LoanMinimum] And <=[M_Loan_Parameters]![LoanMaximum]
and Q is pulled from a dropdown or is Null
​

What happens is sort of weird - the results don't pull the correct values based on the numbers entered in X Y or Z. I tried changing the order of operations on the Query Design screen, but it didn't "stick". I wonder if I need to change something in the SQL? I tried messing with the INNER vs. LEFT JOINs, but that didn't seem to help so I put it back to the system generated SQL.

(BTW - the Queries/Forms I'm referring to are LoanSearch, LoanSearchQuery)

Thoughts?

 

[JHB]

Could you show which value to put in where and also what you get and what you expected to get, because it is difficult for me to decide what result is right or what is wrong.

 

[Noob1972]

Yes - I entered the following into the form:
Company Rev = 100
Company EBITDA = 100
Min Loan Needed = 3000
Loan Type = BLANK/NULL

I got loans returned that exceeded the loan MAXIMUM. The other parameters seem to be okay. Recall that I have the loan search parameters set to return loans BETWEEN the min and max. Also, if i delete the Loan Type parameter entirely from the query design, this works just fine. ALSO, if I enter something into the Loan Type, it works okay - including excluding the incorrect results BETWEEN the loan min and loan max. It must be a problem with how the SQL works with the Null entry.

Thank you soooo much! You've been a real lifesaver - as well as teaching me a lot about how to do these queries.

-Jenny

 

[JHB]

Did you get the problem solved?

 

[Noob1972]

No - I answered your questions above that you asked for clarification on... but I was unable to figure it out on my own. (Was in meetings last week and replied a bit late - sorry). Can you take a look? The database was posted on 1-17-2015.

 

[JHB]

The differens is this, (the problem is the grouping in criteria, either must FinancingType be equal to the selection in the combo box or the combo box is empty, that is ONE criteria)!

...
WHERE ((((L_Financing_Types.FinancingType)=[Forms]![LoanSearch]![CboFinType] Or [Forms]![LoanSearch]![CboFinType] Is Null)) AND (([Forms]![LoanSearch]![TxtCompanyRev])>=[M_Loan_Parameters]![LoanMinimum]) AND (([Forms]![LoanSearch]![TxtCompEBITDA])>=[M_Loan_Parameters]![CompanyEBITDAMin]) AND (([Forms]![LoanSearch]![TxtCompLoan])>=[M_Loan_Parameters]![LoanMinimum] And ([Forms]![LoanSearch]![TxtCompLoan])<=[M_Loan_Parameters]![LoanMaximum]));

WHERE (((L_Financing_Types.FinancingType)=[Forms]![LoanSearch]![CboFinType]) AND (([Forms]![LoanSearch]![TxtCompanyRev])>=[M_Loan_Parameters]![LoanMinimum]) AND (([Forms]![LoanSearch]![TxtCompEBITDA])>=[M_Loan_Parameters]![CompanyEBITDAMin]) AND (([Forms]![LoanSearch]![TxtCompLoan])>=[M_Loan_Parameters]![LoanMinimum] And ([Forms]![LoanSearch]![TxtCompLoan])<=[M_Loan_Parameters]![LoanMaximum])) OR ((([Forms]![LoanSearch]![CboFinType]) Is Null));

Database attached, run "Copy Of LoanSearchQuery".
Maybe you should consider to create the query on the fly, then it could be much more easier to maintain as what you've in "LenderSearchFIlteredQuery".

 

[Brianwarnock]

first a disclaimer. I Don't have ACCESS any more and its a while since I used it, I just happened to browse this thread.

This Query looks as though it was created in the design grid owing to its excess (()), I always started in the DSG to save typing and its error potential, but when you get a "or parameter Is Null" in the criteria such as you now have saving from the DSG creates a mess, so from this point on I would stay in SQL view, and I would break each criteria onto a new line making it easier to read and maintain.

Brian

 

[Noob1972]

Thank you both for your help.

Maybe you should consider to create the query on the fly, then it could be much more easier to maintain as what you've in "LenderSearchFIlteredQuery".

What do you mean "on the fly" ? I looked at the Database you attached, but I can't get it to work either. Am I missing something? (I did use the "Copy of LenderSearchQuery" instead of my original "LenderSearchQuery".) It does look simpler in the Design View, but I don't follow what you've done or how I need to set up the criteria inputs properly.

I'm stuck home due to a snowstorm today, so I will dig further - but any additional help is appreciated. I am still so new at this

Thank you - Jenny

 

[JHB]

... I looked at the Database you attached, but I can't get it to work either. Am I missing something? (I did use the "Copy of LenderSearchQuery" instead of my original "LenderSearchQuery".)

I don't know if it is a typo but the query name is "Copy Of LoanSearchQuery" and not "Copy of LenderSearchQuery".
Below is a picture of result when I put in the values you gave me.

 

[JHB]

..
What do you mean "on the fly" ?

By creating the query and the criterias using code!

Open the search form and click the button "JHB-OnFly".
Below is the code behind the button.

  Dim dbs As DAO.Database, qdfNew As QueryDef, sqlString As String
    
  Call SletQuery("NewLenderSearchFIlteredQuery")
  Set dbs = CurrentDb
  sqlString = "SELECT InstitutionName, GeoRegionName, SpecialtyCategory, SBA, EquityDebt, LenderType " _
  & "FROM LenderSearchFullQuery Where "
  If Not IsNull(Me.CboGeography) Then
    sqlString = sqlString & "GeoRegionName='" & Me.CboGeography & "' AND "
  End If
  If Not IsNull(Me.CboSpecialty) Then
    sqlString = sqlString & "SpecialtyCategory='" & Me.CboSpecialty & "' AND "
  End If
  If Not IsNull(Me.CboSBA) Then
    sqlString = sqlString & "SBA=" & Me.CboSBA & " AND "
  End If
  If Not IsNull(Me.CboEqtyDebt) Then
    sqlString = sqlString & "EquityDebt='" & Me.CboEqtyDebt & "' AND "
  End If
  If Not IsNull(Me.CboBankNon) Then
    sqlString = sqlString & "LenderType='" & Me.CboBankNon & "' AND "
  End If
  sqlString = Left(sqlString, Len(sqlString) - 5)
  Set qdfNew = dbs.CreateQueryDef("NewLenderSearchFIlteredQuery", sqlString)
  DoCmd.OpenQuery "NewLenderSearchFIlteredQuery", acViewNormal