Dsum Not Working (1 Viewer)

Drand

Registered User.
Local time
Today, 15:35
Joined
Jun 8, 2019
Messages
179
Hi Folks

I had an unexpected shutdown of my laptop which has caused some major issues with my application, particularly my functions that use Dsum.

Following is an example of my code that is producing a result but not the correct one!

Public Function ThisMonthWeekdays()
Dim StartDate As Date
Dim EndDate As Date
StartDate = DateSerial(Year(Date), Month(Date), 1)
EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)

ThisMonthWeekdays = DSum("[TotalSales]", "QryYTDSales", "[Event] = No And [date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "#")

End Function

Is there something wrong here as this fdunction worked perfectly before the crash and I just cannot see the problem.

Appreciate your assistance.

Many thanks
David
 

June7

AWF VIP
Local time
Yesterday, 21:35
Joined
Mar 9, 2014
Messages
5,463
Code looks fine. Why do you say result is wrong?
 

Drand

Registered User.
Local time
Today, 15:35
Joined
Jun 8, 2019
Messages
179
Correct Result is $5,198.00
Function is returning$61,982.10!
 

June7

AWF VIP
Local time
Yesterday, 21:35
Joined
Mar 9, 2014
Messages
5,463
Do you have another computer you can test on?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:35
Joined
Feb 19, 2013
Messages
16,600
If date is December then month(date) + 1=13 which will be an illegal value. Also you use the # char to tell sql that the string value between them is to be treated as a date. If you do not use the us date format of mm/dd/yyyy then you will also get problems 5/7/19 will be treated as 7th May, not 5th July
 

June7

AWF VIP
Local time
Yesterday, 21:35
Joined
Mar 9, 2014
Messages
5,463
Computer regional settings messed up?
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:35
Joined
Sep 21, 2011
Messages
14,216
If date is December then month(date) + 1=13 which will be an illegal value. Also you use the # char to tell sql that the string value between them is to be treated as a date. If you do not use the us date format of mm/dd/yyyy then you will also get problems 5/7/19 will be treated as 7th May, not 5th July

To be fair, that works perfectly fine?, so it must be the format?

Code:
? DateSerial(Year(Date), 12 + 1, 0)
31/12/2019
 

Drand

Registered User.
Local time
Today, 15:35
Joined
Jun 8, 2019
Messages
179
Thanks for the responses folks and apologies for not getting back to you as I have been away.

This just gets more curious (and frustrating).

I copied the application to a USB stick and opened it on another computer and it works perfectly. Just not on my new laptop.

I really don't understand this.

Any further suggestions would be greatly appreciated.
 

missinglinq

AWF VIP
Local time
Today, 01:35
Joined
Jun 20, 2003
Messages
6,423
This just reinforces the need to check for missing references! When a Function misbehaves...especially a Date/Time related Function...a common cause is a missing references. The appropriate Library is probably loaded in the working box...but has never been loaded in the new laptop.

Also...just as a warning: when transferring an Access file, using any type of removable medium (such as a USB stick) always leave it in place, both in copying the file to stick..and copying from stick to another machine...for an extra 10 seconds or so after the file appears to have been transferred. For some reason, disconnecting too quickly tends to corrupt the file.

Linq ;0)>
 

Micron

AWF VIP
Local time
Today, 01:35
Joined
Oct 20, 2018
Messages
3,478
always leave it in place, both in copying the file to stick..and copying from stick to another machine...for an extra 10 seconds
99% of the time I used the eject utility, not knowing if it was really necessary. I think I will make it 100% of the time now!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:35
Joined
Feb 28, 2001
Messages
27,122
The "EJECT" functionality is good because it forces an internal "Dismount" function. On a home system, you never (well... hardly ever) mount or dismount anything explicitly. On a server, there is such a thing as mounting or dismounting a disk. A dismount causes all pending I/O operations to be re-prioritized so that they will finish. Then a forceful close will be imposed on all handles to that device. When the file handle to the drive's root volume (held by the O/S file system) gets closed, the device is dismounted. This is true for USB devices when they are being treated as disk-like storage, because in that case they have been formatted to have a root folder.

The catch is that you need to wait until you get the pop-up that says "It is safe to remove xyz volume from your system." At that point, the root folder should be closed and the drive is fully disconnected from all software. At that time you can pull the thumb drive safely.

