Solved

Importing DbaseIII to access and Incrementing date+numbers

Posted on 2003-12-03
3
305 Views
Last Modified: 2010-04-17
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
0
Comment
Question by:SandyRoss
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
bdbrown earned 500 total points
ID: 9866512

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
 

Author Comment

by:SandyRoss
ID: 9867592
Thanks for that it is a simpler version that the one I was trying to use.
0
 
LVL 2

Expert Comment

by:bdbrown
ID: 9867819
Glad I could be of some help
bdb
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
triangle challenge 4 121
wordlen challenge 3 61
cmd.exe will not close when running .bat file to perform FTP upload 18 94
C# application error "Parameter Is Not Valid". 3 79
I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now