Data Type Mismatch
in [Forms]
|
From: Peter on 1 Nov 2009 02:43 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
From: Douglas J. Steele on 1 Nov 2009 07:10 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Peter" <Peter(a)discussions.microsoft.com> wrote in message news:7DD43B4D-04A2-4C01-84F5-E3E63C692123(a)microsoft.com... > 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 >
From: Ken Snell on 1 Nov 2009 07:13 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 -- Ken Snell http://www.accessmvp.com/KDSnell/ "Peter" <Peter(a)discussions.microsoft.com> wrote in message news:7DD43B4D-04A2-4C01-84F5-E3E63C692123(a)microsoft.com... > 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 >
From: Allen Browne on 1 Nov 2009 07:23 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Peter" <Peter(a)discussions.microsoft.com> wrote in message news:7DD43B4D-04A2-4C01-84F5-E3E63C692123(a)microsoft.com... > 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 >
From: anlu on 1 Nov 2009 08:10
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 |