Access date bugged?

[T.C]

Hi there,

Im currently working on a project where i want to Filter a date in a report.
I've got a table that contains a column Date.

I can filter it on any date and it works normally, except the dates that start with 1 or 11.

Example: 13-4-2011 <---- works
1-4-2011 <---- Doesnt work
11-4-2011 <---- doesnt work

And all the other dates just work.

Im at a dead end here and i cant seem to figure out what the problem is.

I hope someone can help me out.

Thanks,

T.C

 

[Trevor G]

Welcome to the Forum TC

A Suggestion is to check out the format of the Field for the date, and adjust it so it is clear. So something like this would work.

Open Table in Design View
Select StartDate

In the properties Set the Date Format to dd-mmm-yyyy

Save and close

If you are then using a query you can set the parameter in the criteria as

23-May-2011

 

[T.C]

Hi Trevor,

Thanks for your fast reply.

I tried it with no luck.
It still gives me the same problem.

The strange thing is, all the other dates get filtered normally except the ones starting with 1 and 11......

Greets,

T C

 

[Trevor G]

Can you post a sample of your database?

Have you created a query to run the report?

 

[T.C]

Yes i've created a query for the report.

Could it be something with my code?
Here is the code i got to filter the report:

Private Sub Knop20_Click()
Dim strAfdeling As String
Dim strInvnr As String
Dim strDatum As Date

If IsNull(Me.zk_afdeling.Value) Then
strAfdeling = "Like '*'"
Else
strAfdeling = "='" & Me.zk_afdeling.Value & "'"
End If

If IsNull(Me.zk_invnr.Value) Then
strInvnr = "like '*'"
Else
strInvnr = "='" & Me.zk_invnr.Value & "'"
End If

If Not IsNull(Me.zk_datum.Value) Then
strFilter = "[afdeling_naam] " & strAfdeling & " AND [Invnr] " & strInvnr & " AND [datum_migratie] =#" & Me.zk_datum.Value & "#"
Else
If IsNull(Me.zk_datum.Value) Then
strFilter = "[afdeling_naam] " & strAfdeling & " AND [Invnr] " & strInvnr & " AND [datum_migratie] Like '*'"
End If
End If

With Reports![report]
.Filter = strFilter
.FilterOn = True
End With
End Sub





[Datum_Migratie] is the date column.

Greets,

T C

 

[Brianwarnock]

You say 1 and 11but are you sure that it is not all dates with a day number less than 13 it is probably the usual problem of VBA requiring dates in US format, however it is not normally a problem when using me. Field name but I wonder if using # causes the problem, I don,t think they are needed but have never created a filer like this so could be wrong.

Brian

 

[PeterF]

In VBA you need to format the date in US format.

format(Me.zk_datum.Value, "mm/dd/yyyy")

 

[T.C]

Yeah i just did another thest and indeed everything less than 13 doesnt show up.
Instead it automaticly changes 1-3-2011 to 3-1-2011.

If i dont use the # it will give me an error saying the Datatypes dont match.

Any way to fix the USA format?

Thanks in advance,

T C

Edit:
Ok thanks Peter.
Lets see if it works.

 

[T.C]

I get a syntax error after trying that.
I'm a VBA noob so i dont rly know why it gives me the error.

 

[T.C]

Ok fixed!!!

Thanks for your help.

I found the syntax error and now it all works like a charm.

Really apreciate your time and effort to help me.

Thank you,

T C