Comparing fields in a query

[TB99]

I am trying to produce a query in Access 2003 that will itemize the part #'s that are on back order or have not been received.

Two fields are OrddQty and OrddReceivedQty . I simply want to know all values where the OrddReceivedQty is less than the OrddQty. This will tell me what has not been received and what has.

in layman's terms the "formula" in the OrddReceivedQty criteria would be = < OrddQty.

What is the proper syntax for this?

[Seth Schrock]

Just add to your current WHERE clause or create a WHERE clause if you don't have one already. Here is what it would look like:

WHERE OrddReceivedQty <= OrddQty

This query will then only show those records.

[NeoPa]

In Query Design view you could create a field called BackOrder :

BackOrder: [OrddQty]>[OrddReceivedQty]

Alternatively, if you didn't need to see this value in your query, but only needed to show those items that are on back order then :

Field =    [OrddQty]
Criteria = >[OrddReceivedQty]

[Rabbit]

You pretty much have the syntax so I'm not sure what you're asking.

SELECT *
FROM someTable
WHERE OrddReceivedQty < OrddQty

[NeoPa]

Rabbit:
You pretty much have the syntax so I'm not sure what you're asking.

They were asking for how this would be expressed within Design View of a QueryDef. Specifically, what to put in the Criteria part. I'm guessing they didn't realise that the natural expression they'd already arrived at would have worked.

@TB99
I would suggest you try the very basics before posting a question in future. I'm sure you had managed to get yourself confused by the time you posted, but it's always a good idea to check the basics before posting.

[Seth Schrock]

I don't see the part where TB99 asked for it in design view.

[NeoPa]

TB99:
in layman's terms the "formula" in the OrddReceivedQty criteria would be = < OrddQty.

I never said it was expressed correctly or clearly Seth, but if you look carefully you can infer it from what was said.