replacing two characters in string

[Bob Bevins]

Hi all,

I am finding it difficult to replace the last two characters in a field.
Actually, inserting 20 in front of the last two characters in a field.


dat in field is like:

010404

I need to change it to:
01042004

Their is about 854 records in this table.

I tried with the rplace function in access and I cannot get it to work.
I tried right$ with replace and I cannot figure it out.
Could someone help me please?

Bob

 

[Dirk Goldgar]

Hi all,

I am finding it difficult to replace the last two characters in a
field.
Actually, inserting 20 in front of the last two characters in a field.


dat in field is like:

010404

I need to change it to:
01042004

Their is about 854 records in this table.

I tried with the rplace function in access and I cannot get it to
work.
I tried right$ with replace and I cannot figure it out.
Could someone help me please?

Is the text in every field the same length, 6 characters? If so, you
can probably use an update query like this:

UPDATE YourTable
SET YourField =
Left(YourField, 4) & "20" & Right(YourField, 2)
WHERE YourField Is Not Null;

 

[Bob Bevins]

WOW,

That worked great.

I found another problem. These are dates I am trying to fix and import into
another type of db.
It is actually a text field, so I have to manipulate them with code.

they have the day first and the month second and I need it the other way
around.
re:
actual: dd/mm/yyyy
required: mm/dd/yyyy

any suggestions on this one?

Thanks again,

bob

 

[Dirk Goldgar]

WOW,

That worked great.

I found another problem. These are dates I am trying to fix and
import into another type of db.
It is actually a text field, so I have to manipulate them with code.

they have the day first and the month second and I need it the other
way
around.
re:
actual: dd/mm/yyyy
required: mm/dd/yyyy

any suggestions on this one?

I'm not sure what you mean by "import into another type of db". If you
wanted to update the fields in place, changing them from "ddmmyyyy" to
"mmddyyyy" (no slashes), then you could use:

UPDATE MyTable
SET MyField =
Mid(MyField, 3, 2) &
Left(MyField, 2) &
Right(MyField, 4)
WHERE MyField Is Not Null;

If you want to insert slashes, too -- but the original value doesn't
have slashes -- you could use this instead:

UPDATE MyTable
SET MyField =
Mid(MyField, 3, 2) & "/" &
Left(MyField, 2) & "/" &
Right(MyField, 4)
WHERE MyField Is Not Null;

If you want to convert to an actual date/time data type -- which would
have to be stored in a different field -- I'd have to know more about
what you wanted in order to make a reasonable suggestion.