Total using sum

[spn200286]

Good morning, hope everyone who read this is well

I am trying to do a running Total for my marketing database my company has asked me to produce

A client buys Product A, B, C the quantity is entered and a total (price*quantity) is figured. now in my form i created a text box that works out the total and displays it.

when i went to put that in a report i realised that the textbox i made couldn't be linked too (i believe?) so i made a query that will work out the running total for me.

I created a Dsum to work this out.
what iv got is

 STotal: Dsum("total","qrySales","SalesID <=" &[MySales])

this works, however when i refine my query to just show data for one Client (e.g. Testco) the Stotal function includes the totals from the other Clients
e.g. instead of starting at 45, it starts at 105, including 60 from the previous Client.

If you follow me so far my question is, am i doing this the right way, or do i need to change my approach.

I believe its my "SalesID <=" &[MySales] which is the issue, however my minds blank as to what clause i should be using!

Thanks for any help you can give

 

[Galaxiom]

Maybe something like this:

 STotal: Dsum("total","qrySales","SalesID <=" &[MySales] & " AND ClientID=" & [MyClient])

 

[spn200286]

Thank solved 1/2 my problem thank you!
i changed it to

STotal: Dsum("total","qrySales","SalesID <=" &[MySales] & " AND ClientID[COLOR="Red"]<[/COLOR]=" & [MyClient])

because the tutorial I was using said < is important

When i want to search between XX date and YY Date however hows the running total for all of the Client
(e.g. shows the total for all of Testcos purchases)
is there a way without modifying the Dsum with a MYdate bit, as i can see that setting complicated (if its even possible)

 

[Galaxiom]

Unless you want a total for all clients with IDs less than the current client (I can't image why you would) I would not add the "less than" comparitor to the ClientID.

Basically you want to add something like this for date.

" AND [datefield] Between [XXDate] and [YYDate]"

I THINK the DLookUp uses the system date format.

Note however that in SQL one must always use mm/dd/yyyy format for dates expressed in a string as #mm/dd/yyyy#.

This is format is best achieved with the expression:
Format([somedate], "\#mm\/dd\/yyyy\#")

 

[spn200286]

It is the DLOOKUP that deals with date.

The problem is i dont know the dates the user will want to search, so i was using a parameter querie on the [Date] field.

and im not sure how to add this into the DLOOKUP, or if i even can

(I hate sounding like such a novice, however i forgotten alot which i knew through misuse!)