Trouble with my data parsing

[Gina]

I am trying to clean up some data in a field. Some of the field
records have characters at the end of it which are enclosed in square
brackets thus:

The quick brown fox [ABC 321]

I am using a VBA code to strip out everything to the left of the first
square bracket and overwrite the field. I can't get the code to work
so have extracted the code into query mode for testing.

The first part, identifying those records which contain the " [ "
works OK

SELECT DISTINCTROW tblImport.fldFilmTitle
FROM tblImport
WHERE (((tblImport.fldFilmTitle) Like "*[[]*"));

but when I change this to the update query as follows:

UPDATE DISTINCTROW tblImport SET tblImport.fldFilmTitle =
Left("fldFilmTitle",InStr(1,"fldFilmTitle",'[[]')-1)
WHERE (((tblImport.fldFilmTitle) Like "*[[]*"));

I get an invalid procedure call. Where is my syntax going wrong?


Gina

 

[Stuart McCall]

I am trying to clean up some data in a field. Some of the field
records have characters at the end of it which are enclosed in square
brackets thus:

The quick brown fox [ABC 321]

I am using a VBA code to strip out everything to the left of the first
square bracket and overwrite the field. I can't get the code to work
so have extracted the code into query mode for testing.

The first part, identifying those records which contain the " [ "
works OK

SELECT DISTINCTROW tblImport.fldFilmTitle
FROM tblImport
WHERE (((tblImport.fldFilmTitle) Like "*[[]*"));

but when I change this to the update query as follows:

UPDATE DISTINCTROW tblImport SET tblImport.fldFilmTitle =
Left("fldFilmTitle",InStr(1,"fldFilmTitle",'[[]')-1)
WHERE (((tblImport.fldFilmTitle) Like "*[[]*"));

I get an invalid procedure call. Where is my syntax going wrong?


Gina

Remove the quotes surrounding both instances of fldFilmTitle. Use of quotes
means that the expression interpreter will treat the value as a literal
string of characters, whereas what you want is the field's value.

 

[Gina]

I am trying to clean up some data in a field.  Some of the field
records have characters at the end of it which are enclosed in square
brackets thus:

The quick brown fox [ABC 321]

I am using a VBA code to strip out everything to the left of the first
square bracket and overwrite the field. I can't get the code to work
so have extracted the code into query mode for testing.

The first part, identifying those records which contain the " [ "
works OK

SELECT DISTINCTROW tblImport.fldFilmTitle
FROM tblImport
WHERE (((tblImport.fldFilmTitle) Like "*[[]*"));

but when I change this to the update query as follows:

UPDATE DISTINCTROW tblImport SET tblImport.fldFilmTitle =
Left("fldFilmTitle",InStr(1,"fldFilmTitle",'[[]')-1)
WHERE (((tblImport.fldFilmTitle) Like "*[[]*"));

I get an invalid procedure call.  Where is my syntax going wrong?

Gina

Remove the quotes surrounding both instances of fldFilmTitle. Use of quotes
means that the expression interpreter will treat the value as a literal
string of characters, whereas what you want is the field's value.- Hide quoted text -

- Show quoted text -

Thanks Stuart,

Don't know how they got in there ! . Anyway, making that adjustment
eliminates the invalid procedure call but there is obviously still a
problem with the code because instead of updating the field, it erases
the contents of those records which contain the " [ ". Can you see
what is wrong with the code?

Gina

 

[Stuart McCall]

I am trying to clean up some data in a field. Some of the field
records have characters at the end of it which are enclosed in square
brackets thus:

The quick brown fox [ABC 321]

I am using a VBA code to strip out everything to the left of the first
square bracket and overwrite the field. I can't get the code to work
so have extracted the code into query mode for testing.

The first part, identifying those records which contain the " [ "
works OK

SELECT DISTINCTROW tblImport.fldFilmTitle
FROM tblImport
WHERE (((tblImport.fldFilmTitle) Like "*[[]*"));

but when I change this to the update query as follows:

UPDATE DISTINCTROW tblImport SET tblImport.fldFilmTitle =
Left("fldFilmTitle",InStr(1,"fldFilmTitle",'[[]')-1)
WHERE (((tblImport.fldFilmTitle) Like "*[[]*"));

I get an invalid procedure call. Where is my syntax going wrong?

Gina

Remove the quotes surrounding both instances of fldFilmTitle. Use of
quotes
means that the expression interpreter will treat the value as a literal
string of characters, whereas what you want is the field's value.- Hide
quoted text -

- Show quoted text -

Thanks Stuart,

Don't know how they got in there ! . Anyway, making that adjustment
eliminates the invalid procedure call but there is obviously still a
problem with the code because instead of updating the field, it erases
the contents of those records which contain the " [ ". Can you see
what is wrong with the code?

Gina

Too many brackets in your instr expression. Change the code to this:

UPDATE DISTINCTROW tblImport SET tblImport.fldFilmTitle =
Left(fldFilmTitle,InStr(1,fldFilmTitle,'[')-1)
WHERE (((tblImport.fldFilmTitle) Like "*[[]*"));

Didn't spot that last time, sorry.

 

[Gina]

news:[email protected]...

