Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help With DCount Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following code in form before update, trying to make sure strRefNo has not been entered within the past 10 months.

    The following worked OK and returned a count of 1

    <pre>strStartCode = "201"
    strEndCode = "211"
    strStartDate = "04/24/2005"
    strEndDate = "02/24/2006"

    lngCount = Nz(DCount("strRefNo", _
    "qrytbl_RevTran_MasterDetail_All", _
    "strRefNo=" & Chr(34) & Me.strRefNo & Chr(34) & _
    " And strRevCode Between " & Chr(34) & strStartCode & Chr(34) & _
    " And " & Chr(34) & [strEndCode] & Chr(34) & _
    " And dtmRevDate Between #" & strStartDate & "# and #" & strEndDate & "#"), 0)
    </pre>


    I changed the code to the following and my count is 0

    <pre>lngCount = Nz(DCount("strRefNo", _
    "qrytbl_RevTran_MasterDetail_All", _
    "strRefNo=" & Chr(34) & Me.strRefNo & Chr(34) & _
    " And strRevCode Between " & Chr(34) & strStartCode & Chr(34) & _
    " And " & Chr(34) & strEndCode & Chr(34) & _
    " And dtmRevDate Between " & DateAdd("m", -10, Date) & " and " & Date), 0)

    MsgBox "lngCount: " & lngCount & " " & DateAdd("m", -10, Date) & " - " & Date
    </pre>


    What am I doing wrong?

    Thanks, John

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Help With DCount Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi,
    Your second version is missing the # date delimiters - try adding them back in as in the first version - i.e.:
    <code>" And dtmRevDate Between #" & DateAdd("m", -10, Date) & "# and #" & Date & "#"), 0)</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With DCount Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thanks Rory

    Didn

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

    Re: Help With DCount Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    One other point: if you (or your clients) potentially have users with non-US systems, you must force the date into US date format - it's the only format that SQL understands:
    <code>
    " And dtmRevDate Between #" & Format(DateAdd("m", -10, Date), "mm/dd/yyyy") & "# and #" & Format(Date, "mm/dd/yyyy") & "#"), 0)</code>

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With DCount Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thanks for the heads up

    John

Posting Permissions

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