Mid function

[enak]

I am new to Access but am a very experienced vb programmer. I am using Access
2003. I have been given a database and asked to code some enhancements. I am
running into an error with the following code:

Set cnn = CurrentProject.Connection
rstApp.Open "Applicant", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
rstFilenum.Open "nextfilenum", cnn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

I get the error on the last line - rstFilenum.Open

Error message: Undefined function 'Mid' in expression.

Why am I getting this?

Thanks.

 

[Douglas J Steele]

nextfilenum must be a query that includes the Mid function.

I don't believe you can use VBA functions such as Mid when you're using ADO
to interact with the database.

 

[enak]

Great, Thanks for the info but how do I fix it? Everything works fine on the
Client's machine.

 

[Roger Carlson]

If it works on a client's machine, but doesn't work on yours, then it's time
to check the References. Look here:
http://allenbrowne.com/ser-38.html


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[enak]

I already did that. Everything looks fine. I am running it on my development
machine which is running Win2k3. Most of my development is in .net. I don't
know if this will make a difference.

 

[Rob Oldfield]

Can you open 'nextfilenum' directly on your machine? And what does the SQL
look like?

 

[enak]

Here is the query:

SELECT Max([file_num]) AS Nextfile_num
FROM Applicant
WHERE (Mid(Str([file_num]),Len([file_num])-2,2)=DatePart("m",Date()) OR
mid(str([file_num]),len([file_num])-1,1)=DatePart("m",Date())) AND
Right(Str([file_num]),2)=Right(Str(DatePart("yyyy",Date())),2);

 

[Rob Oldfield]

I'm with Doug that I'm surprised that that works on the client's machine.
What references are live on that machine?

And how are you handling the connection? Is this an adp or are you using
ODBC in some way?

Here is the query:

SELECT Max([file_num]) AS Nextfile_num
FROM Applicant
WHERE (Mid(Str([file_num]),Len([file_num])-2,2)=DatePart("m",Date()) OR
mid(str([file_num]),len([file_num])-1,1)=DatePart("m",Date())) AND
Right(Str([file_num]),2)=Right(Str(DatePart("yyyy",Date())),2);

--
enak

Can you open 'nextfilenum' directly on your machine? And what does the SQL
look like?


enhancements. I
am

 

[enak]

This is from one of my previous posts:

Set cnn = CurrentProject.Connection
--
enak

I'm with Doug that I'm surprised that that works on the client's machine.
What references are live on that machine?

And how are you handling the connection? Is this an adp or are you using
ODBC in some way?

Here is the query:

SELECT Max([file_num]) AS Nextfile_num
FROM Applicant
WHERE (Mid(Str([file_num]),Len([file_num])-2,2)=DatePart("m",Date()) OR
mid(str([file_num]),len([file_num])-1,1)=DatePart("m",Date())) AND
Right(Str([file_num]),2)=Right(Str(DatePart("yyyy",Date())),2);

--
enak

Can you open 'nextfilenum' directly on your machine? And what does the SQL
look like?


I am new to Access but am a very experienced vb programmer. I am using
Access
2003. I have been given a database and asked to code some enhancements. I
am
running into an error with the following code:

Set cnn = CurrentProject.Connection
rstApp.Open "Applicant", cnn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rstFilenum.Open "nextfilenum", cnn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

I get the error on the last line - rstFilenum.Open

Error message: Undefined function 'Mid' in expression.

Why am I getting this?

Thanks.

 

[Rob Oldfield]

Yes. I read that. And it can apply to various different connection
methods.

adp, mdb or what? What references are live on the machine that works?

This is from one of my previous posts:

Set cnn = CurrentProject.Connection
--
enak

I'm with Doug that I'm surprised that that works on the client's machine.
What references are live on that machine?

And how are you handling the connection? Is this an adp or are you using
ODBC in some way?

Here is the query:

SELECT Max([file_num]) AS Nextfile_num
FROM Applicant
WHERE (Mid(Str([file_num]),Len([file_num])-2,2)=DatePart("m",Date()) OR
mid(str([file_num]),len([file_num])-1,1)=DatePart("m",Date())) AND
Right(Str([file_num]),2)=Right(Str(DatePart("yyyy",Date())),2);

--
enak


:

Can you open 'nextfilenum' directly on your machine? And what does

the
SQL

look like?


I am new to Access but am a very experienced vb programmer. I am using
Access
2003. I have been given a database and asked to code some enhancements. I
am
running into an error with the following code:

Set cnn = CurrentProject.Connection
rstApp.Open "Applicant", cnn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rstFilenum.Open "nextfilenum", cnn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

I get the error on the last line - rstFilenum.Open

Error message: Undefined function 'Mid' in expression.

Why am I getting this?

Thanks.