Order number "from" - "To" in form not working when combined

[rmork]

This is tricky..
I have a Form that produces a report. The first control is a combobox vith a list of names. I have also made it possible to choose "all" in the list.
The second control is for getting orders with date "from and "To".
The third is complicating things for me.
I would like a similar "From" - "To" fields as the date only with oredernumber instead, but I cant get it to work.

If I choose one name from the list it works, but if I choose not to enter ordernumber from/to I get nothing. I need help with the code that will return results even if the ordernumber-fields are left blank.

The second problem is that if I choose "All" and enter date and ordernumber it returns all ordernumbers, and not just those within the range that I entered..

The code that i use is:
WHERE (((AVD02Projects.OrderNumber) Between [Forms]![rptAVD02PrincipalCommission]![ONrFrom] And [Forms]![rptAVD02PrincipalCommission]![ONrTo]) AND ((AVD02Projects.OrderDate) Between [Forms]![rptAVD02PrincipalCommission]![PeriodFrom] And [Forms]![rptAVD02PrincipalCommission]![PeriodTo]) AND ((Principals.PrincipalID)=[Forms]![rptAVD02PrincipalCommission]![PrincipalID])) OR ((([Forms]![rptAVD02PrincipalCommission]![PrincipalID])='*'));

You help...

 

[TimTDP]

Try basing you report on a query. Filter the query on feilds in your form. Remember to use "like" for criterea that could be all.

 

[rmork]

Thanks for reply TimTDP.
Not sure hvat you mean...
The report is based on a query. How would you suggest using the "like"-operator in this.

This is my query in sql-wiew:

SELECT AVD02Projects.Projectnumber, Principals.PrincipalName, Clients.ClientName, AVD02Projects.OrderNumber, AVD02Projects.OrderDate, AVD02ProjectInvoices.CommissionPercent, AVD02Projects.ProjectValue, AVD02Projects.CommissionValue, AVD02Projects.CurrencyID, Currencies.Description, AVD02Projects.ProjectTypeID, AVD02ProjectInvoices.InvoiceDate, AVD02ProjectInvoices.PaidDate, AVD02Projects.PrincipalOrderNumber, AVD02ProjectInvoices.InvoiceNumber, AVD02ProjectInvoices.ExchangeRate, AVD02ProjectInvoices.InvoiceCommissionAmount, AVD02ProjectInvoices.InvoiceAmount, Principals.PrincipalNumber, Principals.PrincipalID
FROM (ClientCategoryTypes INNER JOIN Clients ON ClientCategoryTypes.ClientCategoryTypeID = Clients.ClientCategoryTypeID) INNER JOIN ((((AVD02Projects INNER JOIN Currencies ON AVD02Projects.CurrencyID = Currencies.CurrencyID) INNER JOIN Principals ON AVD02Projects.PrincipalID = Principals.PrincipalID) INNER JOIN Vessels ON AVD02Projects.VesselID = Vessels.VesselID) INNER JOIN AVD02ProjectInvoices ON AVD02Projects.AVD02ProjectID = AVD02ProjectInvoices.AVD02ProjectID) ON Clients.ClientID = Vessels.ClientID
WHERE (((AVD02Projects.OrderDate) Between [Forms]![rptAVD02PrincipalCommission]![PeriodFrom] And [Forms]![rptAVD02PrincipalCommission]![PeriodTo]) AND ((Principals.PrincipalID)=[Forms]![rptAVD02PrincipalCommission]![PrincipalID])) OR (((AVD02Projects.OrderNumber) Between [Forms]![rptAVD02PrincipalCommission]![ONrFrom] And [Forms]![rptAVD02PrincipalCommission]![ONrTo]) AND (([Forms]![rptAVD02PrincipalCommission]![PrincipalID])='*'));

 

[JoeAtWork]

Try basing you report on a query. Filter the query on feilds in your form. Remember to use "like" for criterea that could be all.

This approach often produces very slow running queries, because it means you are always filtering all fields, even the ones left blank on the search form.

I had a customer with a search form that had one indexed ID field, plus two or three search fields against text fields. The search usually took 30 seconds every time it was run. Most of the time, the users were searching by the ID and left the text fields blank.

I took out all the search parameters from the query. I then built up the WHERE clause of the query in code, only including filters that were actually filled in by the user. Something like:

Dim WHERE As STRING

If Len(txtID) > 0 Then
  WHERE = "ID = " & txtID
End If

If Len(txtName) > 0 Then
  If Len(WHERE) > 0 Then
    WHERE = WHERE & " AND "
  End If

  WHERE = WHERE & "LastName LIKE '*" & txtName & "*'"
End If

If Len(txtPhone) > 0 Then
  If Len(WHERE) > 0 Then
    WHERE = WHERE & " AND "
  End If

  WHERE = WHERE & "Phone LIKE '*" & txtPhone & "*'"
End If

DoCmd.OpenReport "rpt_TheReport", , , WHERE

The advantage of the above is that if the user only wants to search against ID (which was indexed), the WHERE clause only included that and the search was much faster (1 or 2 seconds) then when the LastName or Phone fields where also searched against.

 

[rmork]

Very interesting JoAtWork..

So how can I implement the ordernumber from/to in this code?
In the query I have tried between [] and () but it stops when nothing is filled out.

I am not a programmer so this is very difficult for me, but I'm learning..

 

[rmork]

I fixed it by adding this line to the cmdReportPreview_Click():

DoCmd.OpenReport "rptAVD02PrincipalCommission", acViewPreview, , "[OrderNumber] >= " & Me.ONrFrom.Value & " AND [OrderNumber] <= " & Me.ONrTo.Value & ""

how can I get results without entering ordrnumbers in the fields. I know I have to add something to this line, but can't figure it out...

 

[rmork]

update:
It does not work if I choose the "all" criteria in the combo...
Error saying: the expression is to complicated to be evaluated...(something else in norwegian)

 

[ProgramError]

You have to check for null or "" in the search fields first and if it is null or = "" then you need to search for the single criteria or supply a default for the missing data.

eg

dim searchcriteria as string
if Me.ONrTo = "" or isnull(Me.ONrTo) then
searchcriteria = "[OrderNumber] >= " & Me.ONrFrom.Value & " AND [OrderNumber] <= " & Me.ONrTo.Value & ""
else
searchcriteria = "[OrderNumber] = " & Me.ONrFrom.Value
end if

DoCmd.OpenReport "rptAVD02PrincipalCommission", acViewPreview, ,searchcriteria

remember: you may have to put '' around Me.ONrFrom if it is a number or ## if it is a date ie." <= '" & Me.ONrFrom.Value & "'"
try putting in a debug.print at the end of the 'if then else' statement to check the criteria string.

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[rmork]

Thank You ProgramError.
I will try this when i get back from vacation. My wife is naging me to go now...