Can I use Iif for this?

[optidisk]

I have a working query which has 3 fields derived from a table - Length, Grade & CDUB, following those fields I have a calculated field which is RAD that contains a formula RAD:=[CDUB]/2, following that another calculated field which contains VOL:=3.141593*[RAD]^2*[Length]/100000.
Now I want the VOL formula to make a choice;
if Grade = P then look for data in another table using the values in Length and CDUB or if Grade is anything else then 3.141593*[RAD]^2*[Length]/100000.
The data in the other table to be looked up has values such as;
Length 8.0, CDUB 2
Length 8.0, CDUB 4
Length 8.0, CDUB 6 and so on, there will be 14 different set lengths and each length has 5 set CDUB ratings so maybe a dlookup would work, this is where I am stuck.
Is this possible?

Cheers
optidisk

 

[raskew]

Could you provide a few rows of sample data?

Bob

 

[optidisk]

I have attached a sample db with the query in question showing the formulas that I currently use.
The data for the items with the 'O" and "Z" grade items is calculating correctly - the problem arises when the Grade is "P" then it must compare the Length and CDUB to the table tblPoles and retrieve the Volume for that item.

 

[optidisk]

If Iif won't work in a query for the above scenario what then can I use?

 

[Brianwarnock]

The simple approach is to use 2 queries your current one for O and Z grades the a new one for P grades joining tbllogdata and tblpoles on length and CDUB thus getting the Volume.

Brian

 

[optidisk]

2 queries sounds easy but the problem is that the original query for "O" and "Z" grades allows the user to enter CDUB and Length in a subform, thus entering those values into the subforms' associated table, with another query for "P" I don't want the user to be able to enter into the tblPoles but only to choose and then that data needs to be put in the same associated table as the "O" and "Z" grades.
Is this possible at all?
cheers
optidisk

 

[optidisk]

I have attached an updated sample which includes the form and sub form that the user enters log data into.
cheers
optidisk

 

[Brianwarnock]

Hmm, I was thinking that you were retrieving data from tables for a report, couple of select queries and maybe a Union. To do what you want at data input time I think that you will need to use DLOOKUP,

Boy was that fun, I couldn’t get the syntax right for the dlookup with 2 criterea fields, in the end wrote a function, still no good searched forum and found the answer in a post by Pat Hartman, who else!. After getting function to work got it correct directly in the query.

volume: IIf([grade]="P",DLookUp("volume","tblpoles","length =" & [length] & " And " & "cdub =" & [cdub]),3.141593*[RAD]^2*[Length]/100000)

and in the properties of the volume field code Format Fixed Decimal places 3

Hope this helps, it certainly brightened a dull day

Brian

 

[optidisk]

A thousand thanks, works like a charm.
This has been a fantastic learning adventure which would not have been possible without all your help.

cheers
optidisk