Expression contains invalid syntax

S

student

I'm trying to use this code below I saw on this website. When I paste the
code in the column just to the right of the column that contains the words I
want to replace I get this:

The expression you entered contains invalid sytax.
You may have entered an operand without an operator.

Code: 
ORIGINAL:

UPDATE table INNER JOIN Words
ON field Like "*" & Words.Word & "*"
SET field = Replace(field, Words.Word, Words.Replacement)

MODIFIED FOR TESTING:

UPDATE TestTable INNER JOIN Words
ON RealAudioLink Like "*" & Words.Word & "*"
SET field = Replace(RealAudioLink, Words.Word, Words.Replacement)

Per the instructions I created an Access Table called Words. I has one
column called RealAudioLink. I entered one record into this table to test.
The data entered was: wkdy20070416-a.rm.

What I'm trying to do here is do a massive word replace in a large table
called T_Programs_TestTable. It contains over 11,000 records. Specifically in
the RealAudioLink column I have many wkdy references. The problem here is
that they are all listed by day, month and year.

Sample of real data:

wkdy20070416-a.rm (needs to be WeekdayA20070416.rm)
wkdy20070416-b.rm (needs to be WeekdayB20070416.rm )
conv20061120.rm (needs to be conversation20061120.rm)

So I need to replace all instances of wkdy with Weekday, get rid of the dash
and put a capital A or B after the word Weekday in the filename.

How do I do this in Access?
 
J

Jerry Whittle

Replacing wkdy with Weekday or conv with conversation is easy. Check out the
Replace function in Help.

As for the rest, something convoluted like this might work in a query:

SwapAround: IIf(InStr("wkdy20070416-a.rm ","-") > 0,
Left("wkdy20070416-a.rm",InStr("wkdy20070416-a.rm","2")-1) &
UCase(Mid("wkdy20070416-a.rm",InStr("wkdy20070416-a.rm","-")+1,1)) &
Mid("wkdy20070416-a.rm",InStr("wkdy20070416-a.rm","2"),InStr("wkdy20070416-a.rm","-")-InStr("wkdy20070416-a.rm","2"))& ".rm", "wkdy20070416-a.rm" )

Before putting the above in a query, change all the "wkdy20070416-a.rm" to
the actual name of the field without the double-quotes.
 
S

student

In your suggestion...

Before putting the above in a query, change all the "wkdy20070416-a.rm" to
the actual name of the field without the double-quotes.

Did you mean replace wkdy20070416-a.rm with what I really want it to say
which would be WeekdayA20070416.rm and keep the quotes?
--
Technical School Studen


Jerry Whittle said:
Replacing wkdy with Weekday or conv with conversation is easy. Check out the
Replace function in Help.

As for the rest, something convoluted like this might work in a query:

SwapAround: IIf(InStr("wkdy20070416-a.rm ","-") > 0,
Left("wkdy20070416-a.rm",InStr("wkdy20070416-a.rm","2")-1) &
UCase(Mid("wkdy20070416-a.rm",InStr("wkdy20070416-a.rm","-")+1,1)) &
Mid("wkdy20070416-a.rm",InStr("wkdy20070416-a.rm","2"),InStr("wkdy20070416-a.rm","-")-InStr("wkdy20070416-a.rm","2"))& ".rm", "wkdy20070416-a.rm" )

Before putting the above in a query, change all the "wkdy20070416-a.rm" to
the actual name of the field without the double-quotes.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


student said:
I'm trying to use this code below I saw on this website. When I paste the
code in the column just to the right of the column that contains the words I
want to replace I get this:

The expression you entered contains invalid sytax.
You may have entered an operand without an operator.

Code: 
ORIGINAL:

UPDATE table INNER JOIN Words
ON field Like "*" & Words.Word & "*"
SET field = Replace(field, Words.Word, Words.Replacement)

MODIFIED FOR TESTING:

