> "#" & Format([CheckIn], "dd/mm/yyyy") & "#"
No, that is no good, because that would result in a date like 31/12/2025, which is not a legal date in US format.
You have to either use US format mm/dd/yyyy, or ISO: yyyy-mm-dd
It has to be in US or ISO format regardless of where you are. I am in the UK.
I used this, that I found on the net.
'Public Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#" 'Needed for dates in queries as Access expects USA but will accept ISO format.
However for Domain functions it will recognise your region.
Another very important point: there is an important difference between the VALUE of a date, and the way it is DISPLAYED.
Under the hood every date is an 8-byte floating point value.
We can display it in many ways, such as the several ways mentioned above, and many others.
Example (in the Immediate window)
?#12/31/2025# = #2025-12-31#
True
As I wrote in #5, as long as you are storing the date value as a Date/Time field in the database, you are good. How you format it to display to the user does not matter to the date math and queries.
If you are storing your date values in Short Text fields, that is really bad and should be fixed right away. Bite that bullet now.
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:
Code:
=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:
Code:
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.
Just a note: IF you are working on the same data table you showed us in a different thread, your Arrival (or Departure) will include a date and time. To take data from that table to summarize over a single day for all times, you need to do something to strip out the times for the comparison.
One easy way to strip away time from field [ArbitraryDateTime] is CDATE(CLNG([ArbitraryDateTime])) ... if that field is a date/time field. It gets more complex if the field is actually a text field that hasn't been converted to date/time yet.
Just a note: IF you are working on the same data table you showed us in a different thread, your Arrival (or Departure) will include a date and time. To take data from that table to summarize over a single day for all times, you need to do something to strip out the times for the comparison.
One easy way to strip away time from field [ArbitraryDateTime] is CDATE(CLNG([ArbitraryDateTime])) ... if that field is a date/time field. It gets more complex if the field is actually a text field that hasn't been converted to date/time yet.
Just a note: IF you are working on the same data table you showed us in a different thread, your Arrival (or Departure) will include a date and time. To take data from that table to summarize over a single day for all times, you need to do something to strip out the times for the comparison.
One easy way to strip away time from field [ArbitraryDateTime] is CDATE(CLNG([ArbitraryDateTime])) ... if that field is a date/time field. It gets more complex if the field is actually a text field that hasn't been converted to date/time yet.
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):
Code:
=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.
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):
Code:
=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.
The criteria argument of the DSum function call should be a string expression:
Code:
=DSum("Covers","tbl_Net_RestaurantBookings", MyToday("CheckIn") & " And Status Is Null")
Note how the criteria argument now evaluates to the string expression returned by the MyToday function concatenated to the literal string ' And Status Is Null'
This code is is closest, however it is counting ALL covers in the table - I am looking to count just that day and also exclude cancellations (status is null)
Code:
=DSum("Covers","tbl_Net_RestaurantBookings","MyToday(CheckIn) And [Status] Is Null")
It seems to be ignoring the MyToday(CheckIn) part?? Weird
That part needs to be evaluated first, outside the quotes delimiting the literal string, and its return value then concatenated to the latter. See post #18 above.
