Importing DbaseIII to access and Incrementing date+numbers

My query is as follows.  I currently run a simple accounting suite in dBase III. It consists of a ledger, customer, client and bank files.  The ledger has one entry per transaction and connects to the customer and client files by postcode. It connects to the bank ledger by cheque number for both debits and credits, it also has for a second reference the customer post code and a field number from the ledger.  The field number in the ledger is the controlling field and due to the limitations of DOS consists of the date of the transaction in YYYY, MM, DD, number format the number increments for each transaction for that day i.e. "2003.12.03.01" "2003.12.03.02" etc
Each ledger entry actually contains two entries on one line, the first part is the debit side and the second is the receipt of payments to that transaction.
 
 I can access all the records through Access and currently print my invoices and various forms from there. However I would like to import all the information into Access and use it as my database.  The problem I have is in dBase when I start making entries the programme checks the last entry on file, If it is an additional entry for that day it will increment the number. If it is the first entry for that day it will generate a new number starting at "YYYY, MM, DD.01" incrementing the last two digits for each entry.  I can with difficulty get Access to generate the number in the correct format but not to increment for each entry. However I am having trouble in getting it to check the latest entry on the file in order to generate the correct number?  

The second problem that may arise is that I have to submit a summary each week to my bankers. This is achieved by having a field corresponding to the week number. Therefore I will need a piece of code to calculate the week number.

It would be appreciated if you could give me some indications of how to write a data entry programme, which will check for the last entry in the file and generate the correct number.
Sabdy
SandyRossAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bdbrownCommented:

In your Access Query, parse the [DateField] into two parts and sort on the both fields.

SELECT Left([DateField],10) AS DatePart, Right([DateField],Len([DateField])-11) AS Number FROM Table2
ORDER BY Left([DateField],10) DESC , Right([DateField],Len([DateField])-11) DESC;

I use this method in a few apps where I need to find the next avalible number. If you sort DESC, the last entry is always the first record returned so you don't have execute a .MoveLast to get it.

bdb

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SandyRossAuthor Commented:
Thanks for that it is a simpler version that the one I was trying to use.
0
bdbrownCommented:
Glad I could be of some help
bdb
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.