Data type mismatch

[Domino2]

I am getting an error 3494 and message saying data type mismatch. Can someone tell me whats wrong. Thanks

strStringSearch = "Barcode = " & Me.List89.Column(0) & " AND " & _
"ID3 = " & Me.ID3.Caption

rs.FindFirst strStringSearch

 

[missinglinq]

What datatype is List89.Column(0)? Text or Number? Your syntax is set up indicating both List89.Column(0) and ID3.Caption are Numeric, and obviously the caption of a label is Text, not a number.

This shows the syntax used if both are text:

"[Field1] = '" & Me.[Field1] & "' AND [Field2] = '" & Me.[Field2] & "'"

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Domino2]

Many thanks. I since went down another route, however the first time its run it works and puts the record in the table, however if I run it again it reports a data type mismatch.

MySql = "SELECT RelatedTapes.ID3, RelatedTapes.ID1, RelatedTapes.Barcode FROM RelatedTapes"

Set rs = db.OpenRecordset(MySql, dbOpenDynaset)

strSearch = "[Barcode] = " & Me.List89.Column(0) & " AND " & _
"ID3 = " & Me.ID3.Caption

rs.FindFirst strSearch

If rs.NoMatch Then

' Not found, so put in
rs.AddNew
rs.Fields("ID3") = Forms!Descriptions4.Form.ID1.Caption
rs.Fields("ID1") = Forms!Descriptions4.Form.ID3.Caption
rs.Fields("Barcode") = Me.List89.Column(0)
rs.Update

Else

MsgBox "FOUND"


End If

 

[dhookom]

I would not open the recordset of the entire table. You should be able to add the appropriate where condition to the MySql and then open. Check for (rs.EOF AND rs.BOF) to see if a match was found.

Duane
Hook'D on Access
MS Access MVP

 

[RoyVidar]

Error number 3494 says "Unable to retrieve the specified Registry parameter for the Synchronizer" on my setup.

You haven't told us what datatype the fields are.

Datatype mismatch usually tells us that you're stuffing values of wrong datatype into a variable, field, property etc (or that you through dynamic SQL are querying or applying value to a text field without proper text qualifiers).

In the last part of the code, you're assigning the caption of a label called ID1 into a field called ID3 and the caption of a label called ID3 into a field called ID1.

Might I guess it should be the other way around, and that one of these fields are text and one numeric?

Another guess, since there's a mismatch the second time, is that perhaps one, or more of the controls have changed values to something illegal, or are empty?

Roy-Vidar

 

[Domino2]

Many thanks all for coming in, yesterday was so quiet I had to gues/try combinations. Also had to change what I was doing. The code I ended up getting to work and using was:

Set rs = db.OpenRecordset(MySql, dbOpenDynaset)
strSearch = "[Barcode] = '" & Me.List89.Column(0) & "'" & " AND " & "ID1 = " & Me.ID1.Caption & ""
rs.FindFirst strSearch

Barcode is a text field in the table, ID1 is a number in the table. There is very little to refer to the sql changes when an item is text or a number, let alone the trickery in syntax when you add more items together in a combination. Many thanks everyone, no doubt I will be in another mess sometime in the future. I must try and learn sql in Access.