why is this prompting me????

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I want to set the recordsource in the on open event of my form. I am having
trouble with the code,

Dim str_sql As String
Dim IntType As String
IntType = "d"

str_sql = "SELECT
DateAdd(IntType,-Weekday([meetingdate]),[meetingdate])+1
AS WeekOf, tbl_ReportTempData.employeeID FROM tbl_ReportTempData GROUP BY
DateAdd(IntType,-Weekday([meetingdate]),[meetingdate])+1,
tbl_ReportTempData.starttime, tbl_ReportTempData.employeeID ORDER BY
tbl_ReportTempData.starttime;"
Me.RecordSource = str_sql

When I open the form I am being promped for the "IntType". Cant figure this
one out!
Probably something simple. Any help is appreciated.
 
Your DateAdd statements don't look right ---

DateAdd looks like:
DateAdd("d",some number,some date)
 
When you're running a query, it doesn't know about values contained in
variables therefore you can't refer to a variable in your DateAdd function
as you;re doing.

Either:

Dim str_sql As String

str_sql = "SELECT
DateAdd(""d"",-Weekday([meetingdate]),[meetingdate])+1
AS WeekOf, tbl_ReportTempData.employeeID FROM tbl_ReportTempData GROUP BY
DateAdd(IntType,-Weekday([meetingdate]),[meetingdate])+1,
tbl_ReportTempData.starttime, tbl_ReportTempData.employeeID ORDER BY
tbl_ReportTempData.starttime;"
Me.RecordSource = str_sql

or

Dim str_sql As String
Dim IntType As String
IntType = "d"

str_sql = "SELECT
DateAdd(" & Chr(34) & IntType & Chr$(34) &
",-Weekday([meetingdate]),[meetingdate])+1
AS WeekOf, tbl_ReportTempData.employeeID FROM tbl_ReportTempData GROUP BY
DateAdd(IntType,-Weekday([meetingdate]),[meetingdate])+1,
tbl_ReportTempData.starttime, tbl_ReportTempData.employeeID ORDER BY
tbl_ReportTempData.starttime;"
Me.RecordSource = str_sql
 
Jeff said:
I want to set the recordsource in the on open event of my form. I am having
trouble with the code,

Dim str_sql As String
Dim IntType As String
IntType = "d"

str_sql = "SELECT
DateAdd(IntType,-Weekday([meetingdate]),[meetingdate])+1
AS WeekOf, tbl_ReportTempData.employeeID FROM tbl_ReportTempData GROUP BY
DateAdd(IntType,-Weekday([meetingdate]),[meetingdate])+1,
tbl_ReportTempData.starttime, tbl_ReportTempData.employeeID ORDER BY
tbl_ReportTempData.starttime;"
Me.RecordSource = str_sql

When I open the form I am being promped for the "IntType".
Click to expand...


You can not refer to a VBA variable name in an SQL
statement, you have to use its value instead:

str_sql = "SELECT DateAdd(" & IntType & ",-Weekday . . .
 
Back
Top