Left, Mid, Right Function

The statment below worked for me in this database while the database was in Access97. Now that I've converted the database to Access2000, this query no longer works, I get the message:

Undefined Function 'Left' in expression.

SSN: Left([DUES].[SSN],3)+Mid([DUES].[SSN],5,2)+Right([DUES].[SSN],4)

Can anyone tell me why?

Thanks for your help.


Sponsored Links:

Hi !

I use access 2000 (french) and runtime access 2000 (english) on XP.
After upgrading workstations with last microsoft security patchs, the use of left, mid or right functions in queries doesn't work anymore.
We know now that the patch acted on MDAC, is it the answer?
I don't know how to make it work.

If you have any idea, it would be great.


Hi all,
What reference do I need to check to use the left and right string functions.

I get

Compile Error:
Can't find project or library.

for now.

I'm using Access 2000.

Thanks all.

A2007. Default date format is day month year.

I have a text [Time Stamp] field with the data imported from an Excel spreadsheet in the form of "3/21/2012 8:14:52 PM" or "3/12/2012 8:52:11 AM" - the US format. (Attemps to change to a date format in Excel failed too - it seems Excel does not like the time included).

If converted during import from Excel the second example formats as the 3rd of December 12.

Conversion by changing the Data Type in the table to Date/Time after import or with a query recognises the first example as 21 March but leaves the second example as 3 December - in this case a date in the future.

Can anyone tell me how to change the text field contents to a Date/Time field please, accurately reflecting the true date? I have tried many examples/suggestions in this forum. Because the number of digits in the month and day can either be single or double I haven't been able to get the Left, Mid, Right function to work either.

Thanks in anticipation.

Is there a way to write a query where it will only capture numbers? For example, if I have the following:

"Testing transactions 11100202020 in the following order"

What I would like to do is capture the numbers that is in the middle of the sentence. I have tried using the len, left, mid, right functions in my expressions, but the sentence length may change from time to time. Is ther a way to tell access to only capture the numbers?

Thanks in advance

Please help!! I've got a table that was imported from Excel. The Name field in the table has the following format: Last Name, First Name. I need a query that will trim the last name up to the comma so that I can populate a new field last name only. I can use the LEFT, MID and RIGHT functions when I define the number of characters that I want but I don't know how to go LEFT until I reach the comma. Can anyone help?

I have written an app. using Access. In the forms i used functions such as Left, Right, Mid, format, etc. In my machine they are function well. But when i deploy it to the end user these functions don't work.

Then i created a new access app. on end user's machines and test for these functions (test it in the debugging window), they all work! how can this happen?


I have a query :
pol: IIf(Right(CStr(CInt(Mid([EGN_zastrahovasht];9;1))/2);1)="5";'';'')

but when i try to start it access give to me "Undefined Right function"
Any idea!!!

Hi All,
I need your help on this.
In my Access 97 m/c, when i am running a query as
select * from tbl_xxx where LEFT(field1,10) = "text 1" and right(field2,10)="text 2".

While running i am getting the Error as :

3075 function is not availabale in expressions in query expression LEFT(field,10) = "text 1"

Please can you let me know what are the libraries in the references i should add to get rid of this error.

Thanks a lot for all your help in advance.

I have an excel spreadsheet with text in one of the cells in the follow format:

Nrecs: 19
Nrecs: 192
Nrecs: 2219
Nrecs: 33319

Nrecs: Stands for the No of records (or rows) that contain data.

How do I strip the Nrecs: to leave just the number ?

I have tried all variants of the left and right functions without success.


I have a text field which i need to split in a query. I have just converted the query from 97 to 2000 and im getting an error message saying "Undefined function 'Left' in expression".

The expression i have is:

Test: Left([Field1],4)

Any help would be gratefull, as its doing my head in.


Please tell me, is there a way to get records to show Left-To-Right instead of up to down on a report? I have a query that pulls only 5 records, and it's listing those 5 records for each heading all the way down the page. I just want a few lines with the 5 numbers across the page. I looked in my page setup and there's a section to set the columns, but most of it is grayed out. Help!

I have a report in Access. It its Detail section, there is a control that lists tables. The report prints just fine, listing each table after another down the page. However, these tables take up a lot of space going down the page, looking something like this:

