Error 3464 Data type mismatch in crieria expression 2

[fileman1]

I get an error trying to find a record based on a date selected from a list box.

rs3.FindFirst "ID1 = " & Me.ID1 & " AND NewsDate = '" & Me.List0.Column(1) & "'"

I changed it to:

rs3.FindFirst "ID1 = " & Me.ID1 & " AND NewsDate = " & Me.List0.Column(1) & ""

It does not error anymore, but does not find the record? I did a debug.print on my List0 value and it's a clear short date

 

[dhookom]

If NewsDate is a date field then try:

rs3.FindFirst "ID1 = " & Me.ID1 & " AND NewsDate = [b][COLOR=#204A87]#[/color][/b]" & Me.List0.Column(1) & "[b][COLOR=#204A87]#[/color][/b]"

You should also change the name of List0 to something that makes sense.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

That's why I suggest this:
[tt]
Dim strF As String

strF = "ID1 = " & Me.ID1 & " AND NewsDate = #" & Me.List0.Column(1) & "#"
Debug.Print strF

rs3.FindFirst strF
[/tt]

So you can see what your DB gets


Have fun.

---- Andy

 

[fileman1]

Many thanks both.

Andy, using your code I got:
ID1 = 34 AND NewsDate = #08/10/2013#
ID1 = 34 AND NewsDate = #12/04/2013#
ID1 = 34 AND NewsDate = #11/04/2013#

which was what I was trying to get. This produced the same thanks Duane with your code.

However I have a mystery as to why some dates will delete and some not?

08/10/2013
02/04/2013
12/02/2013
Do not get deleted

30/04/2013
29/04/2013
17/04/2013
Delete okay

 

[dhookom]

Access queries expect the date to be formatted either as m/d/y or be more explicit like 1-jan-2013.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

rs3.FindFirst "ID1=" & Me!ID1 & " AND NewsDate=#" & Format(Me!List0.Column(1), "yyyy-mm-dd") & "#"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fileman1]

Thanks Duane, the field in the table is set to date/time, short date. i.e. 20/04/2013. In the UK this is most common format.

If that's the case than I will look for the primary key for each date selected in the list and delete that way.

 

[fileman1]

Thank you PHV, that solved my problem, deletes all dates, format kept the same. Regards

Thanks Andy, I will use that in the futer.