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.

 

[MICKI0220]

The report always comes up with the person's name and ID that we are running the report for. I would like it to also put their balance on the report even if they have no commission....The commision are the records that appear in the detail section. Every sales person used to draw a commission check every month regardless if they accrued any commission. Now it is time for them to payback what they drew. The balance is what they owe the company....When they have commission from that month, we take the balance and subtract the commission they made for that month and they get a new balance....This is all in a table [tbl_ats_slsrep]and needs to be shown on the report....If they didn't get any commission that month, they still want to supply them the report showing what their balance was, minus the no commision (0) and the new balance, which would be [tbl_ats_slsrep]![balance] where sls_repId = dlookup("sls_repid","slsRepforReport")

I hope I explained it correctly.

 

[dhookom]

I think I understand. You are running a report for a person and the report returns no records. You still want to display a balance in the report.

Assuming sls_repid is numeric, you could try:

=dlookup("Balance","[tbl_ats_SlsRep]","sls_repid=" & [Forms]![frmRptSalesRepPaidHist]![cboSls_RepFrom])

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

I tried your solution and still an error....Sls_RepId is a text field.

 

[dhookom]

Try:

=dlookup("Balance","[tbl_ats_SlsRep]","sls_repid=""" & [Forms]![frmRptSalesRepPaidHist]![cboSls_RepFrom] & """")

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

Beautiful.....That did it. Thanks for all your time with this.

 

[MICKI0220]

Accounting just gave me a new twist with this....
For this dlookup

=IIf([balance]='0',0,(Sum([calc_commision])/2))

I need to ad an OR statement....Basically I need it to say

=IIf([balance]='0' Or [Sls_RepId] = '102' Or [Sls_RepId] = '104',0,(Sum([calc_commision])/2))

Right now with that code I get an Error

Thanks for all your help

 

[dhookom]

Where does [Balance] come from in your recent post? Where does Calc_commision come from?

Are 102 and 104 numeric or text?

IMO, you should never hard-code values into expressions like this (102 and 104).

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

text

 

[dhookom]

???
One answer from three questions and a recommendation?

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

sorry I looked at it quickly

The 102 is a text field

I know I shouldn't hard code it, but this condition doesn't apply to these two sales men at this time....In the future I need to remove it.

Balance comes from =DLookUp("Balance","[tbl_ats_SlsRep]","sls_repid=""" & Forms!frmRptSalesRepPaidHist!cboSls_RepFrom & """")

calc_Commision is a field on the report that is from report source shown up above in previous posts....If the salesperson doesn't have any records it shows 0

 

[dhookom]

Do you have a Sum([calc_commision])/2 in your report section? You can make it invisible and then reference it in another text box where you apply your logic regarding the 101 and 103.

BTW: [balance]='0' suggests this is a text value.

Duane
Hook'D on Access
MS Access MVP