Puzzling Chr$ error message

D

Dudley

I have forms where the user is required to enter data as for example, if type
=1, then data = the first three letters of the town of birth, and I have put
in error checking to only allow letters as

If Forms![Director1 John]![Director1Type1] = "7" And _
(Forms![Director1 John]![Director1Data1] < Chr$("65") Or
Forms![Director1 John]![Director1Data1] = Chr$("91") _
Or Forms![Director1 John]![Director1Data1] = Chr$("92") Or
Forms![Director1 John]![Director1Data1] = Chr$("93") _
Or Forms![Director1 John]![Director1Data1] = Chr$("94") Or
Forms![Director1 John]![Director1Data1] = Chr$("95") _
Or Forms![Director1 John]![Director1Data1] = Chr$("96") Or
Forms![Director1 John]![Director1Data1] > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) > Chr$("122")) Then

fnErrorCheck = False
MsgBox "Invalid mother's maiden name"
Exit Function
Else
fnErrorCheck = True
End If

This worked fine until a user put in the first letter as Z, when he got the
error message. I have found that the problem does not occur when Z is the
second or third letter, so I have tried code:

If Forms![Director1 John]![Director1Type1] = "1" And _
(Mid(Forms![Director1 John]![Director1Data1], 1, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) > Chr$("122")) Then

fnErrorCheck = False
MsgBox "Invalid town of birth"
Exit Function
Else
fnErrorCheck = True
End If

This always works including for first letter Z. Can anyone advise what the
problem is with the first code, and is there any way round me going through
all the many cases where I have used the first code and revising it?

Thanks for any help.
Dudley
 
D

Douglas J. Steele

The Chr function expects a numeric value, not a string. Try using Chr$(65),
Chr$(91), Chr$(92), etc. instead.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Dudley said:
I have forms where the user is required to enter data as for example, if
type
=1, then data = the first three letters of the town of birth, and I have
put
in error checking to only allow letters as

If Forms![Director1 John]![Director1Type1] = "7" And _
(Forms![Director1 John]![Director1Data1] < Chr$("65") Or
Forms![Director1 John]![Director1Data1] = Chr$("91") _
Or Forms![Director1 John]![Director1Data1] = Chr$("92") Or
Forms![Director1 John]![Director1Data1] = Chr$("93") _
Or Forms![Director1 John]![Director1Data1] = Chr$("94") Or
Forms![Director1 John]![Director1Data1] = Chr$("95") _
Or Forms![Director1 John]![Director1Data1] = Chr$("96") Or
Forms![Director1 John]![Director1Data1] > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) > Chr$("122"))
Then

fnErrorCheck = False
MsgBox "Invalid mother's maiden name"
Exit Function
Else
fnErrorCheck = True
End If

This worked fine until a user put in the first letter as Z, when he got
the
error message. I have found that the problem does not occur when Z is the
second or third letter, so I have tried code:

If Forms![Director1 John]![Director1Type1] = "1" And _
(Mid(Forms![Director1 John]![Director1Data1], 1, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) > Chr$("122"))
Then

fnErrorCheck = False
MsgBox "Invalid town of birth"
Exit Function
Else
fnErrorCheck = True
End If

This always works including for first letter Z. Can anyone advise what the
problem is with the first code, and is there any way round me going
through
all the many cases where I have used the first code and revising it?

Thanks for any help.
Dudley
Click to expand...
 
G

Graham Mandeno

Hi Dudley

In addition to Doug's advice of the Chr$ function, there are much easier
ways to check that the first three characters of a string are alphabetic:

If Not str Like "[A-Z][A-Z][A-Z]*" Then ...

Also, how will you handle the case where the mother's maiden name is
"O'Brien"?

And what about abc123$&^%^":# - is that valid?

It seems to me you should be checking that the first character is
alphabetic, and that all subsequent characters are alphabetic or apostrophe
or space or hyphen. Presumably you have many fields to validate in this
way - first name, last name, middle names, maiden name, towns/cities,
next-of-kin, etc. Instead of repeating your code many times, write a single
function:

Public Function NameIsValid( _
strName as Variant, _
AllowNull as Boolean ) as Boolean
If IsNull(strName) then
NameIsValid = AllowNull
Else
' do your test
End If
End Function

Just some food for thought :)
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Dudley said:
I have forms where the user is required to enter data as for example, if
type
=1, then data = the first three letters of the town of birth, and I have
put
in error checking to only allow letters as

If Forms![Director1 John]![Director1Type1] = "7" And _
(Forms![Director1 John]![Director1Data1] < Chr$("65") Or
Forms![Director1 John]![Director1Data1] = Chr$("91") _
Or Forms![Director1 John]![Director1Data1] = Chr$("92") Or
Forms![Director1 John]![Director1Data1] = Chr$("93") _
Or Forms![Director1 John]![Director1Data1] = Chr$("94") Or
Forms![Director1 John]![Director1Data1] = Chr$("95") _
Or Forms![Director1 John]![Director1Data1] = Chr$("96") Or
Forms![Director1 John]![Director1Data1] > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) > Chr$("122"))
Then

