query with date range and two criteria from form

[tagteam]

I am trying to run a report that allows the user to enter a start date and end date in a form as well as a car number and invoice number.

If just the date is entered it should pull all data for that date range
If the date and the car number is entered it should pull just entries with that car number during that date range
if just the invoice number is entered it should pull just entries with that invoice number during that date range

however when i run it if I dont enter a car number or an invoice number i dont get any records

in the query i have
Import Date - criteria code is: between [forms]![repairs]![startdate] and [forms]![repairs]![enddate] - to get the date range

Car number - criteria code is: NZ([forms]![repairs]![carnumber], >1) so that if they dont enter a car number it will get any car greater than one or in this case all cars

invoice Number - in the or line is: NZ9[forms]![repirs]![invoicenumber], like "*") so that it will get everything if it is left blank.



If i run this directly in the query it works but I dont end up with the correct number of cars

If i run it off the form i dont get any results.
Thanks

[ItsMe]

Perhaps an unbound form and some VBA would be the best approach. Parameterized queries have limitations but you could introduce a wildcard or two to your expressions in the query to manage controls on your form that do not have a value.

[orange]

I would just caution you that when "users" input data values manually, there can be many typos.
You might consider listbox or combobox to identify existing car number and/or invoice number.
I would also suggest a datepicker for the dates.

I'm not saying you can't have users typing values, you can. But you can limit typos etc by selecting from a list.

[tagteam]

Sorry, the date fields are date pickers and the car number and the invoice number are combo boxes that only show the possible cars or invoice number for that date range and then the form is set to refresh on update.

Not too good with VBA, I will search the web to try to find something basic that is already done and see if I can modify it. the form is tied to the query but I have no problem putting the query in vba if i can get it to work.

Any tips on how to get started on this, I am pretty bad but would it be something like this?

Private Sub viewinfo()
dim carnumberint as integer
dim invoicenumberint as integer

carnumberint = [forms]![repairs]![carnumber]
invoicenumberint = [forms]![repairs]![invoicenumber]

SQL="Select [add fields] FROM [repairstable]  WHERE ([importdate] is between [forms]![repairs]![startdate] AND [forms]![repairs]![startdate]) AND CarNumber is = carnumberint OR invoicenumber is = invoicenumberint

DoCmd.Run sql 

end

The main problem is that with the date range I cant seem to leave any of the variables blank or it doesnt give me the right number of cars.

[orange]

See this set of tutorials. It will help with setting up parameters for running a report.
There are 6 and they are well worth reading.
Good luck.

[ItsMe]

You want to learn as much as possible because it will help you to ask the correct questions. It will also help to understand strings as they get more and more complicated because of validation.

When you build criteria for a WHERE clause you want to do data validation. This is why I recommended using VBA. It may be beneficial to use VBA to validate data before adding it to your WHERE clause.

The code you provided is in the correct spirit. For starters you may want to make sure that you have a value in a control before you try to initialize your variable with it.

If Not IsNull([Forms]![repairs]![carnumber]) Then
carnumberint = [Forms]![repairs]![carnumber]
End If
If Not IsNull([Forms]![repairs]![invoicenumber]) Then
invoicenumberint = [Forms]![repairs]![invoicenumber]
End If

Integers can not accept null. So you will want to test for that. It can happen if a user decides to erase the value in the control.

Here is another approach to testing for null. If a control was null then your variable will = 0.

carnumberint = 0
carnumberint = Nz([Forms]![repairs]![carnumber])
invoicenumberint = 0
invoicenumberint = Nz([Forms]![repairs]![invoicenumber])

THe following code does not usually go into a single procedure but this would be an example of adding to a SELECT statement after doing data validation.

Dim strSQL As String
Dim strWhere As String
strWhere = "WHERE [PrimaryKey] >0"  'Start your WHERE clause with criteria that will always return all of the records
'This alows you to concatenate additional criteria to the existing WHERE clause
strSQL = "Select * FROM [repairstable] " & strWhere 'This will be your base SELECT query
'For now, we are done with strWhere. Initialize to empty string so we can build onto it again
strWhere = ""
'Now validate your data in the various controls and add to your base SELECT query
If Not IsNull([Forms]![repairs]![invoicenumber]) And Not IsNull([Forms]![repairs]![carnumber]) Then
strWhere = (strWhere & " AND ") & "CarNumber = " & carnumberint & " OR invoicenumber = " & invoicenumberint
'save the Between operator for last - personal preference
If Not IsNull([Forms]![repairs]![StartDate]) And Not IsNull([Forms]![repairs]![StartDate]) Then 'Make sure we have dates for each side of BETWEEN
strWhere = (strWhere & " AND ") & "[importdate] BETWEEN #" & [Forms]![repairs]![StartDate] & "# AND #" & [Forms]![repairs]![StartDate] & "#"
Else
MsgBox "Please provide a date in each of the date fields"
Exit Sub  'Abort mission until after user popolates date controls
End If
'Now that strWhere may or may not have changed, concatenate to your strSQL
strSQL = strSQL & strWhere

[tagteam]

I see that you break the sql statement down into parts and assign it to variables. That is much more complex than I am able to write on they fly. Over the weekend I will watch the videos that orange recommended. then I will try to dissect this code and apply it to my scenario.
Thanks

[ItsMe]

You do not have to assign the values to a variable before adding it to a WHERE clause. I was just providing examples. You had an example using an integer variable. I elaborated on that. What you do need to do, though, is data validation.

So after you get some tutorials behind you, you can take another look. When, where and how you validate your data is key.

[orange]

The tutorials I recommended are not videos. They are online examples with step by step info.

[June7]

Here is another example of dynamic parameterized query http://datapigtechnologies.com/flash...earchform.html

Here is another example of VBA alternative http://www.allenbrowne.com/ser-62code.html