setting the forms recordsource to a sql

[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
str_sql = "SELECT DateAdd("d",-Weekday([meetingdate]),[meetingdate])+1
AS WeekOf, tbl_ReportTempData.employeeID FROM tbl_ReportTempData GROUP BY
DateAdd(d,-Weekday([meetingdate]),[meetingdate])+1,
tbl_ReportTempData.starttime, tbl_ReportTempData.employeeID ORDER BY
tbl_ReportTempData.starttime;"
Me.RecordSource = str_sql

The problem is in the "str_sql =" line. I am getting a syntax error. I am
sure is does not like the quotes in the DateAdd.
What do I do?

 

[Jeff]

I have set the "d" to string IntType = "d" and placed the IntType in both
of the DateAdd's. Now when I run the form it is prompting me for IntDate.

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

I must be missing something simple.

 

[Duane Hookom]

You are attempting to include quotes inside of quotes. Try use single
quotes:
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('d',-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
str_sql = "SELECT " & _
"DateAdd('d',-Weekday([meetingdate]),[meetingdate])+1 AS WeekOf, " & _
"employeeID " & _
"FROM tbl_ReportTempData " & _
"GROUP BY DateAdd('d',-Weekday([meetingdate]),[meetingdate])+1, " & _
"starttime, employeeID " & _
"ORDER BY starttime;"
Me.RecordSource = str_sql

 

[John Spencer (MVP)]

When you want to include quotes inside a string in VBA you need to double them
up. So the assignment statement should end up looking something like the following.

str_sql = "SELECT " & _
" DateAdd(""d"",-Weekday([meetingdate]),[meetingdate])+1 as WeekOf," & _
" tbl_ReportTempData.employeeID" & _
" FROM tbl_ReportTempData" & _
" GROUP BY " &_
" DateAdd(""d"",-Weekday([meetingdate]),[meetingdate])+1," & _
" tbl_ReportTempData.starttime," & _
" tbl_ReportTempData.employeeID" & _
" ORDER BY tbl_ReportTempData.starttime"

By the way, I added quotes around the second instance of d in DateAdd and
reformatted with the continuation character for easier readability. note the
included spaces at the beginning of the lines (after the quotation mark).

 

[Jeff]

Duane,
Do you think it is possible to build a "form" like your calendar report?
Will the form allow a continuous type if it has subforms depicting the days
instead of the subreports?

You are attempting to include quotes inside of quotes. Try use single
quotes:
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('d',-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
str_sql = "SELECT " & _
"DateAdd('d',-Weekday([meetingdate]),[meetingdate])+1 AS WeekOf, " & _
"employeeID " & _
"FROM tbl_ReportTempData " & _
"GROUP BY DateAdd('d',-Weekday([meetingdate]),[meetingdate])+1, " & _
"starttime, employeeID " & _
"ORDER BY starttime;"
Me.RecordSource = str_sql



--
Duane Hookom
MS Access MVP
--

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
str_sql = "SELECT DateAdd("d",-Weekday([meetingdate]),[meetingdate])+1
AS WeekOf, tbl_ReportTempData.employeeID FROM tbl_ReportTempData GROUP BY
DateAdd(d,-Weekday([meetingdate]),[meetingdate])+1,
tbl_ReportTempData.starttime, tbl_ReportTempData.employeeID ORDER BY
tbl_ReportTempData.starttime;"
Me.RecordSource = str_sql

The problem is in the "str_sql =" line. I am getting a syntax error. I
am
sure is does not like the quotes in the DateAdd.
What do I do?

 

[Duane Hookom]

I had given some thought to a dynamic form but it didn't seem like it would
be worth the effort. There are some third party "form" solutions that I
would purchase before I would do much with creating one myself.

--
Duane Hookom
MS Access MVP
--

Duane,
Do you think it is possible to build a "form" like your calendar report?
Will the form allow a continuous type if it has subforms depicting the
days
instead of the subreports?

You are attempting to include quotes inside of quotes. Try use single
quotes:
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('d',-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
str_sql = "SELECT " & _
"DateAdd('d',-Weekday([meetingdate]),[meetingdate])+1 AS WeekOf, " &
_
"employeeID " & _
"FROM tbl_ReportTempData " & _
"GROUP BY DateAdd('d',-Weekday([meetingdate]),[meetingdate])+1, " & _
"starttime, employeeID " & _
"ORDER BY starttime;"
Me.RecordSource = str_sql



--
Duane Hookom
MS Access MVP
--

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
str_sql = "SELECT DateAdd("d",-Weekday([meetingdate]),[meetingdate])+1
AS WeekOf, tbl_ReportTempData.employeeID FROM tbl_ReportTempData GROUP BY
DateAdd(d,-Weekday([meetingdate]),[meetingdate])+1,
tbl_ReportTempData.starttime, tbl_ReportTempData.employeeID ORDER BY
tbl_ReportTempData.starttime;"
Me.RecordSource = str_sql

The problem is in the "str_sql =" line. I am getting a syntax error.
I
am
sure is does not like the quotes in the DateAdd.
What do I do?