Problem with MID function

Hello,

I'm having a problem with the MID function within Access 97. I have
been trying to build a function to check to make sure that a field on a
form does not have any spaces or dashes. This should be easy enough to
do using the MID function in a For...Next statement. However, I've had
more than a few problems.

My first problem was that I had a data type mismatch. Access simply
refused to evaluate anything I put in there no matter what I tried. I
tried every possible way I could think of to insure it was evaluating a
string, but still got data type mismatches. I even broke down and
created a fresh database and copy/pasted Microsoft's example code from
the help file and STILL got a data type mismatch. (I even tried it on
two different machines)

I left the problem alone for a month and worked on other things before
coming back to this today. It seems I can't put it off anymore =)

The first thing I noticed when I came back to it today is that I had a
field named 'MID' in the table's datasource. Figuring that this may
have been the problem all along (but still confused as to why the
function didn't work in other applications) I changed the field name
from MID to MIDNum and thought I'd have it fixed.

Or not. Now when I use the MID function I get an error indicating that
Access can't find the field 'MID'. I tried adding a $ after the MID to
see if that helped anything. All I got was "compile error:
Type-declaration character does not match declared data type."

I carefully searched throughout my entire application and removed any
reference to the old 'MID' field, but still cannot get VB to recognize
this as a function rather than a field. Do any of you have any tricks
to force VBA to recognize the function?

Here is the code. *****Note: The field GroupNum is an alpha numeric
text field, NOT a numeric field.****

Private Sub Command9_Click()
Dim FilterStr As String
Dim SpaceCheck As String
Dim i As Long
FilterStr = ""

If Not IsNull(GroupNum) Or GroupNum <> "" Then
'*********problem area below this line*********
For i = 1 To Len(GroupNum)
SpaceCheck = MID$(GroupNum, i, 1)
If SpaceCheck = " " Or SpaceCheck = "-" Then
Beep
MsgBox "Please do not enter any spaces or dashes in the
group number field."
Exit Sub
End If
Next
'********problem area above this line. ********
FilterStr = "groupnumber like '*" & GroupNum & "*'"
If cmbBenType <> "" Or Not IsNull(cmbBenType) Then
FilterStr = FilterStr + " AND bentype like '*" & cmbBenType
& "*'"
End If
Else
Beep
MsgBox "Please enter a valid entry into the Group Number field"
Exit Sub
End If

Me.Filter = FilterStr
Me.FilterOn = True

IssueNumber.Visible = True
DateIdentified.Visible = True
GroupNumber.Visible = True
GroupName.Visible = True
cmdView.Visible = True
Status.Visible = True
BenType.Visible = True
cmdAddNew.Enabled = True

If Me.RecordsetClone.RecordCount = 0 Then
lblNoMatch.Visible = True
Else
lblNoMatch.Visible = False
End If
End Sub

If any of you have any suggestions I'd appreciate hearing them!

The Mid() function may not be the way to go. The following function
will remove all instances of specified characters from a string.

Function ChopIt(ByVal pstr As String, ParamArray varmyvals() As
Variant) As String
'*******************************************
'Purpose: Remove a list of unwanted
' characters from a string
'Coded by: raskew
'Inputs: From debug window:
' '? chopit("(626) 123-5555", ")","(", "-")
'Output: 626 123 5555
'*******************************************

Dim strHold As String
Dim i As Integer
Dim n As Integer

strHold = Trim(pstr)
'check for entry
If UBound(varmyvals) < 0 Then Exit Function
For n = 0 To UBound(varmyvals())
Do While InStr(strHold, varmyvals(n)) > 0
i = InStr(strHold, varmyvals(n))
strHold = Left(strHold, i - 1) & Mid(strHold, i + 1)
Loop
Next n
ChopIt = Trim(strHold)

End Function

HTH - Bob

Sorry for the late response, I wrote the op just as I was leaving work
yesterday.

Thanks for the code example Bob. I'll be sure and give it a try.

Andy_Khosravi@bcbsmn.com wrote:
: Sorry for the late response, I wrote the op just as I was leaving work
: yesterday.

: Thanks for the code example Bob. I'll be sure and give it a try.


Please note that the function provided *removes* the offending
characters:
[color=blue]
>'*******************************************
>'Purpose: Remove a list of unwanted
>' characters from a string
>'Coded by: raskew[/color]


while the code that you have shown insists that the user
correct his/her entry:
[color=blue]
> If Not IsNull(GroupNum) Or GroupNum <> "" Then
>'*********problem area below this line*********
> For i = 1 To Len(GroupNum)
> SpaceCheck = MID$(GroupNum, i, 1)
> If SpaceCheck = " " Or SpaceCheck = "-" Then
> Beep
> MsgBox "Please do not enter any spaces or dashes in the
> group number field."
> Exit Sub
> End If[/color]
The entry is refused, not corrected

Why not redimension SpaceCheck as Boolean and use something like

if SpaceCheck = GroupNum like "* *" or SpaceCheck = "*-*"
--thelma

<Andy_Khosravi@bcbsmn.com> wrote in message
news:1130281058.953200.262230@g47g2000cwa.googlegr oups.com...[color=blue]
> Hello,
>
> I'm having a problem with the MID function within Access 97. I have
> been trying to build a function to check to make sure that a field on a
> form does not have any spaces or dashes. This should be easy enough to
> do using the MID function in a For...Next statement. However, I've had
> more than a few problems.
>
> My first problem was that I had a data type mismatch. Access simply
> refused to evaluate anything I put in there no matter what I tried. I
> tried every possible way I could think of to insure it was evaluating a
> string, but still got data type mismatches. I even broke down and
> created a fresh database and copy/pasted Microsoft's example code from
> the help file and STILL got a data type mismatch. (I even tried it on
> two different machines)
>
> I left the problem alone for a month and worked on other things before
> coming back to this today. It seems I can't put it off anymore =)
>
> The first thing I noticed when I came back to it today is that I had a
> field named 'MID' in the table's datasource. Figuring that this may
> have been the problem all along (but still confused as to why the
> function didn't work in other applications) I changed the field name
> from MID to MIDNum and thought I'd have it fixed.
>
> Or not. Now when I use the MID function I get an error indicating that
> Access can't find the field 'MID'. I tried adding a $ after the MID to
> see if that helped anything. All I got was "compile error:
> Type-declaration character does not match declared data type."
>
> I carefully searched throughout my entire application and removed any
> reference to the old 'MID' field, but still cannot get VB to recognize
> this as a function rather than a field. Do any of you have any tricks
> to force VBA to recognize the function?
>
> Here is the code. *****Note: The field GroupNum is an alpha numeric
> text field, NOT a numeric field.****
>
> Private Sub Command9_Click()
> Dim FilterStr As String
> Dim SpaceCheck As String
> Dim i As Long
> FilterStr = ""
>
> If Not IsNull(GroupNum) Or GroupNum <> "" Then
> '*********problem area below this line*********
> For i = 1 To Len(GroupNum)
> SpaceCheck = MID$(GroupNum, i, 1)
> If SpaceCheck = " " Or SpaceCheck = "-" Then
> Beep
> MsgBox "Please do not enter any spaces or dashes in the
> group number field."
> Exit Sub
> End If
> Next
> '********problem area above this line. ********
> FilterStr = "groupnumber like '*" & GroupNum & "*'"
> If cmbBenType <> "" Or Not IsNull(cmbBenType) Then
> FilterStr = FilterStr + " AND bentype like '*" & cmbBenType
> & "*'"
> End If
> Else
> Beep
> MsgBox "Please enter a valid entry into the Group Number field"
> Exit Sub
> End If
>
> Me.Filter = FilterStr
> Me.FilterOn = True
>
> IssueNumber.Visible = True
> DateIdentified.Visible = True
> GroupNumber.Visible = True
> GroupName.Visible = True
> cmdView.Visible = True
> Status.Visible = True
> BenType.Visible = True
> cmdAddNew.Enabled = True
>
> If Me.RecordsetClone.RecordCount = 0 Then
> lblNoMatch.Visible = True
> Else
> lblNoMatch.Visible = False
> End If
> End Sub
>
> If any of you have any suggestions I'd appreciate hearing them![/color]

Why not use the INSTR() function during the BeforeUpdate event?
You can then give the user a msgbox telling him to remove the offending
character.
Fred Zuckerman