Combo box fields selecting certain records 1

[LaurelLee]

Hi,
I am trying to get this query to work, and it won't budge. Please enlighten me!!
On a form, I have a combo box in which there are two fields, <>100 and >=0. I take this value and use it as my criteria within my query, but for some reason it is not pulling any records. When I hard code the <>100 or >=0, it works just fine. My boss seems to think it is interpreting these values as strings when they come from the form, and therefore not returning any values from the numeric field. Does this seem right to you all? And if so, what are the steps I need to take to get this to evaluate to a numeric field?

 

[TonyJollans]

Hi LaurelLee,

I think your boss is probably right. How are you getting these values into your Query? Can you post a bit more detail, please?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[scriverb]

Your boss is correct in that the query is trying to find a record with a value of "<> 100" as an example. You can however modify the actual SQL in the query to update the WHERE statement using the string coming from the combobox. Here is an example:

Dim db as DAO.Database
db.QueryDefs("[red]YourQueryName[/red]").SQL = Mid$(db.QueryDefs("[red]YourQueryName[/red]").SQL, 1, Instr(1,db.QueryDefs("[red]YourQueryName[/red]").SQL, "WHERE")+ 5) & " [[red]YourFieldName[/red]] " & FORMS![[red]YourFormName[/red]]![[red]cboYourComboName[/red]] & ";"
db.close
DoCmd.OpenQuery "[red]YourQueryName[/red]"

Now update the red code with the appropriate query name, form name, and combobox name. We may have to tweak it a little to get the syntax correct. You can check this by going into the query's SQL window and seeing what the code actually looks like as it will actually be changed in the code structure after the VBA code runs.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

Tony
I am using forms!frmSort!cbPCT in the criteria section within query builder. My combo box contains the values <>100 and >=0.

Bob
Where do I need to put this code? I am not sure which procedure this would be in. Right now, I have a form load, and a run report click. Do I need to put it somewhere new, or in one of these procedures?

Thank you both! Awaiting your replies....

 

[TonyJollans]

Hi LaurelLee,

If you just enter a field name in the criteria a default comparison operator of "=" is assumed; and you don't really have the option to parameterise (is that a real word?) the operator.

Designing a single query to do this will be awkward and some sort of coding will be required. If you just have these two operations you could do what Bob suggests and edit the SQL before you run it, or you could have two separate queries and run the appropriate one, but any workaround is going to be exactly that, and it really depends on the particular case how best to do it.


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[scriverb]

Just put the VBA code provided in the AfterUpdate event procedure of the combobox. It will modify your query and run the query after you make the pick.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

Bob,

A few questions/clarifications:

Should I delete the criteria that I have in my present query? Does this then replace any criteria for that particular field?

Also, this is actually happening in two queries that look identical but are pulling data that is different, which are then stuck in a union query, which is then plopped into a make table. I am doing it this way because my users don't like waiting half an hour for their report!
Can I use the same object for the second query, and do the same thing for the second query, or shoud I declare another object for the second one? I am not sure what kind of scope issues are at hand here.
Thanks for your advice!
Laurel

 

[LaurelLee]

Bob,

When I run the code you provided, I get an Compile Error: user type not defined, on the ...db as DAO.... line. I am not sure what is going on here, could you please elaborate as to why I may be getting this error? Thanks again...
Laurel

 

[scriverb]

You are probably using ACCESS 2000 or higher. You need to reference the MS DAO 3.6 object library. You can do this by opening your code window. You do this by opening a form in design view and click the Code button in the button bar. Now select the Tools menu and select References. Now select the MS DAO 3.6 Object Library and close the windows. The code should now run.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

Bob,
That did the trick. I am getting another error right now, but I will try to figure that out in the meantime. Could you please briefly explain this code to me? I am not sure how it works or what is going on in the code. I have looked for a simple explanation of what QueryDef is in the past, and I am still fuzzy as to why and when you use it, and just what it is! I know that it is a query definition, but that doesn't clarify it for me. If you could explain it in the context of your code, that would be very helpful!

I just read a post about poeple posting dumb questions. I apologize in advance if I appear that way to you! I am a newbie in ivery sense of the word. Thanks for your help.

 

[scriverb]

LauraLee: Fear not. There are no real dumb questions in this forum as long as you are trying to learn something. I will try to explain it to you.

Here is the ACCESS definition from the Help files:

A QueryDefs collection contains all QueryDef objects of a Database object in a Microsoft Jet database, and all QueryDef objects of a Connection object in an ODBCDirect workspace.

Now what this means is that in your database every saved query is a QueryDef object in the QueryDef collection. So, the query that you are trying to modify is in the QueryDef collection as an object with a name. We have dimensioned the current database as an Alias "db". Then we are making a reference to the db's QueryDef collection and specifically the saved query that you named "whatever". That object has a property called .SQL which is the actual Structured Query Language code generated when you created your query in the Query Design Window. If you have never seen this code just open the query in design and click the left button at the top and select SQL. You will now be looking at the code that we are trying to programmatically modify with the code that I provided you.

Now how does the code work? Let's use an example.

