Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    DSum dates (Acces97SR2)

    Advice please on running Dsum with date values.
    In a query I want to sum number of docs whose date is less than and including current record.
    Table comprises three fields:
    field1: Sys_number - three figure system number such as 100;101;120;345 etcetera
    field2:Number_of_docs - number of documents in this system
    field3:Expected_Date - short date format field
    Query comprises:
    field1:Sys_Number
    field2:Number_Of_Docs
    field3:Expected_Date (sort ascending)
    field4:RunsumSum("NumberOfDocs","tblSafetyPriorityDocs","[Expected_Date]<=" & [Expected_Date] & "")

    Each time the query tries to run I get dialog box with:
    Syntax error (missing operator) in query expression '[Expected_Date]<='.

    I'm sure we had success previously by using DSum("NumberOfDocs","tblSafetyPriorityDocs","[Expected_Date]<=#" & [Expected_Date] & "#")
    to enclose the date field but I searched through the help files and MS suggest # is not necessary if the fields are already defined as date format. (All fields are defined according to their data as the norm).

    I have also tried forcing the format of the field using:
    DSum("NumberOfDocs","tblSafetyPriorityDocs","[Expected_Date]<=" & Format([expected_date],"dd/mm/yy"))

    Any suggestions anyone?
    TIA
    Alan
    Cheshire
    UK

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum dates (Acces97SR2)

    You should always use US date format in SQL. Try the following:

    DSum("Number_of_Docs","tblSafetyPriorityDocs","Exp ected_Date<=#" & Format([Expected_Date],"mm/dd/yyyy") & "#")

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: DSum dates (Acces97SR2)

    Thanks Hans. Just tried it and the results are as expected. Excellent.

Posting Permissions

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