Dlookup syntax issue 1

[MICKI0220]

I am not sure if I am writing the correct syntax for this code
=(DLookUp("Balance","[tbl_ats_SlsRep]","Sls_RepId= [tbl_ats_SlsRep]![Sls_RepId]"))

What it is trying to establish is get the [Balance] from the table [tbl_ats_SlsRep] Where the reports [Sls_RepId] is the same as the table's [tbl_ats_SlsRep] field [Sls_RepId]. The value in [tbl_ats_SlsRep]![Sls_RepId] is a negative value, such as -43,050
I do get a value on the report but it is $0.00.

I am not sure if it is not bringing in the correct value because it is negative or the syntax is wrong.

 

[MajP]

The criteria is wrong.
if this dlookup is in code then it would be something like

DLookUp("Balance","[tbl_ats_SlsRep]","Sls_RepId= " & me.[Sls_RepId])
assuming the sales rep id is numeric.

if this is a calculated control on the report then

DLookUp("Balance","[tbl_ats_SlsRep]","Sls_RepId= " & [Sls_RepId])

That criteria would resolve to something like
Sls_RepId = 123
where 123 is the current id on the report

Yours simplies resoves to
Sls_RepId= [tbl_ats_SlsRep]![Sls_RepId]
Which would always return the ID of the first record in the table.

 

[MICKI0220]

Thank you I will try that....It is a control on the report, not code in the background.

 

[MICKI0220]

Majp

It gives me an error now in that field.

 

[MajP]

Please show what you actually put in the calculated control.
Show the recordsource of the report.
What is the name of the field on the report with the sales rep ID.

If the recordsource is a query show the actual sql of the query not the query name.

 

[MICKI0220]

The record source of the report is:
SELECT DISTINCT tbl_ats_Comm_Draw_Hist.Sls_RepID, tbl_ats_Comm_Draw_Hist.Tran_Type, tbl_ats_Comm_Draw_Hist.Cust_ID, tbl_ats_Comm_Draw_Hist.Contract_ID, tbl_ats_Comm_Draw_Hist.Calc_Comm_Draw_Amt, tbl_ats_Comm_Draw_Hist.Comm_Draw_Date, dbo_JobMaster.[Job Name - Master Reference], tbl_ats_Comm_Draw_Hist.Calc_Commision, tbl_ats_Comm_Draw_Hist.Inv_Date, tbl_ats_Comm_Draw_Hist.Inv_Num, tbl_ats_Comm_Draw_Hist.Pmt_Amt, tbl_ats_SlsRep.Rep_name, tbl_ats_Comm_Draw_Hist.Pmt_Date, tbl_ats_Comm_Draw_Hist.Search_Date, tbl_ats_Comm_Draw_Hist.EligibleForComm, tbl_ats_Contract.Search_Key
FROM tbl_ats_Contract AS tbl_ats_Contract_1, ((tbl_ats_Comm_Draw_Hist INNER JOIN dbo_JobMaster ON tbl_ats_Comm_Draw_Hist.Cust_ID = dbo_JobMaster.JobNo) INNER JOIN tbl_ats_SlsRep ON tbl_ats_Comm_Draw_Hist.Sls_RepID = tbl_ats_SlsRep.Sls_RepId) INNER JOIN tbl_ats_Contract ON tbl_ats_Comm_Draw_Hist.Contract_ID = tbl_ats_Contract.Bill_Type_ID
WHERE (((tbl_ats_Comm_Draw_Hist.Sls_RepID)=[Forms]![frmRptSalesRepPaidHist]![cboSls_RepFrom]) AND ((tbl_ats_Comm_Draw_Hist.Tran_Type)="C") AND ((tbl_ats_Comm_Draw_Hist.Calc_Commision)<>0) AND ((tbl_ats_Comm_Draw_Hist.Search_Date) Between [Forms]![frmRptSalesRepPaidHist]![DateFrom] And [Forms]![frmRptSalesRepPaidHist]![DateTo]) AND ((tbl_ats_Comm_Draw_Hist.EligibleForComm)=-1));


I copied your suggestion for the control box.
=DLookUp("Balance","[tbl_ats_SlsRep]","Sls_RepId= " & [Sls_RepId])

The name of the field on the report is [Sls_RepId]

 

[MajP]

I think the issue was no space
Sls_RepId=
It would treat that as the fieldname. You can legally have an = in a field name.

=DLookUp("Balance","[tbl_ats_SlsRep]","Sls_RepId = " & [Sls_RepId])

in the immediate window you can test this. Pick a valid ID
and type
x = 123
hit return
then type
?DLookUp("Balance","[tbl_ats_SlsRep]","Sls_RepId = " & X)
hit return

 

[MICKI0220]

Still says ERROR in the field

 

[MajP]

And in the immediate window?

 

[MICKI0220]

I really don't understand this immediate window instruction

 

[MajP]

http://vbatips.com/2007/12/05/using-the-immediate-window/

 

[dhookom]

Since tbl_ats_SlsRep is already in the record source of the report, why not just add the Balance field? There is no need to use DLookup().

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

oops I missed that.

 

[MICKI0220]

When I press ctrl G, it comes up with the immediate windows, however an insert SQL statement is already in it. When I put that code in it and hit enter, nothing happens

 

[MajP]

Do what Duane suggests.

 

[MICKI0220]

I didn't see the post by Duane... I missed that also. This is a report I was modifying. Thanks....That worked.

 

[MICKI0220]

I have a problem with that work around. That worked okay for someone who had records, but if there are no records for the query, I still need it to look back and get the persons balance out of the tbl_ats_SlsRep table. Anymore suggestions.

 

[dhookom]

Ok, so how does a Sls_RepId even get into the report if there is no record in the report's record source. All of the joins are "INNER JOIN".

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

It gets the salesman's name and id from the previous form that gets all the query criteria

=DLookUp("Sls_RepId","slsRepForReport")

 

[dhookom]

Are you suggesting you want to include the Balance field on the report for Sls_RepId that are not included in the report?

Duane
Hook'D on Access
MS Access MVP