I know what the problem is I just can't figure out how to fix it. The query is based on a cross tab query. According to Access help I need to set the query parameter values. However the criteria I'm using comes from a combo box in a form... I'm stumped.
scal1 is a pivot table. I'm assuming that's where the problem is coming from.
SELECT scal1.IBLITM, scal.IBSRP4, scal1.IBMCU, scal1.IMDSC1, scal1.SSTOCK, scal1.IBSTKT, scal1.MFDRQJ, static_calendar.[calendar_month/year], scal1.UCOST, scal3.ICLEAD, IIf(IsNull([AD]),0,[AD]) AS demand, IIf(IsNull([CF]),0,[CF]) AS forecast, ([demand]-[forecast]) AS var, Abs([demand]-[forecast]) AS abs_var, IIf((scal1!IBSTKT='S' Or scal1!IBSTKT='M'),28,[ICLEAD]) AS ltime, Round(Sqr([LTIME]),1) AS sqr_ltime
FROM (scal1 LEFT JOIN scal3 ON scal1.IBLITM = scal3.ICPROD) LEFT JOIN static_calendar ON scal1.MFDRQJ = static_calendar.julian_date
Yes, I don't think you can reference Forms values like that in a query. You actually need to build the literal SQL code in VBA.
Here is a real simple example. Let’s say you have a combo box named “cboName” and you wanted to select all fields from “Table1” where the “strName” field was equal to the name you have selected in your combo box. Then the VBA code for that would look something like:
Dim strSQL as String
strSQL = “SELECT [Table1].* From [Table1] Where [Table1].strName=” & Chr(34) & cboName & Chr(34)
Note: The Chr(34) expression just puts quotes around your cboName field
If you want to see what you just built, you could use the line:
(I use this a lot when testing to make sure I am building the string correctly. I will often work backwards by building the query I want manually abd viewing the SQL code I need to build. Then I will try to replicate it in my VBA and use the MsgBox to verify I am building it correctly).
Now you need to assign the SQL string you built to query. This code will apply it to “Query1”:
CurrentDb.QueryDefs("Query1").SQL = strSQL
Now if you open this query (or any Form/Report based on this query), it will show you your new results based on the criteria you entered on your form.
That's the stipulation. The form does need to be up to work. But for what I'm doing, I need to have the form up anyway. Usually what I'm doing is exporting data to Excel through a button (executing a macro) on said form. I can use a combo box on the form to select criteria, whether it be a month/year or a location.