Re: adding a text string to data from one field in one database to another

sellcraig@xxxxxxx wrote in
news:1157936291.383155.321180@xxxxxxxxxxxxxxxxxxxxxxxxxxx:


1 validation for code field
If I want the code field to contain only test or numbers -
(NO symbols - ie: /? -;+=space) and also text only in lower
case only. The data in the code field contains product
codes of various lengths I think there are two was I can
think of this working, 1) Not permitting the text to be
entered upon entry. using some validation rule
2) Ignoring the non accepted info and correcting to
lower case when leaving field. Ie: wAsrH 12-4 becomes
wasrh124

I looked at
Validation rule suspect- StrConv([code], 2) plus a
function that blocks anything but numbers or letters. ( I
do not know which one) Format do not know
Input mask do not know
I also want to make the field a unique field eliminating
any duplicates of the item code being entered twice.


I strongly suggest UPPERCASE letters, for technical
reasons... I would put this in VB code in the textbox
AfterUpdate event.

private sub ItemCode_AfterUpdate() as string
Dim stAllowedChars as string
dim stFrom as string stTo as string
dim iPtr as integer.

stAllowedChars ="ABCDEFGHJKLMNPRTUVWY0123456789"
'do not allow IOQSXZ, they can be confused with numbers.
stFrom = me.Itemcode.value

For iPtr = 1 to len(stFrom)
If instr(1,stAllowedChars,mid(stFrom,iptr,1))>0 then
stTo = stTo & ucase(mid(stFrom,iptr,1))
end if
next iPtr
me.Itemcode.value = stTo
end sub

What if I am not using forms?

How will you be entering data? If you intend to allow entry of
part numbers in the table, you are doing something dangerous.

If you are planning to import from a file generated outside of
Access, then place the code in a user defined function, and call
that function in a query

and the data needs to be lower case with all characters
because they are part numbers and also will be part of an web
address address and some sites do not support capitals and
lower case letters as the same.

because they are part numbers? The D.O.D. in the usa and NATO
High Command require part numbers in UPPERCASE only, with the
letters excluded as shown in my code.
Some sites? not many, as the web has evolved. If you still
insist, then add one function to the code to convert the string
to lowercase
Change from
me.Itemcode.value = stTo
To
me.Itemcode.value = StrConv([stTo], 2)




--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com
Warning: Do not use Ultimate-Anonymity
They are worthless spammers that are running a scam.

.

Relevant Pages

  • Re: adding a text string to data from one field in one database to another
    ... Dim stAllowedChars as string ... dim stFrom as string stTo as string ... dim iPtr as integer. ...
    (comp.databases.ms-access)
  • Re: Help a beginner - simple lowercase to uppercase and so on function
    ... can understand everything but it should take shape with more practice ... it compiles and returns uppercase letters ... in this string!" ... to compile on both. ...
    (comp.lang.c)
  • Re: Format text not all caps
    ... PROPER - Straight out of *Help on this function* when you look at the function ... Converts all other letters to ... =PROPERProper case of first string ... The excel file that I ...
    (microsoft.public.excel)
  • Re: Generate Number on MS. Access
    ... with 10 digits with the combination of 5 Letters from the 26 letters ... Optional NumericOnly As Boolean = False) As String ... Dim l As Long, b As Byte ... Oops, I see this is ten alpha or numerics, but not five of each as you ...
    (comp.databases.ms-access)
  • RE: Output text from numbers
    ... Exit Sub ... ) Then simbol = "I" ... with letters and output a text string. ... letters quoted in the table for that input. ...
    (microsoft.public.excel.worksheet.functions)