Error 3085 – ‘Undefined function ‘mid’ in expression.’ occurred in

[jomuir]

I have an Access database that we use to export files in the correct format,
and this database is on a server (3-4 of us run these exports)

I just got someone else’s pc after they left as it had other software on it
that I need to continue some of his roles. The only problem being that when I
export now it fails with the following error:-

Error 3085 – ‘Undefined function ‘mid’ in expression.’ occurred in…..

I have created my own database and made a query to test the mid
functionality and it work.
I have looked at the Reference Library and have nothing listed as Missing:
I have the following ticked:-
Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
I tried unchecking the all (only the bottom 3 would) saving it closing down
then rechecking them, this did not work

Not sure what to try now?

Details:-
PC – Windows XP Professional SP3
Access 2003 (11.8166.8221) SP3
Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) .NET Framework
Version 2.0.50727 SP1

 

[Douglas J. Steele]

See whether using VBA.Mid instead of Mid makes a difference. (note that
there's a period between VBA and Mid)

 

[jomuir]

A simple database with field test1, and a query:-

Expr1: Mid([test1],2,1) - Worked

Expr2: VBA.Mid([test1],2,1) - Undefined function 'VBA.Mid' in expression.

Is this what you mean?

 

[Douglas J. Steele]

Yes, that's what I meant. Unfortunately, I didn't bother testing that it
would, in fact, work if used directly in a query.

Try creating a wrapper function:

Function MyMid(InputString As String, Start As Long, Optional Length As Long
= -1) As String

Dim strLength As Long

If Length < 0 Then
strLength = Len(InputString)
Else
strLength = Length
End If

MyMid = VBA.Mid(InputString, Start, strLength)

End Function

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

A simple database with field test1, and a query:-

Expr1: Mid([test1],2,1) - Worked

Expr2: VBA.Mid([test1],2,1) - Undefined function 'VBA.Mid' in expression.

Is this what you mean?

See whether using VBA.Mid instead of Mid makes a difference. (note that
there's a period between VBA and Mid)

 

[jomuir]

Sorry, I am not sure where/what I should do with this function, I put it in
the qyery and it would not run.

Not sure if this helps, but I opened the mde, and pressed Ctrl G and in the
Immediate window type ?Application.BrokenReference and hit return, the
response I got was 'True'

All other users get 'False'

Yes, that's what I meant. Unfortunately, I didn't bother testing that it
would, in fact, work if used directly in a query.

Try creating a wrapper function:

Function MyMid(InputString As String, Start As Long, Optional Length As Long
= -1) As String

Dim strLength As Long

If Length < 0 Then
strLength = Len(InputString)
Else
strLength = Length
End If

MyMid = VBA.Mid(InputString, Start, strLength)

End Function

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

A simple database with field test1, and a query:-

Expr1: Mid([test1],2,1) - Worked

Expr2: VBA.Mid([test1],2,1) - Undefined function 'VBA.Mid' in expression.

Is this what you mean?

See whether using VBA.Mid instead of Mid makes a difference. (note that
there's a period between VBA and Mid)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have an Access database that we use to export files in the correct
format,
and this database is on a server (3-4 of us run these exports)

I just got someone else's pc after they left as it had other software
on
it
that I need to continue some of his roles. The only problem being that
when I
export now it fails with the following error:-

Error 3085 - 'Undefined function 'mid' in expression.' occurred in...

I have created my own database and made a query to test the mid
functionality and it work.
I have looked at the Reference Library and have nothing listed as
Missing:
I have the following ticked:-
Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
I tried unchecking the all (only the bottom 3 would) saving it closing
down
then rechecking them, this did not work

Not sure what to try now?

Details:-
PC - Windows XP Professional SP3
Access 2003 (11.8166.8221) SP3
Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) .NET
Framework
Version 2.0.50727 SP1

 

[Douglas J. Steele]

I thought you'd said you'd checked the references and none were marked as
missing. Did you do that on the workstation where the problem exists, or on
your machine?

Regarding my previous suggestion, you'd put that function into a module (not
a class module, nor a module associated with a form or report), then use the
MyMid function in your query where you currently use the Mid function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry, I am not sure where/what I should do with this function, I put it
in
the qyery and it would not run.

Not sure if this helps, but I opened the mde, and pressed Ctrl G and in
the
Immediate window type ?Application.BrokenReference and hit return, the
response I got was 'True'

All other users get 'False'

Yes, that's what I meant. Unfortunately, I didn't bother testing that it
would, in fact, work if used directly in a query.

Try creating a wrapper function:

Function MyMid(InputString As String, Start As Long, Optional Length As
Long
= -1) As String

Dim strLength As Long

If Length < 0 Then
strLength = Len(InputString)
Else
strLength = Length
End If

MyMid = VBA.Mid(InputString, Start, strLength)

End Function

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

A simple database with field test1, and a query:-

Expr1: Mid([test1],2,1) - Worked

Expr2: VBA.Mid([test1],2,1) - Undefined function 'VBA.Mid' in
expression.

Is this what you mean?

:

See whether using VBA.Mid instead of Mid makes a difference. (note
that
there's a period between VBA and Mid)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have an Access database that we use to export files in the correct
format,
and this database is on a server (3-4 of us run these exports)

I just got someone else's pc after they left as it had other
software
on
it
that I need to continue some of his roles. The only problem being
that
when I
export now it fails with the following error:-

Error 3085 - 'Undefined function 'mid' in expression.' occurred
in...

I have created my own database and made a query to test the mid
functionality and it work.
I have looked at the Reference Library and have nothing listed as
Missing:
I have the following ticked:-
Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
I tried unchecking the all (only the bottom 3 would) saving it
closing
down
then rechecking them, this did not work

Not sure what to try now?

Details:-
PC - Windows XP Professional SP3
Access 2003 (11.8166.8221) SP3
Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) .NET
Framework
Version 2.0.50727 SP1

 

[jomuir]

I have check the references on my pc, and all my databases...but not this one
(it is grayed out on this database, so I cannot view them). Since I am the
only user with this problem I presumed it could not be the databases
references, but I do not know, and should not presume.

I thought you'd said you'd checked the references and none were marked as
missing. Did you do that on the workstation where the problem exists, or on
your machine?

Regarding my previous suggestion, you'd put that function into a module (not
a class module, nor a module associated with a form or report), then use the
MyMid function in your query where you currently use the Mid function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry, I am not sure where/what I should do with this function, I put it
in
the qyery and it would not run.

