Search button using more than one text field Access vba

[Abeetan]

This is my code below. I am trying to search a database using two different dates, company name

I am getting an error when one of the date fields is empty or null. How can I solve this issue or bypass if the date search field is empty to ignore it in the search or search for an empty field?

<Dim SQL As String
SQL = "SELECT * from qryRequestInternal where ([DateRequestSent] = #" & txt_Search_Sdate & "# AND [DateReceived] = #" & txt_Search_Rdate & "# AND (companyName like ""*" & txt_SCompNa & "*"") )"
Me.sfrmRequestInternal.Form.RecordSource = SQL
Me.sfrmRequestInternal.Form.Requery

Me.sfrmRequestInternal_col.Form.RecordSource = SQL
Me.sfrmRequestInternal_col.Form.Requery
End Sub/>

 

[oleronesoftwares]

Add the OR operator in your sql statement

SQL = "SELECT * from qryRequestInternal where ([DateRequestSent] = #" & txt_Search_Sdate & "# AND [DateReceived] = #" & txt_Search_Rdate & "# AND (companyName like ""*" & txt_SCompNa & "*"") OR [DateRequestSent] = #" & txt_Search_Sdate & "# OR [DateReceived] = #" & txt_Search_Rdate & "# AND (companyName like ""*" & txt_SCompNa & "*"" )"

 

[Abeetan]

Add the OR operator in your sql statement

SQL = "SELECT * from qryRequestInternal where ([DateRequestSent] = #" & txt_Search_Sdate & "# AND [DateReceived] = #" & txt_Search_Rdate & "# AND (companyName like ""*" & txt_SCompNa & "*"") OR [DateRequestSent] = #" & txt_Search_Sdate & "# OR [DateReceived] = #" & txt_Search_Rdate & "# AND (companyName like ""*" & txt_SCompNa & "*"" )"

Thank you for your reply. That did not work. I am still getting an error message.

 

[CJ_London]

am getting an error when one of the date fields is empty or null

what is the error?

what do you mean by date field? the field in the table or the control on the form?

and what do you want to happen if one (or both) the date fields/controls (confirm as above) are null? to return a record anyway? or not return the record? or something else?

 

[Abeetan]

what is the error?

what do you mean by date field? the field in the table or the control on the form?

and what do you want to happen if one (or both) the date fields/controls (confirm as above) are null? to return a record anyway? or not return the record? or something else?

okay, So I have two date fields and a few other short name fields. I have a form that has text fields that I use for keyword searches. For instance, I want a record with the date field such "26/07/2021" and company Name "ACEM" and the remaining fields including the other date field are empty or blank (i.e. I did not specify a search criterion in that date field box). Here is where the issue arises, all date field boxes (text fields) must be not null. Otherwise, I get an error in expression.

 

[Gasman]

Build the SQL string depending on what controls you have completed.

 

[Abeetan]

 

[Uncle Gizmo]

bypass if the date search field is empty

You return turn the date parameter through a function and set the function up so that if the date field is empty the function returns an empty string thereby removing itself from the SQL statement.

I demonstrate the technique in my my videos on showing how to build a search form here:-

Building Search Criteria - Nifty Access

Want to build yourself a Search Form? Want to build yourself a Search Form? Then You are in the right place! This is no Ordinary Search Form, this Search Form incorporates some Object Orientated programming techniques. The process I take you through is exactly the same process as I took when I...

www.niftyaccess.com

 

[strive4peace]

hi @Abeetan,
if your Windows date format isn't mm/dd/yy, it would be good to wrap txt_Search_Sdate in a function that puts it that way, or use the universal yyyy-mm-dd and then time, if it's relevant

...  #" & format(Me.txt_Search_Sdate, "yyyy-mm-dd hh:nn:ss" & "# ...

If time ISN'T relevant, realize that Date/Time stores a fractional time as well, and it'll be there if Now() instead of Date() was used to supply the value. In the underlying number stored to represent date and time, date is the integer part while time is a fraction -- so if you might have times as well, wrap DateRequestSent and DateReceived in functions that strip the time part.

also good, imo, to preface control references with 'Me.' so its clear they're coming from the form that the code is behind

 

[Uncle Gizmo]

UPDATE!!! I posted a link to an earlier set of instructions on creating a search form. The correct instructions are here:-

Building Advanced Search Criteria:-

Building Advanced Search Criteria - Nifty Access

Building Advanced Search Criteria My second set of Videos on Building Search Criteria! In this set of Videos I build on the information provided in the first set of videos here:- Building Search Criteria The code from the first set works ok, but it's not that efficient. It could be Slow on...

www.niftyaccess.com

which follows on from the set of instructions I posted earlier.

And this Video in Particular:-

Building Advanced Search Criteria 2 - Nifty Access​

- at time index 30 Seconds -

I demonstrate how to to build an SQL Statement and return all of the records when the textbox is empty. You build your SQL Statement from a set of functions and when the function that, in this case, points at the user surname, and the user surname is empty, then the function that builds that particular part of the search criteria is set to return an empty string, in effect removing itself from the SQL statement:-

Building Advanced Search Criteria 2 - Nifty Access​

 

[HillTJ]

There is also a good treatise on building a search string on AllenBrowne.com. I've adapted this on multiple occasions.