intermittent form link criteria fault

[getting balder]

and tearing out my hair with this one...
Someone out there please help:

I have a form that should open, linking to the date on another form (that is changeable using a Calendar Control), and display records for that date.

My underlying data is available for a variety of dates but some of the dates (and therefore the records) will not appear when this form opens.

They will all show if the form is opened without the filter. Or they will show in a report.
So I know that the coding is correct.
This intermittent-type thing is really bugging me.

Have even tried re-typing some of the dates in the table in case the format has somhow got changed.

Nothing is working.

 

[RuralGuy]

It is difficult to say what might be the problem with the information you have supplied so far. Could it be the format of your date? Return Dates in US #mm/dd/yyyy# format

 

[getting balder]

Don't think so as it works for some records and not others!!....

 

[RuralGuy]

Why not temporarily put a MsgBox on your next form OnLoad event that shows the current filter and see what you are getting.

 

[getting balder]

Sure, worth a try, thanks.
Now I have to remember how!!

 

[gemma-the-husky]

i wouldn't be at all surprised if its not to do with US/UK dates, as RG says, which is a nasty surprise lurking to catch all non-US (especially UK) citizens.

if you refer to a data in a sql statement (and in some other contexts), access tries to use a us date if it can.

so an unambiguous UK date like 13/11/06 is treated as nov 13th, but an ambiguous date like 12/11/06 is treated as Dec 11th (US) instead of nov 12th (UK).

the easiest way to avoid this is everywhere this might happen force the date to be a long date

as format(mydate,"long date") which instead of using the ambiguous 12/11/06 treats it (unambiguously) as 12th Nov 2006.

 

[getting balder]

Have just tried changing dates to long date, but seems to makes no difference.
The stupid thing is the form works without the filtering...
And I have tried using different techniques for opening the form, but it always fails in these particular dates.
I think it may be the ambiguous ones so what else do I need to change, that I must have missed?

 

[getting balder]

and where do I stick the 'as format(mydate,"long date") ' you mentioned?
(Clean answers only please!)

 

[getting balder]

progress - slight but sure.

Have noticed that the date of 11 December shows up when link criteria should be 12/11/2006. (thanks guys - you were spot on!)
It is definitely to do with the us/uk date thing, but can someone help with the actual coding to resolve this?
I have not come across this before and I have selected on dates many times!!

 

[RuralGuy]

Doesn't the link I supplied help? Post your WhereCondition string and we'll offer suggestions.

 

[getting balder]

Hi, thanks for getting back to me - didn't understand from your link....

I have got the following code where the Me.Text21 is the Date box updating from the Calendar6 control. I also tried it directly using Me.Calendar6.Value but the prob remains!
:

Private Sub Calendar6_Exit(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_Schedule"
stLinkCriteria = "Date=" & "#" & Me.Text21 & "#"

DoCmd.OpenForm stDocName, , , stLinkCriteria


End Sub

 

[RuralGuy]

Try the following:

Private Sub Calendar6_Exit(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_Schedule"
stLinkCriteria = "[Date] =" & Format(Me.Text21, "\#mm\/dd\/yyyy\#;;;\N\u\l\l")

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

BTW, Date in not a very good name for your field. It can easily confuse Access since it is a reserved word. Here's some links for clarification:
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

I'm not so sure the exit event is where I would put your code but if it is working for you then......

 

[getting balder]

You star!
A BIG THANK YOU

I would never have found that in a million years.

Don't know what it means but it seems to work!

3 days of work (or lack of) is ended.

Cheers

 

[RuralGuy]

Glad I was able to help. If you look up Format() in VBA help (not Access help) it explains what is on that line.

 

[getting balder]

Seriously, thanks!

 

[RuralGuy]

You are very welcome!