We help IT Professionals succeed at work.

Data type mismatch in criteria expression using access and vb6

628 Views
Last Modified: 2013-11-05
gday all,
WHen i try running my code i am getting a Data type mismatch in criteria expression here is the code below what i am trying to do is find out how many day there has been since a old date

Private Sub ListView1_Click()
Dim rs As New ADODB.Recordset
      Dim sql As String
        cnn1.Open
        sql = "select * from [client info] where [client id] = " & ListView1.SelectedItem.SubItems(2)
         rs.Open sql$, cnn1, adOpenDynamic, adLockPessimistic
     
    fname.Caption = rs.Fields("Title") & " " & rs.Fields("first name") & " " & rs.Fields("last name")
    clientID.Caption = rs.Fields("Client ID")
    address.Caption = fixnull(rs.Fields("address"))
    cityinfo.Caption = fixnull(rs.Fields("suburb") & " " & rs.Fields("state") & " " & rs.Fields("post code"))
    'Menu.DOB.Caption = fixnull(rs.Fields("DOB"))
    'Menu.SEX.Caption = fixnull(rs.Fields("sex"))
    phone.Caption = "Ph:" & "" & (rs.Fields("Phone"))
    molb.Caption = "Molb:" & "" & (rs.Fields("molb"))
    first.Caption = "Client Since" & " " & (rs.Fields("first visit"))
rs.Close
        sql = "select * from [visits] where [client id] = " & ListView1.SelectedItem.SubItems(2) & ""
              rs.Open sql, cnn1, adOpenDynamic, adLockPessimistic
              Label4.Caption = rs.Fields("last visit")
Label6.Caption = DateDiff("DDDD", CVDate(Label4.Caption), Date) - IIf(Format$(Date, "mmdd") < Format(Label7, "mmdd"), 1, 0)
rs.Close
cnn1.Close
    fname.Visible = True
    clientID.Visible = True
    address.Visible = True
    cityinfo.Visible = True
    phone.Visible = True
    molb.Visible = True
   
    ListView1.ListItems.Clear
    Picture1.Visible = False
    Picture2.Visible = True
    massageg.Visible = False
    noedit.Visible = False
    massagev.Visible = True
    first.Visible = True
    Label6.Visible = True
   
   
End Sub
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
on which of the 2 queries do you get the error?
what data type is the field client id?
your error is most likely in this SQL line:

sql = "select * from [visits] where [client id] = " & ListView1.SelectedItem.SubItems(2) & ""


In the first SQL, you seem to indicate that [Clinet ID] is a NUMBER (in the table, but here you are trying to use a TEXT value.  Eliminate the final "'", so thie second SQL string looks like this:

sql = "select * from [visits] where [client id] = " & ListView1.SelectedItem.SubItems(2)

On another point, it is NOT a good idea to embed blanks in the names of Tables or Fields in your database.  That is why you need the [...] around [Client ID].  Access (and most databases for that matter0, have a real problem with embedded blanks.  It would be much better, and much easier for you as the programmer, to use _, like this:

Client_ID
Customer_Table

get the idea?  Just a helpful suggestion, take it for what it is worth.

AW

Author

Commented:
yea thank arthur, i have changed it now and will continue to use it that way thanks, but after i changed that andchanged the code to a number string i am still getting the same error

rs.Close
        sql = "select * from [visits] where [client_id] = " & ListView1.SelectedItem.SubItems(2)
              rs.Open sql, cnn1, adOpenDynamic, adLockPessimistic
              Label4.Caption = rs.Fields("last_visit")
Label6.Caption = DateDiff("DDDD", CVDate(Label4.Caption), Date) - IIf(Format$(Date, "mmdd") < Format(Label7, "mmdd"), 1, 0)
rs.Close
cnn1.Close
    fname.Visible = True
    clientID.Visible = True

Author

Commented:
the fuild should be number
try this change:

rs.Close
        sql = "select * from [visits] where [client_id] = " & ListView1.SelectedItem.SubItems(2)
              rs.Open sql, cnn1, adOpenDynamic, adLockPessimistic
              Label4.Caption = rs.Fields("last_visit")
Label6.Caption = DateDiff("d", CDate(Label4.Caption),Date) - IIf(Format$(Date, "mmdd") < Format(cDate(Label7.Caption), "mmdd"), 1, 0)
rs.Close
cnn1.Close
    fname.Visible = True
    clientID.Visible = True

It is also a VERY GOOD idea to rename your controls (Label4, Label6, Label7,...) to something more meaningful (Label4 --> lblLastVisit, for example).  That way, when you look at this code next month, you will remember what the controls are being used for, and not have to guess, or read through the code, to figure out what you were trying to do.

AW

Author

Commented:
no AW same problem

Author

Commented:
it stops here rs.Open sql, cnn1, adOpenDynamic, adLockPessimistic
try this to help debug the code:

rs.Close
        sql = "select * from [visits] where [client_id] = " & ListView1.SelectedItem.SubItems(2)

 ' This will display the SQL text that is about to be executed, so you can verify that it is correct
      MsgBox ("SQL = " & sql, vbOkOnly)

              rs.Open sql, cnn1, adOpenDynamic, adLockPessimistic
              Label4.Caption = rs.Fields("last_visit")
Label6.Caption = DateDiff("d", CDate(Label4.Caption),Date) - IIf(Format$(Date, "mmdd") < Format(cDate(Label7.Caption), "mmdd"), 1, 0)
rs.Close


Then post back here what is shown in the Message Box that is displayed.

AW

Author

Commented:
ok mate i get false in the message box

Author

Commented:
ok it is now kinda working i now get a type mismatch on this line
Label6.Caption = DateDiff("d", CDate(Label4.Caption), Date) - IIf(Format$(Date, "mmdd") < Format(CDate(Label7.Caption), "mmdd"), 1, 0)
If you are getting False in the Message Box, then I suspect that your code looks like this:

sql = "select * from [visits] where [client_id]" = ListView1.SelectedItem.SubItems(2)

and not like this:

sql = "select * from [visits] where [client_id] = " & ListView1.SelectedItem.SubItems(2)


This first line (where I think the error lies), is easier to see if you were to add parentheses:

sql = ("select * from [visits] where [client_id]" = ListView1.SelectedItem.SubItems(2))  Notice that the = is OUTSIDE the "


this is comparing the string "select * from [visits] where [client_id]"  to the value of ListView1.SelectedItem.SubItems(2)  and setting the value of the variable sql to the TRUTH or FALSENESS of that comparison (which is obviously FALSE)

AW

Author

Commented:
i have got it going past that error now but i am gett a type mismatch on
Label6.Caption = DateDiff("d", CDate(Label4.Caption), Date) - IIf(Format$(Date, "mmdd") < Format(CDate(Label7.Caption), "mmdd"), 1, 0)
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
i get a datediff of 233 then i get the type mismatch issue

Author

Commented:
and a delta of 0

Author

Commented:
ok thanks mate i worked it out your the man
ok, what was the problem?

also, glad to be of assistance

AW
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.