Hale, Jim
Jim.Hale at FleetPride.com
Mon Jun 21 09:56:07 CDT 2004
<Since I cant use a variable as a parameter>
I'm glad your solution is working. However, if you are creating SQL strings
and executing them via code you can use variables and/or functions as you
build the string. For ex the function below uses multiple variables to
create the string that is ultimately executed. Perfdata (below) builds
slightly different strings with Sales data, Gross margin, etc. that are then
pasted into an Excel spreadsheet via a function called Datapaste. The trick
is to enclose variable in ampersands "& variable &" when building the
string.
Jim Hale
For x = 1 To 5 'load summary inc stmt numbers
qryString = Perfdata(x)
Set recset(1) = dbs2.OpenRecordset(qryString)
If x = 1 Then
blstate = DataPaste("Branchperf", appExcel, recset(1))
Else
blstate = DataPaste("Branchperf", appExcel, recset(1), True)
End If
Next x
Function Perfdata(intRU As Integer, Optional strRegion As String) As String
Dim strVar As String, intAcct As Integer
'generates the records to place in perf summary data table
If strRegion = "" Then
strRegion = " Like '*'"
Else
strRegion = "= '" & strRegion & "'"
End If
Select Case intRU
Case 1
intAcct = 4000 'Sales
Case 2
intAcct = 4500 'Gross Margin
Case 3
intAcct = 5000 'SG&A
Case 4
intAcct = 6000 'EBIT
Case 5
intAcct = 6100 'Payroll
End Select
strVar = "SELECT [byear] & '_' & [flddptloc] & '_' & "
strVar = strVar & "[rollup" & intRU & "] & '_' & [fldarea] AS SumifKey,
[byear] &"
strVar = strVar & " '_' & [flddptloc] & '_' & [rollup" & intRU & "] &
'_' & "
strVar = strVar & "[bmonth] AS PrKey, GLMBALS.BYEAR, GLMBALS.BMONTH, "
strVar = strVar & "GLMBRANCHES.fldCOMPY, GLMBRANCHES.fldRegion, "
strVar = strVar & "GLMBRANCHES.fldArea, GLMBRANCHES.fldDPTLOC, First"
strVar = strVar & "(GLMBRANCHES.fldDPNAME) AS FirstOffldDPNAME, "
strVar = strVar & "tblReportsrollups.ROLLUP" & intRU & ",
tblReportsrollups."
strVar = strVar & "RUPNAME" & intRU & ", Sum(NZ([peramt]))*-1 AS Amt "
strVar = strVar & "FROM (tblPeriods INNER JOIN (GLMBRANCHES INNER "
strVar = strVar & "JOIN (tblReports INNER JOIN GLMBALS ON tblReports."
strVar = strVar & "GL = GLMBALS.GL) ON (GLMBRANCHES.fldDEPT = "
strVar = strVar & "GLMBALS.DEPT) AND (GLMBRANCHES.fldCOMPY = GLMBALS."
strVar = strVar & "COMPY)) ON (tblPeriods.fldMonth = GLMBALS.BMONTH) "
strVar = strVar & "AND (tblPeriods.[fld year] = GLMBALS.BYEAR)) "
strVar = strVar & "INNER JOIN tblReportsrollups ON tblReports."
strVar = strVar & "RPTLINE = tblReportsrollups.RPTLINE "
strVar = strVar & "WHERE (((tblPeriods.[fld year])=" & CurrYear() & ")
AND ((GLMBALS.PERAMT)<>0) AND ("
strVar = strVar & "(GLMBALS.REPOST)='n') AND ((tblPeriods.fldMonth)<="
strVar = strVar & "" & CurrMonth() & ")) OR (((tblPeriods.[fld year])="
strVar = strVar & "" & CurrYear() & "-1) AND ((GLMBALS.PERAMT)<> 0) AND
((GLMBALS.REPOST)='n') AND ("
strVar = strVar & "(tblPeriods.fldMonth)<=" & CurrMonth() & ")) "
strVar = strVar & "GROUP BY GLMBALS.BYEAR, GLMBALS.BMONTH, "
strVar = strVar & "GLMBRANCHES.fldCOMPY, GLMBRANCHES.fldRegion, "
strVar = strVar & "GLMBRANCHES.fldArea, GLMBRANCHES.fldDPTLOC, "
strVar = strVar & "tblReportsrollups.ROLLUP" & intRU & ",
tblReportsrollups."
strVar = strVar & "RUPNAME" & intRU & " "
strVar = strVar & "HAVING (((GLMBRANCHES.fldRegion)" & strRegion & ")
AND "
strVar = strVar & "((tblReportsrollups.ROLLUP" & intRU & ")=" & intAcct
& "));"
Perfdata = strVar
End Function
-----Original Message-----
From: JMoss [mailto:jmoss111 at bellsouth.net]
Sent: Thursday, June 17, 2004 6:12 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] How to set a value from one recordset as a
parameter for a select statement that is source to another recordset
Jim,
Thanks for your answer, but what I'm doing in using a distinct value
companyid from the first query which is the recordsource to a recordset
which I'm moving through one record at a time in an outer loop to supply the
parameter for the second recordset in an inner loop which has several
departments per companyid. Then I use the second recordset to populate and
build Excel spreadsheets via automation. Since I cant use a variable as a
parameter, I created a function to get the data and used the function as the
select's parameter. Anyway, it's working.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Hale, Jim
Sent: Thursday, June 17, 2004 10:52 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] How to set a value from one recordset as a
parameter for a select statement that is source to another recordset
It sounds to be that you need to use the first query as a sub query of the
second. To do this copy the sql of the first query, put brackets around it,
and paste it into the criteria line of the appropriate field of the second
query. As long as the first query is only returning one "answer" ie one
field of one record, it should work. Good luck
Jim Hale
-----Original Message-----
From: JMoss [mailto:jmoss111 at bellsouth.net]
Sent: Wednesday, June 16, 2004 8:05 PM
To: Accessd at Databaseadvisors.Com
Subject: [AccessD] How to set a value from one recordset as a parameter
for a select statement that is source to another recordset
Is there a way to do this? I have select distinct COID statement as source
for a recordset that I'm looping through, and want to use the distinctly
selected COID as a parameter in a select statement that is recordsource for
another recordset that I'm using to create Excel files.
Thanks in advance for any help!
Jim
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com