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!

re: Problem with MID function

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

re: Problem with MID function

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.

re: Problem with MID function

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

re: Problem with MID function

<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