Not sure if this helps, but I opened the mde, and pressed Ctrl G and in
the
Immediate window type ?Application.BrokenReference and hit return, the
response I got was 'True'

All other users get 'False'

Yes, that's what I meant. Unfortunately, I didn't bother testing that it
would, in fact, work if used directly in a query.

Try creating a wrapper function:

Function MyMid(InputString As String, Start As Long, Optional Length As
Long
= -1) As String

Dim strLength As Long

If Length < 0 Then
strLength = Len(InputString)
Else
strLength = Length
End If

MyMid = VBA.Mid(InputString, Start, strLength)

End Function

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


A simple database with field test1, and a query:-

Expr1: Mid([test1],2,1) - Worked

Expr2: VBA.Mid([test1],2,1) - Undefined function 'VBA.Mid' in
expression.

Is this what you mean?

:

See whether using VBA.Mid instead of Mid makes a difference. (note
that
there's a period between VBA and Mid)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have an Access database that we use to export files in the correct
format,
and this database is on a server (3-4 of us run these exports)

I just got someone else's pc after they left as it had other
software
on
it
that I need to continue some of his roles. The only problem being
that
when I
export now it fails with the following error:-

Error 3085 - 'Undefined function 'mid' in expression.' occurred
in...

I have created my own database and made a query to test the mid
functionality and it work.
I have looked at the Reference Library and have nothing listed as
Missing:
I have the following ticked:-
Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
I tried unchecking the all (only the bottom 3 would) saving it
closing
down
then rechecking them, this did not work

Not sure what to try now?

Details:-
PC - Windows XP Professional SP3
Access 2003 (11.8166.8221) SP3
Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) .NET
Framework
Version 2.0.50727 SP1

 

