Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-16-2019, 07:14 AM   #16
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,856
Thanks: 36
Thanked 565 Times in 533 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Dsum Not Working

Quote:
If date is December then month(date) + 1=13 which will be an illegal value.
That is not true. Date serial is very robust and will guess at what you want.
Code:
?dateserial(2019,14,1)
2/1/2020
However, whenever working with literal dates in a sql string to ensure it always works it has to be in the form MM/DD/YYYY regardless of your regional settings. I would add

dim strStart as string
dim strEnd as string
...
strStart = format(startDate, "MM/DD/YYYY")
strStart = "#" & strStart & "#"
strEnd = format(endDate, "MM/DD/YYYY")
strEnd = "#" & strEnd & "#"

ThisMonthWeekdays = DSum("[TotalSales]", "QryYTDSales", "[Event] = 'No' And [date1] BETWEEN " & StrStart & " AND " & strEnd)

Also I am suprised that ever worked if the literal string "No" was not in quotes.

MajP is offline   Reply With Quote
Old 06-16-2019, 09:05 AM   #17
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,577
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Dsum Not Working

MajP, I also thought DateSerial was "smart" about such things - but I proposed a simple test to see if the problem is in the start/end dates or in the data. If the problem doesn't change, then we can eliminate those issues. However, your point about formatting is valid as well. Adding proper formatting should help.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-17-2019, 01:21 AM   #18
Drand
Newly Registered User
 
Join Date: Jun 2019
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Drand is on a distinguished road
Re: Dsum Not Working

Thanks The_Doc_man

I tried what you suggested and unfortunately, I am still getting the wrong result.

As this function is "month to date" I also tried changing enddate to simply "date" as that would give me the correct outcome.

In all instances I have debug.printed the variables and they are returning the correct dates!

In desperation, I imported the appropriate components into a new application and again, it returned same, but incorrect amount!

I am only an amateur developer but this is is surely a bit weird.

Cheers

Drand is offline   Reply With Quote
Old 06-17-2019, 05:09 AM   #19
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,577
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Dsum Not Working

Code:
ThisMonthWeekdays = DSum("[TotalSales]", "QryYTDSales", "[Event] = No And [date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "#")
Quote:
Correct Result is $5,198.00
Function is returning$61,982.10!
OK, the experiment shows it wasn't the way you formed the dates. So the next step has to be filtration, which is managed by the criteria clause. MajP points out that using "No" in this criteria statement confused him and thinks you mean the word "NO" whereas I wonder if the event is being selected by a number for which the variable name is No.

If I am right, that statement should be written as

Code:
ThisMonthWeekdays = DSum("[TotalSales]", "QryYTDSales", "[Event] = " & Str(No) & " And [date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "#")
Here, the problem is that the reference to a variable named "No" in your criteria statement cannot be seen by the SQL processor that would eventually execute the internal SQL statement built by the DSum function. The way to make it visible is to substitute the value of No into the criteria statement. However, I have no idea why DSum returned anything at all in that case, just as MajP commented.

There is one more question to consider: You state the expected and actual returned values, which implies you have another way of computing what you would expect. I would presume you looked at the list and added it up by hand.

However, if so, manually computed sums have a disadvantage here. When you do the accounting by hand, you AUTOMATICALLY do the right thing and avoid double-dipping. The question you ask of DSum must be set up to programmatically avoid double-dipping because DSum is very literal in what it does.

Is there a chance that QryYTDSales "double-dips" at any point? If it is a JOIN query then a poorly constructed JOIN would cause multiple records to appear with the same date, so that you might be counting individual [TotalSales] more than once in taking that sum. If there is a multi-valued field involved in QryYTDSales (perhaps included from the table where such a field resides) then that could easily happen.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-22-2019, 11:39 PM   #20
Drand
Newly Registered User
 
Join Date: Jun 2019
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Drand is on a distinguished road
Re: Dsum Not Working

Hi The_Doc_Man

Again, apologies for the slow response, I travel a lot and cannot get to this for quite a few days at a time.

In relation to your comments...

The "no" result comes from a Yes/No checkbox on a form and is formatted as a yes/no in the table. It places a Yes or No value in the table and query.

I have tried all sorts of variable types and tried your code but same result.

In terms of obtaining the correct amount, I have both exported the result to Excel and queried the query and both times obtain the correct total. It is only my function that is wrong.

If I change the start and enddate variables to actual dates instead of variables I also achieve the correct result.

There is no join query that could create the double dipping effect you mentioned.
Drand is offline   Reply With Quote
Old 06-23-2019, 05:39 AM   #21
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,577
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Dsum Not Working

Quote:
The "no" result comes from a Yes/No checkbox on a form and is formatted as a yes/no in the table. It places a Yes or No value in the table and query.
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.)

Quote:
If I change the start and enddate variables to actual dates instead of variables I also achieve the correct result.
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.

Last edited by The_Doc_Man; 06-23-2019 at 02:50 PM. Reason: fixed typo
The_Doc_Man is offline   Reply With Quote
Old 06-23-2019, 06:19 AM   #22
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,396
Thanks: 436
Thanked 805 Times in 780 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Dsum Not Working

Put all the criteria into a single string, then you can debug.print it and see *exactly* what you are getting.

Your formatting looked OK in previous posts, but you never know.?
Show what is being used, not what looks like being used.


__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DSum not working properly theperson Queries 8 06-25-2013 11:52 AM
DSum working but not working .... really strange Arvin Modules & VBA 3 04-02-2011 08:08 AM
VBA DSum not working as I want.. Badvoc Forms 10 05-27-2010 12:18 AM
Like in a DSUM statement not working adonahue15 Queries 3 01-28-2009 07:15 AM
DSum not working :S jamesWP Modules & VBA 16 08-19-2005 03:12 AM




All times are GMT -8. The time now is 10:59 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World