Results 1 to 7 of 7
  1. #1
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91

    Accumulated total for current month to date stopped working

    Hi;
    I have the following code to give me the total amount spent and the total amount received for the current month and displayed in a textbox. It has started showing up as 0.00 all the time since January. I can't seem to figure out if I changed something or what I have done wrong



    Here is my code if anyone can shed some light on this
    Code:
    =Nz(DSum("[Amount]","Expenses"," YEAR([InvoiceDate]) = " & Year(Date()) & " AND Month([InvoiceDate]) = " & Month(Date())),0)
    
    =Nz(DSum("[AmountPaid]","PaymentsReceived"," YEAR([DateReceived]) = " & Year(Date()) & " AND Month([DateReceived]) = " & Month(Date())),0)
    Thanks for any help you can give me.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I see you are using the Intrinsic Date function and Year() function etc. These functions and the Date data type format dates as American Standard. mm/dd/yyyy

    Any other standard needs to be stored as text and preferably in their own fields, a field for year, a field for, day, etc. There may be a better solution because I use the American standard most of the time but this is most likely what is causing the unexpected results.

  3. #3
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    Thanks for your help. It would seem unusual that MS would focus date format solely on US Standard but you've pointed me in the right direction. At least now I have something to go on and try to solve this.

    Thanks again

  4. #4
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    Still hitting a dead end here. It's unusual that it was working until the new year and then stopped. I've been digging around and the code I have seems to be the preferred method to get this data.

    Maybe it is the AMerican date format that is screwing it up but would the locale of the machine not recognise the date formats ae UK/Irish format. Also the tables have the date set to UK/Irish format


    kind regards
    max

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    AFAIK the date is stored as data like this. Month/Date/Year

    Actually, it is stored as a number (Double) that counts the hours, minutes and seconds from a certain date in history.

    Formatting does not have an effect on the actual number that is stored in the table. You can format the field to display the number one way or another. The number does not change because you format it a certain way. So, when you use the built in functions to interact with a date field in a table, you need to understand that the function will assume - Month/Date/Year. The function looks at the number and then displays the date as US standard by default.

    If you carefully plan everything out and manage the proper constraints for data entry, you can use formatting to tell Access how to determine the number of days, hours, minutes, and seconds your date is from the magical date/time Access uses as a benchmark. If I predict I will be using a date format other than US standard, I will use separate fields to store the Year, Month and Date. This is how I choose to manage constraints for dates. It is too difficult to always remember to feed the date field a certain way. Using separate fields makes it obvious and even if I visit the DB years later, I will not make a formatting mistake that could result in the incorrect number being stored in the table.

    This link offers some insight to what I mean when I say the date is stored as a number.
    http://support.microsoft.com/kb/210276

  6. #6
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    Thanks IsMe. This has solved my problem. thanks for your time and insight into this.

    Kind Regars
    Max

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by maxmaggot View Post

    ...It would seem unusual that MS would focus date format solely on US Standard...
    How so, 'unusual?'They had to choose some standard, and Microsoft is an American company.

    Allen Browne, from 'Down Under,' has to deal with this on a regular basis and has an excellent article on the subject that may be of use to you

    http://allenbrowne.com/ser-36.html

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-07-2013, 12:01 PM
  2. Access has stopped working
    By mrkaye in forum Access
    Replies: 0
    Last Post: 05-22-2012, 04:09 PM
  3. Access has stopped working
    By phillb in forum Forms
    Replies: 9
    Last Post: 01-10-2012, 12:22 PM
  4. Current Month and Year-To-Date
    By DSnipeFunk in forum Access
    Replies: 4
    Last Post: 05-31-2011, 11:38 AM
  5. All Buttons Stopped Working and...
    By rhoridge in forum Programming
    Replies: 1
    Last Post: 12-02-2010, 11:25 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums