Bdtjtk

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

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.

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.