Search inside string

[johnisotank]

Hi all,

solution to this little problem just seems to be out of my reach..

Some of our customers need to have
a) specific length order numbers or
b) order numbers starting with certain characters or
c) need a combination of both

I would like a field in our customers table which contains this information and I was thinking of this (if anyone knows a better method pls advise)

if the order number needed to be 5 chars long then the field would contain (5).

If it needed to start with ABC then it would contain [ABC] if it needed both then it would contain (5)[ABC]

This seems logical to me but I now need the code to be able to search the order number typed in and compare it to the customer string.

If the user provided the order number 'AB12' what code would I need to check that string against the OrderNumber validation and tell the user he has failed to enter both correct no. of chars and wrong starting chars?

cheers for any help

John

 

[jebenson]

Well, off the top of my head I'd say make 2 fields in the database, one for the length of the order number and one for any required prefix. So:

ClientID OrderNumberLength Prefix
1 5 ABC
2 7 Q
3 8 <NULL>

Then when validating an order number:

Select * from OrderNumbers where CleintID=1

Then you can check the length of the Prefix (assuming a DataTable is used to hold the data):

Dim PrefixLength As Integer

If IsDBNull DataTable1.Item("Prefix") Then
PrefixLength = 0
Else
PrefixLength = Len(Trim(DataTable1.Item("Prefix")))
End If

Dim ThisOrderNumber As String

ThisOrderNumber = TextBox1.Text.Trim

Dim ThisPrefix As String
Dim PrefixMatch As Boolean

If PrefixLength > 0 Then
ThisPrefix = ThisOrderNumber.Substring(0, PrefixLength)
If ThisPrefix = Trim(DataTable1.Item("Prefix")) Then
'Prefix matches
PrefixMatch = True
Else
PrefixMatch = False
MsgBox("Invalid Order Number Prefix")
End If
End If

I'll leave it to you for checking the other part of the order number, but it is similar to the above. Post again if you need any more assistance.

You also might want to look into regular expressions, they could probably make this code much shorter.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!