CODEWITHSUNDEEP
vbams-access
Tom Zvolensky
Tom Zvolensky

Reputation: 73

Access VBA Function produces different results for different users

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

Upvotes: 1

Views: 89

Answers (1)

Gustav
Gustav

Reputation: 55906

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.

Upvotes: 1

Related Questions