In an attempt to make this as clear as possible here is what I am trying to do...

I have..

- db - Invoices

- db - Inventory (338 line Items)

- db - Codes (7 common discriptions, 42 possible codes)

The INVOICE db has a popup value ilst that inserts discription and unit price from INVENTORY (line items), Somehow i want a field on the INVOICE that will show a 'CODE' (one of 42) when the discription and the unit price add to a 'specified' The pricing calculations are working what i cannot get is the 'code' to come into a designated field..

- Example -

....Discription....Units....Unit price....Amount owed

.....Woozle-A.......(1).......$23.00........$23.00

the resulting code should be "A-1"

furthermore...

....Discription....Units....Unit price....Amount owed

.....Woozle-A.......(2).......$23.00........$46.00

the resulting code should be "A-2"

....Discription....Units....Unit price....Amount owed

.....Wample-A.......(2).......$23.00........$46.00

the resulting code should be "B-2"

- and so on..

I have tried unsucessfully to use the IF, as well as the CASE logical functions... any help would be greatly appreciated..

Did you mean to say "Wample-B" rather than "Wample-A" in your 3rd example? If so, then, is the prefix to your code the last character of the description? If so, then you can make the code a calculated field:

Code = Right(Inventory::Description,1) & "-" & NumToText(Units)

Better yet, why not have a CodePrefix field in your Inventory file that holds the prefix value for the product type. Then the calculation would be:

Code = Inventory::CodePrefix & "-" & NumToText(Units)

[ July 19, 2001: Message edited by: BobWeaver ]

Wooozles, wamples, whiples, whoolops, weeezles, wherabouts, whomz... who am i working for...Dr Suess?? unfortunately...the idea behind this little brain storm is to allow the over 300 items in the "INVENTORY" db to survive without having to go back and add an extra field to the db. My idea is that i have only 42 codes available so i have brought the entire Iventory db list down to (7) seven catergories....the Maximum a customer can purchase is 6 units in any catergory... for each transaction i would like a code generated .. in my example the "Wooozles" and Wamples" are the same price..so i want to put a differnt code (1/42) on each transaction.

(discription =woozles and extended price =$23), "A-1"

(discription =wamples and extended price =$23), "B-1"

i hope this helps clear my problem up alittle..Many thanks to all those cruising the forum and wondered...is it true that who's on first?

______________

looking forward to finding out WHAT i have done wrong

doC

Okay, you have 7 product categories and 6 possible quantities for a total combination of 42 codes. So, you will have codes that range from:

A-1, A-2....A-6

B-1...

C-1...

.

.

G-1, G-2....G-6.

Am I right so far?

The important question here is: How do you determine which of the seven categories a particular inventory item belongs to? If you don't already have one, you probably need a category field added to your inventory db, unless there is some very simple way to figure it out from the description field like so:

case(

Left(description,8)="Wooozles","A",

Left(description,7)="wamples","B",

Left(description,7)="whiples","C",

Left(description,8)="whoolops, ","D",

Left(description,8)="weeezles,","E",

Left(description,10)="wherabouts","F",

Left(description,5)="whomz","G",

"INVALID")&"-"&NumToText(Units)

THANK YOU Mr. Weaver....

If you are ever in my corner of the AB prairies look me up, we have a huge Tepee outside the back door..