Textbox controlsource = SQL statement ?

[Jay]

Is it possible to assign an SQL statement string to a TextBox's controlsource
property as in the example below? And if so, what is wrong with the syntax
(I get a "Run-time error '7956': The syntax in the subquery in this
expression is incorrect.")

JobID = 1

sSQL = "=SELECT Sum([Hours]*[HourlyRate]) " & _
"FROM Job_TimeCard " & _
"GROUP BY Job_TimeCard.JobID, Job_TimeCard.Billable " & _
"HAVING (((Job_TimeCard.JobID)=" & [JobID] & ") " & _
"AND (Job_TimeCard.Billable)=True))"

Forms!Job_Log.Text186.ControlSource = sSQL

 

[Allen Browne]

No. You can't assign a SQL statement to a Control Source.

Use DSum():
=DSum("[Hours] * [HourlyRate]", "Job_TimeCard",
"(JobID = " & Nz([JobID],0) & ") AND (Billable = True)")

 

[Jay]

Fantastic Allen. Thanks so much for the rapid solution. It worked like a
charm.

!
--
Jay

No. You can't assign a SQL statement to a Control Source.

Use DSum():
=DSum("[Hours] * [HourlyRate]", "Job_TimeCard",
"(JobID = " & Nz([JobID],0) & ") AND (Billable = True)")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Is it possible to assign an SQL statement string to a TextBox's
controlsource
property as in the example below? And if so, what is wrong with the
syntax
(I get a "Run-time error '7956': The syntax in the subquery in this
expression is incorrect.")

JobID = 1

sSQL = "=SELECT Sum([Hours]*[HourlyRate]) " & _
"FROM Job_TimeCard " & _
"GROUP BY Job_TimeCard.JobID, Job_TimeCard.Billable " & _
"HAVING (((Job_TimeCard.JobID)=" & [JobID] & ") " & _
"AND (Job_TimeCard.Billable)=True))"

Forms!Job_Log.Text186.ControlSource = sSQL