1

I have made a search form that allows the user to search within company expense reports for certain clients, projects, employees, and date ranges.

For the Date Criteria Box I have the following formula:

Between [Forms]![Search Form]![txtDateFrom] And [Forms]![Search Form]![txtDateTo]

This works fine but if I don't input any dates then no records show up, I want to change this so that all records will show up if they are left blank.

I've been able to do this for the project, client, and employee fields with the following formulas:

Like "*" & [Forms]![Search Form]![cboProject] & "*"
Like "*" & [Forms]![Search Form]![cboClient] & "*"
Like "*" & [Forms]![Search Form]![txtEmployee] & "*"

You guys probably know exactly what this means but I'll briefly explain it anyways: when I enter something in the textbox for Employee, the combo box for Project, or the combo box for Client in my search for and run the query, only the values that match what I entered appear. The Like "*" bit makes it so that if nothing is entered all the values show up.

I want to do the same for the date range but it is a bit trickier for me to do, can anyone help me with the syntax of this to make my date from and date to boxes show everything if left empty? Because right now if i leave them empty no records are shown which is a huge problem for the user side.

I've tried:

Like "*" & (Between [Forms]![Search Form]![txtDateFrom] And [Forms]![Search Form]![txtDateTo]) & "*"

But it didn't work for me and when I would input date ranges the incorrect date ranges would appear in the query.

What is the correct way to write this?

Ricky Bobby
  • 106
  • 1
  • 14

2 Answers2

1

Sound like now your query returns the correct rows when both txtDateFrom and txtDateTo contain non-Null values.

But you want all rows returned when either txtDateFrom or txtDateTo is Null. In that case you can add 2 conditions to your WHERE clause:

WHERE
       your_date_field Between [Forms]![Search Form]![txtDateFrom] And [Forms]![Search Form]![txtDateTo]
    OR [Forms]![Search Form]![txtDateFrom] Is Null
    OR [Forms]![Search Form]![txtDateTo] Is Null
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • this is really great @HansUp but will this require sql? or can i type this into my criteria box – Ricky Bobby Feb 20 '15 at 17:38
  • Switch your query from Design View to SQL View. Add the first Is Null condition in SQL View and then you can switch back to Design View again to see how it is represented there. – HansUp Feb 20 '15 at 17:41
  • nevermind!! @HansUp this is amazing! worked like a charm thank you so much. i would upvote your comment but i don't have the reputation for it sadly. when i do i'll be sure to come back to this and give you that upvote! – Ricky Bobby Feb 20 '15 at 17:42
  • i was able to do it in the criteria box after all – Ricky Bobby Feb 20 '15 at 17:42
  • OK. Still take some time to compare the same changes in Design View and SQL View. That effort will introduce you to Access SQL syntax ... and that understanding will be invaluable for future Access development. Cheers. – HansUp Feb 20 '15 at 17:44
  • how do i include with multiple wheres? Like this WHERE ((([Master Expense Sheet].[Employee Name]) Like "*" & Forms![Search Form]!txtEmployee & "*") And (([Master Expense Sheet].Year) Like "*" & Forms![Search Form]!txtYear & "*") And (([Master Expense Sheet].Month) Like "*" & Forms![Search Form]!txtMonth & "*") And (([Master Expense Sheet].Date) Between Forms![Search Form]!txtDateFrom And Forms![Search Form]!txtDateTo) And (([Master Expense Sheet].Client) Like "*" & Forms![Search Form]!cboClient & "*"); – Ricky Bobby Feb 20 '15 at 20:52
  • sorry for the messiness – Ricky Bobby Feb 20 '15 at 20:53
  • I can't figure out the logic you're trying to accomplish from that. As I wild guess, I suspect you may need `OR` in place of `AND` for some of those. And use parentheses to group conditions which should be evaluated together. But that's really all I can say. You may want to submit a new question for that complex set of `WHERE` criteria. – HansUp Feb 20 '15 at 20:59
-2
Dim A As String

Dim B As String

Dim p As String

A = Nz(Forms!frmsearchInv!txtDateFrom, "1")

B = Nz(Forms!frmsearchInv!txtDateFor, "1")

If A = "1" Then

A = "0000-01-01"

End If

If B = "1" Then

B = "9999-01-01"

End If

  p = "select * from orders where [orderDate] >= #" & A & "# AND [orderDate] <=#" & B & "# "
dbc
  • 104,963
  • 20
  • 228
  • 340
Hamza Abbas
  • 1
  • 1
  • Thanks for posting this. But, might you please [edit] your answer to give some textual explanation of how this answers the question? A [proper explanation](https://meta.stackoverflow.com/q/392712) of how this works would add to the long-term value of your answer. – dbc May 13 '21 at 13:27
  • Also, since you are doing string concatenation, your code may be vulnerable to [SQL Injection Attack](https://bobby-tables.com/msaccess). See [Microsoft Access SQL Injection](https://stackoverflow.com/q/17244693/3744182), [How do I use parameters in VBA in the different contexts in Microsoft Access?](https://stackoverflow.com/q/49509615/3744182), [Avoiding SQL Injection in MS Access](https://stackoverflow.com/q/50166009/3744182). – dbc May 13 '21 at 13:34
  • hello, I was searching for the same subject, finally I thought about the largest and the lowest acceptable date in access, when you want to show all data, you can put the largest acceptable date range, or you can put your own date from textbox , and its worked for me – Hamza Abbas May 14 '21 at 15:57