Results 1 to 6 of 6

DMAX returns Null value

  1. #1
    jtan is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2013
    Location
    Singapore
    Posts
    17

    DMAX returns Null value

    I'm taking over a database from a colleague who had retired.
    In this database, there is a table (Receiving Records) and a Append Query. User has to run the Append Query few times daily and this query will prompt user to enter 2 criterions (date and time of the last record from table (Receiving Records)). The query will then extract records from MRP system and append them (with Receiving date and Receiving time > than the 2 criterions) into table (Receiving Records).
    I'm now starting to automate this manual entry process with programming as below.
    Click image for larger version. 

Name:	program.jpg 
Views:	18 
Size:	20.5 KB 
ID:	38353

    The LastRecordedDate is correct. But the LastRecorded Time returns Null value, instead of 190700. Can someone help to check what had caused this error
    Below the last few records from the table (Receiving Records) and field data type:
    Click image for larger version. 

Name:	last few records.jpg 
Views:	21 
Size:	25.4 KB 
ID:	38354

    Click image for larger version. 

Name:	data type.jpg 
Views:	18 
Size:	11.1 KB 
ID:	38355


    Appreciate your help.
    Best Regards.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,996
    What is the complete function? It's cut off in the picture.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    jtan is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2013
    Location
    Singapore
    Posts
    17
    Private Sub Add_New_Receiving_Record_Click()
    Dim LastRecordedDate As Date
    Dim LastRecordedTime As Variant

    LastRecordedDate = DMax("[Receiving Date]", "[Receiving Records]")
    LastRecordedTime = DMax("[Receiving Time]", "[Receiving Records]", "[Receiving Date] =# " & LastRecordedDate & "#")

  4. #4
    isladogs's Avatar
    isladogs is offline Very intense programming
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,434
    PMFBI.
    It looks like your dates are in dd/mm/yyyy format.
    For the final line, you need to format as mm/dd/yyyy

    Code:
    lastrecordedtime=dmax("[receiving time]","[receiving records]","[receiving date]=#" & Format(lastrecordeddate,"mm/dd/yyyy") & "#")
    Recommend you remove all the spaces from field names to avoid the need for endless []
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  5. #5
    jtan is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Nov 2013
    Location
    Singapore
    Posts
    17
    Thanks, isladogs
    It's works. But I don't understand why I need to format it to mm/dd/yyyy. The date data in the table is dd/mm/yyyy. Can you help to explain ?

  6. #6
    isladogs's Avatar
    isladogs is offline Very intense programming
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,434
    Whe you use code, Access expects dates to be in mm/dd/yyyy format.
    So if you have a date like 8/5/2019, it is 'intelligently' interpreted as 5 Aug rather than 8 May. You have no records for 5 Aug so the output was null.
    If you had records for both dates it would have used those for 5Aug.

    Just to confuse matters, Access will correctly interpret unambiguous dates like 14/05/2019 as that has to be 14 May.
    So to ensure correct results, always format as mm/dd/yyyy. Its a PITA but for those of us outside the US, you will have to do it all the time
    Alternatively, you can use yyyy-mm-dd as used in SQL Server

    Note that the issue doesn't arise in queries where the dates are always interpreted according to regional settings like those in the UK (dd/mm/yyyy)
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

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

Similar Threads

  1. Replies: 8
    Last Post: 02-02-2018, 07:20 PM
  2. DMAX Error when Null
    By grant99 in forum Programming
    Replies: 3
    Last Post: 04-27-2016, 01:10 PM
  3. DMax returning Null
    By Markb384 in forum Access
    Replies: 1
    Last Post: 05-01-2014, 09:11 AM
  4. Replies: 6
    Last Post: 07-24-2012, 03:02 PM
  5. Query returns null..based on two tables
    By shsh_shah in forum Queries
    Replies: 1
    Last Post: 03-08-2009, 01:45 PM

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