How to get Nested IIF statements to stay number 1

[cimoli]

I am having trouble getting a calulated query field to remain as a NUMBER. It seems to automatically become a text field which then messes up the calculated field afterwards.

I have a qryDetail. Amongst the fields are 4 kinds of ID's per record that are number long interger fields.

I made 2 calculated fields in the qry. The first calculated field is named EmailIDused and contains the many IIF statements to determine which of the 4 ID's to use. For some reason, this calculated field is left justified appearing as Text. I was hoping it would be a number.

The second calculated field EmailUsed assumes it is looking at EmilIDused as a number.
So it bombs out when it finds EmailIDused is text.

The first 4 fields are number fields in a table.
ID

SendGroupTicketToCode

SendSplinterTicketToCode

SendLineRunTicketToCode

The next 2 calculated fields are in the qry.

EmailIDused: Nz(IIf(IsNull(Nz([SendGroupTicketToCode])) Or IsNull(Nz([SendSplinterTicketToCode])) Or IsNull(Nz([SendLineRunTicketToCode])),[tblDetail].[ID],IIf(Nz([SendGroupTicketToCode]) Is Not Null,Nz([SendGroupTicketToCode]),IIf(Nz([SendSplinterTicketToCode]) Is Not Null,Nz([SendSplinterTicketToCode]),IIf(Nz([SendLineRunTicketToCode]) Is Not Null,Nz([SendLineRunTicketToCode]))))))

The above gets me the correct answer. However, it is left justified as text.
I wanted it to remain as a number field.


My plan was to use EmailIDused to do a lookup into the customer table for the proper email
address to use.

EmailUsed: DLookUp("","tblCustInfo","[tblCustInfo]![ID]=Nz([EmailIDused])")

When i run the Qry, I get the message
"the expression you entered as a query parameter produced this error, microsoft cannot find the name EmailIDUsed".

I tried making a parameter long interger for the calculated field EmailIDused but it did not help. Cimoli
Thanks for the help.

 

[PHV]

What about this ?
EmailIDused: IIf(IsNull([SendGroupTicketToCode]+[SendSplinterTicketToCode]+[SendLineRunTicketToCode]),tblDetail.ID,IIf([SendGroupTicketToCode] Is Not Null,[SendGroupTicketToCode,IIf([SendSplinterTicketToCode] Is Not Null,[SendSplinterTicketToCode],IIf([SendLineRunTicketToCode] Is Not Null,[SendLineRunTicketToCode],0))))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cimoli]

Hi - I tried the above and get an error message, 'wrong # of arguments".

I was about to write back that my EmailIDused iif's were wrong. You must have figured that out.

Could you try again? This is hard. As a reminder, I am asking the EmailIDused field to evaluate 4 fields and pick the one that has an answer and post to EmailIduser.

And then EmailIduser becomes the code to do a dlookup for the proper email address to use.

thanks so much PHV. I bet you are close. Probably just a comma or something.

 

[cimoli]

Update: As a test, I hard coded the calculated field EmailIdUsed with a number 106934.
EmailIdUser:106934 inside the qry.

Ran it. The 2nd calculated field barked. Says that it cannot find the name EmailIdUser.

EmailUsed: DLookUp("","tblCustInfo","[tblCustInfo]![ID]=[EmailIdUsed]")

So even if we figure out the IIF syntax for EmailIdUser, it looks like I have a second problem.
Which using emailIdUser to help go get a real email address.

thanks for checking.

 

[cimoli]

Just not to mislead you. If the 3 "sendto" fields are blank, then i wanted the ID field for emailIduser. If any 1 of the 3 "sendto" fields have a number in it, I want emailIduser to use that number. There can only be 1 answer per record in those 3 "sendto" fields.

Sometimes, all 3 may be blank and i would use the ID field for emailIduser.

just wanted to clarify. thanks

 

[PHV]

wrong # of arguments
???
EmailIDused= IIf(IsNull(SendGroupTicketToCode+SendSplinterTicketToCode+SendLineRunTicketToCode),tblDetail.ID,IIf(SendGroupTicketToCode Is Not Null,SendGroupTicketToCode,IIf(SendSplinterTicketToCode Is Not Null,SendSplinterTicketToCode,IIf(SendLineRunTicketToCode Is Not Null,SendLineRunTicketToCode,0))))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

EmailUsed: DLookUp("","tblCustInfo","[tblCustInfo]![ID]=" & [EmailIdUsed])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Anyway, a simpler way:
EmailIDused: Nz(SendGroupTicketToCode,Nz(SendSplinterTicketToCode,Nz(SendLineRunTicketToCode,ID)))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cimoli]

YES !!! It works great. Nice job. I will send a token of appreciation. Cimoli