Like...or problem calculating w/2 values

[tweetyksc]

This is probably going to be very easy...
Background:
I have a database that I calculate commissions on. All the commissions are in one big data file, the employee numbers indicate whether it is "regular" or "excess" commission. For one company, this was easy, if it was regular, it began with an 11 (11*), if excess the number began with AT (AT*).
I created two fields in the query -RegComm, that shows a value if number is 11, 0 if not. Same for a field called ExComm - if the number was AT, value, 0 if not. This way I could break out the regular and excess.
Problem:
I'm now working on a company that has two prefixes for exces (GL and MN) and also numerous employee number prefixes.
I simply can't even get working the equation for the excess to compute when looking for "GL*" or "MN*".
I can get it to work for either singly, but when I put the OR in there it just reports everything.
Here's the expression:
-------
ExCom$: IIf([employee number] Like "GL*" Or "MN* ",[Com Amount],0)
-----------
I'm also going to use this for the regular comm, since there are numerous numbers not standard, and just swap the t/f placement.

Such a simple thing...do I need to do a complete compound IIF?

 

[KKilfoil]

It is usually 'bad form' to try to make a single field do more than one thing.

The employee number is probably not the best place to keep 2-state 'yes/no' information like "regular" or "excess commission".

What are you going to do when ANOTHER company is added? Going through the tangled logic is confusing, and you may get contradictory prefixes with new companies.

What I would do is add another field for each record for CommissionType.

If there can only ever be two choices, create a Yes/No field called 'Excess', where a 'Yes' or checked value means Excess, and a 'No' means Regular.

If there can be multiple types beyond the two you have mentioned, you might want to create a separate CommissionType table with a lookup from your existing table.


Once you have a value in your table that explicitly states the type of commission, your queries should get very simple.

 

[tweetyksc]

Well, I got this working, had to go back to basic Like functions and think about it differently... basically using brackets and looking for alpha characters.

I'm posting just in case someone else runs across this and is stuck with a similar problem.

Ex of data:

Empl# Name Commission
115066 Mary Brown 50.00
135067 Joe Brown 50.00
GL5066 Mary Brown 25.00
MN5067 Joe Brown 10.00

Each empl. has two numbers: 1 indicating regular commission (all numbers) and with GL or MN indicating excess. I had to break out and give total for each.

I created two fields in query, one for regular (Com$) and one for excess (ExCom$) which will run for each line in the data table.

---------
Com$: IIf([employee number] Like "[A-Z]*",0,[Com Amount])
ExCom$: IIf([employee number] Like "[A-Z]*",[Com Amount],0)
---------
The query will now show:

Empl#.........Name...........Commission.....Com$.....ExCom$
115066....Mary Brown... .......50.00........50.00.........0.00
135067....Joe Brown..............50.00....... 50.00.........0.00
GL5066....Mary Brown...........25.00....................... 25.00
MN5067....Joe Brown ......... ..10.00........................10.00


Again, very basic but sometimes you get stuck because you can't see the forest for the trees...