- Local time
- Today, 00:11
- Joined
- Feb 28, 2001
- Messages
- 27,122
Syntactically, this should be portrayed as
Code:
ThisMonthWeekdays = DSum("[TotalSales]", "QryYTDSales", "[Event] = False And [date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "#")
{if [Event] contains TRUE or FALSE}
(or)
ThisMonthWeekdays = DSum("[TotalSales]", "QryYTDSales", "[Event] = 'No' And [date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "#")
{if you load [Event] with the words Yes or No}The syntax of the original statement has the word NO acting as though it were a field named [No] or a variable named No. That is, however, not what you just described. Whatever [Event] contains is some value. Your Event = No (where No just kind of sits there like a lump) doesn't convey that type of comparison. The word "No" is not an Access reserved word because despite the name, a Yes/No field is actually TRUE or FALSE. (There IS a constant value called vbNo but it is used in a different context.)
This points to issues in date formatting. Review comments about the differences between USA dates and UK dates - and recognize that even if your Windows settings include a particular date format, Access doesn't always honor that. The syntax of your date selection ( [date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "#" ) suggests that you are substituting dates in some literal format and perhaps that format doesn't match what Access wants. OR something is wrong with the format of [Date1].
Others have also suggested issues with your dates. I was trying to be open-minded about causes so that I wouldn't miss something, but I still can't rule out that this is a date formatting problem. In fact your comment about manual vs. programmed dates strengthens that viewpoint. All I can suggest is that when formatting the [Date1], StartDate, and EndDate items, they must ALL be treated identically because otherwise you are not making an "apples to apples" comparison.
Last edited:
