Access Report Based On Multiple Criteria (1 Viewer)

vagues0ul

Registered User.
Local time
Today, 07:50
Joined
Sep 13, 2018
Messages
103
I am trying to open an access report based on two criteria on my form. here is the macro i am using.

Code:
="atdate = '" & [Forms]![Student_Attendance]![dateS] & "'"

for a single criteria it is working fine. but i am unable to add multple criteria for report. Here is the code i wrote for multiple criteria.

Code:
="atdate = '" & [Forms]![Student_Attendance]![dateS] & "'" And ="atdate = '" & [Forms]![Student_Attendance]![clasS].value & "'"

dateS is a textbox with date picker
clasS is a CBO with values in it.


the result i am looking for is that the user select a date and class and the report prints/displays data based on these two queries.
 

isladogs

MVP / VIP
Local time
Today, 14:50
Joined
Jan 14, 2017
Messages
18,186
If it's a date field, you need date delimiters rather than text delimiters

Also if you are specifying that 'atdate' needs to be equal to two different form controls, then both must be identical so why are both needed? If you must have both, set the textbox to equal the combo value after it is updated
 

June7

AWF VIP
Local time
Today, 06:50
Joined
Mar 9, 2014
Messages
5,423
Is this part of macro code? How would you expect atdate to be equal to two different values? Perhaps the second atdate should be some other field?

Don't use the = sign that follows the And. Too many quote marks: " AND somefield=". # delimiters for date parameter.

No need to explicitly reference .Value as it is the default property of data controls.
Code:
"atdate = #" & [Forms]![Student_Attendance]![dateS] & "# And somefield = '" & [Forms]![Student_Attendance]![clasS] & "'"
 
Last edited:

vagues0ul

Registered User.
Local time
Today, 07:50
Joined
Sep 13, 2018
Messages
103
Code:
="atdate = #" & [Forms]![Student_Attendance]![dateS] & "# And somefield = '" & [Forms]![Student_Attendance]![clasS] & "'"
[/QUOTE]

i have ammended my code as per your code given above and here is my code after adding the value
Code:
="atdate = #" & [Forms]![Student_Attendance]![dateS] & "# And stclass = '" & [Forms]![Student_Attendance]![clasS].[Value] & "'"

but it is not filtering any result. Let me also include the report is based on a query so am i going in the right way regarding macro?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:50
Joined
May 7, 2009
Messages
19,169
hi, if this is a Macro, you don't need to put your Where Clause inside the quote, here:

[atdate]=[Forms]![student_attendance]![dates] And [stclass]=[Forms]![student_attendance]![class]
 

vagues0ul

Registered User.
Local time
Today, 07:50
Joined
Sep 13, 2018
Messages
103
hi, if this is a Macro, you don't need to put your Where Clause inside the quote, here:

[atdate]=[Forms]![student_attendance]![dates] And [stclass]=[Forms]![student_attendance]![class]

not working

i have attached the screenshots after updating the macro code. also let me know what should be the record source of report? the actual table where i am saving attendance log or the query which is selecting the records?:confused:
 

Attachments

  • form.jpg
    form.jpg
    96.8 KB · Views: 144
  • attlog.jpg
    attlog.jpg
    100.2 KB · Views: 152

June7

AWF VIP
Local time
Today, 06:50
Joined
Mar 9, 2014
Messages
5,423
What does 'not working' mean - error message, wrong results, nothing happens?

RecordSource can be table or query, doesn't matter.

arnelgp syntax is correct. My suggestion is VBA syntax

If you want to provide db for analysis, follow instructions at bottom of my post.
 

vagues0ul

Registered User.
Local time
Today, 07:50
Joined
Sep 13, 2018
Messages
103
What does 'not working' mean - error message, wrong results, nothing happens?

RecordSource can be table or query, doesn't matter.

arnelgp syntax is correct. My suggestion is VBA syntax

If you want to provide db for analysis, follow instructions at bottom of my post.



the first part of the macro is running fine if i enter it seperately.
Code:
[atdate]=[Forms]![student_attendance]![dates]
the part after the AND is creating blank report/result
Code:
[stclass]=[Forms]![student_attendance]![class]


