Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1

    Unanswered: Code for Criteria in a Pass Through Query Help Please !!

    Hi

    Hope you can help, I have a pass through query working fine within my Access 2003 Database

    select partcode, SUM(salestotal) As sales
    from history
    group by PartCode

    What I want to do is pass some date ranges through from a form (StartDate / EndDate)

    something like

    select partcode, SUM(salestotal) As sales
    from history
    group by PartCode

    where Forms!SetUp!MisStartDate and Forms!SetUp!MisEndDate

    Keep geting sql error, what am i doing wrong

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  3. #3
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Sinndho

    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"

    CurrentDb.QueryDefs("MIS").SQL = SQLString

    Regards

    Mark

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •