Return values on a form based on .... criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

C Markowitz - 29 Nov 2004 19:53 GMT

Help - I want to return the following values in a form:

Most Current Sale by Agent
Month to Date Sales by Agent
Year to Date Sales by Agent

I am able to extract the most current sale however I can't seem to get the
correct sum when I get the correct date.

My problem also continues when I try to get the Month to Date and Year to
Date Sales Figures.

I have a subform in the main form that returns the most current sale.

Any suggestions would be helpful.

I have been away from access programming for a long time and I have searched
several of the boards but can't seem to find anything to help me.

Thanks in advance.

Reply to this Message

Ted Allen - 29 Nov 2004 21:35 GMT

Hi,

There are multiple ways that you could go about this. Using domain
aggregate functions as the data source for the controls would likely be the
most simple, assuming that the sales info is in a table with the related
employee id.

For instance, for the most current sale amount, if you know the latest sale
date (air code so watch for typos & substitute your acutual field/control
names):

=DLookup("[YourSaleAmtField]","YourTableName","EmployeeID =" &
YourFormEmployeeIDControl & " AND SaleDate = #" & YourFormSaleDateControl &
"#")

Note that in the previous, you could also use DMax() to return the max sales
date if it wasn't avail on the form.

For the current months sales:

=DSum("[YourSaleAmtField]","YourTableName","EmployeeID =" &
YourFormEmployeeIDControl & " AND SaleDate >= #" &
DateSerial(Year(Date()),Month(Date()),1) & "#")

Year to date sales would be the same, but the month part of the dateserial
would be replaced with a 1.

Another way to get the values that you want would be to construct sql
statements in VBA and read the values from recordsets opened using them, but
the domain aggregate functions should be much simpler.

HTH, Ted Allen

> Help - I want to return the following values in a form:
>
[quoted text clipped - 16 lines]
>
> Thanks in advance.

Reply to this Message

C Markowitz - 30 Nov 2004 16:39 GMT

Ted,

I appreciate your help but I am still having problems.

I am really getting frustrated with myself - I have forgotten way too much
about Access (and programming in general).

I used the DSum function you suggested and I don't receive an error message
but I also don't receive any data - I just don't know where to go at this
point.

Cyndi

> Help - I want to return the following values in a form:
>
[quoted text clipped - 16 lines]
>
> Thanks in advance.

Reply to this Message