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

H

hrdpgajjar

Registered User.
Local time
Today, 23:17
Joined
Sep 24, 2019
Messages
153
Hi all,
I have designed a search data form which has a "Searchbox" (a text box with that name). Now i need to search by a farmer name (a string) or a dispatch date (date value) with the same text box. how can i do that ? thanks

below is my query to search
Code: 
Like "*" & [Forms]![View Inward Register]![Searchbox] & "*"


it works fine for search by name but not works in date


cheers
 
Dates need # delimters and in mm/dd/yyyy or yyyy-mm-dd format.
You could use OR and the concatenated date control?
 
Code: 
WHERE FarmerName & DispatchDate Like "*" & Forms!View Inward Register!Searchbox & "*"
This is a suggestion for simple requirements.
The solution and the whole approach you want has less to do with good programming that takes performance into account.
 
If you are searching for complete dates (and not just partial dates) then you can try something like:
Code: 
WHERE (IsDate([Forms]![View Inward Register]![Searchbox]) = False AND [Farmer] Like "*" & [Forms]![View Inward Register]![Searchbox] & "*")
   OR (IsDate([Forms]![View Inward Register]![Searchbox]) = True AND [DateField] = [Forms]![View Inward Register]![Searchbox])
 
Last edited:
Gasman said:
Dates need # delimters and in mm/dd/yyyy or yyyy-mm-dd format.
You could use OR and the concatenated date control?
Click to expand...
can u guide me how can i apply that in my query as i am new to access
 
If you use cheekbuddha's code, you do not need to format, as it will compare date against date.
 
cheekybuddha said:
If you are searching for complete dates and not just partial dates) then you can try something like:
Code: 
WHERE (IsDate([Forms]![View Inward Register]![Searchbox]) = False AND [Farmer] Like "*" & [Forms]![View Inward Register]![Searchbox] & "*")
   OR (IsDate([Forms]![View Inward Register]![Searchbox]) = True AND [DateField] = [Forms]![View Inward Register]![Searchbox])
Click to expand...
Tried does not work
 
cheekybuddha said:
How did you try?

Please change your query to SQL view and copy and paste the whole query here.
Click to expand...
below is my SQL view

Code: 
SELECT [Inward Register].ID, [Inward Register].[Received Date], [Inward Register].[Farmer Name], [Inward Register].Village, [Inward Register].[Name Of Sender], [Inward Register].Place, [Inward Register].District
FROM [Inward Register]
WHERE (IsDate([Forms]![View Inward Register]![Searchbox1]) = False AND [Name Of Sender] Like "*" & [Forms]![View Inward Register]![Searchbox1] & "*")
OR (IsDate([Forms]![View Inward Register]![Searchbox1]) = True AND [Received Date] = [Forms]![View Inward Register]![Searchbox1])


what i am doing wrong?


thanks
 
Last edited:
I'll give you a hint to save future typing and make things easier to read. You have only a single item in the FROM clause so you can omit its use in other clauses and end up typing less. I.e. only one data source, no need to qualify references from that source.

Then, if you remove spaces and just run the words together in all of your multi-word names, you can drop the square brackets. This cuts down on required typing two fewer characters per reference and it isn't any harder to read than the stuff with lots of [] floating around everything.

