Query Filter Coding Question (1 Viewer)

BC_Seattle

Registered User.
Local time
Today, 15:08
Joined
Jun 18, 2019
Messages
24
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:08
Joined
Oct 29, 2018
Messages
21,358
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()
 

BC_Seattle

Registered User.
Local time
Today, 15:08
Joined
Jun 18, 2019
Messages
24
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
 

Attachments

  • Syntax issue.JPG
    Syntax issue.JPG
    28.8 KB · Views: 99

theDBguy

I’m here to help
Staff member
Local time
Today, 15:08
Joined
Oct 29, 2018
Messages
21,358
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...
 

BC_Seattle

Registered User.
Local time
Today, 15:08
Joined
Jun 18, 2019
Messages
24
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:08
Joined
Oct 29, 2018
Messages
21,358
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.
 

Micron

AWF VIP
Local time
Today, 18:08
Joined
Oct 20, 2018
Messages
3,476
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?
 
Last edited:

BC_Seattle

Registered User.
Local time
Today, 15:08
Joined
Jun 18, 2019
Messages
24
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:08
Joined
Sep 21, 2011
Messages
14,047
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]));
 

Users who are viewing this thread

Top Bottom