CODEWITHSUNDEEP
sqlms-access
Jason Bayldon
Jason Bayldon

Reputation: 1296

Changing SQL select query to VBA

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?

Upvotes: 0

Views: 402

Answers (1)

shahkalpesh
shahkalpesh

Reputation: 33474

"' 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.

Upvotes: 1

Related Questions