0

I am trying to transition my queries into the ability to select data for a mail merge using VBA. Right now I have a query built in access that uses two parameters:

PARAMETERS startdate DateTime, enddate DateTime;

and i basically want to recreate that query at run time to populate letters.

date1 = InputBox("Please enter the start date, e.g. 5/28", "Please enter the start date")
date2 = InputBox("Please enter the end date, e.g. 6/28", "Please enter the end date")   

date1 = date1 & "/" & thisYear
date2 = date2 & "/" & thisYear


sqlstatement:="SELECT * FROM [Customer Data] WHERE [Customer Data].[Status]='Complete' AND [Customer Data].[CompletedBy] = '" & userID & "' AND [Customer Data].[Date Completed] Between " & date1 & " And " & date2 & ";"

There is something inherently wrong with my "between date1 and date2" part, if I remove that part of the statement, the query works fine, but i will require the ability to specify a date range. Right now it is telling me that no data was found. What am I doing wrong with that part?

Jason Bayldon
  • 1,296
  • 6
  • 24
  • 41
  • Please visit [Bobby Tables - a web site dedicated to educating people about SQL Injection vulnerabilities.](http://bobby-tables.com/) - you have one that should be corrected. Unfortunately that site doesn't address passing parms in Access, but the issue remains. – Dan Pichelman Jun 03 '13 at 14:41
  • @DanPichelman, I don't think Bobby Tables applies to an Access engine. – Sablefoste Jun 03 '13 at 14:43
  • @SableFoste - I looked it up. Access is less vulnerable to Sql Injection than others, but there are still some issues. See also [Non-web SQL Injection](http://stackoverflow.com/q/512174/427192) and [MS Access Sql Injection](http://nibblesec.org/files/MSAccessSQLi/MSAccessSQLi.html). – Dan Pichelman Jun 03 '13 at 14:52
  • @DanPichelman considering I inherited a database where nothing is locked down, sql injections are the least worry right now. However, I will take this into consideration at redesign, thanks for the links – Jason Bayldon Jun 03 '13 at 16:53
  • @DanPichelman I actually have a question about this too: Is a mail merge susceptible to an SQL injection attack? – Jason Bayldon Jun 03 '13 at 17:21
  • I don't know - that might be a good question to post on its own. – Dan Pichelman Jun 03 '13 at 17:59

1 Answers1

1

"' AND [Customer Data].[Date Completed] Between #" & date1 & "# And #" & date2 & "# ;"

Just as you are using apostrophe to surround the string data, the date values should be enclosed with #. Post your comments, if this doesn't help.

shahkalpesh
  • 33,172
  • 3
  • 63
  • 88