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

H

hrdpgajjar

Registered User.
Local time
Tomorrow, 05:01
Joined
Sep 24, 2019
Messages
71
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: 49
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