Access String Compairison

[Preston.Borchelt]

I have a weird bug in my problem when trying to check and format of a sting in my code.



In my program I allow the user to define how their job numbers should be formatted when entering. This has worked for almost 16 years and now my client has found this bug.

user defines job number format as: "000000-000"
user enters job number as: "990002-002"

After I run this command:
Me![ClientNumber] = Format(Me![ClientNumber], strFormatJobNumber)

Me![ClientNumber] = 001848-891

For some reason the format command has a bug that only has an issue when the starting two values are a multiple of "3". for example: "33", "66", "99".

If my end user enters: "990002002", the format statement works and the out come is "990002-002"

I am trying to come up with another way to do this comparison, but cannot think of a way to check and make sure the job number is formatted correctly.

Any help would be appreciated.

Thanks,
Preston

[June7]

You mean when user enters value with a hyphen, the entry is wrong? Just tested that and see it happening. It is not your program, has to do with Format function and maybe also the hyphen. I tested with + in place of the hyphen and that works.

I would set input mask property instead of using formatting.

This client allows staff to individually structure job number?

[dreday]

You can create a function that checks each character

Function formatcheck(byval cnum as string) as boolean

dim i as integer
i = 1

Do while i < 10
dim testchar as string
testchar = mid(cnum,i,1)
if i<>7 then
if not(asc(testchar) between 48 and 57) then 'tests to see if the character is 0 to 9 via ascii value
return false
end if
else
if testchar <> "-" then 'makes sure hyphen is 7th character
return false
end if
Loop

return true

end function

[Preston.Borchelt]

That is correct, but only when the number they are entering starts with "33", "66", and "99".

No the client doesn't allow the staff to structure job numbers. This feature is there because I have many clients using this software and not all of them structure their job numbers the same.

Input masks would be a fix if this was not customizable per client using the system.

[Preston.Borchelt]

You can create a function that checks each character

Function formatcheck(byval cnum as string) as boolean

dim i as integer
i = 1

Do while i < 10
dim testchar as string
testchar = mid(cnum,i,1)
if i<>7 then
if not(asc(testchar) between 48 and 57) then
return false
end if
else
if testchar <> "-" then
return false
end if
Loop

return true

end function

This is what I was thinking of as well, but was curious if there was something already built into access.

Here is what I came up with:

strFormatJobNumber = "######-###"
If Not Me![ClientNumber] like strFormatJobNumber Then
Me![ClientNumber] = Format(Me![ClientNumber], strFormatJobNumber)
End If

Quick testing seems to work correctly. Just have to retrain the end users when setting up data files.

[boblarson]

Input masks would be a fix if this was not customizable per client using the system.

As long as you don't set them at table level and just at form level, they are totally customizable via code so you could do this.

[June7]

I tested in the VBA immediate window and it did not work. It returned the same value as the other pattern, only with leading zeros dropped.

What you are trying to do is force users to enter value according a defined pattern. This is input masking.