By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,377 Members | 1,655 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,377 IT Pros & Developers. It's quick & easy.

sql string with cell value from excel

100+
P: 374
Hi all,

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

Expand|Select|Wrap|Line Numbers
  1. 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
Mar 3 '10 #1

✓ answered by TheSmileyCoder

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

Try this:
Expand|Select|Wrap|Line Numbers
  1. Function MakeUSDate(x As Variant)
  2.     If Not IsDate(x) Then Exit Function
  3.     MakeUSDate = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#"
  4. End Function

Share this Question
Share on Google+
3 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Expand|Select|Wrap|Line Numbers
  1. Dim myDate as Date
  2. myDate=Worksheets("overall performers").Range("K5")
  3. Set RS = Db.OpenRecordset("Select tblCheck.lTableID, tblCheck.sStaffNumber, tblError.sStaffNumber " & _
  4.      "FROM tblCheck LEFT OUTER JOIN tblError " & _
  5.      "ON tblCheck.lTableID = tblError.lCheckID " & _
  6.      "WHERE dteCheckCompletedDate BETWEEN " & _
  7.      "#" & myDate & "# AND #" & dateadd("m",-1,myDate) & "#") 
  8.  
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.
Mar 3 '10 #2

100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1. With Sheets("overall performers")
  2.  .Range("k2").Value = CDate(Replace(.Range("k1"), ".", "/"))
  3.  End With
  4.  
  5.  
  6. Dim myDate As Date
  7.  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:

Expand|Select|Wrap|Line Numbers
  1.  Set RS = Db.OpenRecordset("Select tblCheck.lTableID, tblCheck.sStaffNumber, tblError.sStaffNumber, tblcheck.dteCheckCompletedDate " & _
  2.      "FROM tblCheck LEFT OUTER JOIN tblError " & _
  3.      "ON tblCheck.lTableID = tblError.lCheckID " & _
  4.      "WHERE dteCheckCompletedDate BETWEEN " & _
  5.      "#" & 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:
Expand|Select|Wrap|Line Numbers
  1. lTableID tblCheck.sStaffNumber tblError.sStaffNumber dteCheckCompletedDate 63945 57222309  30/01/2010 63846 57222309 00000001 30/01/2010 
  2.  
  3. Down to:
  4.  
  5. 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:
Expand|Select|Wrap|Line Numbers
  1. lTableID tblCheck.sStaffNumber tblError.sStaffNumber dteCheckCompletedDate 53277 43721306  22/02/2010 55273 44614811  22/02/2010 
  2.  
  3. Down to:
  4.  
  5. 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.
Mar 5 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Its a regional settings thing. Im guessing your from a place that doesn't use US dates (like myself).

Try this:
Expand|Select|Wrap|Line Numbers
  1. Function MakeUSDate(x As Variant)
  2.     If Not IsDate(x) Then Exit Function
  3.     MakeUSDate = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#"
  4. End Function
Mar 5 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.