=DSum("[ReceiptCash]","tblReceipts","DateOfReceipt = between [Forms]![rptReportsMainScreen]![txtDateFrom] and [Forms]![rptReportMainScreen]![txtDateTo])
The two items "=" and "between" are both operators and it is syntactically incorrect to have them next to each other. Use one or the other, but not both.
I don't think it's the 'BETWEEN' keyword that is actually giving you trouble. There's nothing else to it other than what lagbolt said. Instead, from what I've seen of several of your posts, your main problems are that you need to learn how to write SQL strings and you need to proof-read your code better. Your current string looks like this:
Code:
[COLOR=#000000][COLOR=#DD0000][COLOR=Black]"DateOfReceipt = between [Forms]![rptReportsMainScreen]![txtDateFrom] and [Forms]![rptReportMainScreen]![txtDateTo]"[/COLOR]
[/COLOR][/COLOR]
The most basic problem is that your quotes are in the wrong places. As an example, let's say your txtDateFrom is currently May 30, 2012 and your txtDateTo is July 15, 2012. You want the SQL statement to look like:
Code:
DateOfReceipt BETWEEN #30/05/2012# AND #15/07/2012#
But since you left txtDateFrom and txtDateTo inside of the quotes, your SQL statement looks exactly like the first block, which has no dates. Instead, it's interpreted as between two strings with values "[Forms]![rptReportsMainScreen]![txtDateFrom]" and "[Forms]![rptReportMainScreen]![txtDateTo])". In order to get this to evaluate properly, you need to leave your controls and variables outside of the string. Thus, you should have something like this:
Code:
Dim strQuery As String
strQuery = "DateOfReceipt between " & [Forms]![rptReportsMainScreen]![txtDateFrom] & " and " & [Forms]![rptReportMainScreen]![txtDateTo]
The strQuery variable isn't necessary, but it makes debugging much easier as you can just add "Debug.Print strQuery" at the end to take a look at what your actual query string looks like. If you do, you'll notice that the current string looks like this:
Code:
DateOfReceipt BETWEEN 30/05/2012 AND 15/07/2012
That's still a little different from what it should be. Dates in SQL statements, when defined literally like this, should be surrounded by #. This tells SQL that these are dates, not numbers or strings. So, you would have to modify your string a bit more:
Code:
Dim strQuery As String
strQuery = "DateOfReceipt between #" & [Forms]![rptReportsMainScreen]![txtDateFrom] & "# and #" & [Forms]![rptReportMainScreen]![txtDateTo] & "#"
Now you should notice the output is:
Code:
DateOfReceipt BETWEEN #30/05/2012# AND #15/07/2012#
If you're lucky, you're finished. Unfortunately, date formats can differ depending on country. Sometimes it's DD/MM/YYYY and sometimes it's MM/DD/YYYY. While it's perfectly clear that 30/05/2012 is May 30, 2012, it's not so clear whether 12/01/2012 is January 12 or December 1. In this case, I believe the UK uses DD/MM/YYYY whereas Access defaults to the US standards for date, which happens to be MM/DD/YYYY. Thus, you need to add a little bit more to get the dates to look correctly. You will use the Format function. In this case, it will look like this
Code:
Format(<date>,"mm/dd/yyyy")
So, your complete statement would look like this:
Code:
Dim strQuery As String
strQuery = "DateOfReceipt between #" & Format([Forms]![rptReportsMainScreen]![txtDateFrom],"mm/dd/yyyy") & "# and #" & Format([Forms]![rptReportMainScreen]![txtDateTo],"mm/dd/yyyy") & "#"
Now assuming we didn't miss any quotation marks, brackets or any other symbols, or spelled anything wrong, you should be able to stick this back into your DSum as:
that must of taken ages to write. with things like this i do admit that i need to learn sql. i am only really getting to grips with vba. i have found vba to open my eyes to what is possible and just how easy the beginners stuff is. i do agree that my next hurdle will be sql as this kind of goes hand in hand with vba and it should make my thread creation slow down a bit.
also could you point me in the direction of sql strings, all you need to know.
thanks
thanks for all the effort you have put in to this.
just one thing. i prefer to put DLoopUp and DSum actually in the control rather than into vba and then to refer to it.
neutron. i did try the actual code you supplied. put it in the vba on report load event. the only thing is that it seems to ignor the date range and sum the whole field. i have noticed another thread whilst searching through google but no one seems to have answer that members concerns. that thread was back in 2010.
Hmm, it might still be something with the formatting. Honestly, since I live in North America, I usually don't really have to worry about the date formatting issue, so I may have messed that part up. This thread might help though: http://www.access-programmers.co.uk/forums/showthread.php?t=229037
Have you resolved this, only I've found that the = sign is not required and also the use of the format function also not required
ie
DSum("[monthlyTotal]", "[dbo_MasterMonthly]", "" _
& "[MonthDate] between #" & Me.StartDate & "# and #" & Me.EndDate & "#")
David
hi dave. not this has not been solved yet. i ahve tried it without the formatting. without the = sign. i have even tried it with #01/01/2012# just so there is not confusion with formatting and the text box criteria.
it seems to ignor the date for some reason. is there any other way to skin this cat. anything other than DSum()?
Have you resolved this, only I've found that the = sign is not required and also the use of the format function also not required
ie
DSum("[monthlyTotal]", "[dbo_MasterMonthly]", "" _
& "[MonthDate] between #" & Me.StartDate & "# and #" & Me.EndDate & "#")
David
Woops, I need to stop being sloppy with copy/paste. The equal sign definitely shouldn't be there. As for formatting, it may or may not be required depending on your system locale and the way your dates are formatted in the database.
@shutzy, Sorry, I think I may have placed the format in the wrong location. Try instead:
Code:
Dim strQuery As String
strQuery = "Format(DateOfReceipt,""mm/dd/yyyy"") between #" & [Forms]![rptReportsMainScreen]![txtDateFrom] & "# and #" & [Forms]![rptReportMainScreen]![txtDateTo] & "#"
Sorry to butt in, but you are getting off into the wilderness.
Code:
strQuery = "Format(DateOfReceipt,""mm/dd/yyyy"") between #" & [Forms]![rptReportsMainScreen]![txtDateFrom] & "# and #" & [Forms]![rptReportMainScreen]![txtDateTo] & "#"
This is wrong, because the final SQL contains a string with the date, which then is to be compared to dates (and not strings), and this is bound to go bad.
The SQL expression service actually does work in domain functions, so
Code:
[COLOR=#000000][COLOR=#007700]=[/COLOR][COLOR=#0000BB]DSum[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"[ReceiptCash]"[/COLOR][COLOR=#007700],[/COLOR][COLOR=#DD0000]"tblReceipts"[/COLOR][COLOR=#007700],[/COLOR][COLOR=#DD0000]"DateOfReceipt between [Forms]![rptReportsMainScreen]![txtDateFrom] and [Forms]![rptReportMainScreen]![txtDateTo])[/COLOR][/COLOR]
DSum("[monthlyTotal]", "[dbo_MasterMonthly]", "" _
& "[MonthDate] between #" & Me.StartDate & "# and #" & Me.EndDate & "#")
You can get away with this implict date-to-string conversion in US locale. Elsewhere, you need to do it explicitly, and so use the format function. Unless you skip all this and just rely on the expression service, as per my post #11, and then no worries about locale.
Sorry to butt in, but you are getting off into the wilderness.
Code:
strQuery = "Format(DateOfReceipt,""mm/dd/yyyy"") between #" & [Forms]![rptReportsMainScreen]![txtDateFrom] & "# and #" & [Forms]![rptReportMainScreen]![txtDateTo] & "#"
This is wrong, because the final SQL contains a string with the date, which then is to be compared to dates (and not strings), and this is bound to go bad.
The SQL expression service actually does work in domain functions, so
Code:
[COLOR=#000000][COLOR=#007700]=[/COLOR][COLOR=#0000BB]DSum[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"[ReceiptCash]"[/COLOR][COLOR=#007700],[/COLOR][COLOR=#DD0000]"tblReceipts"[/COLOR][COLOR=#007700],[/COLOR][COLOR=#DD0000]"DateOfReceipt between [Forms]![rptReportsMainScreen]![txtDateFrom] and [Forms]![rptReportMainScreen]![txtDateTo])[/COLOR][/COLOR]
Ah, interesting. Ya, I was a little concerned about where the format was going, although it seemed to work. It got a bit confusing since I wasn't sure which part of Access was defaulting dates to system locale and which one was doing US standard.
Hmm, didn't know that you could handle form objects like that directly in SQL strings. It will certainly simplify things quite a bit.
@spikepl. i put this straight into the control source
=DSum("[ReceiptCash]","tblReceipts","DateOfReceipt between [Forms]![rptReportsMainScreen]![txtDateFrom] and [Forms]![rptReportMainScreen]![txtDateTo])
it doesnt work. invalid string. im sure that it needs some " although when i have put in control source in the past i havent. the solutions either work without error and ignor the date criteria or dont work at all. i dont think the formatting is an issue as the date in the table are in uk format and the dates in the txt boxes are in uk format also. unless access converts these to us format if it does then it would do the same for both table and txt boxes!
i cant believe that this seems to be do difficult. every other DSum works but this is the 1st with dates as the criteria.
as per my last post. are there any other options to do this other than a list box. i really prefer to keep the sql in the control source.
I think spikepl almost had it, try
=DSum("[ReceiptCash]","[tblReceipts]","[DateOfReceipt] between #" & Format([Forms]![rptReportsMainScreen]![txtDateFrom],"mm/dd/yyyy") & "# and #" & Format([Forms]![rptReportMainScreen]![txtDateTo],"mm/dd/yyyy") & "#")
@spikepl. i put this straight into the control source
=DSum("[ReceiptCash]","tblReceipts","DateOfReceipt between [Forms]![rptReportsMainScreen]![txtDateFrom] and [Forms]![rptReportMainScreen]![txtDateTo])
it doesnt work. invalid string. im sure that it needs some " although when i have put in control source in the past i havent. the solutions either work without error and ignor the date criteria or dont work at all. i dont think the formatting is an issue as the date in the table are in uk format and the dates in the txt boxes are in uk format also. unless access converts these to us format if it does then it would do the same for both table and txt boxes!
i cant believe that this seems to be do difficult. every other DSum works but this is the 1st with dates as the criteria.
as per my last post. are there any other options to do this other than a list box. i really prefer to keep the sql in the control source.
It is definitely missing a " at the end of the query string. It should be:
PHP:
=DSum("[ReceiptCash]","tblReceipts","DateOfReceipt between [Forms]![rptReportsMainScreen]![txtDateFrom] and [Forms]![rptReportMainScreen]![txtDateTo]")
Keep in mind people responding might typo, especially when we're not working on Access at the moment and thus don't have a compiler to check for us. Don't be afraid to try fixing it if you think it looks like it's missing something.
The reason why you need quotes in this case is because these are criteria for the DSum function. As for formatting, I'm not terribly knowledgeable with it, but I believe the issue is between VBA, SQL and tables, where some of them default to US format when faced with ambiguous dates, while others default to your system locale. I'm not sure which does what though. Depending on how you're inserting the dates into your queries, they may also be translated from something like "January 12, 2012" to "01/12/2012" which can now be interpreted as either January 12, 2012 or December 1, 2012.
That aside, I've noticed your naming convention says "rpt" and "Report", both of which are naming conventions for a report, which is contrary to you using "[Forms]" (reports use "[Reports]"). If "rptReportMainScreen" is a report, you could simply have a textbox in the footer of the report with the Control Source:
=Sum([ReceiptCash])
You would input the dates in two textboxes on a form, then open up the report with the filter for the dates.
@David, I think what spike is trying to say is you can pass the form elements into the query without taking it out of the string. And in doing so, Access will automatically interpret the dates to the correct formatting, so the Format function and the # signs aren't necessary.
Have you tried with dates after the 12th of the month? For example, January 13 to February 24? If it's a formatting issue, then at least those should be interpreted properly since there's no 13th or 24th month in a year.
