1. The WHERE clause should come after the FROM and before the GROUP BY.
2. The Date values should be properly formatted, this depends on the server and the date format(s) it accepts. Don't forget that in a Pass-Through query the Jet Engine won't perform any conversion or formatting of the data.
Firstly sorry in the delay in getting back to you, Thanks for your response, and indeed your help, you were correct in the formatting of the dates, the little critters threw me for a while
The way I got around this issue and know doubt you guy's will pobably say Nooo , was to pass the whole stament to the pass through query as one large string see below, if there is a more efficient way please let me know
Dim SQLString As String
SQLString = "select partcode,sum(salestotal) As sales " & _
"from history " & _
"where date between '" & _
Forms!SetUp!txtFromDate & "' and '" & _
Forms!SetUp!txtToDate & "'" & _
"group by partcode"
I won't say anything but this: If it works and the server accepts the dates as they are transmitted in the query, the goal is reached and all is well. However I would test with several dates where ambiguities might arise: 4/05/2011 and 5/04/2011, 1/12/2011 and 12/01/2011, etc.
Glad you could find a solution, and you're welcome by the way!