Query - Use the same field to populate additional fields

[guyfromstl]

Hello,



I have a rather complex query where I need to populate ticket fees into two columns. The first column I want to return all ticket fees that are divisible by $12.00, and the second column I want to return all ticket fees that are divisible by $13.50. I thought I could use the field ticket fee twice and for the criteria use "like 12 or 24" for $12 values and use "like 13.50 or 27.00" for the $13.50 values. I also tried iif statements and both were unsuccessful. I am trying to determine the best way without having to revamp the query or make it more complex than necessary.

Thank you.

[rzw0wr]

You may want to look at the Mod() function.
Test for 0 returned number.

iif(mod(ticket/12)>0,Truesomething,iif(Mod(ticket/13.5)>0, TRUEsomething,FalseSomething))


Dale

[guyfromstl]

How would this work to have this same expression in two different fields so when I run the query I see two columns (one with $12's and one with $13.50's)

[rzw0wr]

Add the field twice.
One field
Twelve:Iif(Mod(Field/12)>0,true,false)
Other field
Thirteen:Iif(Mod(Field/13.5)>0 , true,False)

Dale

[guyfromstl]

The following is the syntax I used:
Twelve:Iif(Mod([ticket_fee_2]/12)>0,[ticket_fee_2],0
I get a popup box saying the expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier.

[June7]

Will all data be divisible evenly by either 12 or 13.50?

Try:

Column1: IIf([fieldname] Mod 12 = 0, [fieldname], Null)

Column2: IIf([fieldname] Mod 12 = 0, Null, [fieldname])
OR
Column2: IIf([fieldname] * 100 Mod 13.50 * 100 = 0, [fieldname], Null)

[rzw0wr]

Good grief Dale.
Thanks June7.
I was thinking more about how to get the problem solved than the syntax.

Sorry guy for the error in syntax for Mod().
Thanks again june7 for straightening me and the problem out.

Dale

[guyfromstl]

Thanks all for your help. This works perfectly. Time for me to do some research on the Mod() function.