SQL Strings, Looping and Using Date Values

I am looking for some advice on passing a range of dates to a SQL string that I have.

I want to run my macro and obtain the date part of my SQL string from a cell on another worksheet, currently I have the following line in to achieve this;

SQL_RTSids = SQL_RTSids & "'" & Worksheets("DateRange").Range("A2") & " 06:00:00' "

QUESTION 1 : How can I pass a date to my SQL string (I am currently passing a text string ie ’14-NOV-10 and then adding the time of 0600hrs onto the date within the SQL string.
I also have a list of dates that I want to pass through my SQL.

Given my limited knowledge of creating a SQL string I presume I would need to;

1. Create my string using the first date (worksheet ‘DateRange”, cell “A2”) from the other worksheet and paste it from a record set into my active worksheet
2. Run the SQL again but this time use the next date (worksheet ‘DateRange”, cell “A3”) on the other worksheet and then paste in underneath the previous set of data
3. Repeat the above until the date in the cell is less than sysdate

QUESTION 2 : How can I loop though the data on my other worksheet and include them in my SQL string, stopping when the date is less than sysdate?

It's difficult to say without a sample workbook, to pass a date in SQL you need to enclose in #'s. It should also be in the US date format "mm/dd/yyyy".

I'm not clear on whether you are wanting to pass a range of dates in one query, or loop through multiple queries, one for each date.

Also you are currently adding "06:00:00" manually, so I'm guessing you don't have this in the date cell?

I think what you are looking for is something like the below:

Please Login or Register  to view this content.

Let me know if this isn't what you are looking for

Thanks for your help.

I managed to get around QUESTION 1 using a TEXT(AA2,"DD-MM-YY HH:MM:SS") formula to convert my date into a text string.

QUESTION 2 is still causing me problems.

I have my date range in cells AB2:AB100 ..... my code is below, how can I loop the SQL to get the section of code .....

& Worksheets("DateRange").Range("AB2") &

.... to get the next date each time it runs (ie. 1st time get date in AB2 and paste code in worksheet (I have the code to paste, it is just the loop that is proiving challneging), 2nd time use date in AB3, 3rd time use date in AB4 etc etc etc until I get to AB100.

Please Login or Register  to view this content.