Currently I have a problem using Dlookup function with the following statement:
Code:
DLookup("[MaxThreshold]", "tblTaxBands", "[Band] = 'TaxBasic' AND [FromDate] >= #" & format(forms![frm_Income]![FromDate],"mm/dd/yyyy") & "# AND [ToDate] <= # " & format(forms![frm_Income]![ToDate], "mm/dd/yyyy")&"#")
the result from the Function is 'NULL', when it should be "£31785".
if I remove
Code:
AND [ToDate] <= # " & format(forms![frm_Income]![ToDate], "mm/dd/yyyy")&"#")
and only have
Code:
DLookup("[MaxThreshold]", "tblTaxBands", "[Band] = 'TaxBasic' AND [FromDate] >= #" & format(forms![frm_Income]![FromDate],"mm/dd/yyyy") & "# ")
it displays the correct result,furthermore (with the working code with only one date criteria, above), If the [FromDate] is not in the current year, the result is 'NULL', the test date was 25/06/2016 which resulted in 'NULL'. The Test date was in the table [tblTaxBands] to show next years tax thresholds.