Newbie to Access

  • Thread starter Thread starter Stephen Hynes
  • Start date Start date
S

Stephen Hynes

I have a table with the following fields ,

1. Job Numer ( Text )
2. Value ( Currency )
3. Memo ( Memo )

in the Job number fields there is data like

023142
023143
023445
023446**
023447*



How do I get rid of the Asterix's

Tks

STephen
 
Hi Stephen... if all your Job Numbers are the same length
before the asterisks (here all of them are 6 characters),
go into design view on your table and change the Field
Size in the General tab under Field Properties to 6. This
will truncate everything longer than 6 characters, and get
rid of the asterisks in your example.

Good luck!
 
Unfortunately Not ,
There can be other job numbers like

023448BR*
023449S**

ETC

But thanks for the reply

Steve
 
Hi again Stephen,

I don't know how many records you have in your table, but
if you copy the entire Job Number column and paste it into
a new Notepad document (right click on desktop, New ->
Text Document) you can find and replace (Edit Menu ->
Replace) the asterisk with nothing. Doing it in a MS
Office program replaces the entire line with nothing
because the asterisk signifies a wildcard in those
programs.

After you replace all the asterisks, select everything in
the Notepad document, copy, then select the Job Number
column again and paste the data back into your table.

Hope this helps!
 
Steve

As long as the "*" are all trailing put the following in the "update to"
part of the field [Job Numer] in an update query

=IIf(InStr(1,[Job Numer],"*"),Mid([Job Numer],1,InStr(1,[Job
Numer],"*")-1),[Job Numer])

The above extracts out the string before the first "*" if there is one, else
leaves the string untouched.

N.B. I haven't added checks for all "*".

Change "Numer" if it is a typo.

Peter
 
You will use the following update query to remove all "*"
in a given [job number]:

UPDATE jobnumber SET jobnumber.[job number] = replace([job
number],"*","");


royc
 
Peters' Suggestion worked for me perfectly , Thanks for yesser help on
this.
Its gotten me out of a fix

Steve



Peter Surcouf said:
Steve

As long as the "*" are all trailing put the following in the "update to"
part of the field [Job Numer] in an update query

=IIf(InStr(1,[Job Numer],"*"),Mid([Job Numer],1,InStr(1,[Job
Numer],"*")-1),[Job Numer])

The above extracts out the string before the first "*" if there is one, else
leaves the string untouched.

N.B. I haven't added checks for all "*".

Change "Numer" if it is a typo.

Peter

Stephen Hynes said:
I have a table with the following fields ,

1. Job Numer ( Text )
2. Value ( Currency )
3. Memo ( Memo )

in the Job number fields there is data like

023142
023143
023445
023446**
023447*



How do I get rid of the Asterix's

Tks

STephen
Click to expand...
Click to expand...
 
Hi Stephen,

I use a function StringTrans() for such tasks. In your case use it in the
query like this:

JobNum: StringTrans([Job Number]; "*")

Good luck!

--Just put code below in a module-------------------------------
Public Function StringTrans(strString As String, strSearch As String,
strReplace As String, Optional blnExact As Boolean) As String
Dim nCurPos As Integer

If IsMissing(blnExact) Then
blnExact = True
End If
nCurPos = InStr(1, strString, strSearch, IIf(blnExact, 0, 1))
While nCurPos > 0
strString = Left(strString, nCurPos - 1) + strReplace +
Mid(strString, nCurPos + Len(strSearch))
nCurPos = InStr(nCurPos + Len(strReplace), strString, strSearch,
IIf(blnExact, 0, 1))
Wend
StringTrans = strString
End Function
 
Hi Again,

Sorry, forgot a param in function call, should be:

JobNum: StringTrans([Job Number]; "*";"")

Sid.

Sidney Linkers said:
Hi Stephen,

I use a function StringTrans() for such tasks. In your case use it in the
query like this:

JobNum: StringTrans([Job Number]; "*")

Good luck!

--Just put code below in a module-------------------------------
Public Function StringTrans(strString As String, strSearch As String,
strReplace As String, Optional blnExact As Boolean) As String
Dim nCurPos As Integer

If IsMissing(blnExact) Then
blnExact = True
End If
nCurPos = InStr(1, strString, strSearch, IIf(blnExact, 0, 1))
While nCurPos > 0
strString = Left(strString, nCurPos - 1) + strReplace +
Mid(strString, nCurPos + Len(strSearch))
nCurPos = InStr(nCurPos + Len(strReplace), strString, strSearch,
IIf(blnExact, 0, 1))
Wend
StringTrans = strString
End Function
------------------------------------------------------------------

Sid.

Stephen Hynes said:
I have a table with the following fields ,

1. Job Numer ( Text )
2. Value ( Currency )
3. Memo ( Memo )

in the Job number fields there is data like

023142
023143
023445
023446**
023447*



How do I get rid of the Asterix's

Tks

STephen
Click to expand...
Click to expand...
 
Back
Top