Using Variable in SQL Statement

[John]

I have two variables established on startup as CYr and PYr which contain the
last two digits of the current and previous years respectively.
I want to use these variables in the following SQL statement and need some
help. Anywhere there is a "CYr" or a "PYr" should be referring to these
global variables.

SELECT [qryDataG].[Plant Name], [qryDataG].[Customer Name],
[qryDataG].[Customer Number], IIf(Right([qryDataG]![Period Name],2)="CYr" And
[qrySelectPeriodB]![CurrentMonthID]>=[qryDataG]![ID],[qryDataG]![GrossSales]+[qryDataG]![SalesCredits],0)
AS CYrYTDSales, IIf(Right([qryDataG]![Period Name],2)="CYr" And
[qrySelectPeriodB]![CurrentMonthID]>=[qryDataG]![ID],[qryDataG]![Std
Margin],0) AS CYrYTDStdMargin, IIf(Right([qryDataG]![Period Name],2)="CYr"
And [qrySelectPeriodB]![CurrentMonthID]>=[qryDataG]![ID],[qryDataG]![Actual
Margin],0) AS CYrYTDActMargin, IIf(Right([qryDataG]![Period Name],2)="PYr"
And
[qrySelectPeriodB]![CurrentMonthID]>=[qryDataG]![ID],[qryDataG]![GrossSales]+[qryDataG]![SalesCredits],0)
AS PYrYTDSales, IIf(Right([qryDataG]![Period Name],2)="PYr" And
[qrySelectPeriodB]![CurrentMonthID]>=[qryDataG]![ID],[qryDataG]![Std
Margin],0) AS PYrYTDStdMargin, IIf(Right([qryDataG]![Period Name],2)="PYr"
And [qrySelectPeriodB]![CurrentMonthID]>=[qryDataG]![ID],[qryDataG]![Actual
Margin],0) AS PYrYTDActMargin,
IIf([qrySelectPeriodB]![CurrentMonth]=[qryDataG]![Period
Name],[qryDataG]![GrossSales]+[qryDataG]![SalesCredits],0) AS MonthNetSales,
IIf([qrySelectPeriodB]![CurrentMonth]=[qryDataG]![Period
Name],[qryDataG]![Std Margin],0) AS MonthStdMargin,
IIf([qrySelectPeriodB]![CurrentMonth]=[qryDataG]![Period
Name],[qryDataG]![Actual Margin],0) AS MonthActMargin,
IIf([qrySelectPeriodB]![12PreviousMonth]=[qryDataG]![Period
Name],[qryDataG]![GrossSales]+[qryDataG]![SalesCredits],0) AS
LYMonthNetSales, IIf([qrySelectPeriodB]![12PreviousMonth]=[qryDataG]![Period
Name],[qryDataG]![Std Margin],0) AS LYMonthStdMargin,
IIf([qrySelectPeriodB]![12PreviousMonth]=[qryDataG]![Period
Name],[qryDataG]![Actual Margin],0) AS LYMonthActMargin
FROM qryDataG, qrySelectPeriodB;


Any help here?

 

[Douglas J. Steele]

Create a couple of public functions that return the values of the variables,
and plug the functions into the query.

 

[Klatuu]

If you're not familiar with the technique in Douglas' post, here is an example:

Static Function GetSSN(Optional ByVal varNewSSN As Variant) As String
Dim varSSN As Variant

If Not IsMissing(varNewSSN) Then
varSSN = varNewSSN
End If
GetSSN = varSSN
End Function

It should go in a standard module. If you pass it a value, it retains the
value until it receives a new value or you close the database. If you call
it without passing a value, it returns the current value. It is like a
Global variable, but since SQL queries can't accept variables but do accept
functions, it is the way to do it.