Dsum Not Working

[Drand]

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]

Code looks fine. Why do you say result is wrong?

 

[Drand]

Correct Result is $5,198.00
Function is returning$61,982.10!

 

[June7]

Do you have another computer you can test on?

 

[Gasman]

Correct Result is $5,198.00
Function is returning$61,982.10!

Have you run a query to confirm that?

 

[missinglinq]

Have you checked for missing references?

Linq ;0)>

 

[CJ_London]

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]

Computer regional settings messed up?

 

[Gasman]

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?

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

 

[Drand]

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]

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)>

 

[Deleted member 147267]

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]

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.

 

[Drand]

I have checked the references and none are noted as "missing"

 

[The_Doc_Man]

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.

...
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]

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.

?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]

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]

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]

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

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]

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.