Passing Variable From Form to Module - Syntax Problem 2

[JStrand]

I have a basic query shown below which has a crosstab as a datasource. The fields in the crosstab contain periods of the year as the Column header. The query below is pulling period 2 data.


SELECT Branch.Branch, Sum(Abs([ReportGroupBook] In ("12Actual"))*[2]/1000*-1) AS WO_GrossRev_Actual INTO WO_Contract_Watchlist_Tbl_All
FROM Branch INNER JOIN UnionScorecardBranchQryCrosstab_All_ForAnalysis ON Branch.Branch = UnionScorecardBranchQryCrosstab_All_ForAnalysis.Branch
GROUP BY Branch.Branch
ORDER BY Branch.Branch;
-------------------------------------------------------------

I'm converting this SQL into a module so I can pass the period number to the program using a variable passed from the Main Form.

My problem is that My period 2 variable is being interpreted by the program as the literal integer 2 instead of as the column header field [2]. I believe the key syntax I am having problem with is '" & [intPeriod] & "'. Any ideas on chnaging the syntax so that the two is treated as a parameter? Thank you in advance.


--------------------------------------------------------------

Option Compare Database
Option Explicit

Function WatchlistReports()

Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qdf")

'Declare a string variable to hold the period values
Dim intPeriod As Integer

intPeriod = Forms![checkmax]!txtPeriodNumber

Dim StrSQL2 As String

StrSQL2 = ""

StrSQL2 = "SELECT Branch.DivisionName, Branch.RegionName, Branch.Branch, Branch.BranchName, Branch.Status, Branch.ECLead, "
StrSQL2 = StrSQL2 & "Sum(Abs([ReportGroupBook] In (""12Actual""))*'" & [intPeriod] & "'/1000*-1) AS WO_GrossRev_Actual

Debug.Print StrSQL2

qdf.SQL = StrSQL2

DoCmd.SetWarnings False

DoCmd.OpenQuery "qdf"

DoCmd.SetWarnings True

 

[Andrzejek]

That depends on what you have in Forms![checkmax]!txtPeriodNumber
Do you have number 2 or text '[2]'?

Have fun.

---- Andy

 

[dhookom]

Try

StrSQL2 = "SELECT Branch.DivisionName, Branch.RegionName, Branch.Branch, Branch.BranchName, Branch.Status, Branch.ECLead, "
StrSQL2 = StrSQL2 & "Sum(Abs([ReportGroupBook] In (""12Actual""))*[red][[/red]" & [intPeriod] & "[red]][/red]/1000*-1) AS WO_GrossRev_Actual

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

It looks like you are passing an Integer:

In your Form call it:

Call WatchlistReports([blue]Forms![checkmax]!txtPeriodNumber[/blue])

That passes the value of 2 from txtPeriodNumber,
and in your Module:

Option Compare Database
Option Explicit

Function WatchlistReports([blue]intPeriod[/blue] As Integer)

Dim db As Database
Dim qdf As QueryDef
Dim StrSQL2 As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qdf")

StrSQL2 = "SELECT Branch.DivisionName, Branch.RegionName, Branch.Branch, Branch.BranchName, Branch.Status, Branch.ECLead, "
StrSQL2 = StrSQL2 & "Sum(Abs([ReportGroupBook] In (""12Actual""))*'[" & [blue]intPeriod[/blue] & "]'/1000*-1) AS WO_GrossRev_Actual "

Debug.Print StrSQL2

    qdf.SQL = StrSQL2

    DoCmd.SetWarnings False

    DoCmd.OpenQuery "qdf"

    DoCmd.SetWarnings True

Have fun.

---- Andy

 

[JStrand]

[" & [intPeriod] & "]

This did the trick. Thank you Gentlemen!

 

[Andrzejek]

I don't think you need to put [ ] around your variable intPeriod

Have fun.

---- Andy