I am fairly new to Access.

I have a sales agent commission % table with one record per sales agent per payment month that holds the agent's comm% for that month. I am trying to create a query in which I need to look up the commission % for the agent on an individual sale from the sales table. The query joins the agent comm% table and the sales table. How can I set this up similar to vlookup in Excel?

I tried


dlookup("[Agent Comm %], "[Agent Comm Table]", [Payment Month]=-------")

but I'm getting stuck on how to enter the criteria. I had to add a calculated field in the query for the payment month- using dateserial, which worked! What I want the query to do is to compare the date that I calculated from dateserial in the query against the "payment month" field in the agent comm table and give me the correct commisison % for the agent for that sale.

Any help would be greatly appreciated!

Thanks,
Ellen

Hi Ellen,

(IMG:style_emoticons/default/welcome2UA.gif)

If [Payment Month] is a number, and your DateSerial result is a date, you can try to use the Month() function for your criteria. For example:

DLookup("[Agent Comm %]", "[Agent Comm Table]", "[Payment Month]=" & Month(DateSerial(...)))

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

[Payment Month] is the first day of the month - format is 03/01/2012.

Also, I'm not clear on which part needs the brackets. I thought that you don't need the brackets for the components related to the table where the lookup will be pulling the result of the query from, but you do need the brackets for the right side of the criteria as that data is not in the table where you will be pulling the result of the query from.

This post has been edited by Elleng07: May 5 2012, 10:40 PM

Hi Ellen,


Then try using the Month() function against both sides of the criteria. For example:

DLookup(...., "Month([Payment Month])=" & Month([DateSerial Column In Your Query]))



Actually, the brackets are not really necessary unless the object name contains spaces or any special characters.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Sorry - I wasn't that clear. The data in the Payment Month field is not actually the month. It is the first day of the month, so for March 2012, it would be 03/01/2012, and for December 2011, it would be 12/01/2011. I can't just take the month, because we do retroactive adjustments that may cover more than one year.

I thought that it was hanging because it was comparing a calculated field against a field in another table, so I created another query to pull the first day of the month, and then linked that query to the other query. This caused it to run for 53 minutes instead of about 20, and still gave me an error message that the "build" formula was either too complex or not in the right syntax.

Hi,

So, if the field is a full date, and the result of the calculation is also a full date, then why not just compare the two? For example:

..., "[FieldName]=" & DateSerial(...))

Or,

..., "[FieldName]=" & [QueryColumn])

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

This is driving me nuts! It's still not working.

Before I showed you simpler label names to make it easier to follow, but here are the actual values:

My two tables are: 1) ER-Agent Adj Percent, which contains a list of agents and their comm %'s per month (entered as the first day of the month)
and 2) JSBOOD - a complete table of sales

I keep adding brackets and quotations marks and removing them.

Here is the exact syntax of my calculated field for the agent's comm %- what looks wrong??

Agent Comm Pct: DLookUp("[Agent Comm %]","[ER-Agent Adj Percent]","Final Month=" & [Final Pay Month])

When I have it entered exactly as above, I get a pop-up box asking me to enter the Final Pay Month!!!

Final Pay Month is a calculated field in the query, as follows:

Final Pay Month: DateSerial(Year([JSBOOKD]![Final Date]),Month([JSBOOKD]![Final Date]),1)

and Final Date is a field in table JSBOOKD, but the criteria for that field in this query is <=03/31/2012

OMG! It's running!! I flipped the position of Final Month and Final Pay Month!! I can't say that I will become a fan of Access if it works, but I will do a little dance! (IMG:style_emoticons/default/smile.gif)

Ok, I give up. It did not work.

Ok, one last try:


Maybe I need a multiple criteria on the dlookup?

Here's some key sample data from each table and what I would like the dlookup to give me:



Sales Table:

Inv# Agent Code Final Date Total Sale

1 AE 02/02/2012 $1,000
2 AE 03/15/2012 $1,500
3 BH 02/17/2012 $2,000
4 BH 03/22/2012 $2,500


Agent Comm Table:

Agent Code Final Month Comm %
AE 02/01/2012 25%
AE 03/01/2012 30%
BH 02/01/2012 35%
BH 03/01/2012 15%



Result:

Inv# Agent Code Final Date Total Sale Comm%
1 AE 02/02/2012 $1,000 25%
2 AE 03/15/2012 $1,500 30%
3 BH 02/17/2012 $2,000 35%
4 BH 03/22/2012 $2,500 15%

Hi Ellen,

Give this one a try:


If that doesn't work, consider posting a zip copy of your db with test data.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

You usually cannot reuse a calculated field in a further expression, such as a criterion, because it doesn't (can't!) get evaluated until the criteria are applied.

Try using the written-out DateSerial() function itself as the criterion, rather than the calculated field.