and ultimately if i combine both of these via AND clause the report opens but shows nothing.

P.S the DB sie is 9mb + and i think i am not allowed to upload file this big
 

June7

AWF VIP
Local time
Today, 06:50
Joined
Mar 9, 2014
Messages
5,423
Then make copy and remove excess data and unnecessary objects, run C&R, zip. 2MB zip allowed.
 

vagues0ul

Registered User.
Local time
Today, 07:50
Joined
Sep 13, 2018
Messages
103
Then make copy and remove excess data and unnecessary objects, run C&R, zip. 2MB zip allowed.


i have attached the db.
 

Attachments

  • attendance error.accdb
    1.6 MB · Views: 144

June7

AWF VIP
Local time
Today, 06:50
Joined
Mar 9, 2014
Messages
5,423
Value of clasS combobox comes from ID, not Classes field. AttendanceLog has the class name not the class ID. Therefore, data does not match filter criteria and records not retrieved. Either remove ID from the combobox RowSource or change AttendanceLog to save the ID. The latter is preferable.

Advise not to have fields and tables with exactly same name. Name table Classes and fields ClassID and ClassName.

Also, don't use just ID fieldname in every table. Use something more informative.
 
Last edited:

vagues0ul

Registered User.
Local time
Today, 07:50
Joined
Sep 13, 2018
Messages
103
Value of clasS combobox comes from ID, not Classes field. AttendanceLog has the class name not the class ID. Therefore, data does not match filter criteria and records not retrieved. Either remove ID from the combobox RowSource or change AttendanceLog to save the ID. The latter is preferable.

Advise not to have fields and tables with exactly same name. Name table Classes and fields ClassID and ClassName.

Also, don't use just ID fieldname in every table. Use something more informative.

what will be easiest solution ? cant chagne and relink the whole db. kindly tell the easiest soulution accordint to the current situation i have on db. thanks
 

June7

AWF VIP
Local time
Today, 06:50
Joined
Mar 9, 2014
Messages
5,423
Easiest, quickest (not necessarily best) is to remove ID from the combobox RowSource and change combobox properties to:

ColumnCount: 1
ColumnWidths: nothing
 

vagues0ul

Registered User.
Local time
Today, 07:50
Joined
Sep 13, 2018
Messages
103
Easiest, quickest (not necessarily best) is to remove ID from the combobox RowSource and change combobox properties to:

ColumnCount: 1
ColumnWidths: nothing


did what you said but the old macro is still not working. i have written a vb code from google search and its working.
Code:
Private Sub Command47_Click()

If IsNull(Me.clasS) Then
MsgBox "Please select a server."
Me.clasS.SetFocus
Else
DoCmd.OpenReport "AttendanceLog", _
acViewPreview, _
WhereCondition:="stclass=" & _
Chr(34) & Me.clasS & Chr(34)
End If
End Sub

can you tell me how can i bound it to take two values to filter report instead of just one ?
will be better if you edit the code;)
 

June7

AWF VIP
Local time
Today, 06:50
Joined
Mar 9, 2014
Messages
5,423
Suggested changes to combobox work for me. However, the report opens behind the form and cannot see it without closing form.

Although not required, also removed .Value from the macro expression.

VBA would be:
Code:
Private Sub Command47_Click()
If IsNull(Me.clasS) Then
    MsgBox "Please select a server."
    Me.clasS.SetFocus
Else
    DoCmd.OpenReport "AttendanceLog", acViewPreview, , "atdate=#" & Me.dateS & "# AND stclass='" & Me.clasS & "'"
End If
End Sub
Still have to change the combobox settings.
 
Last edited:

June7

AWF VIP
Local time
Today, 06:50
Joined
Mar 9, 2014
Messages
5,423
Remove ID from combobox RowSource.

Set properties:
ColumnCount: 1
ColumnWidths: nothing

After that, macro should work. Did for me. But if VBA is working (should still need to fix combobox), advise you stick with it. I don't use macros.
 

Users who are viewing this thread

Top Bottom