1

I have a VBA module that is run via a macro. The code runs five queries against a single table to count five different quantities and places them in the body of an email message which is sent to various parties.

When I run the module, it produces the correct counts in the message. When an associate runs it, the queries produce zero for each of the five counts.

Both of us use Access 2016, so version is not a factor.

This has me scratching my head.

Has anyone encountered this situation before? I'm at a loss as to the cause.

Code snippet below:

    Set rs = db.OpenRecordset("SELECT Count(*) As Total FROM [Migration List] Where TargetUserMigrationDate = #" & NextMigrationDate & "#")
    Total = rs!Total
    Set rs = db.OpenRecordset("SELECT Count(*) As AUSTcount FROM [Migration List] Where BatchNumber Like 'UAUST*' AND TargetUserMigrationDate = #" & NextMigrationDate & "#")
    AUSTcount = rs!AUSTcount
    Set rs = db.OpenRecordset("SELECT Count(*) As EMEAcount FROM [Migration List] Where BatchNumber Like 'UEMEA*' AND TargetUserMigrationDate = #" & NextMigrationDate & "#")
    EMEAcount = rs!EMEAcount
    Set rs = db.OpenRecordset("SELECT Count(*) As AMERcount FROM [Migration List] Where BatchNumber Like 'UAMER*' AND TargetUserMigrationDate = #" & NextMigrationDate & "#")
    AMERcount = rs!AMERcount
    Set rs = db.OpenRecordset("SELECT Count(*) As APACcount FROM [Migration List] Where BatchNumber Like 'UAPAC*' AND TargetUserMigrationDate = #" & NextMigrationDate & "#")
    APACcount = rs!APACcount
    Set rs = db.OpenRecordset("SELECT Count(*) As MACcount FROM [Migration List] Where [Migration List].[Mac User] = TRUE AND TargetUserMigrationDate = #" & NextMigrationDate & "#")
    MACcount = rs!MACcount
Vincent G
  • 3,153
  • 1
  • 13
  • 30
Tom Zvolensky
  • 63
  • 7
  • 1
    Good to have a VBA module, but if you do not share it, nobody can help you, I am afraid... – FaneDuru Dec 10 '19 at 15:14
  • 1
    Maybe locale can affect the results. – Ricardo Diaz Dec 10 '19 at 15:22
  • A more complete sample could be appreciable See [mcve]. – Vincent G Dec 10 '19 at 15:26
  • https://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better – braX Dec 10 '19 at 15:47
  • Use `Debug.Print`: [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) -- And check that you both are linked to the same data, and use the same date. – Andre Dec 10 '19 at 15:56
  • Also, just as a thought, dates can be affected by using `mm/dd/yyyy` vs `dd/mm/yyyy` format in the variable. Personally, I like to format the date variables to something like `Jan 5, 2019` instead so the DB Engine doesn't get confused. – braX Dec 10 '19 at 16:00
  • My colleague is in the UK, so the format of NextMigrationDate may be the culprit. Will pursue this lead and see what turns up. – Tom Zvolensky Dec 10 '19 at 16:05

1 Answers1

1

This will work both here and there:

Set rs = db.OpenRecordset("SELECT Count(*) As AUSTcount FROM [Migration List] Where BatchNumber Like 'UAUST*' AND TargetUserMigrationDate = #" & Format(NextMigrationDate, "yyyy\/mm\/dd") & "#")

If you don't force the format, the date values will be casted to text expressions as to the local settings.

Gustav
  • 53,498
  • 7
  • 29
  • 55