Query Trouble - Probably easy

[brokenwh]

First of all I am quite new to Access and on on a short deadline, so please bear with me.

I have created a query that lists class numbers. I need the query to limit the class numbers based on an ID field off of a form.

I think I have the ID field related to the form correctly, but I am having problemslimiting the class numbers shown.

My goal is to get a combo box in a form to list ALL Class Numbers if the ID is below 500 and then all Class Numbers above 2000 if the ID is over 500.

Any help would be greatly appreciated!!

 

[Fizzio]

You will either need 2 separate rowsources for the combo tha you select on the Form_Current event or dynamically build it with VBA on the same event. Post the SQL of your query and I'll try to build the code around it.

 

[brokenwh]

HEre's the SQL

Thanks

I think this is what you are wanting. I hope it is explainable.

SELECT [Classes&Premiums].ClassNo, [Classes&Premiums].ClassDesc, Exhibitors.ExhibitorNo
FROM Exhibitors, [Classes&Premiums]
WHERE ((([Classes&Premiums].ClassNo)=IIf([Forms]![frmPlacings]![ExhibitorNo]<500,[OCClasses]![ClassNo],[Classes&Premiums]![ClassNo])) AND ((Exhibitors.ExhibitorNo)=[Forms]![Placings1]![ExhibitorNo]))
ORDER BY [Classes&Premiums].ClassNo;

 

[Fizzio]

Actually, thinking about it again, your IIf may work, just I think you may have the criteria not quite right.

Try this instead

SELECT [Classes&Premiums].ClassNo AS Expr1, [Classes&Premiums].ClassDesc AS Expr2, Exhibitors.ExhibitorNo AS Expr3
FROM Exhibitors, [Classes&Premiums]
WHERE ((([Classes&Premiums].[ClassNo])=IIf([Forms]![frmPlacings]![ExhibitorNo]<500,([Classes&Premiums].[ClassNo])>0,([Classes&Premiums].[ClassNo])>2000)) AND (([Exhibitors].[ExhibitorNo])=[Forms]![Placings1]![ExhibitorNo]))
ORDER BY [Classes&Premiums].ClassNo;

 

[brokenwh]

Almost

Thanks Fizzio

I think we are really close. I had a few errors that I worked out.

The only problem is my query is empty with any of the ID numbers tested.

Anyother ideas?

Thanks

 

[Fizzio]

Is ClassNo a text field or numerical?

 

[brokenwh]

It is a number field as a Double Field Size

 

[Fizzio]

Try This

SELECT [Classes&Premiums].ClassNo, [Classes&Premiums].ClassDesc, Exhibitors.ExhibitorNo
FROM Exhibitors, [Classes&Premiums]
WHERE (((IIf([Forms]![frmPlacings]![ExhibitorNo]<500,[Classes&Premiums].[ClassNo]>0,[Classes&Premiums].[ClassNo]>2000))) AND (([Exhibitors].[ExhibitorNo])=[Forms]![Placings1]![ExhibitorNo]))
ORDER BY [Classes&Premiums].ClassNo;

 

[brokenwh]

THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!

That worked.

Thanks for all of your help!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[Fizzio]

No problem - got the grey matter working overtime though!