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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-11-2019, 10:21 PM   #1
Drand
Newly Registered User
 
Join Date: Jun 2019
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Drand is on a distinguished road
Dsum Not Working

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

Drand is offline   Reply With Quote
Old 06-11-2019, 10:33 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,290
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Dsum Not Working

Code looks fine. Why do you say result is wrong?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
Old 06-11-2019, 10:38 PM   #3
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

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

Drand is offline   Reply With Quote
Old 06-11-2019, 11:56 PM   #4
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,290
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Dsum Not Working

Do you have another computer you can test on?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
Old 06-12-2019, 12:19 AM   #5
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,105
Thanks: 421
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Dsum Not Working

Quote:
Originally Posted by Drand View Post
Correct Result is $5,198.00
Function is returning$61,982.10!
Have you run a query to confirm that?
__________________
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 online now   Reply With Quote
Old 06-12-2019, 02:13 AM   #6
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,300
Thanks: 11
Thanked 716 Times in 666 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Dsum Not Working

Have you checked for missing references?

Linq ;0)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 06-12-2019, 02:16 AM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,263
Thanks: 40
Thanked 3,649 Times in 3,519 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Dsum Not Working

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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-12-2019, 12:22 PM   #8
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,290
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Dsum Not Working

Computer regional settings messed up?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
Old 06-12-2019, 12:47 PM   #9
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,105
Thanks: 421
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Dsum Not Working

Quote:
Originally Posted by CJ_London View Post
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
__________________
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 online now   Reply With Quote
Old 06-15-2019, 04:41 PM   #10
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 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.
Drand is offline   Reply With Quote
Old 06-15-2019, 05:31 PM   #11
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,300
Thanks: 11
Thanked 716 Times in 666 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Dsum Not Working

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)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 06-15-2019, 08:14 PM   #12
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 942
Thanks: 10
Thanked 195 Times in 185 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Dsum Not Working

Quote:
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!
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 06-15-2019, 08:34 PM   #13
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,382
Thanks: 87
Thanked 1,648 Times in 1,530 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

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.
__________________
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-16-2019 at 06:23 AM. Reason: Had a big addendum regarding EJECT and why to use it
The_Doc_Man is offline   Reply With Quote
Old 06-15-2019, 08:45 PM   #14
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

I have checked the references and none are noted as "missing"
Drand is offline   Reply With Quote
Old 06-16-2019, 06:35 AM   #15
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,382
Thanks: 87
Thanked 1,648 Times in 1,530 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:
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.

__________________
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
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 11:57 AM.


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