[red]Select A.Name, A.Address, A.City, A.State, A.Zip, A.Amount 
FROM Customers as A 
WHERE [/red]A.Amount >= 100;

The red portion of the code we do not want to change because that should be static. The expression behind the WHERE clause needs to be modified each time you run it.

What we want to do with the code is manipulate a large string of characters. First, we want to preserve the beginning of the string from character 1 thru the 5th character after the word "WHERE". The below green code does that.

db.QueryDefs("YourQueryName").SQL = [green]Mid$(db.QueryDefs("YourQueryName").SQL, 1, Instr(1,db.QueryDefs("YourQueryName").SQL, "WHERE")+ 5)[/green] & " [YourFieldName] " & FORMS![YourFormName]![cboYourComboName] & ";"

This code looks at the SQL string of the query and using a combination of Mid$(mid-string) and Instr(instring) functions selects characters 1 thru the 5th character past the beginning of the word WHERE or:

Select A.Name, A.Address, A.City, A.State, A.Zip
FROM Customers as A
WHERE

Now we want to cancatenate(add to this string of characters) the new expression to select the city. So using the ampersand(&) we add the name of the field & " A.[Amount] " Then adding on the value from the forms combobox by referencing it with the long reference. & "FORMS![formname]![comboboxname]". Then we add on the closing semicolon & ";"

After all of this, this SQL string is assigned in the expression to the db.QueryDef.SQL =

I hope that I have not confused you with all of this. If you have any questions please feel free to post back.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

Bob,

Thank you for the explanation. That really clears up QueryDefs, as well as your code. I appreciate you taking the time to do that!

Well, I am not getting any compile or run time errors anymore, which is good. But, alas, I am still seeing values that my sort should be filtering out. If this code is converting the strings "<>100" and ">=0" into an actual expression, I don't see it. This has to be my problem, as I don't see where else it could be! Any suggestions? I am at a loss with this, as I have been staring at it for 12 or so hours now over the last day or two.

Thanks again,
Laurel

 

[LaurelLee]

Oooops, I spoke too soon. Now, I am getting a run time error: Object variable or with block variable not set. I am guessing that I need to set db to something, although I thought that we were by using the SQL property of it. I really don't even know what to try here, or what to set it to.
??

 

[scriverb]

Yes, sorry left a line out. Right under the Dim db statement put in the following:

Set db = CurrentDB

Now run it.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

After you run the code successfully post the new modified SQL from your query. We should see an updated WHERE statement clause with the "operator" selection from your combobox.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

Right after I posted that I had a DUH moment, as I figured out the set thing.
Anyways, now I have yet another run time error. Invalid SQL statement, expected INSERT, UPDATE, etc...
As I understand the code to work, the SQL is contained within my existing query, which is called within the code. To put in another SELECT seems redundant to me. And when I have it in debug mode and mouse over the highlighted line, it does show that the query is being read. Hmmmm.....

 

[scriverb]

Copy the SQL from your saved query and post it here. Also, copy the code VBA code that you have put in the AfterUpdate of the ComboBox. let me take a look at it.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

Here is my query:

SELECT mtbl_Union_Pred_Holds.HULL, mtbl_Union_Pred_Holds.Predecessor_WP, mtbl_Union_Pred_Holds.act_c, mtbl_Union_Pred_Holds.pct_c, mtbl_Union_Pred_Holds.wp, mtbl_Union_Pred_Holds.pln_c
FROM mtbl_Union_Pred_Holds;

And here is the code:

Private Sub cbxPCT_C_AfterUpdate()

Dim db As DAO.Database

Set db = CurrentDb
db.QueryDefs("qryHolds_With_Filter").SQL = Mid$(db.QueryDefs("qryHolds_With_Filter").SQL, 1, InStr(1, db.QueryDefs("qryHolds_With_Filter").SQL, "WHERE") + 5) & " [PCT_C] " & FORMS![frmSort1]![cbxPCT_C] & ";"
db.Close
DoCmd.OpenQuery "qryHolds_With_Filter"

End Sub

 

[scriverb]

First thing the SQL code in your query MUST have the WHERE statement at the end of the query. Just open the SQL window and type in any legitimate WHERE statement. Doesn't matter what it is as long as the fields are legitimate and the syntax is correct. But, you must have the word WHERE in there.

Also, we need to update the VBA code that I provided to finish off the expression. We have put the FIELD NAME([PCT_C]) and then the value of the combobox(arith. Operator), but we failed to finish off the code and put the field or value to the right of the expression.

 "WHERE") + 5) & " [PCT_C] " & FORMS![frmSort1]![cbxPCT_C] & [RED]XXXXXXXXX[/RED] & ";"

Since you never have posted what this value is in your query I can't post a field name or a value. So, update the red X's with the appropriate comparison field name or value.

Do these things and everything should work fine.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[LaurelLee]

It seems to me that by putting in the value of the combo box it defeats the purpose of having a combobox. My two values are <>100 and >=0. How does this work, exactly, if the user is to choose which value they want and there is a value hard coded?

I need the values to be read as expressions, not as strings, and I need the user to be able to select one of these expressions. Is this what this code is doing?

I think that I have confused myself. Thanks again for your patience and help!