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.


Post your answer or comment

comments powered by Disqus
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.


Hi All - in a bit of a bind on this one so I could use a bit of help here!

I have an ID field in a db that is a combination of numeric and text put together. An example of what this looks like is this:

Letter P or L "-" Number "-" Number so... this ID could range from this:




Now - what I need to do is seperate this into sections so that I can do some searching on the different sections but I'm at a loss on how to do this!?!? I am pretty comfortable with the Mid(),Left(), and Right() functions but those require knowing the set number of spaces to move and select data from and as you can see from the example above the length of the ID can be anywhere from 5 characters to 11...? If I could break out the string by sections before and after the hyphens then I would be all set but.... again I dont know how...

Any/All help on this is really appreciated!


Hello Tech gurus,

I have a database with existing data, that is not normalized, and all the data is in one field... This what I am dealing with:

As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY on the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.

Thanks so much for your help...


I would like to have a event for the left and right arrow keys, does anyone know how to do this?


Here is me with my problems again. its been 48 houres without sleep and without solution. So i hope someone can help me in here like allways.
ok, this is bothering me:
I have two tables, [orderTable] and [closeTable]
I polulate [orderTable] by clicking on add button. that works fine.
now i want another button that will copy the data from [orderTable] to [closeTable], but if the item already egzist in [closeTable], i need to inner, left, or right join or append or what ever works (in docmd.runsql) with multiple criteria, I need to check if it is the same item and waiter, and only update the Quantity.
ill post some of non working code, so you have a better idea of what im trying to do

	Private Sub porosit_Click()
DoCmd.SetWarnings False
---ignore this part----
If (DCount("Qty", "[orderTables]", "[Table] = " & Me.tableNameLbl.Caption & " and [Waiter] = '" & Me.usertxts.Caption & "'")
= 0) Then
Me.labelmsg.Caption = "Nuk ka asgje per tu shtypur"
Me.labelmsg.Visible = True
If DCount("Qty", "[orderTables]", "[Table] = " & Me.tableNameLbl.Caption & " and [Waiter] = '" & Me.usertxts.Caption & "' and
[Type] = 'Pizza'") > 0 Then
DoCmd.OpenReport "KitchenRpt", acViewNormal, , , acWindowNormal
End If
If DCount("Qty", "[orderTables]", "[Table] = " & Me.tableNameLbl.Caption & " and [Waiter] = '" & Me.usertxts.Caption & "' and
[Type] = 'Drink'") > 0 Then
DoCmd.OpenReport "BarRpt", acViewNormal, , , acWindowNormal
End If
---- from here is where I cnat figure it out------
If (DCount("Qty", "[closeTables]", "[Table] = " & Me.tableNameLbl.Caption & " and [Waiter] = '" & Me.usertxts.Caption & "'")
= 0) Then
DoCmd.RunSQL "INSERT INTO closeTables select * FROM orderTables"
'DoCmd.RunSQL "UPDATE closeTables AS O RIGHT JOIN orderTables AS N ON O.Item = N.Item AND O.Table = N.Table AND O.Waiter =
N.Waiter O.Item = N.Item, O.Table = N.Table, O.Waiter = N.Waiter, O.[Qty] = (N.[Qty] + O.[Qty])"
'DoCmd.RunSQL "UPDATE orderTables LEFT JOIN closeTables ON orderTables.ID = closeTables.ID SET closeTables.Qty =
[closeTables].[Qty] + [orderTables].[Qty] SET"
'DoCmd.RunSQL "UPDATE orderTables INNER JOIN closeTables ON orderTables.Item = closeTables.Item SET closeTables.Qty =
[closeTables].[Qty] + [orderTables].[Qty]"
'DoCmd.RunSQL "UPDATE orderTables LEFT JOIN closeTables ON orderTables.Item = closeTables.Item SET closeTables.Qty =
[closeTables].[Qty] + [orderTables].[Qty] SET"
'DoCmd.RunSQL "UPDATE orderTables INNER JOIN closeTables ON orderTables.Item = closeTables.Item and orderTables.Table =
closeTables.Table and orderTables.Waiter = closeTables.Waiter SET closeTables.Qty = [closeTables].[Qty] +
'DoCmd.RunSQL "UPDATE orderTables INNER JOIN closeTables ON orderTables.Item = closeTables.Item SET closeTables.Qty =
[closeTables].[Qty] + [orderTables].[Qty]"
'DoCmd.RunSQL "UPDATE orderTables inner JOIN closeTables ON ((orderTables.Item = closeTables.Item) AND (orderTables.Table =
closeTables.Table) AND (orderTables.Waiter = closeTables.Waiter)) SET orderTables.[Qty] = [closeTables].[Qty] +
'If DCount("Qty", "[closeTables]", "[Table] = " & Me.tableNameLbl.Caption & " and [Waiter] = '" & Me.usertxts.Caption & "'")
= 0 Then
'DoCmd.RunSQL "INSERT INTO closeTables select * FROM orderTables"
'DoCmd.RunSQL "UPDATE orderTables LEFT JOIN closeTables ON orderTables.Item = closeTables.Item and orderTables.Table =
closeTables.Table SET closeTables.Qty = [closeTables].[Qty] + [orderTables].[Qty]"
DoCmd.RunSQL "UPDATE closeTables FROM orderTables [ LEFT | RIGHT ] JOIN closeTables ON orderTables.Qty = closeTables.Qty +
End If
End If
DoCmd.RunSQL "DELETE  * FROM orderTables where [Table] = " & Me.tableNameLbl.Caption & " and [Waiter] = '" &
Me.usertxts.Caption & "'"
End Sub

I really appreciate it, and thank you for your time

In my table I use the SSN format. In my query I want the SSN without the dashes. Could someone help with this. I have thought about using Left, Mid, Right, string function but can't find proper usage of these in queries.

Any help would be appreciated.

Not finding an answer? Try a Google search.