awolarczuk
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.Sub Items(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.Sub Items(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
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.Sub
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"
'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.Sub
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
your error is most likely in this SQL line:
sql = "select * from [visits] where [client id] = " & ListView1.SelectedItem.Sub Items(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.Sub Items(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
sql = "select * from [visits] where [client id] = " & ListView1.SelectedItem.Sub
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.Sub
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
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.Sub Items(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
rs.Close
sql = "select * from [visits] where [client_id] = " & ListView1.SelectedItem.Sub
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
ASKER
the fuild should be number
try this change:
rs.Close
sql = "select * from [visits] where [client_id] = " & ListView1.SelectedItem.Sub Items(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.Captio n), "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
rs.Close
sql = "select * from [visits] where [client_id] = " & ListView1.SelectedItem.Sub
rs.Open sql, cnn1, adOpenDynamic, adLockPessimistic
Label4.Caption = rs.Fields("last_visit")
Label6.Caption = DateDiff("d", CDate(Label4.Caption),Date
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
ASKER
no AW same problem
ASKER
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.Sub Items(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.Captio n), "mmdd"), 1, 0)
rs.Close
Then post back here what is shown in the Message Box that is displayed.
AW
rs.Close
sql = "select * from [visits] where [client_id] = " & ListView1.SelectedItem.Sub
' 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
rs.Close
Then post back here what is shown in the Message Box that is displayed.
AW
ASKER
ok mate i get false in the message box
ASKER
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.Captio n), "mmdd"), 1, 0)
Label6.Caption = DateDiff("d", CDate(Label4.Caption), Date) - IIf(Format$(Date, "mmdd") < Format(CDate(Label7.Captio
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.Sub Items(2)
and not like this:
sql = "select * from [visits] where [client_id] = " & ListView1.SelectedItem.Sub Items(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.Sub Items(2)) Notice that the = is OUTSIDE the "
this is comparing the string "select * from [visits] where [client_id]" to the value of ListView1.SelectedItem.Sub Items(2) and setting the value of the variable sql to the TRUTH or FALSENESS of that comparison (which is obviously FALSE)
AW
sql = "select * from [visits] where [client_id]" = ListView1.SelectedItem.Sub
and not like this:
sql = "select * from [visits] where [client_id] = " & ListView1.SelectedItem.Sub
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.Sub
this is comparing the string "select * from [visits] where [client_id]" to the value of ListView1.SelectedItem.Sub
AW
ASKER
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.Captio n), "mmdd"), 1, 0)
Label6.Caption = DateDiff("d", CDate(Label4.Caption), Date) - IIf(Format$(Date, "mmdd") < Format(CDate(Label7.Captio
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i get a datediff of 233 then i get the type mismatch issue
ASKER
and a delta of 0
ASKER
ok thanks mate i worked it out your the man
ok, what was the problem?
also, glad to be of assistance
AW
also, glad to be of assistance
AW
what data type is the field client id?