Is this possible in access

hmho

Registered User.
Local time
Yesterday, 17:06
Joined
Apr 7, 2009
Messages
93
I have barcode reader that I use as data entry and we have two different barcodes one has 8 digits and the other has 9 digits.
I set up form where I scan items and it puts in the table as 12345678 or if it is 9 digits as 123456789. whit these numbers I like to change as follows.

8 digits = 123-45678
9 digits = 1234-56789

I'm not sure if this possible in access and if it is I like to know how to proceed. Please help
 
Try this, where [abc] is the field with your starting number and NewFieldName is the name of the new field in your query with the changed data.

NewFieldName: (IIf(Len([abc])=8,Left([abc],3),Left([abc],4))) & "" & "-" & "" & Right([abc],5)

If you want the changed data to display in an unbound textbox then

=(IIf(Len([abc])=8,Left([abc],3),Left([abc],4))) & "" & "-" & "" & Right([abc],5)
 
Mike375,

Thanks that is exactly what I needed. I really appreciated. Thanks
 
Glad to have helped.

Once you use that you might want to search on a couple functions called Mid() and InStr() as these can help a lot with manipulating data.

Mid() extracts characters from the middle rather than just the right or left. InStr is about finding a character position so as to give a figure for Right and Left to use.
 
Mike I have one more question. How can I store the final format only in the table. For example I want to scan the item in the form and the codes you gave me will change the format and then store only the final format not the original barcode. In another way the field for the the barcode reader is only to change the format.
 
You can use an update query. You could also use copy and paste. That is copy the column that has the 123-45678 and paste into the table field.

If you are doing this "one at a time" then you can use an Event on the form or field that sets the value of field in the table with

(IIf(Len([abc])=8,Left([abc],3),Left([abc],4))) & "" & "-" & "" & Right([abc],5)
 
personally i would be inclined to store the barcode without the hyphen - this is really what the barcode IS, and it wil be easier to compare when you scan a new bar code

for display purposes, you seem to be inserting the hyphen before the last five characters - you can easily just add this format into a query column

left(barcode, len(barcode)-5) & "-" & right(barcode,5)

its a matter of taste, I suppose though.
 
Try this, where [abc] is the field with your starting number and NewFieldName is the name of the new field in your query with the changed data.

NewFieldName: (IIf(Len([abc])=8,Left([abc],3),Left([abc],4))) & "" & "-" & "" & Right([abc],5)

If you want the changed data to display in an unbound textbox then

=(IIf(Len([abc])=8,Left([abc],3),Left([abc],4))) & "" & "-" & "" & Right([abc],5)

Mike 375,

How should I code this if I want to post in AfterUpdate?

Thanks
 
Me![abc] = Me![TextBoxname with the new value]

That is assuming that it is run on the form where it is done. Otherwise Me needs to be replaced with Forms!FormName

However, when this runs you will only be able to do it once for the records that started with 12345678. The reason being that after it is run the the barcode value field [abc] will be 123-45678 and that will mean the calculated field will become 123--45678. This if it is run again then [abc] would become 123--45678.

So you can do

If [abc] Like "*-*" Then

Me![abc] = Me![abc]

Else

Me![abc] = Me![TextBoxname with the new value]

End If

Thus running it won't insert 123--45678 into [abc].

To stop the calculated field becoming 123--45678 you do that with the IIF that does the calculation by covering if [abc] has a - in the entry. Thus if [abc] has a - in the entry then the value of the IIF will be [abc] but if there is no - in the entry then the IIF above applies

If you search this site (or Google) on Nested IIF you will get heaps of stuff.

I have not thought about it but you could probably refine all of this. The result will be the same but maybe a nicer way of doing it. For example Gemma has shown a cleaner way to get the 123-45678 or 123-45679 than what I did with IIF
 

Users who are viewing this thread

Back
Top Bottom