Dlookup....

[doran_doran]

OK, I am doing a humangous query and all kinds of dlookup.

My main query table is tbl_groups and I am doing this. I have field called PYE(date field) and I am using this ||Year: Format([PYE],"yyyy")|| so I can filter by year.

Now I am doing a DLookup to bring some values. I want to bring Billing Amount (Invoice_amount) into this query. I do have invoice date so I can only import the year I am filtering on the above.

Step 1: I am import year by following method.
InvYear: IIf(Format(DLookUp("([Invoice_bill_date])","tblInvoice","[ga_number] = '" & [GA_Number] & "' And [plannum]='" & [PlanNum] & "'"),"yyyy")=[Forms]![frmRptDeptDashboard]![txtyear],[Forms]![frmRptDeptDashboard]![txtyear])

Step 2: I am using following code to import my invoice amount only that has the same year for my year filter.

InvAmount: DLookUp("([Invoice_amount])","tblInvoice","[ga_number] = '" & [GA_Number] & "' And [plannum]='" & [PlanNum] & "' and [InvYear]='" & [Year] & "'")

OR

InvAmount: DLookUp("([Invoice_amount])","tblInvoice","[ga_number] = '" & [GA_Number] & "' And [plannum]='" & [PlanNum] & "' and [InvYear]='" & [Forms]![frmRptDeptDashboard]![txtyear] & "'")

=========================================================

Step 1: works, I mean I can get the year on the whole date.

Step 2: bring no value. the cell is emply when i do a datasheet view.

Any input will be appreciated.

Regards
Dianna Goldsberg

 

[doran_doran]

I figured it out....

InvAmount: Int(DLookUp("([Invoice_amount])","tblInvoice","[ga_number] = '" & [GA_Number] & "' And [plannum]='" & [PlanNum] & "' And [pye]=#" & [pye] & "#"))

Above works better and faster.

========================================================
InvAmount: IIf(Format(DLookUp("([Invoice_bill_date])","tblInvoice","[ga_number] = '" & [GA_Number] & "' And [plannum]='" & [PlanNum] & "'"),"yyyy")=[Forms]![frmRptDeptDashboard]![txtyear],DLookUp("([Invoice_amount])","tblInvoice","[ga_number] = '" & [GA_Number] & "' And [plannum]='" & [PlanNum] & "'"))


Thanks everyone.

Dianna Goldsberg

 

[jfgambit]

Pat cringes everytime "DLookUp" is added to a post....