Lookup Combo on Form (1 Viewer)

[AN60]

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

 

[AccessMSSQL]

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

 

[AN60]

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

 

[AccessMSSQL]

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:

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