run time error 3464

[Selby]

I am getting a run time error 3464 - data type mismatch in criteria
expression when I try to run the following:

If IsNull (Combo21) = False Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[Shipment Date] = '" & Me ! [Combo24] & "'"
Me. Bookmark = rs.Bookmark
Me.Combo24 = Null

This runs correctly when I replace the shipment date (date/time) with a text
field - didn't know if it has something to do with the quotation marks or
not. Any help would be greatly appreciated.

 

[Rui]

dates are a special field.

the way to use it is #01/01/2008#

 

[Stefan Hoffmann]

hi Selby,

If IsNull (Combo21) = False Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[Shipment Date] = '" & Me ! [Combo24] & "'"

You need here a date value not a string. Dates are delimited by #.

Try this function:

Public Function SQLDateTime(ADateTime As Variant _
) As String

On Local Error GoTo LocalError

SQLDateTime = Format(ADateTime, "\#m\/d\/yyyy hh\:nn\:ss\#")

Exit Function

LocalError:
SQLDateTime = Format(Now, "\#m\/d\/yyyy hh\:nn\:ss\#")

End Function


e.g.

rs.FindFirst "[Shipment Date] = " & SQLDateTime(Me![Combo24])


mfG
--> stefan <--

 

[Selby]

Hi! Thanks for the promp replies - I must ask where do I put this function?
I am not proficient in vb - just trying to modify some existing code. Thanks!

hi Selby,

If IsNull (Combo21) = False Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[Shipment Date] = '" & Me ! [Combo24] & "'"

You need here a date value not a string. Dates are delimited by #.

Try this function:

Public Function SQLDateTime(ADateTime As Variant _
) As String

On Local Error GoTo LocalError

SQLDateTime = Format(ADateTime, "\#m\/d\/yyyy hh\:nn\:ss\#")

Exit Function

LocalError:
SQLDateTime = Format(Now, "\#m\/d\/yyyy hh\:nn\:ss\#")

End Function


e.g.

rs.FindFirst "[Shipment Date] = " & SQLDateTime(Me![Combo24])


mfG
--> stefan <--

 

[Rui]

Selby

Create a new VBA module and copy and paste Stefan's code.
Save it giving it any name you wish.

To use it, and quoting Stefan's example, simply replace

rs.FindFirst "[Shipment Date] = '" & Me ! [Combo24] & "'"

with

rs.FindFirst "[Shipment Date] = " & SQLDateTime(Me![Combo24])


Take care
Rui

Hi! Thanks for the promp replies - I must ask where do I put this function?
I am not proficient in vb - just trying to modify some existing code. Thanks!

hi Selby,

If IsNull (Combo21) = False Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[Shipment Date] = '" & Me ! [Combo24] & "'"

You need here a date value not a string. Dates are delimited by #.

Try this function:

Public Function SQLDateTime(ADateTime As Variant _
) As String

On Local Error GoTo LocalError

SQLDateTime = Format(ADateTime, "\#m\/d\/yyyy hh\:nn\:ss\#")

Exit Function

LocalError:
SQLDateTime = Format(Now, "\#m\/d\/yyyy hh\:nn\:ss\#")

End Function


e.g.

rs.FindFirst "[Shipment Date] = " & SQLDateTime(Me![Combo24])


mfG
--> stefan <--