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]

 

[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

 

[HillTJ]

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