Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-24-2019, 01:05 PM   #1
BC_Seattle
Newly Registered User
 
Join Date: Jun 2019
Posts: 14
Thanks: 5
Thanked 0 Times in 0 Posts
BC_Seattle is on a distinguished road
Query Filter Coding Question

I have inherited a database and we currently have a query that pulls all invoices that were completed last week. I wanted to add another report that does the same thing for the last 4 weeks starting as of today. This will help audit anything missed. The code I have appears to only use Static weeks and am not sure how to range or count backwards 30-45 days from today's date and pull those invoices.

So you can see what I have to work with any help is appreciated. I am an Access Noob and just trying to learn things as I go so please be gentle.

Here is what I have in the Data Tab under Filter:

(Year([Billing Last Week].[Invoice Date])*53+DatePart("ww",[Billing Last Week].[Invoice Date],0)=(Year(Date())*53+DatePart("ww",Date(),0)-1))

I have determined the last -1 determines what static week to pull from but I would like to have multiple weeks just unsure how it needs to be coded.

BC_Seattle is offline   Reply With Quote
Old 06-24-2019, 01:18 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,063
Thanks: 36
Thanked 729 Times in 712 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Query Filter Coding Question

Hi. If the column/field is a date field, then you should be able to filter it using the DateAdd() function. For example, to return all dates between today and one week ago, try:
Code:
Between DateAdd("w",-1,Date()) And Date()
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 06-24-2019, 01:49 PM   #3
BC_Seattle
Newly Registered User
 
Join Date: Jun 2019
Posts: 14
Thanks: 5
Thanked 0 Times in 0 Posts
BC_Seattle is on a distinguished road
Re: Query Filter Coding Question

I understand the syntax you have and that totally makes sense. How would I string it together? I assume there needs to be an operation that tells it how and what to filter. I am just not good with Access Syntax to understand it yet. I have taught myself really well on like Hotkey and things of that but the syntax is much more straight forward.

Billing Last Week is referring to the Query and Invoice Date is the date it was closed any Ideas how that strings together?

Not sure but does this help shed some light? -Attachment
Attached Images
File Type: jpg Syntax issue.JPG (28.8 KB, 17 views)

BC_Seattle is offline   Reply With Quote
Old 06-24-2019, 01:54 PM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,063
Thanks: 36
Thanked 729 Times in 712 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Query Filter Coding Question

Quote:
Originally Posted by BC_Seattle View Post
I understand the syntax you have and that totally makes sense. How would I string it together? I assume there needs to be an operation that tells it how and what to filter. I am just not good with Access Syntax to understand it yet. I have taught myself really well on like Hotkey and things of that but the syntax is much more straight forward.

Billing Last Week is referring to the Query and Invoice Date is the date it was closed any Ideas how that strings together?

Not sure but does this help shed some light? -Attachment
Hi. Rather than filter the report in design view. Leave it unfiltered, meaning, showing all records. Then, when you open it using code, via a button, you can add the criteria in the WhereCondition argument of the OpenReport method. Or, if you just want to open the report from the Navigation Pane, then you can apply the criteria in the query as the Record Source for your report. Hope that makes sense...
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 06-26-2019, 12:56 PM   #5
BC_Seattle
Newly Registered User
 
Join Date: Jun 2019
Posts: 14
Thanks: 5
Thanked 0 Times in 0 Posts
BC_Seattle is on a distinguished road
Re: Query Filter Coding Question

Thank you for the help I finally figured out the filter function I was looking for. I got 2 of them one for this month and one for last month.

Here is what worked for me:

Year([Invoice Date]) = Year(Now()) And Month([Invoice Date]) = Month(Now())

Year([Invoice Date])* 12 + DatePart("m", [Invoice Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1
BC_Seattle is offline   Reply With Quote
Old 06-26-2019, 01:22 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,063
Thanks: 36
Thanked 729 Times in 712 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Query Filter Coding Question

Quote:
Originally Posted by BC_Seattle View Post
Thank you for the help I finally figured out the filter function I was looking for. I got 2 of them one for this month and one for last month.

Here is what worked for me:

Year([Invoice Date]) = Year(Now()) And Month([Invoice Date]) = Month(Now())

Year([Invoice Date])* 12 + DatePart("m", [Invoice Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 06-26-2019, 01:30 PM   #7
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 727
Thanks: 3
Thanked 148 Times in 142 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Query Filter Coding Question

Quote:
am not sure how to range or count backwards 30-45 days
Wouldn't BETWEEN Date-45 AND Date-30 be simpler?

Never mind - I think I misinterpreted the goal?

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)

Last edited by Micron; 06-26-2019 at 01:31 PM. Reason: added comment
Micron is online now   Reply With Quote
Old 06-26-2019, 01:41 PM   #8
BC_Seattle
Newly Registered User
 
Join Date: Jun 2019
Posts: 14
Thanks: 5
Thanked 0 Times in 0 Posts
BC_Seattle is on a distinguished road
Re: Query Filter Coding Question

Quote:
Originally Posted by Micron View Post
Wouldn't BETWEEN Date-45 AND Date-30 be simpler?

Never mind - I think I misinterpreted the goal?
I did try what theDBguy mentioned and also Between Date() and Date()-30 but for some reason the syntax didn't take. I just needed to get the last 30 days of invoicing. With that I just made 2 reports one for this month and one for last month called it good enough.
BC_Seattle is offline   Reply With Quote
Old 06-26-2019, 09:17 PM   #9
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,672
Thanks: 398
Thanked 637 Times in 618 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Query Filter Coding Question

Quote:
Originally Posted by BC_Seattle View Post
I did try what theDBguy mentioned and also Between Date() and Date()-30 but for some reason the syntax didn't take. I just needed to get the last 30 days of invoicing. With that I just made 2 reports one for this month and one for last month called it good enough.
I would have thought that if you made the last segment a variable/parameter/TempVar, you could allow for any period.?

The above prompts for Days Required, but you get the idea.?

Code:
SELECT Emails.*
FROM Emails
WHERE (((Emails.TransactionDate) Between Date() And Date()-[DaysReq]));

__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
BC_Seattle (06-27-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Cascading Filter Coding Help Needed MG101 Modules & VBA 0 07-16-2013 11:41 AM
VBA coding question Bechert Modules & VBA 1 11-06-2011 10:05 AM
bit of coding question GaryPanic Modules & VBA 3 02-13-2009 12:44 AM
[SOLVED] Coding question? cwicker2 Modules & VBA 2 05-19-2003 05:13 AM
Coding question Peter Paul Modules & VBA 5 03-30-2001 04:51 PM




All times are GMT -8. The time now is 02:35 PM.


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

Featured Forum post


Sponsored Links


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