Solved Search with String and Date in the same textbox in access form

Oops! Managed to strip out a couple too many brackets from the end of the IsDate() function calls 😬

Try:
Code: 
  SQL1 = "SELECT " & vbNewLine & _
         "  ID, " & vbNewLine & _
         "  IsDate([Forms]![Subform TPA]![Searchbox1]) AS Expr1, " & vbNewLine & _
         "  [Received Date], " & vbNewLine & _
         "  [Name Of Sender], " & vbNewLine & _
         "  Place, " & vbNewLine & _
         "  District, " & vbNewLine & _
         "  TPA" & vbNewLine & _
         "FROM [Inward Register] " & vbNewLine & _
         "WHERE (IsDate([Forms]![Subform TPA]![Searchbox1]) = False AND TPA Like '*" & [Forms]![View Inward Register]![Searchbox1] & "*') " & vbNewLine & _
         "   OR (IsDate([Forms]![Subform TPA]![Searchbox1]) = True AND [Received Date] = " & Format([Forms]![View Inward Register]![Searchbox1], "\#yyyy\-mm\-dd hh:nn:ss\#") & ") " & vbNewLine & _
         "ORDER BY [Subform TPA].ID DESC;"
  Debug.Print SQL1
 
You are not using the closing parenthesis for IsDate() in the WHERE
Why do it for the SELECT and not the WHERE? :(
 
Gasman said:
You are not using the closing parenthesis for IsDate() in the WHERE
Why do it for the SELECT and not the WHERE? :(
Click to expand...
Is that directed at me or the OP?
 
Looking at this again, the ORDER BY clause might cause some issue too.

Is [Subform TPA] the actual form? If so, it needs to be:
Code: 
' ...
         "   OR (IsDate([Forms]![Subform TPA]![Searchbox1]) = True AND [Received Date] = " & Format([Forms]![View Inward Register]![Searchbox1], "\#yyyy\-mm\-dd hh:nn:ss\#") & ") " & vbNewLine & _
         "ORDER BY ID DESC;"
' ...
 
I thought the point was to use a single searchbox, but you seem to be referencing searchboxes on two different forms.
 
Sorry for the late reply everyone. I need to search with date or string (other value) with a single search box and to multiple columns in my database. below is my query code in SQL VIEW,

Rich (BB code): 
SELECT [Inward Register].*, [Inward Register].[Received Date], [Inward Register].[Name Of Sender], [Inward Register].[Farmer Name], [Inward Register].Village, [Inward Register].TPA, [Inward Register].[TR Report], [Inward Register].[Other Documents]
FROM [Inward Register]
WHERE ((([Inward Register].[Received Date])=[Forms]![InwardFrm]![Searchbox]) AND ((IsDate([Forms]![InwardFrm]![Searchbox]))=True)) OR ((([Inward Register].[Name Of Sender]) Like "*" & [Forms]![InwardFrm]![Searchbox] & "*") AND ((IsDate([Forms]![InwardFrm]![Searchbox]))=False)) OR ((([Inward Register].[Farmer Name]) Like "*" & [Forms]![InwardFrm]![Searchbox] & "*")) OR ((([Inward Register].Village) Like "*" & [Forms]![InwardFrm]![Searchbox] & "*")) OR ((([Inward Register].TPA) Like "*" & [Forms]![InwardFrm]![Searchbox] & "*")) OR ((([Inward Register].[TR Report]) Like "*" & [Forms]![InwardFrm]![Searchbox] & "*")) OR ((([Inward Register].[Other Documents]) Like "*" & [Forms]![InwardFrm]![Searchbox] & "*"));


now for first instance it works fine. But as i close the form and open it again and try to search i got "Runtime Error :3420 : object invalid or no longer set " error.

Please see "Inwardfrm" in the attached file for your reference.


What i am doing wrong?

Thank You

@cheekybuddha
 

Attachments

Last edited:
Why not walk your code with F8 and setting breakpoints?
That should show you the error and get you into be able to debug your code.

It actually happens to me on first pass of the form?

Edit:
You are just requerying this form source

Code: 
SELECT [inward register].*,
       [inward register].[received date],
       [inward register].[name of sender],
       [inward register].[farmer name],
       [inward register].village,
       [inward register].tpa,
       [inward register].[tr report],
       [inward register].[other documents]
FROM   [inward register]
WHERE  (
       ( ( [inward register].[received date] ) =
         [forms] ! [inwardfrm] ! [searchbox] )
         AND ( ( Isdate([forms] ! [inwardfrm] ! [searchbox]) ) = true ) )
        OR ( ( ( [inward register].[name of sender] ) LIKE
                     "*" & [forms] ! [inwardfrm] ! [searchbox] & "*" )
             AND ( ( Isdate([forms] ! [inwardfrm] ! [searchbox]) ) = false ) )
        OR (( ( [inward register].[farmer name] ) LIKE
                   "*" & [forms] ! [inwardfrm] ! [searchbox] & "*" ))
        OR (( ( [inward register].village ) LIKE "*" & [forms] ! [inwardfrm] !
                                                      [searchbox] & "*" ))
        OR (( ( [inward register].tpa ) LIKE "*" & [forms] ! [inwardfrm] !
                                             [searchbox] & "*"
                ))
        OR (( ( [inward register].[tr report] ) LIKE
              "*" & [forms] ! [inwardfrm] !
                   [searchbox] & "*" ))
        OR (( ( [inward register].[other documents] ) LIKE
                   "*" & [forms] ! [inwardfrm] ! [searchbox] & "*" ));

In fact only searching on the date appears to work?

I have no idea as to the issue ATM, but I would perhaps swap the search around and have date as last. Remove them all and add one at a time.
Perhaps add ' for your string values?
 
Last edited:
Your sql

SELECT [Inward Register].*, [Inward Register].[Received Date], [I....

you are bringing through fields twice which probably confuses the hell out of sql

Lose the .* or remove all the other fields from the select statement (untick the show button) - also modify your form to bind to the right fields

Since you are bringing through all the data initially, no need for your horribly complex where clause, just set the form recordsource to the name of the table and then filter

Code: 
Private Sub Searchbtn_Click()

    If IsDate(Searchbox) Then
        Filter = "[Received Date]= #" & Format(Searchbox, "yyyy-mm-dd") & "#"
    Else
        Filter = "[Name Of Sender] Like '*" & Searchbox & "*'" & _
            " Or [Farmer Name] Like '*" & Searchbox & "*'" & _
            " Or Village Like '*" & Searchbox & "*'" & _
            " Or TPA Like '*" & Searchbox & "*'" & _
            " Or [TR Report] Like '*" & Searchbox & "*'" & _
            " Or [Other Documents] Like '*" & Searchbox & "*'"
    End If

    FilterOn = True

End Sub
 
Gasman said:
Why not walk your code with F8 and setting breakpoints?
That should show you the error and get you into be able to debug your code.

It actually happens to me on first pass of the form?

Edit:
You are just requerying this form source

Code: 
SELECT [inward register].*,
       [inward register].[received date],
       [inward register].[name of sender],
       [inward register].[farmer name],
       [inward register].village,
       [inward register].tpa,
       [inward register].[tr report],
       [inward register].[other documents]
FROM   [inward register]
WHERE  (
       ( ( [inward register].[received date] ) =
         [forms] ! [inwardfrm] ! [searchbox] )
         AND ( ( Isdate([forms] ! [inwardfrm] ! [searchbox]) ) = true ) )
        OR ( ( ( [inward register].[name of sender] ) LIKE
                     "*" & [forms] ! [inwardfrm] ! [searchbox] & "*" )
             AND ( ( Isdate([forms] ! [inwardfrm] ! [searchbox]) ) = false ) )
        OR (( ( [inward register].[farmer name] ) LIKE
                   "*" & [forms] ! [inwardfrm] ! [searchbox] & "*" ))
        OR (( ( [inward register].village ) LIKE "*" & [forms] ! [inwardfrm] !
                                                      [searchbox] & "*" ))
        OR (( ( [inward register].tpa ) LIKE "*" & [forms] ! [inwardfrm] !
                                             [searchbox] & "*"
                ))
        OR (( ( [inward register].[tr report] ) LIKE
              "*" & [forms] ! [inwardfrm] !
                   [searchbox] & "*" ))
        OR (( ( [inward register].[other documents] ) LIKE
                   "*" & [forms] ! [inwardfrm] ! [searchbox] & "*" ));

In fact only searching on the date appears to work?

I have no idea as to the issue ATM, but I would perhaps swap the search around and have date as last. Remove them all and add one at a time.
Perhaps add ' for your string values?
Click to expand...
can u please take a look of attached file ? I am very confused in this
 

Attachments

CJ_London said:
Your sql

SELECT [Inward Register].*, [Inward Register].[Received Date], [I....

you are bringing through fields twice which probably confuses the hell out of sql

Lose the .* or remove all the other fields from the select statement (untick the show button) - also modify your form to bind to the right fields

Since you are bringing through all the data initially, no need for your horribly complex where clause, just set the form recordsource to the name of the table and then filter

Code: 
Private Sub Searchbtn_Click()

    If IsDate(Searchbox) Then
        Filter = "[Received Date]= #" & Format(Searchbox, "yyyy-mm-dd") & "#"
    Else
        Filter = "[Name Of Sender] Like '*" & Searchbox & "*'" & _
            " Or [Farmer Name] Like '*" & Searchbox & "*'" & _
            " Or Village Like '*" & Searchbox & "*'" & _
            " Or TPA Like '*" & Searchbox & "*'" & _
            " Or [TR Report] Like '*" & Searchbox & "*'" & _
            " Or [Other Documents] Like '*" & Searchbox & "*'"
    End If

    FilterOn = True

End Sub
Click to expand...
Thank Sir, Solve all my queries.. This works like a charm. I was very very confused since last 1 week to solve this and may be drop this idea. But now looking forward. Thanks again
 
Use the following SQL as the RecordSource for your form:
SQL: 
SELECT
  ID,
  [Received Date],
  [Name Of Sender],
  Place,
  District,
  [Farmer Name],
  Village,
  TPA,
  [TR Report],
  [Other Documents]
FROM [Inward Register]
WHERE (IsDate([Forms]![InwardFrm]![Searchbox]) = True AND [Received Date]=[Forms]![InwardFrm]![Searchbox])
OR (IsDate([Forms]![InwardFrm]![Searchbox]) = False AND
  (
    [Name Of Sender] Like "*" & [Forms]![InwardFrm]![Searchbox] & "*"
    OR
    [Farmer Name] Like "*" & [Forms]![InwardFrm]![Searchbox] & "*"
    OR
    Village Like "*" & [Forms]![InwardFrm]![Searchbox] & "*"
    OR
    TPA Like "*" & [Forms]![InwardFrm]![Searchbox] & "*"
    OR
    [TR Report] Like "*" & [Forms]![InwardFrm]![Searchbox] & "*"
    OR
    [Other Documents] Like "*" & [Forms]![InwardFrm]![Searchbox] & "*"
  )
);

The * (all fields) caused Access to have a freak out, so I found you had to list all fields individually - not sure why.

When you paste in the SQL above, Access will change it in to its incomprehensible garbage, but it will be functionally equivalent.

hth,

d
 
CJ_London said:
Your sql

SELECT [Inward Register].*, [Inward Register].[Received Date], [I....

you are bringing through fields twice which probably confuses the hell out of sql

Lose the .* or remove all the other fields from the select statement (untick the show button) - also modify your form to bind to the right fields

Since you are bringing through all the data initially, no need for your horribly complex where clause, just set the form recordsource to the name of the table and then filter

Code: 
Private Sub Searchbtn_Click()

    If IsDate(Searchbox) Then
        Filter = "[Received Date]= #" & Format(Searchbox, "yyyy-mm-dd") & "#"
    Else
        Filter = "[Name Of Sender] Like '*" & Searchbox & "*'" & _
            " Or [Farmer Name] Like '*" & Searchbox & "*'" & _
            " Or Village Like '*" & Searchbox & "*'" & _
            " Or TPA Like '*" & Searchbox & "*'" & _
            " Or [TR Report] Like '*" & Searchbox & "*'" & _
            " Or [Other Documents] Like '*" & Searchbox & "*'"
    End If

    FilterOn = True

End Sub
Click to expand...
To be fair, I tried with just .* and still got the error? Also tried with singe quotes :(
Only on the text fields though?
 
Gasman said:
To be fair, I tried with just .* and still got the error?
Click to expand...
I also found I still got the error with SELECT * (using the complicated WHERE clause, didn't test with Me.Filter), and I too continued to get the error.

Only by listing all the required fields individually stopped the error.

Using Me.Filter method, I just replaced the SQL RecordSource with solely the table name (not a SELECT statement) and it worked OK.
 
cheekybuddha said:
The * (all fields) caused Access to have a freak out, so I found you had to list all fields individually - not sure why.
Click to expand...
Because the controls were bound to [inward register].fieldname due to the repeating names. When you remove the repeat, access is looking for a column with the table name which now no longer exists
 
You must log in or register to reply here.

Similar threads

R
Search form works w/32bit not w/64bit Access
Replies
4
Views
243
arnelgp
arnelgp
J
SQL string v stored query
2
Replies
20
Views
766
GPGeorge
GPGeorge
H
Get data from previous entry in a text box
Replies
5
Views
397
Pat Hartman
P
T
Solved Filter form after update with code
Replies
9
Views
222
arnelgp
arnelgp
E
Search Button Not Finding Record and populating the text boxes on form.
Replies
13
Views
860
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom