Expression contains invalid syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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...
 
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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

what is wrong with the following SQL syntax using "CONTAINS"? 2
invalid syntax... 3
invalid syntax 2
correct syntax like or contains ?? 8
Subquery Issue 1 5
Syntax error in query expression 2
Performing expression on all (varying number of) fields 3
expression contains invalid syntax (filtering for "IN") 2

Back
Top