multiple criteria

  • Thread starter Thread starter taco
  • Start date Start date
T

taco

Hi everyone;

I have a Table called Transactions. In this table I'm storing data as; which
employee, for which customer, on which machine, what amount of trasaction
made. Now I'm trying to build a form for reporting. On this form, criterias
are choosen by combo boxes. I would like to have report based on just
employee, or customer or between dates. One by one there is no problem to
build different queries for each report criteria. But when I want to have a
report like "Between 01/11/08 and 20/11/08, employee "A"'s actions (date
criteria and customer is blank) query is not working. How can I build the
query to work properly for any criteria variation ? Just employee's actions,
or just customer's actions, or emloyee's actions with one single spesific
customer, or between some dates customer's actions on one spesific machine
regardless who was the employee, etc.

here is the query I've tried;
SELECT Transactions.TrDate, Transactions.TrHour, Transactions.Machine,
Transactions.Attendance, Transactions.Customer, Transactions.CrIn,
Transactions.CrOut, Transactions.TrValue, Transactions.Cash
FROM Transactions
WHERE Transactions.TrDate Between Forms!SDate!Text15 And Forms!SDate!Text17
And Transactions.Machine=Forms!SDate!Combo10 And
Transactions.Customer=Forms!SDate!Combo13 And
(Transactions.Attendance=Forms!SDate!Combo8 Or Forms!SDate!Combo8 Is Null);


Your help is most appreciated.

Regards;
 
