Access select field value AFTER a date

[Bill]

Question:
For a table like this:
Date | Description | Amount | Balance
9/25/04 | payment on acct | 25.00 | 0.00
10/1/04 | delivery | 54.25 | 54.25
11/5/04 | payment on acct | 54.25 | 0.00
11/10/04 | delivery | 48.57 | 48.57

Is there any way I can find the account balance on 11/1/04, when I
don't have an entry for 11/1/04?

I don't want to SELECT Balance WHERE (Date = #11/1/03#); because that
won't show anything for this customer. I don't want to SELECT Balance
WHERE (Date > #11/1/03#); because that will give me an entry for every
date after that, which is too much. You and I know that, for this
customer, I'd want to search on 11/5/04, but I need this for all
customers in the database, where this simple example limits it only to
one customer.

Any help would be appreciated!
Bill

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a range of dates as parameters:

PARAMETERS [Start Date?] Date, [End Date?] Date;
....
WHERE ... [Date] BETWEEN [Start Date?] And [End Date?]

Try not to use the word "Date" as a column 'cuz it is a reserved word
and can sometimes be confused for the Date() function. To avoid that
confusion delimit the column name w/ square brackets.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQaep3oechKq OuFEgEQIVtgCg0q VUvZmtjBQaOhKhV Se0OSGM6UgAnisN
mg6O5JGDZ7QwVsv G8wlVF42S
=xQGC
-----END PGP SIGNATURE-----
Bill wrote:

Question:
For a table like this:
Date | Description | Amount | Balance
9/25/04 | payment on acct | 25.00 | 0.00
10/1/04 | delivery | 54.25 | 54.25
11/5/04 | payment on acct | 54.25 | 0.00
11/10/04 | delivery | 48.57 | 48.57

Is there any way I can find the account balance on 11/1/04, when I
don't have an entry for 11/1/04?

I don't want to SELECT Balance WHERE (Date = #11/1/03#); because that
won't show anything for this customer. I don't want to SELECT Balance
WHERE (Date > #11/1/03#); because that will give me an entry for every
date after that, which is too much. You and I know that, for this
customer, I'd want to search on 11/5/04, but I need this for all
customers in the database, where this simple example limits it only to
one customer.

[Bob Quintal]

bi**@ehrhartpro pane.com (Bill) wrote in
news:b4******** *************** **@posting.goog le.com:

Question:
For a table like this:
Date | Description | Amount | Balance
9/25/04 | payment on acct | 25.00 | 0.00
10/1/04 | delivery | 54.25 | 54.25
11/5/04 | payment on acct | 54.25 | 0.00
11/10/04 | delivery | 48.57 | 48.57

Is there any way I can find the account balance on 11/1/04,
when I don't have an entry for 11/1/04?

I don't want to SELECT Balance WHERE (Date = #11/1/03#);
because that won't show anything for this customer. I don't
want to SELECT Balance WHERE (Date > #11/1/03#); because that
will give me an entry for every date after that, which is too
much. You and I know that, for this customer, I'd want to
search on 11/5/04, but I need this for all customers in the
database, where this simple example limits it only to one
customer.

Any help would be appreciated!
Bill
You will always run into problems with your structure. Balance
should always be calculated, and not stored. If you happen to
enter a transaction out of order, you're toast.

The better way is to calculate the running balance when you need
it as the sum of credits - sum of debits.
LedgerDate| Description | Amount | Credit
9/25/04 | payment on acct | 25.00 | true
10/1/04 | delivery | 54.25 | false

the query to return your balance on any date is

Select accountID, sum(amount*iif( credit,1,-1)) from ledger where
ledgerdate <= statementdate

--
Bob Quintal

PA is y I've altered my email address.