Need to Compare the format of 2 fields

[JS1521253]

I apologize if there is a repeat here, but I've looked for this topic and can't seem to find it.

I am trying to find a way to compare fields (both are account id's) from separate sources to see if their accounts id's are in the same format.

I picture having a table with the expected format and another with the actual client data for comparison. The expected format could be any number of things: 1234-abc, 12345678, 12-ab-24, etc.

Is there a way to use VBA to validate a format of an account?

Thanks

 

[traingamer]

Are you saying that all of the ids from one source have a dash (-) and that all of the ids from a second source have two dashes and that all of the ids from a third source will have no dashes?

If, as I'm guessing, the ids may or may not have varying numbers of dashes within a single source, the difficulty comes in comparing them with each other. Making the assumption that account ids 1234-abc, 12-34-abc and 1234abc are all for the same account, you can compare them by removing all of the dashes with a REPLACE() function. e.g.

...where Replace(file1.acctid, "-", "") = Replace(file2.acctid, "-", "")

If that is not what you mean, please clarify your question.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

You can also remove spaces and other extraneous characters in the same manner.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[JS1521253]

Actually, I was saying those formats would not match. It may have been better to say that an id could be in a format(mask) of ####-XXX, ######, ###-##-XXX, etc.

I am really looking that 1234-abc matches (or doesn't match) the expected format ####-XXX.

Thanks for the help.

 

[joelflorendo]

There might be a more efficient way but you could do something like this:

'test if first four characters are numbers
If IsNumeric(Left(MyStr,4)) Then

'test if dash is in right position
If Instr(MyStr, "-") = 5 Then

'do something

End If
End If

The last part of testing whether the last 3 characters are letters would be a bit more involved. You might try converting each position to it's ascii equivalent and checking if it's >=65 and <=90 (uppercase) or >=97 and <=122 (lowercase)

Something like this:

for i=6 to 8 'last 3 positions of string
myChr = Mid(MyStr,i,1)

If Asc(myChr)<65 Or (Asc(myChr)>90 and Asc(myChr)<97) Or Asc(myChr)>122 Then
'string is not formatted correctly
End If
next i

 

[CautionMP]

JS1521253,
Take a look at [tt]LIKE[/tt] in the help file, it will allow you to compare strings to a pattern.

Examples:

"1234-abc" LIKE "####-###" = False
"1234-abc" LIKE "####-???" = True
"1234-abc" LIKE "####-[a-b][a-b][a-b]" = True

If your comparing characters watch out for case.

If you need more examples of patterns you can use check out Regular Expressions on MSDN. Most of the patterns that work with RegExp with work with [tt]LIKE[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[CautionMP]

er, eh,

"1234-abc" LIKE "####-[a-b][a-b][a-b]" = False
"1234-abc" LIKE "####-[a-z][a-z][a-z]" = True