Parameter inside identifier

Question

I'm trying to write a add query that will change depending on the parameter. I have several queries:

LastK1StatDate
LastK2StatDate
.
.
LastK15StatDate
LastK16StatDate

My criteria should change depending on the value entered for the parameter "qryKioskNum" when the query is run.

Currently my criteria is this:

>Max("[LastK" & [qryKioskNum] & "StatDate]![K" & [qryKioskNum] & "LastDate]")

qryKioskNum is type Short Text

It keeps giving me the error "The expression is typed incorrectly, or is too complex to be evaluated."

Here is the complete SQL statement for this query:

PARAMETERS qryKioskNum Short;  
INSERT INTO K1DispRejStat ( K1StatDate, K1BillCount1, K1BillCount2, 
K1BillCount3, K1BillCount4, K1BillCount5, K1BillCount6, K1BillRej1, 
K1BillRej2, K1BillRej3, K1BillRej4, K1BillRej5, K1BillRej6 )  
SELECT DateValue([responseFrames]![dispDateTime]) AS [Date], 
Sum(responseFrames.billCount1) AS SumOfbillCount1, 
Sum(responseFrames.billCount2) AS SumOfbillCount2, 
Sum(responseFrames.billCount3) AS SumOfbillCount3, 
Sum(responseFrames.billCount4) AS SumOfbillCount4, 
Sum(responseFrames.billCount5) AS SumOfbillCount5, 
Sum(responseFrames.billCount6) AS SumOfbillCount6, 
Sum(responseFrames.BillRej1) AS SumOfBillRej1, Sum(responseFrames.BillRej2) 
AS SumOfBillRej2, Sum(responseFrames.BillRej3) AS SumOfBillRej3, 
Sum(responseFrames.BillRej4) AS SumOfBillRej4, Sum(responseFrames.billRej5) 
AS SumOfbillRej5, Sum(responseFrames.billRej6) AS SumOfbillRej6  
FROM responseFrames, LastK1StatDate  
WHERE (((responseFrames.kioskID)="K1"))
GROUP BY DateValue([responseFrames]![dispDateTime])
HAVING (((DateValue([responseFrames]![dispDateTime]))>Max("[LastK" & 
[qryKioskNum] & "StatDate]![K1LastDate]")))
ORDER BY DateValue([responseFrames]![dispDateTime]);  

currently everything is set to "K1" but I would like all reference to K1 to be dynamic

I think it is just a syntax issue but can't find how exactly this should be typed out.

Any help is great. Thanks!

*edited for clarity

Answer 1

In msaccess, create a PassThru query (because it retains the multi-line nice format).

Create // QueryDesign // Close // rightClick // SQLSpecific // PassThru

Paste in the following sql.

INSERT INTO kxxdisprejstat

            (kxxstatdate,
             kxxbillcount1,
             kxxbillcount2,
             kxxbillcount3,
             kxxbillcount4,
             kxxbillcount5,
             kxxbillcount6,
             kxxbillrej1,
             kxxbillrej2,
             kxxbillrej3,
             kxxbillrej4,
             kxxbillrej5,
             kxxbillrej6)

SELECT Datevalue([responseframes] ! [dispdatetime]) AS [Date],
       SUM(responseframes.billcount1)               AS SumOfbillCount1,
       SUM(responseframes.billcount2)               AS SumOfbillCount2,
       SUM(responseframes.billcount3)               AS SumOfbillCount3,
       SUM(responseframes.billcount4)               AS SumOfbillCount4,
       SUM(responseframes.billcount5)               AS SumOfbillCount5,
       SUM(responseframes.billcount6)               AS SumOfbillCount6,
       SUM(responseframes.billrej1)                 AS SumOfBillRej1,
       SUM(responseframes.billrej2)                 AS SumOfBillRej2,
       SUM(responseframes.billrej3)                 AS SumOfBillRej3,
       SUM(responseframes.billrej4)                 AS SumOfBillRej4,
       SUM(responseframes.billrej5)                 AS SumOfbillRej5,
       SUM(responseframes.billrej6)                 AS SumOfbillRej6

FROM   responseframes,
       lastkxxstatdate

WHERE  (( ( responseframes.kioskid ) = "kxx" ))

GROUP  BY Datevalue([responseframes] ! [dispdatetime])

HAVING (( ( Datevalue([responseframes] ! [dispdatetime]) ) 
           > Max([lastkxxstatdate]![kxxlastdate]) ))

ORDER  BY Datevalue([responseframes] ! [dispdatetime]); 

Name it kxxInsert (or some such, using kxx to say that it is generalized).

Then add this to the program

Sub getKxx()
    Dim qrykiosknum As Integer  ' temp here to have something
    qrykiosknum = 3             ' temp here for an example

    Dim kxxSQL As String, strSQL As String
    kxxSQL = CurrentDb.QueryDefs("kxxInsert").SQL
    strSQL = Replace(kxxSQL, "kxx", "k" & qrykiosknum)
    'MsgBox (strSQL)  ' it is too big to see all of it
    Debug.Print strSQL

    ' then run strSQL


End Sub

Having dynamic tablename in MSAccess or MSSQLServer is possible when the Replace is done before executing the SQL.

Answer 2

I doubt you can make this work by using a query with a parameter. You are much better off using VBA. Use InputBox to get the variable portion of the query and DoCmd.RunSQL to run the query.