DSum on a Form from Date - I hate dates :-( (1 Viewer)

[Gasman]

I always prefer to create a string variable if more than one criteria, then use that in a function.
That way I can debug.print ìt until I get it correct.

 

[RonPaii]

Ok, so we want to "sum" a column called "Covers" from a table called "tbl_Net_RestaurantBookings", and pull based on a column in the database called CheckIn.

so, the format is thus this:

=DSUM("some column to sum","the table to work on", "[ColumnName] = "#some date#")

So, you do not want to try and format the internal "existing" date column - you ONLY provide the correct formatted date critera.

However, since this is a bit messy, and since the date time column MIGHT have a time portion? Then above will not work.

And since the date criteria is coming from the special "system" form you have setup?

And since we want to use high speed indexing?

Well, then I suggest you build a helper function.


As a FYI? Your origional "where" clause was trying to format BOTH the column, and the supplied date value. As a genreal rule, you ONLY have to format the critera, and you "never" need to try and format such internal columns that are defined as a datetime column......


So, in a standard code module (not a form's module, and not a class module), then put this function:

Public Function MyToday(sField As String) As String

    Dim sWhere      As String
  
    Dim sDateStart  As String
    Dim sDateEnd    As String
  
    Dim dToday      As Date
    Dim sColumn     As String
  
    sColumn = "[" & sField & "]"
  
  
    dToday = Format([Forms]![frmBackGround]![txtLocalSystemDate], "mm/dd/yyyy")

    sDateStart = "#" & Format(dToday, "mm/dd/yyyy") & "#"
    sDateEnd = "#" & Format(dToday + 1, "mm/dd/yyyy") & "#"
  
    sWhere = sColumn & " >= " & sDateStart & " AND " & sColumn & " < " & sDateEnd
  
    MyToday = sWhere

End Function

Once you save above? Then make sure your frmBackGround is open, with a valid date setup.

Then in the debug window, type in say this:

? MyToday("CheckIn")

Take close look at the output - it should result in a correctly formed "SQL where clause" which dmax(), dcount(), dsum() all require.

so, now your mess?

It becomes this:

=DSUM("Covers", "tbl_Net_RestaurantBookings", MyToday("CheckIn"))

And you can use the above for dcount(), dmax() etc.

R
Albert

I would add a explanation why checking a range of dates on date columns with possible time is needed.

"CheckIn >= #12/29/25# AND CheckIn < #12/30/25#"

The time parts starts with 0 at midnight so a range of dates including the start date and excluding the end date will get all the times in the start date.

PS Others noted that you need to provide US date format in the criteria. I don't think that is correct, assuming the field is a date type and you surround the criteria with pound sign, Access will convert the criteria to a date for comparison.

 

[Ken Sheridan]

PS Others noted that you need to provide US date format in the criteria. I don't think that is correct, assuming the field is a date type and you surround the criteria with pound sign, Access will convert the criteria to a date for comparison.

Not so. A date literal must be in US date format or an otherwise internationally unambiguous format such as the ISO standard of YYYY-MM-DD Note that the standard uses case to differentiate months and minutes, whereas Access use m for the former and n for the latter. The CDate function, on the other hand, will respect the local date format, so in my case would not need to formatted as with a date literal to avoid changing 4th July to 7th April!

 

[RonPaii]

Not so. A date literal must be in US date format or an otherwise internationally unambiguous format such as the ISO standard of YYYY-MM-DD Note that the standard uses case to differentiate months and minutes, whereas Access use m for the former and n for the latter. The CDate function, on the other hand, will respect the local date format, so in my case would not need to formatted as with a date literal to avoid changing 4th July to 7th April!

If I use the OP's original date format DD/MM/YYYY in the immediate window.

?#29/12/2025#
12/29/25

Entering that format in the query window, Access converts it to US (On my US computer).
?dlookup("[RevDate]","feRevision","[RevDate]=#29/12/2025#")
#12/29/25#

Note the day must be > 12 for DD/MM/YYYY to convert correctly.

 

[Albert D. Kallal]

Albert, thank you for that, your code below works great for me, I have used it with DCOUNT too.

WORKS

=DSUM("Covers", "tbl_Net_RestaurantBookings", MyToday("CheckIn"))

However, is it possible to add a second criteria to not count cancellations (status column is blank), not sure if this is possible because of the module?
I have tried (amongst other attempts):

=DSum("Covers","tbl_Net_RestaurantBookings",MyToday("CheckIn") And IsNull([Status]))

=DSum("Covers","tbl_Net_RestaurantBookings",MyToday("CheckIn") And ([Status]=Null))

What I get back is #Name?


DocMan and GasMan, thank you both for that - it will be helpful for me.

Thank you

Good for you! - glad this works.

As noted, when working with a Access database, and doing things like a dsum() etc. on the data?

You REALLY but REALLY want to build a expression that high-speed indexing can be used. Since this "goal" was going to increase the complexity of the expression, hence the helper function was indeed a great road to take.

next up -- add additional criteria?

Sure, you can most certainly do this.

hence:

DSum("Covers","tbl_Net_RestaurantBookings",MyToday("CheckIn") & "  And Status Is Null" )

Remember, MyToday() already returns a plain jane string.

hence 
dim abc as string
abc = "#12/29/2025#"

print "abc"
output: abc

print abc
output: #12/29/2025#

So:
print  "MyToday("CheckIn") And [Status] is Null)"
output:
MyToday("CheckIn") And [Status] is Null)

print MyToday("CheckIn") & " and [Status] Is Null)"
output:
[CheckIn] >= #12-29-2025# AND [CheckIn] < #12-30-2025# and Status Is Null

So, yes, are are free to "extend" or add more to the where clause. Just remember that the function returns a string, so any additional criteria will thus ALSO have to be a string such as " AND Status Is Null"

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

 

[Ken Sheridan]

Note the day must be > 12 for DD/MM/YYYY to convert correctly.

Exactly. If, on a system using UK date format, a date literal makes no sense in US format, which is the case with #29/12/2025#, Access will interpret it as the intended date, but if it does make sense, as with #04/07/2025# it will interpret it incorrectly as 7th April, which is not the intended date here in the UK. Consequently we never enter a date literal in UK format. I always use the ISO standard, which makes sense anywhere, and which, unlike US format, I intuitively read correctly. Many developers always call a little function to format date literals, usually in US format to judge by those I've seen posted online.

BTW it wasn't always like this. In the early versions of Access date literals respected the local date format. Microsoft changed it sometime in the 1990s. This, as you can imagine, caused chaos with legacy databases in Access. It was recommended in the CompuServe Access forum, where I was a sysop at the time, that these be recoded to use the CDate function rather than date literals, as this still respected the local format, and would thus handle UK dates correctly. This can be seen in the immediate window on my system:

? #07/04/2025# = CDate("04/07/2025")
True

which would return False in your case of course. Passing a date as a string in the ISO date format into the CDate function would work anywhere, however, so should be done if the database is to be used internationally.