VBA Date Filter problem

[bodylojohn]

Hello Everybody,

I have searched the net and this forum for an answer but I couldn't find it.

I am trying to filter a continious form in MS Access 2003.

The database field called [DeliverDate] and contains values like "23-7-2015 8:23:16"

In the search field (textbox called txtSearchDate) only a date is entered like "23-7-2015"

If the value in the database field is a date only the filter gives a result. But when the database field [DeliverDate] contains a date AND time NO results are shown.

I use the following code:

Private Sub FilterDate

dim SearchDate as string

if trim(nz(txtSearchDate.value,"")) <> "" then SearchDate = [DeliverDate] = #" & Format(cdate(me.txtSearchDate.value),"mm-dd-yyyy") & "#"

me.filter = SearchDate
me.filteron = true

I have tried:
if trim(nz(txtSearchDate.value,"")) <> "" then SearchDate = format([DeliverDate],"mm-dd-yyyy") = #" & Format(cdate(me.txtSearchDate.value),"mm-dd-yyyy") & "#"

But that didn't work.

I realy hope you can help me.

Thanks in advance

 

[spikepl]

You posted just a snippet with the search/filter code missing. Post entire code.

 

[Gasman]

Have you tried "23-7-2015 8:23:16" or a value that exists in the table?

 

[bodylojohn]

You posted just a snippet with the search/filter code missing. Post entire code.

You are right.
I forgot the:
me.filter = SearchDate
me.filteron = true

The code is run when i click a button.

But the problem should be somewhere in my SearchDate condition

 

[spikepl]

Again: please post the code for the CURRENT entire routine - not snippets!
Your syntax is way off : I 'd suggest that you start by printing the content of SearchDate. Insert the filter manually until you get the syntax right.

"But that didn't work." is meaningess - always say what the system did.

Once you get all above sorted out we'll come back to how to find values when the time element is involved. Gasman's way is not the right one.

 

[bodylojohn]

Have you tried "23-7-2015 8:23:16" or a value that exists in the table?

No results!

 

[bodylojohn]

Here is my code:

Private Sub btnZoek_Click()

ZoekAflevering

End Sub

Private Sub ZoekAflevering()

Dim ZoekFilter As String
ZoekFilter = "1=1"

If Trim(Nz(txtZoekBarcode.Value, "")) <> "" Then ZoekFilter = ZoekFilter + " and [Barcode] = '" & txtZoekBarcode.Value & "' "
If Trim(Nz(txtZoekAantal.Value, "")) <> "" Then ZoekFilter = ZoekFilter + " and [Aantal_Afgeleverd] = " & txtZoekAantal.Value
If Trim(Nz(txtSearchDate.Value, "")) <> "" Then ZoekFilter = ZoekFilter + " and [DeliverDate] = #" & Format(CDate(Me.txtSearchDate.Value), "mm-dd-yyyy") & "#"


Me.Filter = ZoekFilter
Me.FilterOn = True

End Sub

 

[spikepl]

Ok that's better.

In the future, check your constructs by using

debug.print ZoekFilter

Then you'll see what it looks like in the Immeidate window.

It will not work, as you noted, because your data have a time ellement but you input doesn't. DateTimes are Double type - before the decimal you have a Long date and after the decimal is the time element as a decimal number. Try to think then why the below works. (.Value is default, and hence unnecessary).

If Trim(Nz(txtSearchDate, "")) <> "" Then ZoekFilter = ZoekFilter + " and [DeliverDate] Between #" & Format(Me.txtSearchDate, "mm-dd-yyyy") & "# AND # " & Format(Me.txtSearchDate +1 , "mm-dd-yyyy") & "#"

Update

For dates, a good check, instead of
If Trim(Nz(txtSearchDate, "")) <> "" Then


If IsDate(Me.txtSearchDate) Then ...

 

[bodylojohn]

Ok that's better.

In the future, check your constructs by using

debug.print ZoekFilter

Then you'll see what it looks like in the Immeidate window.

It will not work, as you noted, because your data have a time ellement but you input doesn't. DateTimes are Double type - before the decimal you have a Long date and after the decimal is the time element as a decimal number. Try to think then why the below works. (.Value is default, and hence unnecessary).

If Trim(Nz(txtSearchDate, "")) <> "" Then ZoekFilter = ZoekFilter + " and [DeliverDate] Between #" & Format(Me.txtSearchDate, "mm-dd-yyyy") & "# AND # " & Format(Me.txtSearchDate +1 , "mm-dd-yyyy") & "#"

Update

For dates, a good check, instead of
If Trim(Nz(txtSearchDate, "")) <> "" Then


If IsDate(Me.txtSearchDate) Then ...

Thanks a bunch.
I learned alot today.
I had to add the "cdate" to get it to work.

Very happy here!!!!!!

 

[spikepl]

I had to add the "cdate" to get it to work. ???????????

Sounds not right. Change the format of the textbox where you input the date into the short date format. That will also give you the pop-up clickable calendar thingy.