setting the forms recordsource to a sql

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff - 19 Oct 2004 06:34 GMT

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?

Reply to this Message

Jeff - 19 Oct 2004 06:55 GMT

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.

> I want to set the recordsource in the on open event of my form. I am having
> trouble with the code,
[quoted text clipped - 10 lines]
> sure is does not like the quotes in the DateAdd.
> What do I do?

Reply to this Message

Duane Hookom - 19 Oct 2004 15:22 GMT

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

Signature

Duane Hookom
MS Access MVP
--

>I want to set the recordsource in the on open event of my form. I am
>having
[quoted text clipped - 12 lines]
> sure is does not like the quotes in the DateAdd.
> What do I do?

Reply to this Message

Jeff - 19 Oct 2004 20:36 GMT

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:
[quoted text clipped - 37 lines]
> > sure is does not like the quotes in the DateAdd.
> > What do I do?

Reply to this Message

Duane Hookom - 19 Oct 2004 22:19 GMT

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.

Signature

Duane Hookom
MS Access MVP
--

> Duane,
> Do you think it is possible to build a "form" like your calendar report?
[quoted text clipped - 48 lines]
>> > sure is does not like the quotes in the DateAdd.
>> > What do I do?

Reply to this Message

John Spencer (MVP) - 19 Oct 2004 15:29 GMT

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

Reply to this Message