5 Replies - 1344 Views - Last Post: 21 March 2012 - 05:24 PM Rate Topic: -----

#1 KyleRose26  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 28
  • Joined: 02-March 12

SQL 'Data type mismatch in criteria expression.' Problem.

Posted 20 March 2012 - 01:37 PM

Probably a simple problem but I'm failing to see how or why it's happening.

The error message I'm getting is Data type mismatch in criteria expression.

My coding is below:

 SQL = "SELECT ExName FROM VirtualLearningExercises WHERE VirtualLearningExercises.Level = '" & UserLevel & "' ORDER BY ExName"
        Da = New OleDb.OleDbDataAdapter(SQL, Con)
        Da.Fill(Ds2, "ExerciseNames")



The error is coming up when i'm filling the dataset, i've already figured that it works when the WHERE is removed. So i'm assuming the mismatch must be from (VirtualLearningExercises.Level = '" & UserLevel). UserLevel is defined as a integer in vb and VirtualLearningExercises.Level is data type number on access. So i'm pretty confused why it's occuring.

Thanks in advance for any help.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL 'Data type mismatch in criteria expression.' Problem.

#2 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 584
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: SQL 'Data type mismatch in criteria expression.' Problem.

Posted 20 March 2012 - 01:39 PM

What data types are these? I mean,
What are VirtualLearningExercises.Level and UserLevel?

This post has been edited by DimitriV: 20 March 2012 - 01:40 PM

Was This Post Helpful? 0
  • +
  • -

#3 KyleRose26  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 28
  • Joined: 02-March 12

Re: SQL 'Data type mismatch in criteria expression.' Problem.

Posted 20 March 2012 - 01:44 PM

View PostKyleRose26, on 20 March 2012 - 01:37 PM, said:

UserLevel is defined as a integer in vb and VirtualLearningExercises.Level is data type number on access.

Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: SQL 'Data type mismatch in criteria expression.' Problem.

Posted 21 March 2012 - 02:22 PM

Try it without the apostrophes surrounding your level variable.
"...Where Level = " & UserLevel & " ORDER BY ....."

Was This Post Helpful? 1
  • +
  • -

#5 KyleRose26  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 28
  • Joined: 02-March 12

Re: SQL 'Data type mismatch in criteria expression.' Problem.

Posted 21 March 2012 - 04:24 PM

Thanks for the reply, however figured it out shortly after posting so already sorted. Thanks anyways.
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: SQL 'Data type mismatch in criteria expression.' Problem.

Posted 21 March 2012 - 05:24 PM

No problem, but one thing I would like to add is that Level will probably be a problem on an insert or update statement. It appears that it is a reserved word in access so keep in mind that if you receive an error when trying to insert or update that you will probably need to wrap the word Level in square brackets [].

This post has been edited by CharlieMay: 21 March 2012 - 05:24 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1