Type Mismatch error

  • Thread starter Thread starter AL
  • Start date Start date
A

AL

I am trying to setup a form that will search based on words entered into,
for example, the txtDivision field. The following is part of the code
applied to a button and works perfectly, but requires a match with the full
field:

If Not IsNull(Me.txtDivision) Then
sSQL = sSQL & " (((tblFolders.Division)=[Forms]![frmSearch]![txtDivision]))
AND"
stCaption = stCaption & "Division = " & Me.txtDivision & ". "
End If

The following simple query also works, and will return partial matches:
Like "*" & [Enter Division Here] & "*"

However, when I try to combine them as follows, I get a "Type Mismatch"
error:

If Not IsNull(Me.txtDivision) Then
sSQL = sSQL & " (((tblFolders.Division) Like " * " &
[Forms]![frmSearch]![txtDivision] & " * ")) AND"
stCaption = stCaption & "Division = " & Me.txtDivision & ". "
End If

The Division field in the original table is a lookup value and in the lookup
table is set as text. I would have assumed both the asterix included in
inverted commas, and the value returned from the data entered into the
txtDivision field in the search form would both be text. Is this the case,
or does one of these need to be converted into text? If so, what's the best
way of doing this? Also, each time I save the code, it automatically enters
spaces on either side of the asterix inside the inverted commas. Could this
be the problem? If so, is there any way to stop this happening?

TIA
Andrew Lantzke
 
Hi,
Try this:

sSQL = sSQL & " (((tblFolders.Division) Like '*" & [Forms]![frmSearch]![txtDivision] & "*')) AND"
 
That works a treat.
Thanks

Dan Artuso said:
Hi,
Try this:

sSQL = sSQL & " (((tblFolders.Division) Like '*" &
Click to expand...
[Forms]![frmSearch]![txtDivision] & "*')) AND"
--
HTH
Dan Artuso, Access MVP


I am trying to setup a form that will search based on words entered into,
for example, the txtDivision field. The following is part of the code
applied to a button and works perfectly, but requires a match with the full
field:

If Not IsNull(Me.txtDivision) Then
sSQL = sSQL & " (((tblFolders.Division)=[Forms]![frmSearch]![txtDivision]))
AND"
stCaption = stCaption & "Division = " & Me.txtDivision & ". "
End If

The following simple query also works, and will return partial matches:
Like "*" & [Enter Division Here] & "*"

However, when I try to combine them as follows, I get a "Type Mismatch"
error:

If Not IsNull(Me.txtDivision) Then
sSQL = sSQL & " (((tblFolders.Division) Like " * " &
[Forms]![frmSearch]![txtDivision] & " * ")) AND"
stCaption = stCaption & "Division = " & Me.txtDivision & ". "
End If

The Division field in the original table is a lookup value and in the lookup
table is set as text. I would have assumed both the asterix included in
inverted commas, and the value returned from the data entered into the
txtDivision field in the search form would both be text. Is this the case,
or does one of these need to be converted into text? If so, what's the best
way of doing this? Also, each time I save the code, it automatically enters
spaces on either side of the asterix inside the inverted commas. Could this
be the problem? If so, is there any way to stop this happening?

TIA
Andrew Lantzke
Click to expand...
Click to expand...
 
Back
Top