Report RecordSource not working 100%. 1

[Petemush]

I have the following code in the on open event of my form:

Private Sub Report_Open(Cancel As Integer)

Dim sDate As Date, eDate As Date


sDate = Format(Form_frmDatePicker.ActiveXCtl1, "Short Date&quot
eDate = Format(Form_frmDatePicker.ActiveXCtl3, "Short Date&quot

Me.RecordSource = "SELECT * FROM qryDNA WHERE ([Course Date] >= #" _
& sDate & &quot;# And [Course Date] <= #&quot; & eDate & &quot;#)&quot;

End Sub

For some reason, the record doesn't produce the correct results. For test purposes, I have a table with six records in it, all containing different dates in one field. I pick two dates where only 3 of these records should be displayed yet 5 records are shown.

It would make more sense if all 6 were shown but they're not.

Anyone know whats up?

Cheers,

Pete

 

[CosmoKramer]

Pete,

Did you try the Between operator?

Me.RecordSource = "SELECT * FROM qryDNA WHERE ([Course Date] Between #" & sDate & "# And #" & eDate & "#)"

 

[Petemush]

Okay I've worked out the problem but I'm not sure how to fix it!

All my dates are stored in the database using the British convention short date: dd/mm/yy

For some reason, sDate and eDate are being treated as American dates: mm/dd/yy

So when I have the dates

19/07/02 - 19th July 2002
19/08/02 - 19th August 2002
23/02/02 - 23rd February 2002
19/06/02 - 19th June 2002
05/01/02 - 5th January 2002
04/07/02 - 4th July 2002

And have the start and end dates:

Start Date = 01/07/02 - 1st July 2002
End Date = 12/8/02 - 12th August 2002

the start and end dates are actually being treated thus:

Start Date = 01/07/02 - 7th January 2002
End Date = 12/8/02 - 8th December 2002

this explains why 5 of the six dates are being shown, because that's how many british dates fall in the american range.

Now all I need to know is what's causing this, I have my suspicions that its the caldendar control I got from Microsoft's free stuff site. I've checked all the way during the process and if I pick the 1st of July as a date, it's stored as 01/07/02, the british convention for writing the 1st of July but the system is treating it as the 7th of January.

I tried using dates that would make no sense in the american style:

18/7/02 - British: 18th July 2002 - American: Nonsensical

and sure enough I got a &quot;no records exist&quot; message when there should be records.

What's making the system do this? I checked the regional settings and they're fine, not using Format has not made a difference either!

Please Help!

Cheer,

Pete

 

[CosmoKramer]

Pete,

Are you using Access 2000? There is a design limitation of the Calendar control....Check out the Knowledge Base article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q209057

 

[Petemush]

I'm using A97 but still tried implementing the suggestion and it didn't work, still get wrong records.

 

[CosmoKramer]

Did you try formatting the date to the British convention from the start?? Instead of:

sDate = Format(Form_frmDatePicker.ActiveXCtl1, "Short Date")
eDate = Format(Form_frmDatePicker.ActiveXCtl3, "Short Date")

try:

sDate = Format(Form_frmDatePicker.ActiveXCtl1, "dd/mm/yy")
eDate = Format(Form_frmDatePicker.ActiveXCtl3, "dd/mm/yy")

 

[Petemush]

Yep, doesn't make a difference.

I have an example database if anyone wants to test themselves. I think I'm going to have to give up using the Calendar control, I reckon that's what's causing all of this.

Pete

 

[CosmoKramer]

Sure, send it to:

sw3540@yahoo.com

 

[Petemush]

There you go, all sent. I added a little note in the email about how to test it, just in case it's unclear where to start.

Cheers,

Pete

 

[CosmoKramer]

Pete,

A fix with explanation is on the way. Let me know how it works for you.....

 

[Petemush]

I've managed to fix it already, I found a Knowledge Base article that while not relating directly to my problem, explained why it was happening:

http://support.microsoft.com/default.aspx?scid=kb;en-us;q210069

When you use Visual Basic for Applications to concatenate dates into an SQL string, you must use a standard U.S. date format, regardless of the region that you have set in the Regional Settings tool in Control Panel. This article shows you....

Cheers for the help Cosmo, I expect your solution is better than mine so I expect I 'll use it. I ended up turning the date selected into a string and then into an american date using datepart but I think that's a bit long winded.


Cheers again,

Pete

 

[mattyjUK]

What about using Y2K year formats, &quot;yyyy&quot;?
Would that not have resolved your problem?

Matt Matt Jenkins
Information Engineer - EDS