setting the forms recordsource to a sql

  • 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
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?
 
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.
 
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
 
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).
 
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?


Duane Hookom said:
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
--

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
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?
Click to expand...
Click to expand...
 
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
--

Jeff said:
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?


Duane Hookom said:
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
--

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
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top