you cannot say Where someField = theDate OR thisDate OR thatDate OR...
It has to be like Where someField = theDate OR someField = thisDate OR someField = thatDate OR ... - get it?
Not likely that you will want to build a string like this in some control. Even if it was properly constructed, it probably wouldn't work as June7 says (though it would be interesting to know if it would work if built properly). If the dates are in a table that someone can peek at, why isn't that table part of the query? If it's because there are many more dates than you want (i.e. it's not a table of last Saturday dates) then one way would be to build a query that returns only those dates, then make that query part of the main query's table structure. Then you will only get records for the dates that apply. Or have a table that you wipe clear then append the dates to. However, since I could also construct the sql in code as June7 suggested, that would be my preferred method, based on my understanding of the situation. One reason might be that as the year progresses, I imagine the date list gets ever longer and such a query might be too complex for you to create and maintain, and I have no idea how you're generating Saturday only dates.
FWIW - please use code tags around your code and indent as necessary. It alleviates problems and makes code easier to read. This s = "#" & rs!calenddate probably should be s = "#" & rs!calenddate & "#"
Note:
- air code - use on a copy of your db first.
- I might have dropped a bracket or similar defining character. I presume the dates are valid (e.g. 01/01/2017) and can be wrapped in date delimiters (#) in code
- you can use line continuation characters to build sql if you prefer - I rarely use them
Code:
Private Sub UpdateString()
'dimension your variables first and specify the type library unless you know it is better not to do so
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strWherePart As String, strSql As String, strEnd As String, strFY As String
Dim lngCust As Long
On Error GoTo errHandler
lngCust = Me.customer
dteFY = Me.CurrentFiscalYear
Set db = CurrentDb
Set rs = db.OpenRecordset("tblcurrentDatesMJC")
strWherePart = "WHERE (((dbo_tblRASdata.wk_ending)=#"
'if this is an action query...
db.Execute "qrydates2mjc", dbFailOnError
If Not rs.EOF And Not rs.BOF Then 'ensure the rs has records before trying to movefirst
rst.MoveFirst
Do While Not rs.EOF
strWherePart = strWherePart & rs!calenddate & "# OR (dbo_tblRASdata.wk_ending) = "
rs.MoveNext
Loop
End If
strSql = "SELECT dbo_tblRASdata.sku, dbo_tblRASdata.Loc, dbo_tblRASdata.CustomerID, dbo_tblRASdata.FY, "
strSql = strSql & "Max(dbo_tblRASdata.retail) AS MaxOfretail, Sum(dbo_tblRASdata.mtd_sales) AS SumOfmtd_sales "
strSql = strSql & "FROM dbo_tblRASdata "
strWherePart = Left(strWherePart(Len strWherePart-32)) & "))" '-32 to strip all from the last OR & add closing brackets
strEnd = "GROUP BY dbo_tblRASdata.sku, dbo_tblRASdata.Loc, dbo_tblRASdata.CustomerID, dbo_tblRASdata.FY "
strEnd = strEnd & "HAVING (((dbo_tblRASdata.CustomerID)=" & lngCust & ") AND ((dbo_tblRASdata.FY)=#" & strFY & "#)) "
strEnd = strEnd & "ORDER BY dbo_tblRASdata.sku, dbo_tblRASdata.Loc;"
strSql = strSql & strWherePart & strEnd
debug.print strSql 'check sql in Immediate Window; copy/paste to new query to test; comment this line out if OK
'what now? Open a report or form based on this sql?
exitHere:
rs.Close
db.Close
Set db = Nothing
Set rs = Nothing
Exit Sub
errHandler:
msgbox "Error " & err.Number & ": " & err.Description
Resume exitHere
End Sub