Convert to SQL

[DS]

How would I convert this to SQL. How do you assign it to the textbox?

Forms!ButtonSales!Text40 = Nz(DSum("[Expr4]", "DollDetailsQ",
"Taxed=True And SalesID=Forms!ButtonSales!SalesID"), 0)

Thanks
DS

 

[Jeff Boyce]

What do you mean "convert this to SQL"? Are you writing a query? Are you
trying to set the value of Text40 in 'code behind form'?

More info, please...

Jeff Boyce
<Office/Access MVP>

 

[DS]

What do you mean "convert this to SQL"? Are you writing a query? Are you
trying to set the value of Text40 in 'code behind form'?

More info, please...

Jeff Boyce
<Office/Access MVP>

How would I convert this to SQL. How do you assign it to the textbox?

Forms!ButtonSales!Text40 = Nz(DSum("[Expr4]", "DollDetailsQ",
"Taxed=True And SalesID=Forms!ButtonSales!SalesID"), 0)

Thanks
DS

Whenever I open the form ButtonSales, I have this on Text40 now. But it
seems to be slow...meaning that I can see the field updating after the
form is open. So I thought if I set the value of text40 using SQL it
would be quicker and neater. So in answer to your question yes I do
want to set the value of Text40 in Code Behind th eform.
Thanks
DS

 

[Jeff Boyce]

Take a look at Access HELP for the OnCurrent property. I suspect you want
the value in Text40 to be "current" with each record you display in the
form.

Access HELP can also get you started on writing expressions like yours for
the event procedure.

--
Regards

Jeff Boyce
<Office/Access MVP>

What do you mean "convert this to SQL"? Are you writing a query? Are you
trying to set the value of Text40 in 'code behind form'?

More info, please...

Jeff Boyce
<Office/Access MVP>

How would I convert this to SQL. How do you assign it to the textbox?

Forms!ButtonSales!Text40 = Nz(DSum("[Expr4]", "DollDetailsQ",
"Taxed=True And SalesID=Forms!ButtonSales!SalesID"), 0)

Thanks
DS

Whenever I open the form ButtonSales, I have this on Text40 now. But it
seems to be slow...meaning that I can see the field updating after the
form is open. So I thought if I set the value of text40 using SQL it
would be quicker and neater. So in answer to your question yes I do
want to set the value of Text40 in Code Behind th eform.
Thanks
DS

 

[Albert D.Kallal]

I can't say that sql will be much faster.

In fact, that dsum is converted to sql anyway.

The advantage of using a expression as you have now is that ms-access can
then figure out "when" to display it.

You could convert the whole thing to code and some sql, but I then you need
to "fire" the code, and that can be a pain in the neck.

You could use the forms on-current, but often during form loading, or
navigating, or entering a new record, you don't yet have the data.

Before you convert, I would MAKE SURE that you got a index on the SalesID
field.

However, for the sake of learning, here is what the code could look like:

dim rstResults as dao.ReocrdSet
dim strSql as string


if nz(me!SalesID,0) <> 0 then

strSql = "select sum("Expr4") from DollDetaisQ where Taxed = True" & _
" and SalesID = " & me!SalesID
set rstResults = currentdb.OpenReocrdSet(strSql)
if rstResults.RecordCount > 0 then
me.Text40 = rstResults(0)
end if
rstResults.Close
set rstResults = nothing
end if