Run-time error 13 type mismatch

Getting the error Run-time error 13 type mismatch on the following line of code

Set CBEDSRst = CurrentDb.OpenRecordset("Select Distinct RecType, CBEDSCourse from [" & TblNmStr & "] WHERE RecType='C' and CrsType='" & CTypeArr(CTypeLpCnt) & "' " & PTCritArr(PTypeLpCnt))
needhelpfast569Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
upload a copy of the db
0
 
Rey Obrero (Capricorn1)Commented:
what is the Data Type of field CrsType ?

if number

try this

Set CBEDSRst = CurrentDb.OpenRecordset("Select Distinct RecType, CBEDSCourse from [" & TblNmStr & "] WHERE RecType='C' and CrsType=" & CTypeArr(CTypeLpCnt) & " & PTCritArr(PTypeLpCnt))

or this

Set CBEDSRst = CurrentDb.OpenRecordset("Select Distinct RecType, CBEDSCourse from [" & TblNmStr & "] WHERE RecType='C' and CrsType=" & CTypeArr(CTypeLpCnt)  & PTCritArr(PTypeLpCnt))

what does the function "CTypeArr" do ?
0
 
mbizupCommented:
Try added a debug .print to write your query string to the immediate window.  Is the query string what you are expecting?

If not, copy and paste it in your next comment.

Also, what is the datatype of the CrsType field?
And is TblNmStr a string variable or the name of an actual table?

Dim strSQL as string
strSQL = "Select Distinct RecType, CBEDSCourse from [" & TblNmStr &  "] WHERE RecType='C' and CrsType='" & CTypeArr(CTypeLpCnt) & "' " & PTCritArr(PTypeLpCnt))
debug.print strSQL
Set CBEDSRst = CurrentDb.OpenRecordset(strSQL)

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
needhelpfast569Author Commented:
Hi Capricorn1,

Both of the fields are numbers

the compiler did not like the line dropping those chracters ' and " added the editing back in and the compiler accepted the line again

what is strange is I have an access 2003 version of the program and this line works but having the problem with same line in the Access 2007 version
0
 
Rey Obrero (Capricorn1)Commented:
what does this two function do

CTypeArr(CTypeLpCnt)  

 PTCritArr(PTypeLpCnt)


can you upload a copy of the db?
0
 
mbizupCommented:
needhelpfast -

You might have missed my comment earlier.

Is TblNmStr a string variable or the name of an actual table?

If it is an actual table name, try this:

Dim strSQL as string
strSQL = "Select Distinct RecType, CBEDSCourse from  [TblNmStr] WHERE RecType='C' and CrsType=" & CTypeArr(CTypeLpCnt) &  PTCritArr(PTypeLpCnt))
debug.print strSQL
Set CBEDSRst = CurrentDb.OpenRecordset(strSQL)

Open in new window


The debug.print strSQL line will write the query string to the debugger window.

If you copy/paste it here, it will give us a better idea of where your query is going wrong.
0
 
Rey Obrero (Capricorn1)Commented:
<what is strange is I have an access 2003 version of the program and this line works but having the problem with same line in the Access 2007 version >

so the line works in A2003,

open your VBA window, then
Tools > References

see if you have a reference with "MISSING" prefix
uncheck and select the available version

Do a DEBUG > COMPILE

make sure that all errors raised is corrected
0
 
needhelpfast569Author Commented:
The references look good
0
 
mbizupCommented:
Ok - I'm going to click "unmonitor" here and leave this to cap1 - very difficult to help if you don't respond to the comments posted.
0
 
needhelpfast569Author Commented:
problem was a reference out of order
0
All Courses

From novice to tech pro — start learning today.