Expression using RIGHT and LEN functions

[Marlene]

I have a table with a text field named Board 2005. It has data like "2005
treasurer" and "2005 librarian". I won't go into why the year has to be
inside the field and not a separate field, but that's the way it has to be.

In a query, I want to create a calculated field that will pull all data from
the above field except for the first five characters. The simplest way seemed
to be:

current_board_no_year: Right([board 2005],Len([board 2005]-5))

My formula is accepted in the sense that I am able to leave the cell without
generating an error, but when I try to run the query, I get the following
message:

Undefined function 'right'in expression.

I've looked & looked but don't understand what's wrong.

Advice, anyone?

 

[Jörg Ackermann]

I have a table with a text field named Board 2005. It has data like
"2005 treasurer" and "2005 librarian". I won't go into why the year
has to be inside the field and not a separate field, but that's the
way it has to be.

In a query, I want to create a calculated field that will pull all
data from the above field except for the first five characters. The
simplest way seemed to be:

current_board_no_year: Right([board 2005],Len([board 2005]-5))

My formula is accepted in the sense that I am able to leave the cell
without generating an error, but when I try to run the query, I get
the following message:

Undefined function 'right'in expression.

I've looked & looked but don't understand what's wrong.

Check the references.
Open the VBA-Editor and select Tools - References in menu.
See if there are 'Not found...' entries and remove it,

Acki

 

[fredg]

I have a table with a text field named Board 2005. It has data like "2005
treasurer" and "2005 librarian". I won't go into why the year has to be
inside the field and not a separate field, but that's the way it has to be.

In a query, I want to create a calculated field that will pull all data from
the above field except for the first five characters. The simplest way seemed
to be:

current_board_no_year: Right([board 2005],Len([board 2005]-5))

My formula is accepted in the sense that I am able to leave the cell without
generating an error, but when I try to run the query, I get the following
message:

Undefined function 'right'in expression.

I've looked & looked but don't understand what's wrong.

Advice, anyone?

You have a reference problem.

That PC has a missing reference.
Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

In addition, after you fix the above reference, you can simplify your
expression by using the Mid() function:

current_board_no_year: Mid([board 2005],6)