Changing SQL select query to VBA

Question

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?

Please visit Bobby Tables - a web site dedicated to educating people about SQL Injection vulnerabilities. - you have one that should be corrected. Unfortunately that site doesn't address passing parms in Access, but the issue remains. — Dan Pichelman
– Dan Pichelman, Commented Jun 3, 2013 at 14:41
@DanPichelman, I don't think Bobby Tables applies to an Access engine. — Sablefoste
– Sablefoste, Commented Jun 3, 2013 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 and MS Access Sql Injection. — Dan Pichelman
– Dan Pichelman, Commented Jun 3, 2013 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
– Jason Bayldon, Commented Jun 3, 2013 at 16:53
@DanPichelman I actually have a question about this too: Is a mail merge susceptible to an SQL injection attack? — Jason Bayldon
– Jason Bayldon, Commented Jun 3, 2013 at 17:21

shahkalpesh · Accepted Answer · 2013-06-03 14:42:50Z

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.

answered Jun 3, 2013 at 14:42

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Changing SQL select query to VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related