Variables & SQL Statement

[Guest]

Hi to All,
I am wondering if it is possible to assign a value to a variable from a SQL
statement. If so, I can I code it so it works, I have tried many ways of
coding it but no success
If I use this one :
Set temp = Rst.Open("SELECT Avril FROM GrossMois WHERE [idbranch] = " & num
& " ") I get the error expected fucnction or variable

If I use this one :
temp = CurrentProject.Connection.Execute _
"SELECT Avril FROM GrossMois WHERE [idbranch] = " & num & " " I get a
syntax error.

My goal is to shrink this line of code:
CurrentProject.Connection.Execute _
"UPDATE BudgetHST SET Avril = (SELECT Avril FROM GrossMois WHERE [idbranch]
= " & num & " + (SELECT Avril FROM GrossMois WHERE [idbranch] = " & num & " *
" & HST & ")) WHERE [IdBranch] = " & num & ""

Thanks

Alain

 

[Dirk Goldgar]

Hi to All,
I am wondering if it is possible to assign a value to a variable from
a SQL statement. If so, I can I code it so it works, I have tried
many ways of coding it but no success
If I use this one :
Set temp = Rst.Open("SELECT Avril FROM GrossMois WHERE [idbranch] =
" & num & " ") I get the error expected fucnction or variable

If I use this one :
temp = CurrentProject.Connection.Execute _
"SELECT Avril FROM GrossMois WHERE [idbranch] = " & num & " " I get a
syntax error.

My goal is to shrink this line of code:
CurrentProject.Connection.Execute _
"UPDATE BudgetHST SET Avril = (SELECT Avril FROM GrossMois WHERE
[idbranch] = " & num & " + (SELECT Avril FROM GrossMois WHERE
[idbranch] = " & num & " * " & HST & ")) WHERE [IdBranch] = " &
num & ""

Thanks

Alain

You mean that you want to get the value [Avril] from the SELECT query
into the variable temp? Have you considered using the DLookup function?

temp = DLookup("Avril", "GrossMois", "[idbranch]=" & num)

You *could* open a recordset on the query, get the value from the
Rst.Fields(0), then close the recordset. But DLookup is simpler.

 

[Guest]

Thanks Dirk,

BTW, is there a good book that I can get more indepth knowledge about
Access, the one I have are good but it seem not enough
Thanks
Alain

Hi to All,
I am wondering if it is possible to assign a value to a variable from
a SQL statement. If so, I can I code it so it works, I have tried
many ways of coding it but no success
If I use this one :
Set temp = Rst.Open("SELECT Avril FROM GrossMois WHERE [idbranch] =
" & num & " ") I get the error expected fucnction or variable

If I use this one :
temp = CurrentProject.Connection.Execute _
"SELECT Avril FROM GrossMois WHERE [idbranch] = " & num & " " I get a
syntax error.

My goal is to shrink this line of code:
CurrentProject.Connection.Execute _
"UPDATE BudgetHST SET Avril = (SELECT Avril FROM GrossMois WHERE
[idbranch] = " & num & " + (SELECT Avril FROM GrossMois WHERE
[idbranch] = " & num & " * " & HST & ")) WHERE [IdBranch] = " &
num & ""

Thanks

Alain

You mean that you want to get the value [Avril] from the SELECT query
into the variable temp? Have you considered using the DLookup function?

temp = DLookup("Avril", "GrossMois", "[idbranch]=" & num)

You *could* open a recordset on the query, get the value from the
Rst.Fields(0), then close the recordset. But DLookup is simpler.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks Dirk,

BTW, is there a good book that I can get more indepth knowledge about
Access, the one I have are good but it seem not enough

I can recommend these from personal knowledge:

Access <version> Developer's Handbook
by Getz, et. al.
from Sybex

Access 2003 Inside Out
by John L. Viescas
from Microsoft Press

The _Access Developer's Handbook_ is rather technical and doesn't try to
teach everything about Access, but is full of the kind of how-to-do-it
advice a developer needs. _Access 2003 Inside Out_ is more
comprehensive and maybe more useful to the general user, but less of a
cookbook.