Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: Problem with Dlookup

    Dear Seniors,

    In my Form, I would like to get the Latest data (Last Transmittal Issued) based on Date. So I had used the Dmax function to get the latest date first. Then I used Dlookup function to get the data based on the Dmax date.

    However my Dlookup Field is displaying Blank value. I had checked my data and found that multiple transmittals are issued on the same date.

    For Ex 3 transmittals are issued on 01-May-14
    TR-3331
    TR-3332
    TR-3333

    I want to display TR-3333 in my form. However in my form I am getting TR-3331 or Blank value

    Could you please help me to sort this issue

    Thanks and Regards
    R. Vadivelan

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    When you need help with code it's a good idea to actually post the code! We have no way of knowing what you currently have.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    =dlookup("transmittal_number", "mytable", "max(transmittaldate)") may work
    otherwise use a recordset rather than a domain function
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2013
    Posts
    163
    Hi,

    Quote Originally Posted by Missinglinq View Post
    When you need help with code it's a good idea to actually post the code! We have no way of knowing what you currently have.

    Linq ;0)>
    I am not using any code. I am just using the Inbuilt Function of Access.

    As I said In the First Text Box I am getting the Max of Date (Last Date) using the following formula

    =DMax("[01_CTRTransmittal]![Revision Date]","[01_CTRTransmittal]")

    In the second text box I am using the following formula to find the Last Transmittals Number

    =DLookUp("[01_CTRTransmittal]![CTR TRNo]","[01_CTRTransmittal]","[01_CTRTransmittal]![Revision Date] = #" & [Forms]![Frm_ImportTransmittals]![MaxDate] & "#")

    However my problem is Dlookup showing the Blank values when there is more than 1 transmittal issued on same date. But I want to get the latest transmittal issued.

    Could you please help me to sort out this issue

    Thanks and Regards
    R. Vadivelan

  5. #5
    Join Date
    Oct 2013
    Posts
    163
    Hi Healdem,

    QUOTE=healdem;6617504]=dlookup("transmittal_number", "mytable", "max(transmittaldate)") may work
    otherwise use a recordset rather than a domain function[/QUOTE]

    Could you please assist me to create a recordset to get the latest data (Transmittal Number), since I am not aware of the recordset.

    As said in the previous post, my dlookup is showing some values but it shows wrong value when there is more than 1 transmittal issued on same date.

    Thanks and Regards
    R. Vadivelan

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Google ms access recordset
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2013
    Posts
    163
    Hi Healdem,

    I had googled for MS Access Recordset, but I could not find the details related to the begineer like me. It will be more useful if you give me the link for learning Recordset and I am interested to learn that.

    But through one of the other post (Find the first or last entered record in a table or form - Access) I created the 2 queries, 1 to find the maxmum date and the another query to find the Transmittal number based on the date. Somewhat it works for me, but I am getting problem when there are more than 1 transmittal issued on same date. Whether this is the correct method or is there any other simpler method to do the same. If it is then I need your help to sort out this issue with easier method.

    Thanks and Regards
    R. Vadivelan

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In this case, there is no need to use several queries (or domain functions) to retrieve what you want. Use a single query with the proper sort order:
    With this table (Tbl_SomeTable):
    Code:
    ID	Col_DateTime	Col_TrID
    --------------------------------
    1	1/05/2014	TR-3331
    2	1/05/2014	TR-3332
    3	1/05/2014	TR-3333
    4	29/04/2014	TR-3334
    This query:
    Code:
      SELECT Tbl_SomeTable.Col_DateTime, 
             Tbl_SomeTable.Col_TrID
        FROM Tbl_SomeTable
    ORDER BY Tbl_SomeTable.Col_DateTime DESC , 
             Tbl_SomeTable.Col_TrID DESC;
    Will always return this as the first row:
    Code:
    ID	Col_DateTime	Col_TrID
    --------------------------------
    3	1/05/2014	TR-3333
    Have a nice day!

  9. #9
    Join Date
    Oct 2013
    Posts
    163
    Thanks Sinndho,

    Thats works fine. Thank you for your tips.

    Could you please provide me the links where I can learn about recordsets?

    Thanks and Regards
    R. Vadivelan

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You should find everything you need to know about recordsets in Access help. Be careful though, that there are 2 kinds of recordset from 2 libraries : DAO.Recordset and ADODB.RecordSet. They have slighly differents objects with other properties, methods and behaviour.

    You can also have a look at (among many others):
    DAO Recordset (MFC) and Recordset Object (ADO) (the full reference)
    Recordsets for Beginners - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com
    Microsoft Access Tips: Accessing Data with VBA Code - Introducing the DAO Recordset
    Have a nice day!

  11. #11
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Thank you very much

    Thanks and Regards
    R. Vadivelan

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •