Dlookup blues

[SHIVA2016]

Hiii to all
I have problem with dlookup function in vba which is not retrieving records can any one help

DLookup("[credit]", "datatable", "[mth_year]=#" & MTH & "#" And "[vch_no] = '" & VCHNO & "' And "[alloc] = """8670"""")

in this mth_year is date field
vch_no and alloc is string field

mth_year and vch_no is a criteria and alloc is hard-coded

thank you
shiva

[ssanfu]

Too many quotes.....

If [alloc] is a number, try:

DLookup("[credit]", "datatable", "[mth_year]=#" & MTH & "# And [vch_no] = '" & VCHNO & "' And [alloc] = 8670")

If [alloc] is text, try:

DLookup("[credit]", "datatable", "[mth_year]=#" & MTH & "# And [vch_no] = '" & VCHNO & "' And [alloc] = '8670'")

[SHIVA2016]

alloc is a text

you guys are great its working thank you

[SHIVA2016]

one more problem, I wan to replace date format in function like

DLookup("[amount]", "data", "[mth_year] =""" & SQLDate(MTH) & """ And [vch_no] = '" & VCHNO & "' And [alloc] = '003456'")

sqldate function is

Function SQLDate(ByRef pvarVdt As Variant) As String
On Error GoTo Err_SQLDate
If IsDate(pvarVdt) Then
SQLDate = "#" & Format$(pvarVdt, "mm/dd/yyyy") & "#"
End If
Exit_SQLDate:
On Error Resume Next
Exit Function
Err_SQLDate:
MsgBox Err.Description, vbCritical, "Module" & ": " & "SQLDate"
Resume Exit_SQLDate
End Function


I getting data type mismatch error
any help on this

thank you
shiva

[ssanfu]

Again, too many quotes:

Try:

DLookup("[amount]", "data", "[mth_year] = " & SQLDate(MTH) & " And [vch_no] = '" & VCHNO & "' And [alloc] = '003456'")

I did not test this, but try
Function:

Function SQLDate(ByRef pvarVdt As Variant) As String
    On Error GoTo Err_SQLDate

    'default return date so a NULL is not returned
    SQLDate = "#1/1/1900#"

    If IsDate(pvarVdt) Then
        SQLDate = "#" & Format(pvarVdt, "mm/dd/yyyy") & "#"
    End If

Exit_SQLDate:
    '    On Error Resume Next
    Exit Function
Err_SQLDate:
    MsgBox Err.Description, vbCritical, "Module" & ": " & "SQLDate"
    Resume Exit_SQLDate

End Function

[SHIVA2016]

Thanks once again its working