ADDENDUM: I apologize for this digression - but it isn't really a digression. Linq's comments about waiting are correct but not complete. This is because of the way Windows manages disks.

Disks have this thing called an "allocation unit" (which on some machines is a disk-cluster). An allocation unit is contiguous and usually aligned with disk geometry so that a complete unit is on the same cylinder and track, and the sector numbers are contiguous. When that happens, Windows can allocate a disk buffer the same size as the allocation unit and can read the whole unit into memory at once. It is no coincidence at all that the Access disk buffer we talk about so often IS the size of a typical allocation unit.

This buffering strategy is called "read-ahead" and saves you physical disk reads. When you ask for a given block of disk, the odds are that you will want the next couple of blocks as well. It works because the disk SEEK operation to find the cylinder costs tens of milliseconds (in a GHz computer, tens of millions of instruction-times) but the disk read is faster, because modern disks are spinning at several thousand RPM, meaning the head is passing over the correct sectors within a couple of milliseconds. That data transfer costs a lot, so the buffering uses memory to save you from needing multiple "rotational latency" times.

The corresponding disk update strategy is called "write-behind" and involves having a "dirty" buffer that is accumulating changes to individual disk sectors, waiting for the program to reach the end of the buffer so it can write out the whole buffer in a single operation. But this is where my discussion returns to the fold. When you are using "write-behind" that means that Windows has intentionally NOT written everything that your program did. Windows is waiting for the buffer to become full.

It applies to Access but even applies to a simple COPY operation. It CERTAINLY applies to Access any time you write something to an external file such as using "TransferToText" or one of the reporting options that writes a report to a PDF or Word or Excel file.

Program exits will trigger what is called I/O rundown, which will trigger forced write-backs of pending buffers. But they are INVISIBLE to you because the program rundown occurs during program EXIT. And programs in rundown are not always visible to Task Manager so you can't check on whether your program is complete. The use of the EJECT function forces the rundowns to finish and then gives you the positive feedback that it is finished.

And of course, for RAM disks, solid-state disks, or zero-rpm-disks (all have been used to describe the same thing) there is NO rotational latency and no seek-arm latency, so those delays of tens of milliseconds become zero. Which is why they are such a fun addition to your system.
 
Last edited:

Drand

Registered User.
Local time
Today, 15:35
Joined
Jun 8, 2019
Messages
179
I have checked the references and none are noted as "missing"
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:35
Joined
Feb 28, 2001
Messages
27,122
Code:
Public Function ThisMonthWeekdays()
Dim StartDate As Date
Dim EndDate As Date
StartDate = DateSerial(Year(Date), Month(Date), 1)
EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)

ThisMonthWeekdays = DSum("[TotalSales]", "QryYTDSales", "[Event] = No And [date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "#")

End Function

OK, I'm not going to say whether this code is absolutely right or not, but try this as a simple test of date generation using an alternative method.

Code:
...
StartDate = DateSerial(Year(Date), Month(Date), 1)
EndDate = DateAdd("m", 1, StartDate ) - 1
...

The DateAdd will add a month, taking into account that you might have done that in December, but will guarantee the same day of the month. Since you specified the first day of the month, that will be in StartDate. Then EndDate becomes the first of the next month minus one day, which is of course the LAST day of the month before it.

This might not give any better answer, but it will either prove or disprove CJ's contention that there is an issue in doing date math within the arguments of DateSerial. So call this a diagnostic test that MIGHT give you a workaround.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:35
Joined
May 21, 2018
Messages
8,516
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:35
Joined
Feb 28, 2001
Messages
27,122
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.
 

Drand

Registered User.
Local time
Today, 15:35
Joined
Jun 8, 2019
Messages
179
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:35
Joined
Feb 28, 2001
Messages
27,122
Code:
ThisMonthWeekdays = DSum("[TotalSales]", "QryYTDSales", "[Event] = No And [date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "#")

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] = [COLOR="Red"]" & Str(No) & "[/COLOR] 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.
 

Drand

Registered User.
Local time
Today, 15:35
Joined
Jun 8, 2019
Messages
179
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.
 

Users who are viewing this thread

Top Bottom