Full Version: How do code this?
hmho
I have form that we will enter data (Numbers) by barcode reader and we have to different kinds barcodes. What I would like to do is formula that says if the first three digits is less than or equal 899 delete the last five digits else delete the last seven digits. Is this possible. here are two examples of the barcodes and what I need the outcome to be.

1. 89912345678 = 899123

2. 90012345678 = 9001

Edited by: hmho on Mon Jun 1 22:44:02 EDT 2009.
MeMedea
Hello,
That data type are you using for the bar code field?
Trish
hmho
Hi MeMedea
I'm using Microsoft Access 2007.
MeMedea
Not version, data type of the barcode field; I would think only TEXT would work and you can't do < or = with a text.
hmho
Oh I'm sorry the fields are numbers.
MeMedea
I guess I'm confused....a number field won't hold a forward 0 (ei: 0123456). What number type? I am not familiar with 2007, so maybe that's my problem.
orry, I can't be more assistance.
Trish
hmho
You are right what i mean is the first three digit equal or less 899 delete the last five digits else delete the last seven digits.
. 89912345678 = 899123
2. 90012345678 = 9001
MeMedea
Here you go...
xpr1: IIf(Left([BARCODE],3)<900,Left([BARCODE],7),Left([BARCODE],5))
Hope this helps
Trish
WildBird
Are they always the same length? In this case 11?
On a query, could use
iif(left([YourFieldName],3) < 900,left([YourFieldName],6),left([YourFieldName],4))
Can continue to store whole field, and strip off the characters needed in reports or queries etc.
hmho
Hi Wildbird,
o they are not always the same length. but there are only two different lengths 20 and 23. The first barcode that are equal or less 899 is 20 digits ant the others are 23 digits. What I need is in another way if it is 20 digits delete the last 5 digits and if it is 23 digits delete the last seven digits.
Thanks
WildBird
bit easier if they are different lengths, something like
iif(len([YourField])=20,left([YourField],15),left([YourField],16))
MeMedea
You can try this...
xpr1: IIf(Len([BARCODE])=20,Left([BARCODE],15),Left([BARCODE],16))
Trish
* What field size are you using??????
MeMedea
Wildbird...we must be submitting at the exact same time!!!!!
WildBird
Great minds think alike!
Rainlover
Coop
ools seldom differ.
o!