Results 1 to 3 of 3
  1. #1
    chrisdd is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    2

    Calculating daily stock totals

    Hi,

    I've written this code and can't see for the life of me what is wrong with it - idea being that it is meant to check if monetary value for stock was calculated on the previous day and if it wasn't - work it out, it should run first thing in the morning by the first user to open the database.

    addstockvalue = DSum("Subtotal", "Stock", "[Despatchno] Is Null")






    latestdate = DMax("[StockDate]", "DailyStockValue")


    yesterday = Date - 1




    If latestdate <> yesterday Then


    Dim DailyStockValue As DAO.Recordset




    Set DailyStockValue = CurrentDb.OpenRecordset("SELECT * FROM [DailyStockValue]")


    DailyStockValue.AddNew
    DailyStockValue![StockDate] = Date - 1
    DailyStockValue![StockTotal] = addstockvalue


    DailyStockValue.Update
    DailyStockValue.Close
    Set DailyStockValue = Nothing

    Any help would be greatly appreciated.

    Thanks

  2. #2
    kd2017 is online now Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    498
    Are there any error messages showing up? What happens if your calls to DSum or DMax don't find any records and return null values? I don't immediately see any errors in your code but it's out of context. Maybe you could provide your database for review?

    I've refactored your code a little bit but it doesn't do much good without context.

    Code:
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim addstockvalue As Variant
        Dim latestdate As Variant
        
        addstockvalue = DSum("Subtotal", "Stock", "[Despatchno] Is Null")
        latestdate = DMax("[StockDate]", "DailyStockValue")
        
        If IsNull(addstockvalue) Then
            'WHAT DO YOU WANT TO DO IF DSUM DIDN'T FIND ANY RECORDS AND RETURNS A NULL?
            
        End If
        
        If IsNull(latestdate) Then
            'WHAT DO YOU WANT TO DO IF DMAX DIDN'T FIND ANY RECORDS AND RETURNS A NULL?
            
        End If
        
        yesterday = Date - 1
    
        If latestdate <> yesterday Then
            Set db = CurrentDb
            db.Execute "INSERT INTO DailyStockValue (StockDate, StockTotal) VALUES (#" & (Date - 1) & "#, " & addstockvalue & ");", dbFailOnError
        End If
    
    ExitHandler:
        Set db = Nothing
        Exit Sub
        
    ErrHandler:
        MsgBox Err.Description, , Err.Number
        Resume ExitHandler
    Side note: Make sure you declare your variables and put Option Explicit on the top of all your code files

    PS: Sometimes when you're using functions like DSum, DMax, etc. it can be helpful to wrap the functions in a Nz function. The Nz function gives you a convenient way to return a different value of the DMax or whatever function returns a null.
    Code:
    someVariable = Nz(DMax(expression, domain, criteria), 0) 'returns zero if DMax were to return null
    someVariable = Nz(DMax(expression, domain, criteria), 100) 'returns 100 if DMax were to return null
    someVariable = Nz(DMax(expression, domain, criteria)) 'The second parameter is optional and will return zero by default
    https://docs.microsoft.com/en-us/pre...v%3Doffice.11)

  3. #3
    chrisdd is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    2
    Thanks - used code above and works fine, just stops working when I compile as .accde and I've no idea why!!

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

Similar Threads

  1. Difficulty calculating daily running total
    By Mildleave in forum Access
    Replies: 1
    Last Post: 10-29-2019, 02:18 PM
  2. truck loads and daily totals?
    By nickdixon14 in forum Access
    Replies: 12
    Last Post: 10-13-2015, 05:16 PM
  3. Calculating Stock On Hand
    By namu23 in forum Access
    Replies: 11
    Last Post: 12-29-2014, 09:08 AM
  4. Calculating stock balance
    By Demerit in forum Queries
    Replies: 11
    Last Post: 12-17-2013, 01:57 AM
  5. Calculating Current Stock in Inventory/Warehouse
    By yohansetiawan in forum Access
    Replies: 5
    Last Post: 03-14-2012, 09:05 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
  •  
Tech Forums: Microsoft Office Forums