Lookup Combo on Form

AN60

Registered User.
Local time
Today, 22:23
Joined
Oct 25, 2003
Messages
283
In my db I have the following in access 2007;

tbl
qry
form (with a combo to look up data)
rpt

My data tbl has several fields but the one I wish to lookup values in is a number field which contains either positive or negative numbers.

I wish to select either positive, negative or all amounts(not making a selection in the combo) by using a combo on the form. in the past I have used combo boxes many times to look up stuff when the data is more specific but as this time I'm dealing with numbers I would like to select either <0 or >0 or if the combo is blank the report will show everything. Does anyone have any suggestions?

Ian
 
You will need to use some VBA code in the after update of the combo box. Set up your combo box with a Value list: <0;>0;

In the after update event of the combo box perform you selection criteria:

if combobox = "<0" then
select * from tbl where field < 0
else
select * from tbl where field > 0
end if
 
Thank you for your speedy reply.

I set up my combo with two choices <0 & >0.

In the after update event I encountered a problem when I typed the * symbol you have shown above. Should I be substituting it with something relevant to my data?

Where you have tbl I have my table name & where you have field I put my field name.

Ian
 
sorry, my code was not syntactically correct. I was just giving an example.

If you are trying to filter the form based on the combo box selection then you need to change it to something like this:

Code:
Dim strSQl as string
 
if combobox = "<0" then
strsql = "select * from tbl where field < 0"
else
strsql = "select * from tbl where field > 0"
end if 
me.recordsource = strsql
 

Users who are viewing this thread

Back
Top Bottom