Using Arguments in RunSQL?

[Guest]

Hi,

I want to use some of the Arguments from my Function within my RunSQL
statement. The RunSQL doesn't seem to recognize the Arguments but sees them
as just text (the argument name itself rather than the argument's value).

Is there some way to use Arguments or Variables within your RunSQL statement?

Thanks.

 

[Dirk Goldgar]

Hi,

I want to use some of the Arguments from my Function within my RunSQL
statement. The RunSQL doesn't seem to recognize the Arguments but
sees them as just text (the argument name itself rather than the
argument's value).

Is there some way to use Arguments or Variables within your RunSQL
statement?

Sure. You just have to build the value of the variable (or argument)
into the SQL string you pass to RunSQL. For example:

Dim MyVar1 As Variant
Dim MyVar2 As Variant

MyVar1 = 123
MyVar2 = 456

DoCmd.RunSQL "UPDATE MyTable SET FieldA = " & MyVar1 & _
" WHERE FieldB = " & MyVar2

 

[fredg]

Hi,

I want to use some of the Arguments from my Function within my RunSQL
statement. The RunSQL doesn't seem to recognize the Arguments but sees them
as just text (the argument name itself rather than the argument's value).

Is there some way to use Arguments or Variables within your RunSQL statement?

Thanks.

Dim strX as String
strX = "Hello"

Docmd.RunSQL "Update YourTable Set YourTable .FieldName = '" &
UCase(strX) & "';"

or....

Dim intX as Integer
intX = 123
DoCmd.RunSQL "Delete YourTable.* From YourTable Where
YourTable.FieldName = " & intX

Notice the different usage of ' and " between the 2 different variable
datatypes.

 

[Guest]

No need. Figured it out. Just have to create string from sql then do a
createquerydef to pass the string with variables included to db engine.
Finally use .execute to run the querydef.

Thanks anyway

 

[John Vinson]

Hi,

I want to use some of the Arguments from my Function within my RunSQL
statement. The RunSQL doesn't seem to recognize the Arguments but sees them
as just text (the argument name itself rather than the argument's value).

Is there some way to use Arguments or Variables within your RunSQL statement?

Thanks.

I'd suggest not using the RunSQL method at all in this case; use a
Querydef instead:

Dim qd As DAO.Querydef
Dim db As DAO.Database
Dim strSQL As String
Dim prm As Parameter
....
strSQL = <some SQL string with parameters in [brackets]>
Set db = CurrentDb
' create an unnamed and therefore unstored Query
Set qd = db.CreateQuerydef("", strSQL)
For Each prm In qd.Parameters
' Evaluate each parameter in turn; e.g. if the parameter is
' [Forms]![frmX]![ctlY] look at that form and find the value
' in that control
prm.Value = Eval(prm.Name)
Next prm
qd.Execute dbFailOnError

As an alternative, you can concatenate the *value* of each parameter
into the SQL string instead of its name.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps