stLinkCriteria Date Problem

[johnsqftwr]

The following works for 15/09/2011, 22/09/2011. 29/09/2011
It does not work for 06/10/2011

Both source tables are dates with the same format, but somehow it thinks that the 2 matching 06/10/2011 are different.

I am tearing my hair out with this one and hopefully someone can let me have some VBA to force Date and Me!Date to be the same.

Many thanks


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmStatsUpdate"

stLinkCriteria = "[Date]= #" & Me![Date] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[mdlueck]

The following works for 15/09/2011, 22/09/2011. 29/09/2011
It does not work for 06/10/2011

Quick guess at first glance.

The only date you say does not work has a leading 0 char, The ones you say work do not have leading 0 chars.

I suspect you have a dependency somewhere that the leading 0 gets missing and causes the code not to work properly.

 

[JANR]

This is why :http://allenbrowne.com/ser-36.html

JR

 

[mdlueck]

uuuggg... thanks for the heads up about Access and dates. YUCK! I already dislike date math as is... Access only seems to worsen the situation.

 

[johnsqftwr]

Many thanks for all the help. Yes, the dates that were not working all worked in the US date format thus 06/10/11 was being interpreted as June 10th 2011.

Changing stLinkCriteria = "[Date]= #" & Me![Date] & "#"
to
stLinkCriteria = "[Date]= #" & Format(Me![Date], "dd-mmm-yyyy") & "#" solved the problem