Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Location
    Manchester, England
    Posts
    23

    Unanswered: Dlookup Date Dilemma

    I have a table with a field 'InputDate' which is populated with the Now() function giving me the exact date and time a record was added. I now need to count the number of records input by a particular user today, and am trying to achieve this with the following code:

    varCount = DCount("EnquiryNum", "tblAspData", "QamPayNo = '" & Payno & "' AND InputDate = " & Date)

    I'm pretty sure this doesn't work because 'InputDate' is date & time, whereas the VB property 'Date' is just date so they will never be equal. Is there a function I can put in front of 'InputDate' to strip the time element or any other method of acheiving the desired result?

    Many Thanks,

    JBee

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What do you have set for the variable "date"? If I understand correctly, you want something like:

    DCount("EnquiryNum", "tblAspData", "QamPayNo = '" & Payno & "' AND InputDate = Date()")
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2004
    Location
    Manchester, England
    Posts
    23
    Teddy,

    Date isn't a variable, it's a VB property which returns the current date (seems you don't need the open-close brackets, as I tried adding them in case that was the problem but they disappeared).

    Cheers,

    JBee

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Just a thought before I dash off for the wekend.

    Try

    varCount = DCount("EnquiryNum", "tblAspData", "QamPayNo = '" & Payno & "' AND InputDate BETWEEN " & Date & " AND " & Date +1)

    Without trying it I don't know if you need the # delimiter with Date function

    HTH

    MTB

  5. #5
    Join Date
    Aug 2004
    Location
    Manchester, England
    Posts
    23
    Thank MTB. That worked!

    Cheers,

    JBee

Posting Permissions

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