Problems with a Query Condition to create Barcode, please Help!

[ldiaz]

hello all
I have a query with these columns:
"WO" this column shows many Work Order numbers
"PlannerCode" this column shows the Plannercodes of the WOs, I have four:
ATM-05, BAM-06, JAXM-06 and DGM-05
"QTY" this column shows the QTY of the WO.

and I want to create a barcode in column "BarCode"
First condition:
IF PlannerCode is equal to ATM-05 the code must start with *ATF
IF PlannerCode is equal to BAM-06 or JAXM-06 the code must start with *BFG
IF PlannerCode is equal to DGM-05 the code must start with *DGF

then it needs to have the WO number and the 5 Last digits are designed for
QTY,
if the Qty is 1 the the last 5 digits must show as 00001* or if it's ten
then must be
00010* and so on.


Example:
WO PlannerCode QTY BarCode

124525 ATM-05 1 *ATF12452500001*
124526 ATM-05 1250 *ATF12452601250*

124520 BAM-06 100 *BFG12452000100*
124521 JAXM-06 32 *BFG12452100032*

124522 DGM-05 100 *DGF12452200100*
124523 DGM-05 10550 *DGF12452310550*

please help on this query,

Thanks in advanced.

Lorenzo Díaz
Cad Technician

 

[Douglas J. Steele]

Switch([PlannerCode] = "ATM-05", "*ATF",
[PlannerCode] = "BAM-06" OR PlannerCode = "JAXM-06", "*BFG",
[PlannerCode] = "DGM-05", "*DGF") & Format([WO], "000000") &
Format([Qty], "00000") & "*"

That having been said, I think you'd be better off having a second table
that maps PlannerCode to bar code prefix, rather than hard-coding like that.

 

[ldiaz]

Hi Douglas,

this code works perfectly, Thank you so much for your help.

I had never have listened about this 'Switch ' statment,


Thanks for your help, I really apreciate it.
--
Lorenzo Díaz
Cad Technician

Switch([PlannerCode] = "ATM-05", "*ATF",
[PlannerCode] = "BAM-06" OR PlannerCode = "JAXM-06", "*BFG",
[PlannerCode] = "DGM-05", "*DGF") & Format([WO], "000000") &
Format([Qty], "00000") & "*"

That having been said, I think you'd be better off having a second table
that maps PlannerCode to bar code prefix, rather than hard-coding like that.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

hello all
I have a query with these columns:
"WO" this column shows many Work Order numbers
"PlannerCode" this column shows the Plannercodes of the WOs, I have four:
ATM-05, BAM-06, JAXM-06 and DGM-05
"QTY" this column shows the QTY of the WO.

and I want to create a barcode in column "BarCode"
First condition:
IF PlannerCode is equal to ATM-05 the code must start with *ATF
IF PlannerCode is equal to BAM-06 or JAXM-06 the code must start with *BFG
IF PlannerCode is equal to DGM-05 the code must start with *DGF

then it needs to have the WO number and the 5 Last digits are designed for
QTY,
if the Qty is 1 the the last 5 digits must show as 00001* or if it's ten
then must be
00010* and so on.


Example:
WO PlannerCode QTY BarCode

124525 ATM-05 1 *ATF12452500001*
124526 ATM-05 1250 *ATF12452601250*

124520 BAM-06 100 *BFG12452000100*
124521 JAXM-06 32 *BFG12452100032*

124522 DGM-05 100 *DGF12452200100*
124523 DGM-05 10550 *DGF12452310550*

please help on this query,

Thanks in advanced.

Lorenzo Díaz
Cad Technician