[jomuir]

I have added that function in, and it works on my test db query.

I have check the references on my pc, and all my databases...but not this one
(it is grayed out on this database, so I cannot view them). Since I am the
only user with this problem I presumed it could not be the databases
references, but I do not know, and should not presume.

I thought you'd said you'd checked the references and none were marked as
missing. Did you do that on the workstation where the problem exists, or on
your machine?

Regarding my previous suggestion, you'd put that function into a module (not
a class module, nor a module associated with a form or report), then use the
MyMid function in your query where you currently use the Mid function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry, I am not sure where/what I should do with this function, I put it
in
the qyery and it would not run.

Not sure if this helps, but I opened the mde, and pressed Ctrl G and in
the
Immediate window type ?Application.BrokenReference and hit return, the
response I got was 'True'

All other users get 'False'

:

Yes, that's what I meant. Unfortunately, I didn't bother testing that it
would, in fact, work if used directly in a query.

Try creating a wrapper function:

Function MyMid(InputString As String, Start As Long, Optional Length As
Long
= -1) As String

Dim strLength As Long

If Length < 0 Then
strLength = Len(InputString)
Else
strLength = Length
End If

MyMid = VBA.Mid(InputString, Start, strLength)

End Function

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


A simple database with field test1, and a query:-

Expr1: Mid([test1],2,1) - Worked

Expr2: VBA.Mid([test1],2,1) - Undefined function 'VBA.Mid' in
expression.

Is this what you mean?

:

See whether using VBA.Mid instead of Mid makes a difference. (note
that
there's a period between VBA and Mid)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have an Access database that we use to export files in the correct
format,
and this database is on a server (3-4 of us run these exports)

I just got someone else's pc after they left as it had other
software
on
it
that I need to continue some of his roles. The only problem being
that
when I
export now it fails with the following error:-

Error 3085 - 'Undefined function 'mid' in expression.' occurred
in...

I have created my own database and made a query to test the mid
functionality and it work.
I have looked at the Reference Library and have nothing listed as
Missing:
I have the following ticked:-
Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
I tried unchecking the all (only the bottom 3 would) saving it
closing
down
then rechecking them, this did not work

Not sure what to try now?

Details:-
PC - Windows XP Professional SP3
Access 2003 (11.8166.8221) SP3
Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) .NET
Framework
Version 2.0.50727 SP1

 

[jomuir]

I was doing this on a new database that I made for testing this, and I have
just created a table and query on the database that I am having problems
with. I cannot create a function in this actual database as modules are
greyed out on it.

However I did get an error with a quesry:- Expr1: Mid([test1],2,1)

Undefined function ‘Mid’ in expression.

I have added that function in, and it works on my test db query.

I have check the references on my pc, and all my databases...but not this one
(it is grayed out on this database, so I cannot view them). Since I am the
only user with this problem I presumed it could not be the databases
references, but I do not know, and should not presume.

I thought you'd said you'd checked the references and none were marked as
missing. Did you do that on the workstation where the problem exists, or on
your machine?

Regarding my previous suggestion, you'd put that function into a module (not
a class module, nor a module associated with a form or report), then use the
MyMid function in your query where you currently use the Mid function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sorry, I am not sure where/what I should do with this function, I put it
in
the qyery and it would not run.

Not sure if this helps, but I opened the mde, and pressed Ctrl G and in
the
Immediate window type ?Application.BrokenReference and hit return, the
response I got was 'True'

All other users get 'False'

:

Yes, that's what I meant. Unfortunately, I didn't bother testing that it
would, in fact, work if used directly in a query.

Try creating a wrapper function:

Function MyMid(InputString As String, Start As Long, Optional Length As
Long
= -1) As String

