Ms Access VBA Date filter

Question

I'm self studying a VBA code, I got one question. Hope can get your help.

Here is my situation:

I've 15 records in the table, they're date value like:
31 Jan 2016, 29 Feb 2016, 30 Nov 2016, 31 Dec 2016
the field name is [dayTo]

And I've two Forms,
1st Form = "F_temp", a txt box "date0" in this form.
2nd Form = "F_visitorRec", this form will show the record from [dayTo]

I'd like to made a openForm with condition like:

DoCmd.OpenForm "F_visitorRec", acNormal, , "[dayTo] >= #" & Me![date0] & "#"

But I never success, if I input 01 Nov 2016, it will show all 15 record.
If I input 09 Nov 2016, it will show the date large than September.
If I input 13 Nov 2016, the result is correct,
it can show the record lager than 13 Nov 2016.
I know this problem should related to the date format, but I try many method but still can't fix.

Hope can get your help.

What is the data type of your table date? It is always best to refer to dates as year-month-day. — Fionnuala
– Fionnuala, Commented Nov 21, 2016 at 16:33
Thanks ! I've set the field, txt box, query format to yyyy/mmm/dd. But still not work. Gustav's ans is work. Many thanks ! — Dan
– Dan, Commented Nov 22, 2016 at 3:05

Gustav · Accepted Answer · 2016-11-21 18:12:27Z

1

Apply a proper format to the string expression for the date value:

DoCmd.OpenForm "F_visitorRec", acNormal, , "[dayTo] >= #" & Format(Me![date0].Value, "yyyy\/mm\/dd") & "#"

answered Nov 21, 2016 at 18:12

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Over a year ago

Many Thanks ! Gustav ! <br/> But which format I should choose in the field, txt box, query ? or just leave it blank ?

Dan Over a year ago

I've try following but why didn't work. <br/> Dim date0 As String date0 = Format(Date, "dd\mmmm\yyyy")

Gustav Over a year ago

Typically, you set the Format property to Short Date or Long Date.

Collectives™ on Stack Overflow

Ms Access VBA Date filter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related