Code: 
SELECT ID, ReceivedDate, FarmerName, Village, NameOfSender, Place, District
FROM InwardRegister
WHERE (IsDate(Forms!ViewInwardRegister!Searchbox1 = False AND NameOfSender Like "*" & Forms!ViewInwardRegister!Searchbox1 & "*")
OR (IsDate(Forms!ViewInwardRegister!Searchbox1) = True AND ReceivedDate = Forms!ViewInwardRegister!Searchbox1)

Two options come to mind.

Code: 
...WHERE IIF( IsDate( Forms!ViewInwardRegister!Searchbox1 ),
    ReceivedDate = "#" & Forms!ViewInwardRegister!Searchbox1 & "#" ,
    NameOfSender Like "*" & Forms!ViewInwardRegister!Searchbox1 & "*"  )

or

Code: 
...WHERE IIF( IsDate( Forms!ViewInwardRegister!Searchbox1 ),
    ReceivedDate = CDate( Forms!ViewInwardRegister!Searchbox1 ),
    NameOfSender Like "*" & Forms!ViewInwardRegister!Searchbox1 & "*"   )

EDITED to fix unbalanced parentheses by TDM
 
Inserted question: Where does one get the conviction that a complete date is entered in Searchbox1?
Code: 
... LIKE "*something*"
Something like this makes you expect anything if it's supposed to be simple and dirty.
 
Our member ebs17 is correct... ISDATE only returns TRUE if the input string not only LOOKS like a date, but could be a VALID date. So if a partial date is entered or the string is not a date you would find on a calendar (like Feb 31st....) you would treat it like a name and get nothing. Granted, you SHOULDN'T get a match in that case, but you wouldn't be sure WHY it didn't give you the match.
 
cheekybuddha said:
It works for me - see attached file.

The search button runs some code to requery the form's recordset.
Click to expand...
I have pasted my subform in another main form "View Inward Register" and pasted all code to search button as under,


Code: 
Dim SQL1 As String


SQL1 = " SELECT [Inward Register].ID, IsDate([Forms]![Subform TPA]![Searchbox1]) AS Expr1, [Inward Register].[Received Date], [Inward Register].[Name Of Sender], [Inward Register].Place, [Inward Register].District, [Inward Register].TPA" _
& "FROM [Inward Register]" _
& "WHERE (((IsDate([Forms]![Subform TPA]![Searchbox1])) = False) And (([Inward Register].TPA) Like " * " & [Forms]![View Inward Register]![Searchbox1])) Or (((IsDate([Forms]![Subform TPA]![Searchbox1])) = True) And (([Inward Register].[Received Date]) = [Forms]![View Inward Register]![Searchbox1]))" _
& "ORDER BY [Subform TPA].ID DESC;"


Me.TPAlist.Form.RecordSource = SQL1
Me.TPAlist.Requery


here it does not work, can u help how can i adjust my code?

My plan is to paste three subforms and run same query by a single button and a searchbox1 only.


cheers
 
hrdpgajjar said:
Code: 
SQL1 = " SELECT [Inward Register].ID, IsDate([Forms]![Subform TPA]![Searchbox1]) AS Expr1, [Inward Register].[Received Date], [Inward Register].[Name Of Sender], [Inward Register].Place, [Inward Register].District, [Inward Register].TPA" _ & "FROM [Inward Register]" _ & "WHERE (((IsDate([Forms]![Subform TPA]![Searchbox1])) = False) And (([Inward Register].TPA) Like " * " & [Forms]![View Inward Register]![Searchbox1])) Or (((IsDate([Forms]![Subform TPA]![Searchbox1])) = True) And (([Inward Register].[Received Date]) = [Forms]![View Inward Register]![Searchbox1]))" _ & "ORDER BY [Subform TPA].ID DESC;"
Click to expand...
This will error when you run you code - you have mucked up the quote marks and are trying to multiply strings!!!

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
 
cheekybuddha said:
This will error when you run you code - you have mucked up the quote marks and are trying to multiply strings!!!

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
Click to expand...
will try and let u know

thanks
 
hrdpgajjar said:
I have pasted my subform in another main form "View Inward Register" and pasted all code to search button as under,


Code: 
Dim SQL1 As String


SQL1 = " SELECT [Inward Register].ID, IsDate([Forms]![Subform TPA]![Searchbox1]) AS Expr1, [Inward Register].[Received Date], [Inward Register].[Name Of Sender], [Inward Register].Place, [Inward Register].District, [Inward Register].TPA" _
& "FROM [Inward Register]" _
& "WHERE (((IsDate([Forms]![Subform TPA]![Searchbox1])) = False) And (([Inward Register].TPA) Like " * " & [Forms]![View Inward Register]![Searchbox1])) Or (((IsDate([Forms]![Subform TPA]![Searchbox1])) = True) And (([Inward Register].[Received Date]) = [Forms]![View Inward Register]![Searchbox1]))" _
& "ORDER BY [Subform TPA].ID DESC;"


Me.TPAlist.Form.RecordSource = SQL1
Me.TPAlist.Requery


here it does not work, can u help how can i adjust my code?

My plan is to paste three subforms and run same query by a single button and a searchbox1 only.


cheers
Click to expand...
ALWAYS, ALWAYS, ALWAYS debug.print your sql string to ensure it is correct , before even trying to use it.
Then comment that out when it is correct and working.
 
cheekybuddha said:
This will error when you run you code - you have mucked up the quote marks and are trying to multiply strings!!!

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
Click to expand...
HI,
I have tried your code and got the attached error. I am not able to copy the error so attached screenshot.

and below is my 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

Me.TPAlist.Form.RecordSource = SQL1
Me.TPAlist.Requery
 

Attachments

  • Screenshot 2024-07-12 110953.png
    Screenshot 2024-07-12 110953.png
    35.6 KB · Views: 153
You must log in or register to reply here.

Similar threads

A
How to Properly Query and Search Multi-Valued Fields in a Form?
2
Replies
23
Views
1,176
MajP
MajP
D
Darshan Hiranandani : Creating a Form for Multiple Entries in a Table (Same Form) - Dispatch Details
Replies
7
Views
661
isladogs
isladogs
J
Sum Query and text box
Replies
11
Views
897
arnelgp
arnelgp
H
Solved Textbox "format" dropdown not working in properties in form design view
Replies
12
Views
900
GPGeorge
GPGeorge
H
Solved How to make a form to enter multiple entries in table with same form
2
Replies
38
Views
3,687
hrdpgajjar
H

Users who are viewing this thread

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