Data Typing Problem (I think)

[charles.kendricks]

I have a report which simply displays a subset of church visitors whose
first visit is between a user specified pair of dates. The code which
I tied to the OnOpen event of the report is as follows:

Private Sub Report_Open(Cancel As Integer)

Dim dteBegin As Variant
Dim dteEnd As Variant
Dim strSql As String

dteBegin = InputBox("Please Enter Beginning Date", "Enter Dates")
dteEnd = InputBox("Please Enter Ending Date", "Enter Dates")

strSql = "SELECT tblVisitors.ID, tblVisitors.Salutation,
tblVisitors.FirstName,"
strSql = strSql + " tblVisitors.LastName, tblVisitors.Address,
tblVisitors.City,"
strSql = strSql + " tblVisitors.State, tblVisitors.Zip,
tblVisitors.HomePhone,"
strSql = strSql + " tblVisitors.FirstTime, tblVisitors.FollowUp,
tblVisitors.Who,"
strSql = strSql + " tblVisitors.Comments, tblVisitors.RegMem,
tblVisitors.HomeChurch,"
strSql = strSql + " tblVisitors.Reason, tblVisitors.FirstVisit,
[Address]+' '+[City]+"
strSql = strSql + "', '+[State]+'. '+[zip] AS WholeAddr FROM
tblVisitors"
strSql = strSql + " WHERE (((tblVisitors.LastName) Is Not Null)
AND"
strSql = strSql + " ((tblVisitors.FirstVisit) Between " + dteBegin
+ " And " + dteEnd + "))"

DoCmd.RunSQL strSql


End Sub


When I set the dteBegin and dteEnd variable as Variant data type I get
the error message:
Run-time error '2342':
"A RunSQLO action requires an argument consisting of an SQL statement".

When I set same two variables as Date date type I get a:
Run-time error '13'
Data type mismatch

in the latter example the interface highlights the last strSql string
assignment.
What am I doing wrong???

Charles

 

[Douglas J. Steele]

You can't use RunSQL with a SELECT query: it's only for action queries
(INSERT INTO, UPDATE, DELETE). What are you hoping that the RunSQL will do,
though?

Also, when you put dates into a SQL statement, you need to delimit them with
# symbols, and they must be in mm/dd/yyyy format, regardless of what your
Short Date format has been set to through Regional Settings (okay, the
second part isn't strictly true: you can use any unambiguous format, such as
yyyy-mm-dd or dd mmm yyyy. The point it, you cannot use dd/mm/yyyy format
and expect it to work properly for the first 12 days of each month). That
means your last line should be:

strSql = strSql & " ((tblVisitors.FirstVisit) Between " &
Format(dteBegin, "\#mm\/dd\/yyyy\#") & " And " & Format(dteEnd,
"\#mm\/dd\/yyyy\#") & "))"

Note that you should use & to concatenate strings, not +.