How to have a field always hold current date? 1

[kellstee]

I have a query with a running total that sums up a bunch of credit card/bank accounts.

This the result set I need:

Account - Closing Date - Balance - Running Total
-------------------------------------------------
Bus Acct - ??? - $400 - $400
VISA 1000 - 08/15/2007 - ($1500) - ($1100)
VISA 1001 - 08/20/2007 - $3000 - $1900
VISA 1002 - 08/23/2007 - ($500) - $1400
VISA 1003 - 08/30/2007 - $1500 - $2900
VISA 1004 - 09/04/2007 - ($2900) - $0


We have money flowing in and out of a variety of accounts on certain dates. The Bus Acct doesn't really have a date associated with it, but in order to make my running total work (and some other formulas I'm doing), I need to make the "closing date" (on the first record above) always equaled to the current date (the date I'm running my query).

How can I do this? I know how to write queries, but not really any other stuff. Any help would be most appreciated.

Kelly

 

[PHV]

Use the Date() function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kellstee]

I'm confused though as the first two columns in my result set come from a simple table. How can I force [COH].Closing_Date to be the current date for the Business Account in the COH table? So then every time the data is retrieved, that field will always be the current date?

Not sure where to put the Date().

 

[PHV]

What is your current SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kellstee]

Oops. That would be helpful.

SELECT [COH].Account,
[Closing Date],
[Amount],
(SELECT Sum([Amount]) FROM [COH], [Trial Balance] WHERE [COH].Account = [Trial Balance].Account and [Closing Date]<=[tbl_Alias].[Closing Date]) AS [Running Total]
FROM [Trial Balance], [COH] as tbl_Alias
WHERE [Trial Balance].Account=tbl_Alias.Account
ORDER BY [Closing Date];

 

[PHV]

Something like this ?
SELECT [COH].Account,
[!]IIf([COH].Account='Bus Acct',Date(),[/!][Closing Date][!]) As ClosingDate[/!],
[Amount],
(SELECT Sum([Amount]) FROM [COH], [Trial Balance] WHERE [COH].Account = [Trial Balance].Account and [Closing Date]<=[tbl_Alias].[Closing Date]) AS [Running Total]
FROM [Trial Balance], [COH] as tbl_Alias
WHERE [Trial Balance].Account=tbl_Alias.Account
ORDER BY [Closing Date];

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kellstee]

Duh. . .that makes sense.