Link to home
Start Free TrialLog in
Avatar of awolarczuk
awolarczukFlag for Australia

asked on

Data type mismatch in criteria expression using access and vb6

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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

Avatar of awolarczuk

ASKER

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
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
no AW same problem
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
ok mate i get false in the message box
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
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)
ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i get a datediff of 233 then i get the type mismatch issue
and a delta of 0
ok thanks mate i worked it out your the man
ok, what was the problem?

also, glad to be of assistance

AW