IIF & Dlookup Formula

[cardonaj8]

All,



i don't even know what i am trying to do is possible..... i Have an account number in my sales report that belongs to the company ( that our dealers can use when their account is low on funds and they rather pay us with a check ). Nonetheless i have a crosstab query that shows each sales rep their daily sales. I have Dlookup formulas looking at the account umber to notify what rep gets what sale.

I have a CEPOData query that has the general account inforamtion and has it seperated by the actual delaer and their information.

My ulitmate goal is to create an IIF function that states ( IIF the customer # ID has ( 30220992 ) " this is the generic account" ... dlookup in "QryCEPOData" , PO Number against CUst PO # ..... ( i want this to give me the actual account number for this specific sale ) but i also need the false dlookup formula to say if the customer IS isn't " 30220992" leave the origianlformula alone .

i have no idea if i make any sense but i created the below:






IIF([Br Cust #="30220992"( DLookUp("[Sales Rep]","QryCEPOData","[PO Number]='" & [Cust PO #], DLookUp("[Sales Rep]","tblmastercustomerlist","[ACCOUNT NUMBER]='" & [Br Cust #] & "'")


any help or gudience would be great thankyou

[pbaldy]

I'm not clear on what you're trying to achieve, but there are several syntax errors in your effort. Try

IIF([Br Cust #]="30220992",DLookUp("[Sales Rep]","QryCEPOData","[PO Number]='" & [Cust PO #] & "'"), DLookUp("[Sales Rep]","tblmastercustomerlist","[ACCOUNT NUMBER]='" & [Br Cust #] & "'"))

[cardonaj8]

I'm not clear on what you're trying to achieve, but there are several syntax errors in your effort. Try

IIF([Br Cust #]="30220992",DLookUp("[Sales Rep]","QryCEPOData","[PO Number]='" & [Cust PO #] & "'"), DLookUp("[Sales Rep]","tblmastercustomerlist","[ACCOUNT NUMBER]='" & [Br Cust #] & "'"))

I will try this on Monday when I am back in the office. My main goal is..... if the field has accnt number 30220992, i need access to dlookup the sales rep by the po order in qrtcepodata by the field classified as po number in that qry that matches the customer po number in the current qry. If the act number isn't 30220992 , i need access to find the sales rep by the proper account nunbers.

Thank you so much for your help.

[pbaldy]

No problem, post back if that doesn't work. Welcome to the site by the way!

[cardonaj8]

No problem, post back if that doesn't work. Welcome to the site by the way!

Thank you so much it worked!!!

now i have one more question is their a specific formula to have a report focuse on the current date ( i need my qry to have the sales for the month and one for a the current date but i don't know te formula for that. )

if you knwo it i would highly appreciate that.


thank you thank you thank you again for your help.

[pbaldy]

The Date() function will return the system date; is that what you're looking for?