I try to remove all dynamic criteria from the query. Consider code to open
the report like:
Dim strWhere as String
strWhere = "1=1 "
If not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [TrDate]>= #" & _
Me.txtStartDate & "# "
End If
If not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND [TrDate]<= #" & _
Me.txtEndDate & "# "
End If
If not IsNull(Me.cboMachine) Then
strWhere = strWhere & " AND [Machine]>= """ & _
Me.cboMachine & """ "
End If
' --- etc ---
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere
 
Thanks a lot for your time and help Duane. I'll try this and let you know
about the result.

Best Regards/

Duane Hookom said:
I try to remove all dynamic criteria from the query. Consider code to open
the report like:
Dim strWhere as String
strWhere = "1=1 "
If not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [TrDate]>= #" & _
Me.txtStartDate & "# "
End If
If not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND [TrDate]<= #" & _
Me.txtEndDate & "# "
End If
If not IsNull(Me.cboMachine) Then
strWhere = strWhere & " AND [Machine]>= """ & _
Me.cboMachine & """ "
End If
' --- etc ---
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP


taco said:
Hi everyone;

I have a Table called Transactions. In this table I'm storing data as; which
employee, for which customer, on which machine, what amount of trasaction
made. Now I'm trying to build a form for reporting. On this form, criterias
are choosen by combo boxes. I would like to have report based on just
employee, or customer or between dates. One by one there is no problem to
build different queries for each report criteria. But when I want to have a
report like "Between 01/11/08 and 20/11/08, employee "A"'s actions (date
criteria and customer is blank) query is not working. How can I build the
query to work properly for any criteria variation ? Just employee's actions,
or just customer's actions, or emloyee's actions with one single spesific
customer, or between some dates customer's actions on one spesific machine
regardless who was the employee, etc.

here is the query I've tried;
SELECT Transactions.TrDate, Transactions.TrHour, Transactions.Machine,
Transactions.Attendance, Transactions.Customer, Transactions.CrIn,
Transactions.CrOut, Transactions.TrValue, Transactions.Cash
FROM Transactions
WHERE Transactions.TrDate Between Forms!SDate!Text15 And Forms!SDate!Text17
And Transactions.Machine=Forms!SDate!Combo10 And
Transactions.Customer=Forms!SDate!Combo13 And
(Transactions.Attendance=Forms!SDate!Combo8 Or Forms!SDate!Combo8 Is Null);


Your help is most appreciated.

Regards;
Click to expand...
Click to expand...
 
Here we go;

I wrote the code you've sent like this according to my database elements;

Private Sub Command33_Click()
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.From) Then
strWhere = strWhere & " AND [TrDate]>= #" & _
Me.From & "# "
End If
If Not IsNull(Me.To) Then
strWhere = strWhere & " AND [TrDate]<= #" & _
Me.To & "# "
End If
If Not IsNull(Me.Mac) Then
strWhere = strWhere & " AND [Machine]>= """ & _
Me.Mac & """ "
End If
If Not IsNull(Me.Cust) Then
strWhere = strWhere & " AND [Customer]>= """ & _
Me.Cust & """ "
End If
If Not IsNull(Me.Att) Then
strWhere = strWhere & " AND [Attendante]>= """ & _
Me.Att & """ "
End If
DoCmd.OpenReport "Transactions", acViewPreview, , strWhere

End Sub

Report is bound to table "transactions" directly. When I execute the code by
clicking the button it says;

"Run Time Error '3464'
Data typr mismatch in criteria expression"

Any idea where I'm wrong ???

taco said:
Thanks a lot for your time and help Duane. I'll try this and let you know
about the result.

Best Regards/

Duane Hookom said:
I try to remove all dynamic criteria from the query. Consider code to open
the report like:
Dim strWhere as String
strWhere = "1=1 "
If not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [TrDate]>= #" & _
Me.txtStartDate & "# "
End If
If not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND [TrDate]<= #" & _
Me.txtEndDate & "# "
End If
If not IsNull(Me.cboMachine) Then
strWhere = strWhere & " AND [Machine]>= """ & _
Me.cboMachine & """ "
End If
' --- etc ---
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP


taco said:
Hi everyone;

I have a Table called Transactions. In this table I'm storing data as; which
employee, for which customer, on which machine, what amount of trasaction
made. Now I'm trying to build a form for reporting. On this form, criterias
are choosen by combo boxes. I would like to have report based on just
employee, or customer or between dates. One by one there is no problem to
build different queries for each report criteria. But when I want to have a
report like "Between 01/11/08 and 20/11/08, employee "A"'s actions (date
criteria and customer is blank) query is not working. How can I build the
query to work properly for any criteria variation ? Just employee's actions,
or just customer's actions, or emloyee's actions with one single spesific
customer, or between some dates customer's actions on one spesific machine
regardless who was the employee, etc.

here is the query I've tried;
SELECT Transactions.TrDate, Transactions.TrHour, Transactions.Machine,
Transactions.Attendance, Transactions.Customer, Transactions.CrIn,
Transactions.CrOut, Transactions.TrValue, Transactions.Cash
FROM Transactions
WHERE Transactions.TrDate Between Forms!SDate!Text15 And Forms!SDate!Text17
And Transactions.Machine=Forms!SDate!Combo10 And
Transactions.Customer=Forms!SDate!Combo13 And
(Transactions.Attendance=Forms!SDate!Combo8 Or Forms!SDate!Combo8 Is Null);


Your help is most appreciated.

Regards;
Click to expand...
Click to expand...
Click to expand...
 
I expect you have a numeric field in the criteria. Your code expects Machine,
Customer, or Attendante to all be text.

If Machine is numeric, the code would be:
If Not IsNull(Me.Mac) Then
strWhere = strWhere & " AND [Machine]>= " & _
Me.Mac & " "
End If

--
Duane Hookom
Microsoft Access MVP


taco said:
Here we go;

I wrote the code you've sent like this according to my database elements;

Private Sub Command33_Click()
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.From) Then
strWhere = strWhere & " AND [TrDate]>= #" & _
Me.From & "# "
End If
If Not IsNull(Me.To) Then
strWhere = strWhere & " AND [TrDate]<= #" & _
Me.To & "# "
End If
If Not IsNull(Me.Mac) Then
strWhere = strWhere & " AND [Machine]>= """ & _
Me.Mac & """ "
End If
If Not IsNull(Me.Cust) Then
strWhere = strWhere & " AND [Customer]>= """ & _
Me.Cust & """ "
End If
If Not IsNull(Me.Att) Then
strWhere = strWhere & " AND [Attendante]>= """ & _
Me.Att & """ "
End If
DoCmd.OpenReport "Transactions", acViewPreview, , strWhere

End Sub

Report is bound to table "transactions" directly. When I execute the code by
clicking the button it says;

"Run Time Error '3464'
Data typr mismatch in criteria expression"

Any idea where I'm wrong ???

taco said:
Thanks a lot for your time and help Duane. I'll try this and let you know
about the result.

Best Regards/

Duane Hookom said:
I try to remove all dynamic criteria from the query. Consider code to open
the report like:
Dim strWhere as String
strWhere = "1=1 "
If not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [TrDate]>= #" & _
Me.txtStartDate & "# "
End If
If not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND [TrDate]<= #" & _
Me.txtEndDate & "# "
End If
If not IsNull(Me.cboMachine) Then
strWhere = strWhere & " AND [Machine]>= """ & _
Me.cboMachine & """ "
End If
' --- etc ---
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP


:

Hi everyone;

I have a Table called Transactions. In this table I'm storing data as; which
employee, for which customer, on which machine, what amount of trasaction
made. Now I'm trying to build a form for reporting. On this form, criterias
are choosen by combo boxes. I would like to have report based on just
employee, or customer or between dates. One by one there is no problem to
build different queries for each report criteria. But when I want to have a
report like "Between 01/11/08 and 20/11/08, employee "A"'s actions (date
criteria and customer is blank) query is not working. How can I build the
query to work properly for any criteria variation ? Just employee's actions,
or just customer's actions, or emloyee's actions with one single spesific
customer, or between some dates customer's actions on one spesific machine
regardless who was the employee, etc.

here is the query I've tried;
SELECT Transactions.TrDate, Transactions.TrHour, Transactions.Machine,
Transactions.Attendance, Transactions.Customer, Transactions.CrIn,
Transactions.CrOut, Transactions.TrValue, Transactions.Cash
FROM Transactions
WHERE Transactions.TrDate Between Forms!SDate!Text15 And Forms!SDate!Text17
And Transactions.Machine=Forms!SDate!Combo10 And
Transactions.Customer=Forms!SDate!Combo13 And
(Transactions.Attendance=Forms!SDate!Combo8 Or Forms!SDate!Combo8 Is Null);


Your help is most appreciated.

Regards;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top