Dlookup() With Multiple Criteria (Specifically: a String, & Two dates)

[QA_Compliance_Advisor]

I think its about a bad database design. Obviously I am trying to lookup the Maximum threshold for a particular tax year, in this case 2015/2016 which range from 06/04/2015 (fromdate) to 05/04/2016 (Todate) (UK)

so what i have been asking the lookup to do is this

06/04/2015 >= 25/04/2015 &


05/04/2016 <= 26/04/2015

this what the problem is, I am basically asking for a large number to be <= to a smaller number, & asking a smaller number to be >= to a larger number.

[knarfreppep]

As I said, I've done it in the way I suggested.

Something else you might consider or not as you see fit ... tax rates have a date of effect ... you might think about a table with just dtmDateOfEffect, bytBand, and sngRate from which you could get the applicable rate with this -

DLookup("sngRate", "tblTaxRates", "bytBand = " & Forms!frmMyForm!cboBand & " AND dtmDateOfEffect= #" & Format(DMax("dtmDateOfEffect","tblTaxRates","dtmDa teOfEffect < #" & Format(Forms!frmMyForm!txtPayDate,"mm/dd/yy") & "#"),"mm/dd/yy") & "#")

[knarfreppep]

Why not try printing (?) the date formatting function in the Immediate window to see what it is returning?

[QA_Compliance_Advisor]

Knarfreppep, what i discovered i needed to do was to put a TaxYear on the form, which linked to a table/query to lookup tax year and its commencement & ending date & then doing the following;

So I set up a Table & Query with Tax Year and the dates, which I use as a combo box in my form to lookup tax year & dates which encompass that tax year period (i.e. Tax Year 2015/2016 is from 06/4/2015 to 05/4/2016) Now I am trying to figure a way around to use the information from the Form (or more accurately, from the form which uses a lookup from a query). I was going to do something silly like

  DLookup("FromDate", "tblTaxYears", "TaxYear = "  &  Forms!frm_Income!cmbTaxYear.Column(2))

however, Can't use Dlookup for query parameters.

then decided a simplier option use the data from the form combo box which would have been the ID from the linked table from TaxYear.

DLookup("MinThreshold", "tblTaxBands", "Band = 'TaxBasic' AND [FromDate< " & DLookup("Fromdate", "tblTaxYears", "ID = " & Forms!frm_Income!cmbTaxYear)

this worked.

[QA_Compliance_Advisor]

side note, both my tables "taxBands" & "TaxYear" Have identical [Fromdate] & [ToDate] yet when i do the following

 DLookup("TaxRate", "tblTaxBands", "Band = 'TaxBasic' AND [FromDate] = " & format(DLookup("Fromdate", "tblTaxYears", "ID = " & Forms!frm_Income!cmbTaxYear),"mm/dd/yyyy") & " And [ToDate] = " & format(DLookup("ToDate", "tblTaxYears", "ID = " & Forms!frm_Income!cmbTaxYear),"mm/dd/yyyy"))

the answer is null however when i change = to > or >= the correct Answer is displayed

DLookup("TaxRate", "tblTaxBands", "Band = 'TaxBasic' AND [FromDate] >= " & format(DLookup("Fromdate", "tblTaxYears", "ID = " & Forms!frm_Income!cmbTaxYear),"mm/dd/yyyy") & " And [ToDate] >= " & format(DLookup("ToDate", "tblTaxYears", "ID = " & Forms!frm_Income!cmbTaxYear),"mm/dd/yyyy"))

any idea for this odd occurrence?