CASE SELECT or IF THEN for Array ... 1

[tanaka99]

I'm new to VBA, using Access 2000. I have a database that reads barcodes from a scanner and stores them in a variable as such:

801R554430403 801R554430506 801BL330444033

I'd like to be able to do some IF THEN or CASE SELECT routines on each of the barcodes of same length,

(i.e. If barcode = 15 Then
Else If barcode = 14 Then
Else )

as they are variable length and require different parses to split the substrings into different fields.


Do you know how I can do this the most effective? Thanks.

 

[kylua]

select case barcode
case 15
'do parsing for 15
case 14
'do parsing for 14
case 11 to 13
' do parsing for 13,12,11
case 10,9
' do parsing for 10 & 9
case else
'do ohmigod, it's been scanned in wrong
end select

 

[RobBroekhuis]

If "barcode" is the name of the variable containing the actual code, you'd replace (in kylua's code above) the first line with

select case len(barcode)

Rob

 

[tanaka99]

Thank you for your posts ... what I can't figure out is how to individualize the substrings to be able to run the case selects on them. I'm comfortable with doing the actual case selects but reading the string and breaking it down is where I'm stuck ...

 

[RobBroekhuis]

Are you familar with the mid() string function? If not, look up the help for it - it allows you to select any part of a particular string.
Rob

 

[tanaka99]

I am familiar with the mid() function, but I don't know if this will help me in this case. I'd like to take all the substrings and run them through the case selects via a loop ... Is there a way to take each substring pass it through the CASE SELECT statements then loop to the next substring?

 

[RobBroekhuis]

Ah, all your barcodes are in ONE variable? From your example above, it looks like they are, with spaces as delimiters. In that case, try something like

do while barcodes<>&quot;&quot;
p=instr(barcodes,&quot; &quot
if p>0 then
barcode=left(barcodes,p-1)
barcodes=mid(barcodes,p+1)
else
barcode=barcodes
barcodes=&quot;&quot;
end if
select case len(barcode)
....
end select
loop

Rob

 

[tanaka99]

Thanks Rob,

I'll play with that and see what happens.

Paul

 

[tanaka99]

Rob,

Looks like that's going to work. I stuck in some counters to test each of the cases and they incremented as expected. Thanks a million.

Paul

 

[tanaka99]

Solution successful, thread closed ... thanks Rob.

 

[xlbo]

Paul / Tanka99 - sounds like Rob really helped you out there and from what I can see, it's definitely an expert post. The way to recognise this is to award a star. This not only shows thanks but also allows people to search through answers given to similar problems they may have and look for good resolutions (ie those marked with a star). To award a star, just click on the &quot;Mark this post as a helpful / expert post&quot; link at the bottom of the answer that has helped you Rgds
~Geoff~