I am trying to clean up some data in a field. Some of the field
records have characters at the end of it which are enclosed in square
brackets thus:
The quick brown fox [ABC 321]
I am using a VBA code to strip out everything to the left of the first
square bracket and overwrite the field. I can't get the code to work
so have extracted the code into query mode for testing.
The first part, identifying those records which contain the " [ "
works OK
SELECT DISTINCTROW tblImport.fldFilmTitle
FROM tblImport
WHERE (((tblImport.fldFilmTitle) Like "*[[]*"));
but when I change this to the update query as follows:
UPDATE DISTINCTROW tblImport SET tblImport.fldFilmTitle =
Left("fldFilmTitle",InStr(1,"fldFilmTitle",'[[]')-1)
WHERE (((tblImport.fldFilmTitle) Like "*[[]*"));
I get an invalid procedure call. Where is my syntax going wrong?
Gina

Remove the quotes surrounding both instances of fldFilmTitle. Use of
quotes
means that the expression interpreter will treat the value as a literal
string of characters, whereas what you want is the field's value.- Hide
quoted text -

- Show quoted text -
Thanks Stuart,

Don't know how they got in there ! .  Anyway, making that adjustment
eliminates the invalid procedure call but there is obviously still a
problem with the code because instead of updating the field, it erases
the contents of those records which contain the " [ ".  Can you see
what is wrong with the code?

Gina

Too many brackets in your instr expression. Change the code to this:

UPDATE DISTINCTROW tblImport SET tblImport.fldFilmTitle =
Left(fldFilmTitle,InStr(1,fldFilmTitle,'[')-1)
WHERE (((tblImport.fldFilmTitle) Like "*[[]*"));

Didn't spot that last time, sorry.- Hide quoted text -

- Show quoted text -

Hey Stuart,

Excellent. Thank you so much.

Gina

 

[Mike Painter]

I am trying to clean up some data in a field. Some of the field
records have characters at the end of it which are enclosed in square
brackets thus:

The quick brown fox [ABC 321]

Looks like your porblem has been solved but two suggestions.

1. If this is a one shot deal a search and replace on the table will take
less time than writing code.

2. If not then check out the Replace function. While not nearly as powerful
as it should be it is a whole lot better than longer codes.

It probably comes from Pick BASIC but they left off the good parts.

Convert "[]" to "" in YourFieldName
gets them all and is how you write the code..

 

[Stuart McCall]

I am trying to clean up some data in a field. Some of the field
records have characters at the end of it which are enclosed in square
brackets thus:

The quick brown fox [ABC 321]

Looks like your porblem has been solved but two suggestions.

1. If this is a one shot deal a search and replace on the table will take
less time than writing code.

2. If not then check out the Replace function. While not nearly as
powerful as it should be it is a whole lot better than longer codes.

It probably comes from Pick BASIC but they left off the good parts.

Convert "[]" to "" in YourFieldName
gets them all and is how you write the code..

Mike

I would like to know how to strip [ABC 123] or [XYZ 321] etc. from the end
of a string using either search & replace or the replace function.

 

[Mike Painter]

I am trying to clean up some data in a field. Some of the field
records have characters at the end of it which are enclosed in
square brackets thus:

The quick brown fox [ABC 321]

Looks like your porblem has been solved but two suggestions.

1. If this is a one shot deal a search and replace on the table will
take less time than writing code.

2. If not then check out the Replace function. While not nearly as
powerful as it should be it is a whole lot better than longer codes.

It probably comes from Pick BASIC but they left off the good parts.

Convert "[]" to "" in YourFieldName
gets them all and is how you write the code..

Mike

I would like to know how to strip [ABC 123] or [XYZ 321] etc. from
the end of a string using either search & replace or the replace
function.

Do you want
1. The long technical version, or
2. the one where I admit I thought the idea was to get rid of the open and
close square brackets, or
3. the one wher I blame it all on my cat?

I probably would have written a function that used A(0) to return the
value.

Function SomeCleverName (InputString)
Dim A() as String

A = Split(Inputstring,"[")
SomeCleverName = A(0)
end function.

 

[Stuart McCall]

Gina wrote:
I am trying to clean up some data in a field. Some of the field
records have characters at the end of it which are enclosed in
square brackets thus:

The quick brown fox [ABC 321]

Looks like your porblem has been solved but two suggestions.

1. If this is a one shot deal a search and replace on the table will
take less time than writing code.

2. If not then check out the Replace function. While not nearly as
powerful as it should be it is a whole lot better than longer codes.

It probably comes from Pick BASIC but they left off the good parts.

Convert "[]" to "" in YourFieldName
gets them all and is how you write the code..

Mike

I would like to know how to strip [ABC 123] or [XYZ 321] etc. from
the end of a string using either search & replace or the replace
function.

Do you want
1. The long technical version, or
2. the one where I admit I thought the idea was to get rid of the open and
close square brackets, or
3. the one wher I blame it all on my cat?

I probably would have written a function that used A(0) to return the
value.

Function SomeCleverName (InputString)
Dim A() as String

A = Split(Inputstring,"[")
SomeCleverName = A(0)
end function.

Lol!

I think version 3 is the one to go for - the cat always deserves the blame.