International Dates - I'm missing something

[carefully]

I recently lost my Windows system files and am having problems re-creating the settings I had on Access 2000 to get round the Visual Basic limitation of dates having to be in US rather than international dates.

The tables I use including tblPlayerClub (below) are in international dates

The relevant section of the VB subroutine is as follows

...
datPrior = #1/6/1992# ' ' 1st June 1992
strSQLAllPurchases = "SELECT PLAYER_TEAM,Joined, Fee FROM tblPlayerClub WHERE FEE > 0 AND JOINED > #" & datPrior & "#;"

Set rst1 = dbs.OpenRecordset(strSQLAllPurchases)
With rst1

rst1.MoveFirst
Do Until rst1.EOF()
strPlayer_Team = rst1!PLAYER_TEAM
datJoined = rst1!Joined
numFee = rst1!Fee
Debug.Print strPlayer_Team, datJoined, numFee

strSQLTransferFee = &quot;SELECT PLAYER_TEAM,Fee,Joined FROM tblPlayerClub WHERE FEE > &quot; & numFee & &quot; AND Joined <= #&quot; & datJoined & &quot;# AND Joined > #&quot; & datPrior & &quot;# ORDER BY FEE DESC;&quot;
...
The first SQL string creates a datPrior of 06/01/92 ( I could have hard-coded this) and produces a correct listing where Joined is after 1st June 1992

The first record in the recordset rst1 has a Joined value of 15th May 1998 but this is shown as
15/05/98 in the second SQL statement rather than 5/15/98, - unlike the datPrior - so I have a mixture of international and US dates which produces erroneous results

I have played around with reformatting and looking to change underlying table with no luck but as this
worked perfectly before my problems without any changes to code their should be a simple explanation
I have tried both US and UK regional settings in the Control panel but that does not appear to make any difference

Any help would be much appreciated

 

[Databaseguy]

Have you tried the Format() function ??

Format(yourstring,&quot;MM/DD/YYYY&quot

 

[carefully]

Yes. Including the line
datJoined = Format(datJoined, &quot;MM/DD/YYYY&quot

This works where there is a US equivalent
e.g 10th June 2001 changes from
10/06/2001 to 06/10/2001

However, where no US date exists when the DD and MM are reversed e.g 14th June 2001
14/06/2001 remains as 14/06/2001 in the SQL statement

Weird eh? Any other suggestions

 

[Databaseguy]

All dates are stored as a number. For example, today is 37,105. Have you explored this. How about format([YourDate],&quot;#####&quot

 

[carefully]

Actually DatabaseGuy, although the Format suggestion looked strange in VB it appeared to work in actuality. I guess the SQL sorted it out

Anyway thanks