Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    1

    Unanswered: Problem with Dlookup function

    Hi,

    This is my first time posting and I'm fairly new to Access and VBA. I'm working on a simple after update function and I can't get the dlookup to work with a date field that its taking from a form. Here's my code so far:

    Code:
    Private Sub txtCDate_AfterUpdate()
     
        If IsNull(DLookup([cheque_date], "Cheque_Details", "[Cheque_Date] =" & Forms![PAS].[txtCDate])) Then
    
            With DoCmd
                .SetWarnings False
                .OpenQuery (qryEmployeeInfo)
                .SetWarnings True
            End With
              
        Else
                
            With DoCmd
                .SetWarnings False
                .OpenQuery (qryChequeDetails)
                .SetWarnings True
            End With
        
        End If
    
    End Sub

    It might have something to do with the format that I have my date in as it's in dd/mm/yyyy format and not standard US date format, but that's the format it needs to be in for storage in my database.

    Any advice anyone may have would be great

    Thanks,
    Jahyd

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Provided that "cheque_date" is the name of a column (field) in the table (or query) "Cheque_Details", you can try:
    Code:
    If IsNull(DLookup("cheque_date", "Cheque_Details", "[Cheque_Date] = #" & Format(Me.txtCDate.Value, "mm/dd/yyyy") & "#")) Then
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access/JET is US centric so it should be mm/dd/yyyy or at a pinch ISO format yyyy/mm/dd
    also there is an expectation that date literals should be encapsualted with #

    Code:
    If IsNull(DLookup([cheque_date], "Cheque_Details", "[Cheque_Date] = " & format(Forms![PAS].[txtCDate],"#yyyy/mm/dd#")) Then
    or
    Code:
    If IsNull(DLookup([cheque_date], "Cheque_Details", "[Cheque_Date] = " & format(Forms![PAS].[txtCDate],"#mm/dd/yyyy#")) Then
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    And since you're stuck in that universe where Dates are not in the US Format, you'd better bookmark Allen Browne's excellent article on the subject:

    Microsoft Access tips: International Dates in Access

    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

Posting Permissions

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