Data Type Mismatch

[Peter]

Hi all again…I receive Date Type Missmatch on this..

The query is Basket Query, the numeric control is Basket ID and Search is my
text field, different form, were I enter the number to filter…????

If DCount("*", "Basket query", "[Basket ID] =" & Me.Search & ") > 0 Then
DoCmd.OpenForm stDocName
Forms![Basket View].Filter = "[Basket ID] ='" & Me.Search & "'"
Forms![Basket View].FilterOn = True
Else
MsgBox “Sorry no Fruit Baskets foundâ€
End If

 

[Douglas J. Steele]

In creating the field, you've got quotes around the value from Me.Search, in
the DCount statement, you don't.

If Basket ID is numeric, lose the quotes in the filter. If it's not numeric,
put them into the DCount statement.

 

[Ken Snell]

Because Basket ID is a numeric field, you don't need the delimiting '
characters in the Filter step:

Forms![Basket View].Filter = "[Basket ID] =" & Me.Search

 

[Allen Browne]

Suggestions:
a) Use a string variable, so you can test (Debug.Print) if you got it right.

b) Test if there is a number in your Search box. If it's blank the string
will be just:
[Basket ID] =
which would fail.

c) Using DLookup() to get the first match will be faster than DCount() which
has to count all matches.

Something like this:

Dim strWhere as String
If IsNumeric(Me.Search) Then
strWhere = "[Basket ID] = " & Me.Search
'Debug.Print strWhere
If Not IsNull(DLookup("[Basket ID], "Basket query", strWhere) Then
With Forms![Basket View]
.Filter = strWhere
.FilterOn = True
End With
End If
Else
MsgBox "Enter a basket number."
End If

This assumes that:
- Basket ID is a Number field (not a Text field) when you open your table in
design view.

- The Basket View form is already open, and is not dirty with a record that
can't be saved.

 

[anlu]

Hi Peter,

My guess is that since your [Basket ID] seems to be a numeric field, this line

Forms![Basket View].Filter = "[Basket ID] ='" & Me.Search & "'"

should be changed to
Forms![Basket View].Filter = "[Basket ID] =" & Me.Search
since the '-delimiter is only used on text expressions.

Regards,
anlu

 

[Peter]

Thanks anlu..this works!

Hi Peter,

My guess is that since your [Basket ID] seems to be a numeric field, this line

Forms![Basket View].Filter = "[Basket ID] ='" & Me.Search & "'"

should be changed to
Forms![Basket View].Filter = "[Basket ID] =" & Me.Search
since the '-delimiter is only used on text expressions.

Regards,
anlu

 

[Peter]

HI all...and MANY thanks for all your help...indeed very valuble...i
understand that tehre are many ways to solve this..hm...i call the Basket
View form from the main meny and a textfield (Search) with a command button
that on the click event triggers this code...as a mater of fact i used the
principle from a MVP...Mr.Browns "search form..In my main Meny i use one
textfield and then 6 command buttons..each with a code that is related to
this textfield..and IF the data matches..Then the related form opens..if
not..or Else..i created a small Msgbox myself...

Suggestions:
a) Use a string variable, so you can test (Debug.Print) if you got it right.

b) Test if there is a number in your Search box. If it's blank the string
will be just:
[Basket ID] =
which would fail.

c) Using DLookup() to get the first match will be faster than DCount() which
has to count all matches.

Something like this:

Dim strWhere as String
If IsNumeric(Me.Search) Then
strWhere = "[Basket ID] = " & Me.Search
'Debug.Print strWhere
If Not IsNull(DLookup("[Basket ID], "Basket query", strWhere) Then
With Forms![Basket View]
.Filter = strWhere
.FilterOn = True
End With
End If
Else
MsgBox "Enter a basket number."
End If

This assumes that:
- Basket ID is a Number field (not a Text field) when you open your table in
design view.

- The Basket View form is already open, and is not dirty with a record that
can't be saved.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all again…I receive Date Type Missmatch on this..

The query is Basket Query, the numeric control is Basket ID and Search is
my
text field, different form, were I enter the number to filter…????

If DCount("*", "Basket query", "[Basket ID] =" & Me.Search & ") > 0 Then
DoCmd.OpenForm stDocName
Forms![Basket View].Filter = "[Basket ID] ='" & Me.Search & "'"
Forms![Basket View].FilterOn = True
Else
MsgBox “Sorry no Fruit Baskets foundâ€
End If

.