Remove a char from the data saved in a table

[leeza]

hey guys,
I have a query which creates a table wid the frieght liner's ID
for eg: EXLA*
is there any way to remove this asterik so that only "EXLA" string gets stored in the record

Pls help me

Thanks,
Leeza

 

[MSAccessRookie]

hey guys,
I have a query which creates a table wid the frieght liner's ID
for eg: EXLA*
is there any way to remove this asterik so that only "EXLA" string gets stored in the record

Pls help me

Thanks,
Leeza

If you want to change "EXLA*" to "EXLA", try searching the Forums for examples of the Replace() command. It can do this for you.

 

[leeza]

I tried using a VB module to perform the replace action here' s the code
Option Compare Database
Option Explicit
Function chan()
DoCmd.OpenTable "Frthedp", acViewDesign, acEdit
Do While Not [Frthedp]![SCAC]
DoCmd.GoToRecord , , acNewRec
If (Right([Frthedp]![SCAC], 1) = "*") Then
Right([Frthedp]![SCAC], 1) = " "
End If
End
End Function

but the problem is the compiler is not identifying the table Frthdp and so its gives a compilation error "external name not defined"


ANY SUGGESTIONS PLS!!!!!!

 

[JANR]

Why don't you just run a simple Update Query??

UPDATE tb1 SET tb1.Fld1 = Replace([Fld1],"*","");

Or if you want to replace with a space

UPDATE tb1 SET tb1.Fld1 = Replace([Fld1],"*"," ");

JR

 

[JANR]

Confused now, after re-reading your post.

I have a query which creates a table wid the frieght liner's ID

DoCmd.OpenTable "Frthedp", acViewDesign, acEdit

These 2 statements indicates that you want to rename a Field in a table and not a record in a table. If so you probably need to look into tableDef collection.

and this:

is there any way to remove this asterik so that only "EXLA" string gets stored in the record

DoCmd.GoToRecord , , acNewRec

But this indicates changing on 1 or several records in your table, then a Replace is what you want.

Which is it???

JR

 

[leeza]

hey guys
Thanx for the help but I tried using the VB module tht's posted it didn't work so I used a simple upadate query with the Iff statement tht took care of all the * tht needed to be removed.

 

[leeza]

Now I have a different prob

I have 3 Tables - Frtheader, Receivable and Payable

The Inv no from Payable matches the Week end no in Frtheader
when I put condition "SRDODXP" or "CTSSPE"

The BOL no from payable matches the pro no in Receivable

Now I want to update the Receivable table field Inv no1 to the Inv no from Payable
How will I do it with update query?

I tried all the methods to upadate the records in Receivable by matching
pro no from receivable to BOL no in Frtheader condition "SRODXP"
BOL no from Payable to pro no in Receivable
combining both the conditions in the update query but to no avail

Pls help me

 

[leeza]

Now I have a different prob

I have 3 Tables - Frtheader, Receivable and Payable

The Inv no from Payable matches the Week end no in Frtheader
when I put condition "SRDODXP" or "CTSSPE"

The BOL no from payable matches the pro no in Receivable

Now I want to update the Receivable table field Inv no1 to the Inv no from Payable
How will I do it with update query?

I tried all the methods to upadate the records in Receivable by matching
pro no from receivable to BOL no in Frtheader condition "SRODXP"
BOL no from Payable to pro no in Receivable
combining both the conditions in the update query but to no avail

Pls help me

Guys never mind I found a way to retreive the info from both the tables and my module is completed
Thanks alot
Leeza