[Lots of blank space]GA086[Lots of blank space]
[Lots of blank space]GA088[Lots of blank space]
[Lots of blank space]GA089[Lots of blank space]
[Lots of blank space]GA092[Lots of blank space]

My question is: Is there a way to have them listed left to right instead?

Thank you,

Steve Geller

I'm having a problem with my lables defaulting to printing up and down. I use the wizard to create a labels sheet and everything works great prints left to right and then all of a sudden i go back in there and the labels are up and down. Cant figure out what is wrong with this i need help asap. Please anyone!!


I would like to have a report that pulls the data, and will display on the screen from left to right:

At current, this is all I get: (just sample data)

Bowen Hills depot
MAN Diesel 100
MAN Diesel 101
MAN Diesel 102
Volvo Diesel 200
Volvo Diesel 202

Carina depot
MAN Diesel 106
MAN Diesel 109
Scania CNG A/C W/C 300
Scania CNG A/C W/C 303
Scania CNG A/C W/C 308
Scania CNG A/C W/C 309
Volvo Diesel 205
Volvo Diesel 208
Volvo Diesel 209

What i'm wanting is this:

Bowen Hills depot
MAN Diesel 100 101 102
Volvo Diesel 200 202

Carina depot
MAN Diesel 106 109
Scania CNG A/C W/C 300 303 308 309
Volvo Diesel 205 208 209

So in other words, when the report comes through, it picks out all the depots and only lists them once for each heading, then lists each chassis type and only lists them once for each depot, and then lists each fleet # beside each other going from left to right then down to the next row and continueing.

I hope what i'm looking for makes sense!


I was wondering if there is a way to do a section (like detail) that prints multiple sections left to right instead of top to bottom. more like grid format. I've got some contact information, and I like formatting in a:


set up, each on their own line, but i can fit 3 contacts in three lines, just formatted left to right. once it hits 4 contacts, it should expand the section to a new "detail" group or what-not, expanding from 3 lines to 6 lines, etc.

Is this possible?
Jaeden "Sifo Dyas" al'Raec Ruiner

I am using the Right function in a text box in a report to return the characters from the right side of a string:
Right(string, length)

This has worked in another exact copy of the report in a different database (all running Access 2003) but I am now getting an error message (still works in the other versions on the same computer):The function you entered can't be used in this expression.
* You may have used an aggregate function, such as Count, in a design grid or in a calculated control or field.

I'm pretty sure it's the Right function that is the problem. Any ideas?



I'm new here and would like to ask a question regarding reports for Access 2007.

Is it possible to generate reports that display in spreadsheet format?
I'm creating a database that track KPIs over a period of time. I'm planning to generate a report that shows a timeline of it
Now, what I can think of is to generate a report that shows it from top to bottom.

For example, it would be

But, what I want my report to be is to display it from left to right, something like in excel, so it would be something like:

KPI.......Achieved.......Date............Achieved. ......Date
1 .........10...............1/2/2013 ......12...............1/3/2013
2 ..........9.................1/1/2013

Any help will be greatly appreciated!

Thanks in advance!

Hello Folks
I need to trim values in field that has the following type of format 0001.01,0001.02. Apparently there is a way of doing it by using the Microsoft Access RIGHT function based on the length(LEN) of each string.
Can anyone explain how this Microsoft Access RIGHT function works and how I would go about using it to trim my fields as noted above.


I've converted an ivt file to excel and need to trim some values(drop some zeros) in a column before exporting to access.
The cells in my column look like:
0001.01, I need 1.01
0001.02, I need 1.02
0017.01, I need 17.02
0220.01, I need 220.01

Assuming 0001.01 was cell A2. I believe using an excel right function such as =RIGHT(A2,4) would work but I don't know how to do the rest ie: 0017.01 etc...0220.01 etc...

Is it possible to construct 1 right function that would convert all the cells regardless of the original string length or does it have to be done individually?

Apparently an algorithm is supposed to work but I have no idea what an algorithm is or how to construct one to achieve the required results. I would prefer to stick with excel right functions if possible.
Any tips would be greatly appreciated.