sql string with cell value from excel

[munkee]

Hi all,

I use the following string to get data from an access database within excel.

Set RS = Db.OpenRecordset("Select tblCheck.lTableID, tblCheck.sStaffNumber, tblError.sStaffNumber FROM tblCheck LEFT OUTER JOIN tblError ON tblCheck.lTableID = tblError.lCheckID WHERE dteCheckCompletedDate BETWEEN date() AND date()-30")

From the above sql string i want to change "date()" to reflect the date specified within an excel cell.

The cell is located at: worksheet "overall performers" cell K5 and is formatted as dd/mm/yyyy


How would the sql string look to achieve this?

Thanks

[TheSmileyCoder]

Dim myDate as Date
myDate=Worksheets("overall performers").Range("K5")
Set RS = Db.OpenRecordset("Select tblCheck.lTableID, tblCheck.sStaffNumber, tblError.sStaffNumber " & _
     "FROM tblCheck LEFT OUTER JOIN tblError " & _
     "ON tblCheck.lTableID = tblError.lCheckID " & _
     "WHERE dteCheckCompletedDate BETWEEN " & _
     "#" & myDate & "# AND #" & dateadd("m",-1,myDate) & "#")

A bit of explanation. The Hash # is used to denote date literals, like " is used to denote string literals.

The dateAdd will add (-1) month to myDate. If you want it to add (-30) days instead use: dateadd("d",-30,myDate)
Hope it made sense.

[munkee]

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 (dteCheckComple tedDate) 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 dteCheckComplet edDate '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.

[TheSmileyCoder]

Its a regional settings thing. Im guessing your from a place that doesn't use US dates (like myself).

Try this:

Function MakeUSDate(x As Variant)
    If Not IsDate(x) Then Exit Function
    MakeUSDate = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#"
End Function