Usign right in an Access Query

[Reggie]

Hi,

I have a field called [add1] and a field called [postcode].

sample of the data in [Add1]

Crown Cottage C***n St R*** GT9 4RG
17 Rd Col****rth G****** GT3 5EW
104 High Street ***** PT1 4YE
104 High St ***** PT1 3WQ

Ok so i have like 2000 records like this. I wanted to just use a
simple update query to update the field postcode to equal .....

Right([Add1], 7)

Whenever I use Right, len, left or sanything like that I get a

"Compile error. in query expression 'Right([add1], 7)'"

I am using Access 2003 and have tried it on 2 different computers, the
reason i sya this is becuase some seem to say check your VBA
references. Unless im not looking in the right place they seem fine.

Tools -> References -> window displaying the following ticked options

Visual basic for applications
Microsoft Access 11.0 Object library
microsoft DAO 3.6 Object library
OLE automation
Microsoft ActiveX data Objects 2.1 library
Microsoft Word 11.0 object library.

Ok so thats it, I hope that I have described my issue in enough
detail.

Any help would be so gratefully appreciated Or its manually cutting
and pasting the last 7 characters from every line in table view :s

Thanks

Reggie

 

[Douglas J. Steele]

What's the actual SQL of your query? (If you're not familiar with SQL, open
the query in design view, and select View | SQL View from the menu to see
what SQL Access has generated for you)

 

[John W. Vinson]

Hi,

I have a field called [add1] and a field called [postcode].

sample of the data in [Add1]

Crown Cottage C***n St R*** GT9 4RG
17 Rd Col****rth G****** GT3 5EW
104 High Street ***** PT1 4YE
104 High St ***** PT1 3WQ

Ok so i have like 2000 records like this. I wanted to just use a
simple update query to update the field postcode to equal .....

Right([Add1], 7)

Whenever I use Right, len, left or sanything like that I get a

"Compile error. in query expression 'Right([add1], 7)'"

I am using Access 2003 and have tried it on 2 different computers, the
reason i sya this is becuase some seem to say check your VBA
references. Unless im not looking in the right place they seem fine.

Hrm. Try typing Ctrl-G and typing

?Right("abcde", 3)

in the Immediate window. Do you get "cde" or an error?

Is it possible that you have a user-written function named Right?

John W. Vinson [MVP]

 

[Reggie]

The SQL is

UPDATE Ipdb_reg_BEST SET Ipdb_reg_BEST.postcode = Right([add1],7);

I could give writing a UDF a shot no problem, but shouldnt this work?
Or am I missing something really stupid?

I do know SQL very well and have spent 5 years writing code so its
baffling to say the least!

 

[John W. Vinson]

The SQL is

UPDATE Ipdb_reg_BEST SET Ipdb_reg_BEST.postcode = Right([add1],7);

I could give writing a UDF a shot no problem, but shouldnt this work?
Or am I missing something really stupid?

I do know SQL very well and have spent 5 years writing code so its
baffling to say the least!

It certainly should work. Did the Immediate window test work?

John W. Vinson [MVP]