Data String Manipulation

[Jerry]

I have a filed on a form that a barcode is being scanned into. The raw scan
contains characters I need to remove. I always need to remove the first
character, then I have to check the first, 6th and tenth sopt for 0 and
remove the first one, then stop. I then have a query that searches a table
for the string. Can this be done in Access? I am guessing it may involve
the instr function in some way, but I am just not sure.

Thanks for any input!

 

[Douglas J. Steele]

Not sure I really follow, but you'd use the Mid function to look at specific
positions.

Mid([MyString], 1, 1) will give you the content of the first position,
Mid([MyString], 6, 1) will give you the content of the sixth position,
Mid([MyString], 10, 1) will give you the content of the tenth position and
so on.

You can also use the Mid function to remove the first character:

Mid([MyString], 2) will return everything from position 2 on.

 

[Jerry]

Thanks, using the Mid function gets me started. It gets very complicated! I
always need to delete the first character in the scanned string, then Check
the 1st number in the string - if it is 0, remove it to get the 10 digit NDC

·If the 1st number is not a zero, check the 6th - if it is 0, remove it to
get the 10 digit NDC

·If neither the 1st nor the 6th digits are 0, check the 10th digit - if it
is 0, remove it to get the 10 digit NDC

·If a 0 is not the 1st, the 6th, or the 10th character in the string, it is
an invalid 11 digit NDC

Hopefully that makes it a little clearer.

Thanks

Not sure I really follow, but you'd use the Mid function to look at specific
positions.

Mid([MyString], 1, 1) will give you the content of the first position,
Mid([MyString], 6, 1) will give you the content of the sixth position,
Mid([MyString], 10, 1) will give you the content of the tenth position and
so on.

You can also use the Mid function to remove the first character:

Mid([MyString], 2) will return everything from position 2 on.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a filed on a form that a barcode is being scanned into. The raw
scan
contains characters I need to remove. I always need to remove the first
character, then I have to check the first, 6th and tenth sopt for 0 and
remove the first one, then stop. I then have a query that searches a
table
for the string. Can this be done in Access? I am guessing it may involve
the instr function in some way, but I am just not sure.

Thanks for any input!

 

[Douglas J. Steele]

I'm assuming that your check of positions 1, 6 and 10 really refer to
positions 2, 7 and 11, since you've deleted the first position. (If no, I'm
confused!) I'm also assuming that your incoming string must be 12 characters
long, and that a valid NDC number is 10 characters long.

Function NDC(IncomingString As String) As String
' Returns an NDC, or a zero-length string ("")
' if IncomingString is invalid

If Len(IncomingString) = 12 Then
If Mid(IncomingString, 2, 1) = "0" Then
NDC = Mid(IncomingString, 3)
ElseIf Mid(IncomingString, 7, 1) = "0" Then
NDC = Mid(IncomingString, 2, 5) & _
Mid(IncomingString, 8)
ElseIf Mid(IncomingString, 11, 1) = "0" Then
NDC = Mid(IncomingString, 2, 9) & _
Mid(IncomingString, 12, 1)
End If
End If

End Function

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, using the Mid function gets me started. It gets very complicated!
I
always need to delete the first character in the scanned string, then
Check
the 1st number in the string - if it is 0, remove it to get the 10 digit
NDC

·If the 1st number is not a zero, check the 6th - if it is 0, remove it to
get the 10 digit NDC

·If neither the 1st nor the 6th digits are 0, check the 10th digit - if it
is 0, remove it to get the 10 digit NDC

·If a 0 is not the 1st, the 6th, or the 10th character in the string, it
is
an invalid 11 digit NDC

Hopefully that makes it a little clearer.

Thanks

Not sure I really follow, but you'd use the Mid function to look at
specific
positions.

Mid([MyString], 1, 1) will give you the content of the first position,
Mid([MyString], 6, 1) will give you the content of the sixth position,
Mid([MyString], 10, 1) will give you the content of the tenth position
and
so on.

You can also use the Mid function to remove the first character:

Mid([MyString], 2) will return everything from position 2 on.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a filed on a form that a barcode is being scanned into. The raw
scan
contains characters I need to remove. I always need to remove the
first
character, then I have to check the first, 6th and tenth sopt for 0 and
remove the first one, then stop. I then have a query that searches a
table
for the string. Can this be done in Access? I am guessing it may
involve
the instr function in some way, but I am just not sure.

Thanks for any input!

 

[dymondjack]

How about something like this


Function MakeString(Scan As String) As String
Dim Ret As String

'Get rid of the first character
Ret = Mid(Scan, 2, Len(Ret) - 1)

'Check 1st Number for 0
If Mid(Ret, 1, 1) = "0" Then
Ret = Mid(Ret, 2, Len(Ret) - 1)
GoTo Exit_Func
End If

'Check 6th Number
If Mid(Ret, 6, 1) = "0" Then
Ret = Mid(Ret, 1, 5) & Mid(Ret, 6, Len(Ret) - 6)
Goto Exit_Func
End If

'Check 10th Number
If Mid(Ret, 10, 1) = "0" Then
Ret = Mid(Ret, 1, 9) & Mid(Ret, 11, Len(Ret) - 11)
End If

Exit_Func:
MakeString = Ret
Exit Function
End Function


I haven't tried the function, you may have to adjust the Len() subtraction
numbers, but this should get you pretty close.

hth
--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Thanks, using the Mid function gets me started. It gets very complicated! I
always need to delete the first character in the scanned string, then Check
the 1st number in the string - if it is 0, remove it to get the 10 digit NDC

·If the 1st number is not a zero, check the 6th - if it is 0, remove it to
get the 10 digit NDC

·If neither the 1st nor the 6th digits are 0, check the 10th digit - if it
is 0, remove it to get the 10 digit NDC

·If a 0 is not the 1st, the 6th, or the 10th character in the string, it is
an invalid 11 digit NDC

Hopefully that makes it a little clearer.

Thanks

Not sure I really follow, but you'd use the Mid function to look at specific
positions.

Mid([MyString], 1, 1) will give you the content of the first position,
Mid([MyString], 6, 1) will give you the content of the sixth position,
Mid([MyString], 10, 1) will give you the content of the tenth position and
so on.

You can also use the Mid function to remove the first character:

Mid([MyString], 2) will return everything from position 2 on.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a filed on a form that a barcode is being scanned into. The raw
scan
contains characters I need to remove. I always need to remove the first
character, then I have to check the first, 6th and tenth sopt for 0 and
remove the first one, then stop. I then have a query that searches a
table
for the string. Can this be done in Access? I am guessing it may involve
the instr function in some way, but I am just not sure.

Thanks for any input!

 

[Jerry]

Thanks for the input! I think this gets me close but I am not sureof the
best spot to call the function. Can I call it from a field exit event or is
there a better spot?

 

[Douglas J. Steele]

If you store only the "raw" number, you can create a query that uses the
function to give you the NDC and then use the query anywhere you would
otherwise have used the table.

Alternatively, only store the NDC. To do this, you'd have them key the "raw"
number into an unbound text box. In the AfterUpdate event of that text box,
you'd call the function to populate the actual NDC field.

 

[Jerry]

Git it working! thanks for all of your help!

If you store only the "raw" number, you can create a query that uses the
function to give you the NDC and then use the query anywhere you would
otherwise have used the table.

Alternatively, only store the NDC. To do this, you'd have them key the "raw"
number into an unbound text box. In the AfterUpdate event of that text box,
you'd call the function to populate the actual NDC field.