UPDATE TestTable INNER JOIN Words
ON RealAudioLink Like "*" & Words.Word & "*"
SET field = Replace(RealAudioLink, Words.Word, Words.Replacement)

Per the instructions I created an Access Table called Words. I has one
column called RealAudioLink. I entered one record into this table to test.
The data entered was: wkdy20070416-a.rm.

What I'm trying to do here is do a massive word replace in a large table
called T_Programs_TestTable. It contains over 11,000 records. Specifically in
the RealAudioLink column I have many wkdy references. The problem here is
that they are all listed by day, month and year.

Sample of real data:

wkdy20070416-a.rm (needs to be WeekdayA20070416.rm)
wkdy20070416-b.rm (needs to be WeekdayB20070416.rm )
conv20061120.rm (needs to be conversation20061120.rm)

So I need to replace all instances of wkdy with Weekday, get rid of the dash
and put a capital A or B after the word Weekday in the filename.

How do I do this in Access?
Click to expand...
Click to expand...
 
J

Jerry Whittle

First and foremost, practice on a copy of the table or database first as what
I'm telling you to do could trash it.

Replace all the "wkdy20070416-a.rm"s with the name of the field where you
find that data. Put square brackets [ ] around the field name especially if
it has any spaces or special characters.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

student said:
In your suggestion...

Before putting the above in a query, change all the "wkdy20070416-a.rm" to
the actual name of the field without the double-quotes.

Did you mean replace wkdy20070416-a.rm with what I really want it to say
which would be WeekdayA20070416.rm and keep the quotes?
--
Technical School Studen


Jerry Whittle said:
Replacing wkdy with Weekday or conv with conversation is easy. Check out the
Replace function in Help.

As for the rest, something convoluted like this might work in a query:

SwapAround: IIf(InStr("wkdy20070416-a.rm","-") > 0,
Left("wkdy20070416-a.rm",InStr("wkdy20070416-a.rm","2")-1) &
UCase(Mid("wkdy20070416-a.rm",InStr("wkdy20070416-a.rm","-")+1,1)) &
Mid("wkdy20070416-a.rm",InStr("wkdy20070416-a.rm","2"),InStr("wkdy20070416-a.rm","-")-InStr("wkdy20070416-a.rm","2"))& ".rm", "wkdy20070416-a.rm" )

Before putting the above in a query, change all the "wkdy20070416-a.rm" to
the actual name of the field without the double-quotes.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


student said:
I'm trying to use this code below I saw on this website. When I paste the
code in the column just to the right of the column that contains the words I
want to replace I get this:

The expression you entered contains invalid sytax.
You may have entered an operand without an operator.

Code: 
ORIGINAL:

UPDATE table INNER JOIN Words
ON field Like "*" & Words.Word & "*"
SET field = Replace(field, Words.Word, Words.Replacement)

MODIFIED FOR TESTING:

UPDATE TestTable INNER JOIN Words
ON RealAudioLink Like "*" & Words.Word & "*"
SET field = Replace(RealAudioLink, Words.Word, Words.Replacement)

Per the instructions I created an Access Table called Words. I has one
column called RealAudioLink. I entered one record into this table to test.
The data entered was: wkdy20070416-a.rm.

What I'm trying to do here is do a massive word replace in a large table
called T_Programs_TestTable. It contains over 11,000 records. Specifically in
the RealAudioLink column I have many wkdy references. The problem here is
that they are all listed by day, month and year.

Sample of real data:

wkdy20070416-a.rm (needs to be WeekdayA20070416.rm)
wkdy20070416-b.rm (needs to be WeekdayB20070416.rm )
conv20061120.rm (needs to be conversation20061120.rm)

So I need to replace all instances of wkdy with Weekday, get rid of the dash
and put a capital A or B after the word Weekday in the filename.

How do I do this in Access?
Click to expand...
Click to expand...
Click to expand...
 
Top