DateDiff Query

[Wheeler2008]

Hi All,

I am currently running a query on a MS Access table, in which I want to be able to compare and total the difference in dates between records. I only have one date field within the table. The table contains the following fields:

Acc. Number
Transaction Date
Detail
Current Balance

Everytime another transaction is completed, the current balance will change. However, a transaction will not be completed every day (it is ad-hoc) and the current balance will remain the same until a transaction is completed.

What I want to do is calculate all current balances for each day. This is to include days that no transactions were recorded, ie, there will be no record for some dates.

I have tried the DateDiff, but this does not seem to work on a single date field (in this case Transaction Date), two date fields are needed (or am I wrong?). Can anyone suggest how I could use the DateDiff argument on a single date field?

Your help is greatly appreciated.

Wheelers.

[NeoPa]

I can only assume you are referring to determining the difference between dates in sequential records. Otherwise this makes no sense to me at all.

Unfortunately, as SQL doesn't even recognise the concept of sequential records anyway, I think you may be disappointed.

Can you confirm what it is you are saying here?

[Wheeler2008]

Hi,

Yes, I am speaking about sequential records. I have one date field, and I want to compare one date with the date in the previous record. Is this possible?

[NeoPa]

Not exactly, but you can probably put something together using VBA and Recordset processing (See Basic DAO recordset loop using two recordsets).

As I mentioned in my previous post, SQL doesn't even recognise the concept of sequential records. Hence, the idea of processing records in a relative manner is not done for you by Access. You will need to implement your logic manually using VBA code.

[Krandor]

You are going to have to loop through your records with a recordset.

One of the values will be the prior date and the other will be the current date. From there you can use datediff.

[aprpillai]

Approximately how many records involved. I can give you an idea not the Code. We use a method to find the Missing Cheque Numbers from the Cheque Payment Voucher Records.

Assume that the Dates involved in your case is between 01-07-2008 and 31-07-2008 maximum 31 dates. But some date may be missing in between. Define an Array in VBA for number of elements i.e End-Date - Begin-Date. Define another parralel array with the same dimension for flaging the missing cases. Generate all the Dates between start-date and end-date and load the array.

The data in table must be sorted on the Date field. Open the table and compare each Array element (say X(K)) with date on the record and flag the second array element for matched cases. If you want to do tabulation of values then you can put that value in the second array. When complete some of the elements in the second array will be empty, which have missing date in the table. You can take a listing of these dates from the first array by checking the status of the second array empty elements.

a.p.r. pillai
** Link Removed **

[NeoPa]

I'm afraid that posting of external links as signatures is not allowed in technical threads (Signature Guidelines) so I have removed the link from your post.

Although there is actually a field that contains your official signature, these rules also to apply to any text used in that way.

Administrator.

PS. I should add that I appreciate your posting help in these forums. That's always appreciated of course. I merely draw your attention to the rules you may not have been aware of.