Re: pulling apart, padding out and re-assembling a pasted alphanumeric text string....
- From: Salad <oil@xxxxxxxxxxx>
- Date: Fri, 04 Sep 2009 17:11:58 -0700
DeZZar wrote:
Hi all,
(sorry for long post, but I need to explain a little)
I've got another one today that I KNOW what I need to do and somewhat
how to go about it, but my code understanding is lacking.
What I have is a system that needs to traslate a pasted string of data
from another system. (users copy paste to an unbound field)
let me explain a little more first. There is a (seperate) system in
our business that generates quotation numbers follwoing the format
rule below:
"Year"+"day of year"+"sequential no."+"sequential variation letter"
So as an example, if I was to give a quote today and I mine was the
250th quote issued today my quote number would be:
092480250A
This system then generates a document based on that number which my
database collects as part of an emailing proceedure.
HERES THE PROBLEM
When the document is generated the quote number is padded out
following the following rules:
Year = 2 Digits, Day of year = 3 digits, Sequential number = 4 digits,
+the trailing letter
So what happens is a quote given on the 20th day of the year might
look like this when copied from the system:
092012 (12th quote, given on the 20th day, 2009)
BUT:
the document will be named: "090200012A" in full....
So I need to create a series of rules to deal with what the user may
paste into my system to translate it into the full number used as the
document name. (noting that what is pasted will not always be the
same, even in length)
I will need to create several different rules based on the length of
the string so I would start with a trim:
strQuoteNo = Left("" & Me.txtPastedQuote & "", InStr(1, "" &
Me.txtPastedQuote & "", "-") - 1)
That removes any trailing numbers not needed from the pasted number
from a "-" (always the case)
Then test for length
If Len(strQuoteNo) = 4 Then (and = 5, then another set of rules etc
etc)
'translate using these rules
but there is where my ability stops....
What are the text grabbing, trimming, padding rules I would need to
say, grab the first two digits, set them aside as year, grab the next
"x" no. of digits, add 1, 2 or 3 zeros to the front and set aside as
DayNo, and then take the remaining number and pad it by 1,2 or 3 zeros
at the front?
I also need to shave off the trailing "letter" from the number and set
it aside...
any help or a point in the right direction would be very very much
appreciated!
Cheers All,
DeZZar
The length of a properly formatted document string is 10. If no alpha at end, then 9.
Beyond that it would appear you are up the creek with no paddle. You have no idea what the formatting is. Is the day zero padded? Is the quote number zero padded? Are they zero padded correctly? There is nothing to separate the "fields" in the string like a dash.
I would consider a parsing out of the value in the BeforeUpdate event of the field and present a message box that displays something like
Year: 09
Day: 020
Quote: 0012
Alpha: A
Is this correct?
Asking somebody to parse it out without separating the values seems a formula for mistakes.
Look at Left(), Mid(), Right(), String(), StrConv(), Split(), and Join() for some string manipulation functions.
You can use Format to zero pad. ? format(123,"0000") returnx "0123" Or something like Right("0000" & "123",4).
If you can create some rules...you might do something like
Select Case Len(string)
Case 0
...
Case 10
End Select
And create your rules depending on the case selected.
.
- Follow-Ups:
- References:
- Prev by Date: pulling apart, padding out and re-assembling a pasted alphanumeric text string....
- Next by Date: Re: pulling apart, padding out and re-assembling a pasted alphanumeric text string....
- Previous by thread: pulling apart, padding out and re-assembling a pasted alphanumeric text string....
- Next by thread: Re: pulling apart, padding out and re-assembling a pasted alphanumeric text string....
- Index(es):
Relevant Pages
|
Loading
