Date filter problem

[Steve C]

May I ask your help with this one please?

My Form0 has a Field called DueDate in Short Date Format (I’m in the UK).

When I click DueDate, I want to open Form1 filtered to DueDate dates.

But I get mixed results. Either the records returned have month and day are reversed or I get no records or it works perfectly!

Any pointers would be appreciated – thank you.

Here’s the VBA that the wizard wrote

Private Sub DueDate_Click()
On Error GoTo Err_DueDate_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form1"

stLinkCriteria = "[DueDate]=" & "#" & Me![DueDate] & "#"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_DueDate_Click:
Exit Sub

Err_DueDate_Click:
MsgBox Err.Description
Resume Exit_DueDate_Click

End Sub

 

[pbaldy]

See if this helps:

http://allenbrowne.com/ser-36.html

 

[Steve C]

Yes - it looks like it will, I'll have a read through and see how I go.

Thank you for such a quick answer.

 

[pbaldy]

No problem. Post back if you get stuck.

 

[Steve C]

The link doesn't quite solve the problem - but it took me near enough to find the rest of the way.

Here's what I did for the benefit of anyone else reading this.

I want to open Form1 from an OnClick event in Form0 and filter by date. The Wizard SEEMS to write the VBA for me BUT DOESN'T

The Wizard declares a variable called stLinkCriteria which will be the String for the filter in Form1 like this;

stLinkCriteria = "[DueDate]=" & "#" & Me![DueDate] & "#"

BUT, being in the UK, my DueDate Field format isn't US and the String doesn't always work although some times it does (depends on what the date is I think).

I adjusted my code to read;

stLinkCriteria = "[DueDate]=" & "#" & Format(Me.DueDate, "mm\/dd\/yyyy") & "#"

And seems to work perfectly. We'll seeeee

Thanks again PBaldy and who ever AllenBrowne is.

 

[pbaldy]

Happy to help. Allen is a very nice gentleman from Australia I had the good fortune to meet a few years ago. He's very well known in the Access community.