From: Peter on 2 Nov 2009 03:09 Thanks anlu..this works! "anlu" wrote: > 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 > > >
From: Peter on 2 Nov 2009 13:44
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... "Allen Browne" wrote: > 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 > > > . > |