Using date variables in sql statements

[gmgenova]

I am an SQL novice. I am going to be putting some of my access queries in macros so I need to automate some of the dates I have in my SQL statements so no user intervention is required. I have researched but have not found what I need yet.

Here is one of my SQL statements. I just need to have a date variable that will take the place of my hard coded date - "201807". In this case, it is YYYYMM. (MM is the previous month). Any help is appreciated. Thank you!!

SELECT DISTINCT [dbo_RX Reporting].[Plan Name], [dbo_RX Reporting].[Therapeutic Class], Sum([dbo_RX Reporting].Price) AS SumOfPrice, [dbo_RX Reporting].Period
FROM [dbo_RX Reporting]
GROUP BY [dbo_RX Reporting].[Plan Name], [dbo_RX Reporting].[Therapeutic Class], [dbo_RX Reporting].Period, [dbo_RX Reporting].[Plan Name]
HAVING ((([dbo_RX Reporting].[Therapeutic Class]) Like "*Analgesics*") AND (([dbo_RX Reporting].Period)="201807") AND (([dbo_RX Reporting].[Plan Name])="Alabama"));

 

[gmgenova]

Since my query is in Access, I ended up doing this to get the date variable I needed:

AND (([dbo_RX Reporting].Period)=Format(DateAdd("m", -1, Date()), "yyyymm"))

 

[SkipVought]

BTW “201807” is not a date in database or code terms. It is a year-month string. And your database table clearly represents it as a Reporting period. You used the Format() function to use a date calculation to return a string.

So technically, you’re looking for a reporting period string variable.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]