Need help with Date filtering through Excel VBA

[NikToo73]

Hello, new here but have used the forums many time for various issues. Finally come up against one I can't find a solution for.

I have an Excel workbook which I use to extract data from an Access database using VBA. A requirement has popped up to show things from the last week. Unfortunately, I can't just use a Date()-7 query in Access as the start point might be different, so it needs to be based on seven days prior to a date field in Excel.

So, I have a cell in Excel with a date, in this case "16/01/2014". I want to then use that date in my query to say "greater than 8 days prior to to this date". The field is formatted as a date and the variable (DatafileDate) taken from this field and passed to the function is a Date. In my Select query I then have this line to select the top 5 marked as UK accounts in this case:

SearchText = "SELECT TOP 5 * FROM `" & TableName & "` WHERE `"  & TableName & "`.`Account`='UK' AND `" AND `" &  TableName & "`.`Close Date` > #" & DatafileDate & "#"

It just will not work and I can't get my head around how to make it work. Is there some way I could have something like

 #" & DatafileDate & "#" -8

in there?

Should mention that the TableName stuff is so the name of the table can be set elsewhere. "Close Date" is the column in my Access query I need to filter on.

Thankful for ANY help.

 

[CJ_London]

A few things

"SELECT TOP 5 * FROM `" & TableName & "` WHERE `" & TableName & "`.`Account`='UK' AND `" AND `" & TableName & "`.`Close Date` > #" & DatafileDate & "#"

Assuming tablename, account, close date ad DatafileDate are the names of cells in your spreadsheet it should be

"SELECT TOP 5 * 
FROM [" & TableName & "] 
WHERE ["  & TableName & "].[" & Account & "]='UK' AND [" &  TableName & "].[" & Close Date & "] > #" & format(DatafileDate,"mm/dd/yyyy") & "#"

The square brackets are required in case you have spaces in your names (as with Close Date).
In Access when using the # for dates, the dates must be in american format
you don't use the ` character in SQL

However this will only produce 5 records with a date less than DataFileDate which I don't think is what you want

If your week runs Sunday to Saturday (the default view) then if you want 'last week' to mean anything from Sunday to Saturday in the previous week then try modifying the code as follows

AND [" & TableName & "].[" & Close Date & "] BETWEEN #" & format(DateAdd("d",-weekday(DatafileDate)-6,DatafileDate),"mm/dd/yyyy") & "# AND #" & ("d",-weekday(DatafileDate),DatafileDate),"mm/dd/yyyy") & "#"

 

[NikToo73]

Thanks CJ, that's fixed it. I'm doing it in Excel VBA so don't know if that makes a difference but I needed to keep the single quotes. And yes, it is just Top 5 I want to display, from a certain date. But that date format fixed it all wonderfully. Thank you very much.