Ascending Unique Record Number - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Ascending Unique Record Number

Paul Maguire

4 years ago

Hi, I am trying to customise the Check Register Database to suit my particular needs. I want the database to reflect the order of my debit and credit transactions as they appear on my bank account. The underlying table I am using is tblTransReg. I created a field TransDate in qryTransReg which formats the TransactionDate field from 17/01/2022 to 20220117. This is stored in the table. I also have a field called DayOrder, again stored in the table. In my query qryTransReg I created a field called TransID which combines TransDate and DayOrder (202201171) to give a unique identifier number that can be sorted ascending or descending. The DayOrder number can be manually changed to match the order of the entry as displayed on my bank account, when I get the statement. I am trying to get the database to add the next number in the DayOrder when I enter a new transaction record, similar to how Richards Check Register assigns the next available chq number. I would like the database to see that 202201171 has been allocated and that the next entry for 17/01/2022 should be allocated a DayOrder number of 2 to end up as 2022011702 and so on. Is this possible? Any help would be greatly appreciated.

Adam Schwanz @Reply

4 years ago

Using dates like that, the highest date should always be the highest number correct? If so you can just DMAX+1 the default value to it DMax if it's on the same day. Now if it's a different day and you want that to work the same way, it's going to get a lot more complicated, you'll have to convert that back to a date, find out the next day,then take that and convert it back into a number like you have.

	Adam Schwanz @Reply 4 years ago
Actually, you'll have trouble when you reach 10, it will end up changing the date at that point. So you're going to need to string concatenate the two numbers. You may need to use the length function to trim the first 8 numbers off, get the numbers that are left, add one to it, and then put them back togethor.

	Paul Maguire OP @Reply 4 years ago
Hi Adam, thanks for getting back. Yes ideally it would revert back to 1 on the following day. In the underlying table tblTransReg I have the following fields stored for each transaction. TransactionDate (formatted as 17/01/2022). TransDate (formatted as 20220117). DayOrder (currently with a default value of 1). A fourth field TransID concatenates TransDate & DayOrder to give me 202201171

Juan C Rivera @Reply

4 years ago

Hi All I did something like this but with Julian dates. yr 22 day lets say 028 for Jan 28 but the last part like you said gave me problems so I took and made the last 3 digit 000 and incremented that by 1. and on the next day check to see if date and now are the same if yes increment by 1 if not reset to 000. Cant remember which tech tip I got this from but it pointed me right direction
Ok so long story short don't stay with 1 digit use as much as you need if you use more that 100 in the day then recommend 4 digits the 0 in the front will help keep your numbers in order.

Adam Schwanz @Reply

4 years ago

So Paul, when you enter a transaction date in the field TransacationDate, you just want it to convert that date on to the next available number on that date using your format right? Then set that value into TransDate? So we could use an after-update event AfterUpdate in the date field. Take the date put in, use a dmax to find the highest number stored on that date, and then add 1 to that. Then set the value of TransDate to that number. If that's all correct I can write up a quick example if you need it.

Paul Maguire OP @Reply

4 years ago

Thanks Juan
Hi Adam yes that is pretty much it. TransDate is a field that just allows me to store the TransactionDate formatted in reverse eg
TransactionDate = 27/01/2022 is then formatted and stored in TransDate as 20220127. DayOrder starts each day as 01, next entry 02 and so on. TransID concatenates TransDate & DayOrder to give me 2022011701. The AfterUpdate in TransactionDate you suggested sounds like it will work. If you could give me an example that would be great. Thank you

	Paul Maguire OP @Reply 4 years ago
sorry that should have read TransID concatenates TransDate & DayOrder to give me 2022012701.

	Adam Schwanz @Reply 4 years ago
OK, I may be confused on what your fields are doing so I'm going to call it RealDate and ChangeDate for the fields, change those to what you use. `RealDate_AfterUpdate Dim A Dim B Dim C Dim D A = NZ(DMAX("ChangeDate","Table/Query","RealDate=" & RealDate),0) B = Mid(A,9) C = Left(A,8) D = B+1 ChangeDate = C & D`

	Adam Schwanz @Reply 4 years ago
Actually that may not work when there is no records. You may need to use something along these lines. (Not where I can test, but this should work) `Dim A Dim B Dim C Dim D A = NZ(DMAX("ChangeDate","Table/Query","RealDate=" & RealDate),0) If A = 0 Then 'ChangeDate = TransDate & "1" Else B = Mid(A,9) C = Left(A,8) D = B+1 ChangeDate = C & D End If`

	Adam Schwanz @Reply 4 years ago
I put a ' there because that may be incorrect, not completely following your setup. Might need to adjust that line. But Basically, A finds the highest ChangedNumber such as 202201101 A= 202201101 B Gets the extra numbers off B=1 C gets the beginning date value back C=20220110 D increases the extra number D= 2 Then we throw them togethor 202201102

	Paul Maguire OP @Reply 4 years ago
Thanks Adam, I really appreciate your help. That looks like it should do what I need. I will let you know. Thanks again

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View