fnErrorCheck = False
MsgBox "Invalid mother's maiden name"
Exit Function
Else
fnErrorCheck = True
End If

This worked fine until a user put in the first letter as Z, when he got
the
error message. I have found that the problem does not occur when Z is the
second or third letter, so I have tried code:

If Forms![Director1 John]![Director1Type1] = "1" And _
(Mid(Forms![Director1 John]![Director1Data1], 1, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) > Chr$("122"))
Then

fnErrorCheck = False
MsgBox "Invalid town of birth"
Exit Function
Else
fnErrorCheck = True
End If

This always works including for first letter Z. Can anyone advise what the
problem is with the first code, and is there any way round me going
through
all the many cases where I have used the first code and revising it?

Thanks for any help.
Dudley
Click to expand...
 
D

Dudley

Many thanks to Doug and Graham. Unfortunately, deleting the quotes in Chr$
did not make any difference.

If Not str Like "[A-Z][A-Z][A-Z]*" Then ...
This was just what I was looking for all along, and I could only track down
Chr$. Many thanks.

So far as apostrophes etc, they do not apply in this situation. I am
complying with a spec which requires any 3 out of 7 details to be entered for
authentication, such as first three letters of town of birth, last 3 digits
of phone number, etc.

I have to specify that, for example, if Director3Type1 = 1 (town of birth),
then only letters are allowed in Director3Data1, whereas if Shareholder1Type3
= 3 (phone number), then only digits are allowed in Shareholder1Data3. I have
not been able to work out a single function to deal with all these
possibilities. Also I have to admit that understanding your suggested
function is beyond my knowledge, but if you have any further suggestions I
should be most grateful.

Thanks
Dudley


Graham Mandeno said:
Hi Dudley

In addition to Doug's advice of the Chr$ function, there are much easier
ways to check that the first three characters of a string are alphabetic:

If Not str Like "[A-Z][A-Z][A-Z]*" Then ...

Also, how will you handle the case where the mother's maiden name is
"O'Brien"?

And what about abc123$&^%^":# - is that valid?

It seems to me you should be checking that the first character is
alphabetic, and that all subsequent characters are alphabetic or apostrophe
or space or hyphen. Presumably you have many fields to validate in this
way - first name, last name, middle names, maiden name, towns/cities,
next-of-kin, etc. Instead of repeating your code many times, write a single
function:

Public Function NameIsValid( _
strName as Variant, _
AllowNull as Boolean ) as Boolean
If IsNull(strName) then
NameIsValid = AllowNull
Else
' do your test
End If
End Function

Just some food for thought :)
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Dudley said:
I have forms where the user is required to enter data as for example, if
type
=1, then data = the first three letters of the town of birth, and I have
put
in error checking to only allow letters as

If Forms![Director1 John]![Director1Type1] = "7" And _
(Forms![Director1 John]![Director1Data1] < Chr$("65") Or
Forms![Director1 John]![Director1Data1] = Chr$("91") _
Or Forms![Director1 John]![Director1Data1] = Chr$("92") Or
Forms![Director1 John]![Director1Data1] = Chr$("93") _
Or Forms![Director1 John]![Director1Data1] = Chr$("94") Or
Forms![Director1 John]![Director1Data1] = Chr$("95") _
Or Forms![Director1 John]![Director1Data1] = Chr$("96") Or
Forms![Director1 John]![Director1Data1] > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) > Chr$("122"))
Then

fnErrorCheck = False
MsgBox "Invalid mother's maiden name"
Exit Function
Else
fnErrorCheck = True
End If

This worked fine until a user put in the first letter as Z, when he got
the
error message. I have found that the problem does not occur when Z is the
second or third letter, so I have tried code:

If Forms![Director1 John]![Director1Type1] = "1" And _
(Mid(Forms![Director1 John]![Director1Data1], 1, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 1, 1) > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 2, 1) > Chr$("122") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) < Chr$("65") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("91") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("92") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("93") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("94") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("95") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) = Chr$("96") _
Or Mid(Forms![Director1 John]![Director1Data1], 3, 1) > Chr$("122"))
Then

fnErrorCheck = False
MsgBox "Invalid town of birth"
Exit Function
Else
fnErrorCheck = True
End If

This always works including for first letter Z. Can anyone advise what the
problem is with the first code, and is there any way round me going
through
all the many cases where I have used the first code and revising it?

Thanks for any help.
Dudley
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Requiring alpha data entry 3
Requiring alpha data entry 2
Verify Canadian Postal Code ~ make the code work 2
How to test variable for chr values 2
Help with Macro please... 6
Names with Mixed cases 0
What formulae to derive? 1
Incorporating feature of second macro into first? 5

Top