By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,222 Members | 1,728 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,222 IT Pros & Developers. It's quick & easy.

DateDiff Query

P: 2
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
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.

Aug 11 '08 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 15k+
P: 31,494
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?
Aug 11 '08 #2

P: 2

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?
Aug 12 '08 #3

Expert Mod 15k+
P: 31,494
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.
Aug 12 '08 #4

P: 50
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.
Aug 13 '08 #5

P: 23
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 **
Aug 15 '08 #6

Expert Mod 15k+
P: 31,494
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.


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.
Aug 15 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.