error 3464 -- data type mismatch in criteria expression

G

Guest

using a2k....

when my vba comes to this part it displays the 3464 in the heading:

Private Sub cboReg2__AfterUpdate()
Me.cboReg1_ = ""
Me.cboStudy_ = " " ' Me.[Study#] = " "
Me.RecordsetClone.FindFirst "[IRB Number] = '" & Me![cboIRB#] & "' AND
[Reg#] = " _
& Me![cboReg2#] & ""
Me.Bookmark = Me.RecordsetClone.Bookmark
[cboStudy#] = Me.RecordsetClone![Study#]
End Sub

in the line beginning with Me.RecordsetClone.FindFirst

is this enough info to uncover the solution behind it?

-ted
 
D

Douglas J Steele

What data type are your two fields (IRB Number and Reg#)?

If they're text, you're missing one quote in the segment "' AND [Reg#] =":
you need a single quote before that final double quote.

If they're numeric, you don't need the single quotes you currently have
there.
 
G

Guest

douglas,

Reg# was changed from Number to Text (forgot about that) in the underlying
table. have changed my vba to read

Private Sub cboReg2__AfterUpdate()
Me.cboReg1_ = ""
Me.cboStudy_ = " " ' Me.[Study#] = " "
Me.RecordsetClone.FindFirst "[IRB Number] = '" & Me![cboIRB#] & "' AND
[Reg#] = '" _
& Me![cboReg2#] & ""
Me.Bookmark = Me.RecordsetClone.Bookmark
[cboStudy#] = Me.RecordsetClone![Study#]
End Sub

and now the error's mutated to read

3077 - syntax error in string in expression?

-ted


Douglas J Steele said:
What data type are your two fields (IRB Number and Reg#)?

If they're text, you're missing one quote in the segment "' AND [Reg#] =":
you need a single quote before that final double quote.

If they're numeric, you don't need the single quotes you currently have
there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ted said:
using a2k....

when my vba comes to this part it displays the 3464 in the heading:

Private Sub cboReg2__AfterUpdate()
Me.cboReg1_ = ""
Me.cboStudy_ = " " ' Me.[Study#] = " "
Me.RecordsetClone.FindFirst "[IRB Number] = '" & Me![cboIRB#] & "' AND
[Reg#] = " _
& Me![cboReg2#] & ""
Me.Bookmark = Me.RecordsetClone.Bookmark
[cboStudy#] = Me.RecordsetClone![Study#]
End Sub

in the line beginning with Me.RecordsetClone.FindFirst

is this enough info to uncover the solution behind it?

-ted
 
D

Douglas J Steele

Is IRB Number text too?

Are there apostrophes in either of the strings? If so, change to

Me.RecordsetClone.FindFirst "[IRB Number] = " & Chr$(34) & _
Me![cboIRB#] & Chr$(34) & " AND [Reg#] = " & _
Chr$(34) & Me![cboReg2#] & Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ted said:
douglas,

Reg# was changed from Number to Text (forgot about that) in the underlying
table. have changed my vba to read

Private Sub cboReg2__AfterUpdate()
Me.cboReg1_ = ""
Me.cboStudy_ = " " ' Me.[Study#] = " "
Me.RecordsetClone.FindFirst "[IRB Number] = '" & Me![cboIRB#] & "' AND
[Reg#] = '" _
& Me![cboReg2#] & ""
Me.Bookmark = Me.RecordsetClone.Bookmark
[cboStudy#] = Me.RecordsetClone![Study#]
End Sub

and now the error's mutated to read

3077 - syntax error in string in expression?

-ted


Douglas J Steele said:
What data type are your two fields (IRB Number and Reg#)?

If they're text, you're missing one quote in the segment "' AND [Reg#] =":
you need a single quote before that final double quote.

If they're numeric, you don't need the single quotes you currently have
there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ted said:
using a2k....

when my vba comes to this part it displays the 3464 in the heading:

Private Sub cboReg2__AfterUpdate()
Me.cboReg1_ = ""
Me.cboStudy_ = " " ' Me.[Study#] = " "
Me.RecordsetClone.FindFirst "[IRB Number] = '" & Me![cboIRB#] & "' AND
[Reg#] = " _
& Me![cboReg2#] & ""
Me.Bookmark = Me.RecordsetClone.Bookmark
[cboStudy#] = Me.RecordsetClone![Study#]
End Sub

in the line beginning with Me.RecordsetClone.FindFirst

is this enough info to uncover the solution behind it?

-ted
 
G

Guest

both are text but the IRB# does not contain apostrophes. in the interval
prior to your latest reply, i modified the code to incorporate yet one more
single apostrophe, and it reads now:

Private Sub cboReg2__AfterUpdate()
Me.cboReg1_ = ""
Me.cboStudy_ = " " ' Me.[Study#] = " "
Me.RecordsetClone.FindFirst "[IRB Number] = '" & Me![cboIRB#] & "' AND
[Reg#] = '" _
& Me![cboReg2#] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
[cboStudy#] = Me.RecordsetClone![Study#]
End Sub

as above....which seems to be working.

i would really like to learn why you wrote your code the way you wrote it,
though:

Me.RecordsetClone.FindFirst "[IRB Number] = " & Chr$(34) & _
Me![cboIRB#] & Chr$(34) & " AND [Reg#] = " & _
Chr$(34) & Me![cboReg2#] & Chr$(34)

with best regards,

-ted




Douglas J Steele said:
Is IRB Number text too?

Are there apostrophes in either of the strings? If so, change to

Me.RecordsetClone.FindFirst "[IRB Number] = " & Chr$(34) & _
Me![cboIRB#] & Chr$(34) & " AND [Reg#] = " & _
Chr$(34) & Me![cboReg2#] & Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ted said:
douglas,

Reg# was changed from Number to Text (forgot about that) in the underlying
table. have changed my vba to read

Private Sub cboReg2__AfterUpdate()
Me.cboReg1_ = ""
Me.cboStudy_ = " " ' Me.[Study#] = " "
Me.RecordsetClone.FindFirst "[IRB Number] = '" & Me![cboIRB#] & "' AND
[Reg#] = '" _
& Me![cboReg2#] & ""
Me.Bookmark = Me.RecordsetClone.Bookmark
[cboStudy#] = Me.RecordsetClone![Study#]
End Sub

and now the error's mutated to read

3077 - syntax error in string in expression?

-ted


Douglas J Steele said:
What data type are your two fields (IRB Number and Reg#)?

If they're text, you're missing one quote in the segment "' AND [Reg#] =":
you need a single quote before that final double quote.

If they're numeric, you don't need the single quotes you currently have
there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


using a2k....

when my vba comes to this part it displays the 3464 in the heading:

Private Sub cboReg2__AfterUpdate()
Me.cboReg1_ = ""
Me.cboStudy_ = " " ' Me.[Study#] = " "
Me.RecordsetClone.FindFirst "[IRB Number] = '" & Me![cboIRB#] & "' AND
[Reg#] = " _
& Me![cboReg2#] & ""
Me.Bookmark = Me.RecordsetClone.Bookmark
[cboStudy#] = Me.RecordsetClone![Study#]
End Sub

in the line beginning with Me.RecordsetClone.FindFirst

is this enough info to uncover the solution behind it?

-ted
 
D

Douglas J. Steele

Chr$(34) is the representation for ". I find it cleaner to use than """"
when you want a " in a string.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ted said:
both are text but the IRB# does not contain apostrophes. in the interval
prior to your latest reply, i modified the code to incorporate yet one
more
single apostrophe, and it reads now:

Private Sub cboReg2__AfterUpdate()
Me.cboReg1_ = ""
Me.cboStudy_ = " " ' Me.[Study#] = " "
Me.RecordsetClone.FindFirst "[IRB Number] = '" & Me![cboIRB#] & "' AND
[Reg#] = '" _
& Me![cboReg2#] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
[cboStudy#] = Me.RecordsetClone![Study#]
End Sub

as above....which seems to be working.

i would really like to learn why you wrote your code the way you wrote it,
though:

Me.RecordsetClone.FindFirst "[IRB Number] = " & Chr$(34) & _
Me![cboIRB#] & Chr$(34) & " AND [Reg#] = " & _
Chr$(34) & Me![cboReg2#] & Chr$(34)

with best regards,

-ted




Douglas J Steele said:
Is IRB Number text too?

Are there apostrophes in either of the strings? If so, change to

Me.RecordsetClone.FindFirst "[IRB Number] = " & Chr$(34) & _
Me![cboIRB#] & Chr$(34) & " AND [Reg#] = " & _
Chr$(34) & Me![cboReg2#] & Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ted said:
douglas,

Reg# was changed from Number to Text (forgot about that) in the
underlying
table. have changed my vba to read

Private Sub cboReg2__AfterUpdate()
Me.cboReg1_ = ""
Me.cboStudy_ = " " ' Me.[Study#] = " "
Me.RecordsetClone.FindFirst "[IRB Number] = '" & Me![cboIRB#] & "' AND
[Reg#] = '" _
& Me![cboReg2#] & ""
Me.Bookmark = Me.RecordsetClone.Bookmark
[cboStudy#] = Me.RecordsetClone![Study#]
End Sub

and now the error's mutated to read

3077 - syntax error in string in expression?

-ted


:

What data type are your two fields (IRB Number and Reg#)?

If they're text, you're missing one quote in the segment "' AND
[Reg#] =":
you need a single quote before that final double quote.

If they're numeric, you don't need the single quotes you currently
have
there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


using a2k....

when my vba comes to this part it displays the 3464 in the heading:

Private Sub cboReg2__AfterUpdate()
Me.cboReg1_ = ""
Me.cboStudy_ = " " ' Me.[Study#] = " "
Me.RecordsetClone.FindFirst "[IRB Number] = '" & Me![cboIRB#] & "'
AND
[Reg#] = " _
& Me![cboReg2#] & ""
Me.Bookmark = Me.RecordsetClone.Bookmark
[cboStudy#] = Me.RecordsetClone![Study#]
End Sub

in the line beginning with Me.RecordsetClone.FindFirst

is this enough info to uncover the solution behind it?

-ted
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top