Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013

    Unanswered: Date Add Expression

    I am trying to get the following expression to work but can not get the formular correct, I keep getting and error that I have not enter the correct operator

    =DCount("*","WarningNotices","Location = 'FCH' AND [Issue Date] between [1stwc] AND DateAdd("d", 6, [1stwc]) AND [Description] = 'Warning'")

    It is the section with the date add if I put in a fixed date then it works correctly basically 1stwc is a varible which the user selects the beginning of the week and the dateadd will calculate the end of the week

    Thank you
    Last edited by pjawynn; 03-15-14 at 21:21.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    When building a fragment of SQL which is effectively what you are doing you need to switch between direct text and vba to build the string

    ... between #" & format(1stwc,"yyyy/mm/dd") & "# and " & dateadd(.......
    If you use a variable or vba function you need to drop out of the string mode inyo vba mode.

    Date literals should be limited by # and be in ISO or US format mm/dd/yyyy
    I thought variable names had to start with a letter, however im also guessing that 1stwc is actually the name of a control as opposed to a variable.
    As ever you would make life easier for yourself to use the debugger to find out what your code actually does as oppisdd to what you think of presume it does. If you cant be bothered to use the debugger (and no semi serious user of access can hold that opinion) then at the least assign contentioys code to a variable and display it via a message box.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    If 1stwc is a variable, you should use (without square brackets):
    =DCount("*", "WarningNotices", "Location = 'FCH' AND [Issue Date] between " & 1stwc & " AND " & DateAdd("d", 6, 1stwc) & " AND [Description] = 'Warning'")
    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