Results 1 to 12 of 12
  1. #1
    mw4 is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2019
    Posts
    7

    VBA count of dates

    I have a table that includes a date that I want to query if there are more than one unique date.

    In this example there are more than one date.

    MyTable:


    Date.........IDnum
    1/1/2001 ABC
    1/2/2001 CDE
    1/2/2001 FGH
    1/2/2001 IJK

    what I want to do in VBA is do something like this:

    If date count>1 then
    Run Query 1
    else
    Run query 2
    EndIf


    Most times the dates are the same like this:

    MyTable:
    Date.........IDnum
    1/2/2001 ABC
    1/2/2001 CDE
    1/2/2001 FGH
    1/2/2001 IJK

    Can someone help me with syntax?


    Alternatively, I use the "On No data" in reports for when there is no data.
    Is there an equivalent use for the above?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not quite making sense. Why should there not be multiple different dates?

    You can do an aggregate query that groups by date and counts records for each date.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mw4 is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2019
    Posts
    7
    I just included a very small snip of data to illustrate.
    This is a daily file that I get from a vendor with invoices.
    Typically there are hundreds of "IDnum"s per day -- 99% of the time with the same date (yesterday) -- what I need is something that tells me if there are more than one day included in the day's data file.

    I currently have a VBA setup which processes the file and shows me the resulting invoices, but I need an alert (I use an emailed report) that says "MORE THAN ONE DATE IN TODAY'S FILE", because they need special attention.

    Make more sense?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You didn't include your sample but why not simply use dCount?

    If Dcount("[MyDateField]","[tblMyTable]")>1 then....

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That DCount will not identify if there are different dates in the file such as 1/1/2001 and 1/2/2001.

    Post your code that processes the file. How is file processed - is it a text file and you read in one line at a time? I am sure it could be modified to accomplish this alert. Something like:
    Code:
    Dim booMultiple As Boolean, dteDate As Date
    <open file for processing>
    dteDate = <date value from first record>
    <loop file to process data>
    If dteDate <> <date value from record> Then
        booMultiple = True
        dteDate = <date value from record>
    End If
    <do something else with data>
    <get next record>
    <end processing file>
    If booMultiple Then MsgBox "MORE THAN ONE DATE IN TODAY'S FILE"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    mw4 is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2019
    Posts
    7
    It is a table which is replaced daily
    Just those two fields InvDate and IDnum

    I just need something like this

    If date count>1 then
    .....Send an email saying "more than two dates today"
    .....Send normal report via email listing today's ID's
    else
    .....Send normal report via email listing today's ID's
    EndIf


    I can currently get the answer in a query like this:
    SELECT Count(*) AS NumberOfRows
    FROM [SELECT DISTINCT InvDt FROM tblTodaysIds]. AS T;

    But I want to put it into my VBA code that prints the report.
    Listed here:

    stDocName = "repTodaysIds"
    DoCmd.SendObject acReport, stDocName, acFormatXLS, _
    "sentto@myemail.com", _
    , , "TodaysIds for " & Date _
    , "Here are today's Ids.", False, False

    If there was no Data, it would be easy with a OnNoData, but I want it for >1.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    As Vlad pointed out use domain aggregate. DCount("*","tblTodaysIds").

    "Here are today's Ids." & vbCrLf & IIf(DCount("*","tblTodaysIds")>1, "MORE THAN ONE DATE IN TODAY'S FILE","")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    mw4 is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2019
    Posts
    7
    sorry, I posted the wrong query

    SELECT Count(*) AS NumberOfRows
    FROM [SELECT DISTINCT InvDt FROM tblTodaysIds]. AS T;

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    My bad on the original dCount, I think you can create a totals query based on the table where you group by the date field (named qryImportedDays - just one field showing the distinct days) then use the dCount in VBA:
    Dcount("[MyDateField]","[qryImportedDays]")>1, no need to loop.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    mw4 is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2019
    Posts
    7
    I can try that, but what is the VBA syntax to pull that dcount?

    that's where I am getting errors.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not sure how that query returns anything because syntax is not correct. Should be:

    SELECT Count(*) AS NumberOfRows FROM (SELECT DISTINCT InvDt FROM tblTodaysIds) AS T;

    Build and save a query object like:

    SELECT DISTINCT InvDt FROM tblTodaysIds;

    This will create a dataset of all unique dates found in tblTodaysIds.

    Now in VBA use DCount().

    "Here are today's Ids." & vbCrLf & IIf(DCount("*","QueryName")>1, "MORE THAN ONE DATE IN TODAY'S FILE","")

    Or use your original nested query and DLookup()

    "Here are today's Ids." & vbCrLf & IIf(DLookup("NumberOfRows","QueryName")>1, "MORE THAN ONE DATE IN TODAY'S FILE","")

    Alternative to saving query object is to open a recordset in VBA and check its Recordcount or field value.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    mw4 is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2019
    Posts
    7
    THANKS TO EVERYONE!!
    This works perfectly:

    "qryOfDays"
    SELECT DISTINCT InvDt
    FROM tblTodaysIds;


    If (DCount("*", "qryOfDays") > 1) Then
    .....Send an email saying "more than two dates today"
    EndIf

    .....
    Send normal report via email listing today's ID's

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Count Consecutive Dates
    By zcavaricci in forum Access
    Replies: 1
    Last Post: 11-19-2018, 12:13 PM
  2. Count Records between two dates
    By MJCRN in forum Access
    Replies: 2
    Last Post: 05-17-2016, 08:23 AM
  3. Replies: 5
    Last Post: 07-12-2014, 02:55 PM
  4. How To Count Dates on Report?
    By netchie in forum Access
    Replies: 18
    Last Post: 08-31-2011, 12:10 PM
  5. Count difference between two dates
    By Costa in forum Forms
    Replies: 3
    Last Post: 03-09-2010, 10:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums