Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-10-2018, 10:56 AM   #1
vagues0ul
Newly Registered User
 
Join Date: Sep 2018
Posts: 59
Thanks: 11
Thanked 2 Times in 1 Post
vagues0ul is on a distinguished road
Access Report Based On Multiple Criteria

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.

vagues0ul is offline   Reply With Quote
Old 10-10-2018, 11:03 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,552
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Access Report Based On Multiple Criteria

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-10-2018, 11:05 AM   #3
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 168 Times in 168 Posts
June7 will become famous soon enough
Re: Access Report Based On Multiple Criteria

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] & "'"

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 10-11-2018 at 10:44 AM.
June7 is offline   Reply With Quote
Old 10-10-2018, 05:36 PM   #4
vagues0ul
Newly Registered User
 
Join Date: Sep 2018
Posts: 59
Thanks: 11
Thanked 2 Times in 1 Post
vagues0ul is on a distinguished road
Re: Access Report Based On Multiple Criteria

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?
vagues0ul is offline   Reply With Quote
Old 10-10-2018, 08:38 PM   #5
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,712
Thanks: 55
Thanked 2,133 Times in 2,044 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Access Report Based On Multiple Criteria

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]
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-11-2018, 10:02 AM   #6
vagues0ul
Newly Registered User
 
Join Date: Sep 2018
Posts: 59
Thanks: 11
Thanked 2 Times in 1 Post
vagues0ul is on a distinguished road
Re: Access Report Based On Multiple Criteria

Quote:
Originally Posted by arnelgp View Post
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?
Attached Images
File Type: jpg form.jpg (96.8 KB, 4 views)
File Type: jpg attlog.jpg (100.2 KB, 3 views)
vagues0ul is offline   Reply With Quote
Old 10-11-2018, 10:42 AM   #7
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 168 Times in 168 Posts
June7 will become famous soon enough
Re: Access Report Based On Multiple Criteria

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.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Old 10-11-2018, 10:59 AM   #8
vagues0ul
Newly Registered User
 
Join Date: Sep 2018
Posts: 59
Thanks: 11
Thanked 2 Times in 1 Post
vagues0ul is on a distinguished road
Re: Access Report Based On Multiple Criteria

Quote:
Originally Posted by June7 View Post
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
vagues0ul is offline   Reply With Quote
Old 10-11-2018, 11:09 AM   #9
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 168 Times in 168 Posts
June7 will become famous soon enough
Re: Access Report Based On Multiple Criteria

Then make copy and remove excess data and unnecessary objects, run C&R, zip. 2MB zip allowed.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Old 10-11-2018, 11:19 AM   #10
vagues0ul
Newly Registered User
 
Join Date: Sep 2018
Posts: 59
Thanks: 11
Thanked 2 Times in 1 Post
vagues0ul is on a distinguished road
Re: Access Report Based On Multiple Criteria

Quote:
Originally Posted by June7 View Post
Then make copy and remove excess data and unnecessary objects, run C&R, zip. 2MB zip allowed.

i have attached the db.
Attached Files
File Type: accdb attendance error.accdb (1.59 MB, 3 views)
vagues0ul is offline   Reply With Quote
Old 10-11-2018, 11:52 AM   #11
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 168 Times in 168 Posts
June7 will become famous soon enough
Re: Access Report Based On Multiple Criteria

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.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 10-11-2018 at 11:57 AM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
vagues0ul (10-11-2018)
Old 10-11-2018, 12:01 PM   #12
vagues0ul
Newly Registered User
 
Join Date: Sep 2018
Posts: 59
Thanks: 11
Thanked 2 Times in 1 Post
vagues0ul is on a distinguished road
Re: Access Report Based On Multiple Criteria

Quote:
Originally Posted by June7 View Post
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
vagues0ul is offline   Reply With Quote
Old 10-11-2018, 12:03 PM   #13
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 168 Times in 168 Posts
June7 will become famous soon enough
Re: Access Report Based On Multiple Criteria

Easiest, quickest (not necessarily best) is to remove ID from the combobox RowSource and change combobox properties to:

ColumnCount: 1
ColumnWidths: nothing
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
vagues0ul (10-11-2018)
Old 10-11-2018, 12:14 PM   #14
vagues0ul
Newly Registered User
 
Join Date: Sep 2018
Posts: 59
Thanks: 11
Thanked 2 Times in 1 Post
vagues0ul is on a distinguished road
Re: Access Report Based On Multiple Criteria

Quote:
Originally Posted by June7 View Post
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
vagues0ul is offline   Reply With Quote
Old 10-11-2018, 12:27 PM   #15
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 168 Times in 168 Posts
June7 will become famous soon enough
Re: Access Report Based On Multiple Criteria

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.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 10-11-2018 at 12:33 PM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
vagues0ul (10-11-2018)
Reply

Tags
and clause , macro builder , report , where condition

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting records based on multiple criteria for a report? TBerg Modules & VBA 3 04-16-2013 12:21 PM
Open report based on multiple criteria Talismanic Modules & VBA 19 08-03-2011 07:09 AM
Creating a report from a form based on multiple criteria Moe23 Reports 8 06-10-2010 10:37 AM
Print Report from Form based on multiple criteria? Alan R Forms 5 11-17-2006 06:24 AM
Report based on multiple criteria selections Andren Reports 4 10-08-2002 03:27 PM




All times are GMT -8. The time now is 03:01 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World