Dim strLength As Long

If Length < 0 Then
strLength = Len(InputString)
Else
strLength = Length
End If

MyMid = VBA.Mid(InputString, Start, strLength)

End Function

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


A simple database with field test1, and a query:-

Expr1: Mid([test1],2,1) - Worked

Expr2: VBA.Mid([test1],2,1) - Undefined function 'VBA.Mid' in
expression.

Is this what you mean?

:

See whether using VBA.Mid instead of Mid makes a difference. (note
that
there's a period between VBA and Mid)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have an Access database that we use to export files in the correct
format,
and this database is on a server (3-4 of us run these exports)

I just got someone else's pc after they left as it had other
software
on
it
that I need to continue some of his roles. The only problem being
that
when I
export now it fails with the following error:-

Error 3085 - 'Undefined function 'mid' in expression.' occurred
in...

I have created my own database and made a query to test the mid
functionality and it work.
I have looked at the Reference Library and have nothing listed as
Missing:
I have the following ticked:-
Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
I tried unchecking the all (only the bottom 3 would) saving it
closing
down
then rechecking them, this did not work

Not sure what to try now?

Details:-
PC - Windows XP Professional SP3
Access 2003 (11.8166.8221) SP3
Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) .NET
Framework
Version 2.0.50727 SP1

 

[Douglas J. Steele]

Is it an MDB or an MDE?

If it's an MDB, it sounds as though code is active. Assuming Access 2003 or
earlier, click End on the Run menu to terminate any active code.

If it's an MDE, take a look at the suggestion I make near the top of
http://www.accessmvp.com/DJSteele/AccessReferenceErrors.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have check the references on my pc, and all my databases...but not this
one
(it is grayed out on this database, so I cannot view them). Since I am the
only user with this problem I presumed it could not be the databases
references, but I do not know, and should not presume.

I thought you'd said you'd checked the references and none were marked as
missing. Did you do that on the workstation where the problem exists, or
on
your machine?

Regarding my previous suggestion, you'd put that function into a module
(not
a class module, nor a module associated with a form or report), then use
the
MyMid function in your query where you currently use the Mid function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry, I am not sure where/what I should do with this function, I put
it
in
the qyery and it would not run.

Not sure if this helps, but I opened the mde, and pressed Ctrl G and in
the
Immediate window type ?Application.BrokenReference and hit return, the
response I got was 'True'

All other users get 'False'

:

Yes, that's what I meant. Unfortunately, I didn't bother testing that
it
would, in fact, work if used directly in a query.

Try creating a wrapper function:

Function MyMid(InputString As String, Start As Long, Optional Length
As
Long
= -1) As String

Dim strLength As Long

If Length < 0 Then
strLength = Len(InputString)
Else
strLength = Length
End If

MyMid = VBA.Mid(InputString, Start, strLength)

End Function

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


A simple database with field test1, and a query:-

Expr1: Mid([test1],2,1) - Worked

Expr2: VBA.Mid([test1],2,1) - Undefined function 'VBA.Mid' in
expression.

Is this what you mean?

:

See whether using VBA.Mid instead of Mid makes a difference. (note
that
there's a period between VBA and Mid)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have an Access database that we use to export files in the
correct
format,
and this database is on a server (3-4 of us run these exports)

I just got someone else's pc after they left as it had other
software
on
it
that I need to continue some of his roles. The only problem being
that
when I
export now it fails with the following error:-

Error 3085 - 'Undefined function 'mid' in expression.' occurred
in...

I have created my own database and made a query to test the mid
functionality and it work.
I have looked at the Reference Library and have nothing listed as
Missing:
I have the following ticked:-
Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
I tried unchecking the all (only the bottom 3 would) saving it
closing
down
then rechecking them, this did not work

Not sure what to try now?

Details:-
PC - Windows XP Professional SP3
Access 2003 (11.8166.8221) SP3
Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) .NET
Framework
Version 2.0.50727 SP1