Textbox controlsource = SQL statement ?

J

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
 
A

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)")
 
J

Jay

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

!
--
Jay


Allen Browne said:
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.

Jay said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top