Thank you for the reply however I have hit an issue. Which I tried to explain on another forum but seems to have got everyone stuck also. What i've found is the sql statement is basically spewing out the wrong records whenever the month/day are below 12. This is basically saying the statement can not differentiate between the month and day if for example it is 01/03/2010. Here is an explenation of what I am actually doing and how I have replicated this error:
A cell looks up a value from a list box which contains "text" dates. These are produced from looking up folder names (which are named as week commencing dates) within a directory.
The cell containing the "text" date is:
01.03.2010
I convert this "text" date which is held in cell K1 in to a real date with the following procedure (split over 2 macros hence why the code doesnt stay within the same with statement) to K2:
- With Sheets("overall performers")
-
.Range("k2").Value = CDate(Replace(.Range("k1"), ".", "/"))
-
End With
-
-
-
Dim myDate As Date
-
myDate = Format(Worksheets("overall performers").Range("K2"), "dd/mm/yyyy")
So I have converted the "text" date in to a real date to give 01/03/2010 for the value in K2 from K1.
This value is then passed to my sql string as myDate:
- Set RS = Db.OpenRecordset("Select tblCheck.lTableID, tblCheck.sStaffNumber, tblError.sStaffNumber, tblcheck.dteCheckCompletedDate " & _
-
"FROM tblCheck LEFT OUTER JOIN tblError " & _
-
"ON tblCheck.lTableID = tblError.lCheckID " & _
-
"WHERE dteCheckCompletedDate BETWEEN " & _
-
"#" & myDate & "# AND #" & myDate - 30 & "#")
This string queries an Access DB for an Accuracy Check ID number, the staff number the check was done on and the staffnumber if an error was found in the check. These are then selected via their date that the check was completed on (dteCheckCompletedDate) from between myDate and myDate - 30 days.
This produces the following table:
- lTableID tblCheck.sStaffNumber tblError.sStaffNumber dteCheckCompletedDate 63945 57222309 30/01/2010 63846 57222309 00000001 30/01/2010
-
-
Down to:
-
-
70806 79902612 03/01/2010 73224 87504104 03/01/2010
As you can see, I passed myDate as being 01/03/2010 the 1st of March 2010. But my results have returned dteCheckCompletedDate 's between 30/01/2010 and 03/01/2010 .. January...
However.. if I select a different date for "myDate" such as 22/02/2010 so 22nd of February 2010 I get the following correct output:
- lTableID tblCheck.sStaffNumber tblError.sStaffNumber dteCheckCompletedDate 53277 43721306 22/02/2010 55273 44614811 22/02/2010
-
-
Down to:
-
-
74642 37089111 23/01/2010 75981 83918005 23/01/2010
So from 22/02/2010 to 23/01/2010.
I really do not understand why this is happening. The only thing I can think of is that somewhere along the line excel or sql can not differentiate between a month and a day if the month and day are sub 12.
For example:
01/03/2010 if you were not told that this was in UK or USA format it could be either march or january.
But..
If you were told 22/02/2010 or 02/22/2010 you could work out that it has to be february as months can't be greater than 12.
Any help with this is appreciated I've tried everything.