Problem with Dlookup function

jahydc91 · #1 (**permalink**) 02-02-12, 07:24

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

Sinndho · #2 (**permalink**) 02-02-12, 07:56

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

healdem · #3 (**permalink**) 02-02-12, 07: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

Missinglinq · #4 (**permalink**) 02-02-12, 16:56

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)>

Internet Services

Web Development

Digital Marketing

Consumer Tech