DLookup Question?

[accessir]

Hi Folks

Im trying to return a value whereby I get a result that shows me the salesman with the LEAST time on the phone for Campaign 1 - so, Im running this:

=DLookUp("[Salesman]","CallsTable","SalesCampaign = 'Campaign 1'" And "LengthOfTheCall = DMin")

However, it doesnt work

I also wanted somethign similar to find out who was spending the MOSt time on the phone and tried to use DMax see below:

=DLookUp("[Salesman]","CallsTable","SalesCampaign = 'Campaign 1'" And "LengthOfTheCall = DMax")

And this didnt work either...

I can successfully find out the least and longest time on teh phone using a separate DMax and DMin query but not joining with the campaign and telling me which salesman is on teh phone!

Arrghh ANy help much appreciated.

 

[JamesMcS]

Well, both are looking in lengthofthecall for the words "Dmax" and "Dmin". You've got the expression a bit screwy. What you'd want is

DLookUp("[Salesman]","CallsTable","SalesCampaign = 'Campaign 1'" And "LengthOfTheCall = " & dMin ("[lengthofthecall]","CallsTable"))

 

[Brianwarnock]

Having used Dmax and Dmin to get the least and most time on the phone join those values back to the original table to Select the rest of the data.

Brian

 

[accessir]

Having used Dmax and Dmin to get the least and most time on the phone join those values back to the original table to Select the rest of the data.

Brian

Hi Brian

Thanks for your input here...alas, I have no idea how to join here - I know for example having used htese functions that min is 2 mins and max is 34...but to join these, I have no clue!! Can you help how to join?

Cheers

 

[accessir]

Well, both are looking in lengthofthecall for the words "Dmax" and "Dmin". You've got the expression a bit screwy. What you'd want is

DLookUp("[Salesman]","CallsTable","SalesCampaign = 'Campaign 1'" And "LengthOfTheCall = " & dMin ("[lengthofthecall]","CallsTable"))

Hi James - tried this but it still brings up the same default salesman (if it helps, it returns the first salesman on the table even though I modified the data to say his figures were neither max or min and tried DMin and DMax!

Cheers though, I appreciate the help...

 

[Galaxiom]

The problem is you are getting the minimum or maximum call time for everyone. You would need to also include the name of the campaign in the DMin or DMax.

However this is becoming a mess. You would probably be better getting this value with a query.

If you are trying to return stats on a recordset that is the RecordSource for a main table it is often worth producing another recordset of the stats to display as a subform. This is much neater, faster and more efficient that nesting Domain functions.

 

[JamesMcS]

Ah yes I forgot to mention you can put WHERE conditions, SQL style, in dmax and dmin statements. Check the help file.....

 

[Brianwarnock]

Ah! Hoist by my own petard. I'm always, well often, complaining that people don't read posts carefully enough , and I've just fallen into that trap. I was thinking Max and Min in a query, not the Domain functions.

In a query use Max and Min to get the 2 values then in another query join MaxOfCalltime in the query to calltime in the table to pull the rest of the info, ditto MinOfCalltime.

This is a standard technique.

Brian