Sub Passing Variables to Query

[christianlott1]

Just gearing up to do a complex series of update queries on a terrible
set of data.

I need this to work:

----------
Public Sub blah()

Dim Result As String
Result = 9
DoCmd.OpenQuery "NewQuery"

End Sub
-----------

Where the query criteria is [Result].

Currently the query asks me for 'Result' in a Parameter pop up, so my
parameter is not being passed. I also tried [Module1]![Result] and
[blah]![Result]. Still not working.

Thanks,

Christian

[storrboy]

On Feb 22, 12:48 pm, [e-mail] wrote:

Just gearing up to do a complex series of update queries on a terrible
set of data.

I need this to work:

----------
Public Sub blah()

Dim Result As String
Result = 9
DoCmd.OpenQuery "NewQuery"

End Sub
-----------

Where the query criteria is [Result].

Currently the query asks me for 'Result' in a Parameter pop up, so my
parameter is not being passed. I also tried [Module1]![Result] and
[blah]![Result]. Still not working.

Thanks,

Christian

Easiest is to modify the sub to run the query directly instead of
opening one.

Public Sub blah()
On Error Goto Stoprun
Dim db As Database
Dim sqlStr As String

Set db=CurrentDB
sqlStr= "InsertQryHereIncludingCriteria_UserVariblesIfNece ssary"
db.Execute sqlStr,dbFailOnError

Exit_Here:
Set db = Nothing
Exit Sub

Stoprun:
_DoSomethingToHandleError
End Sub

[Bruce]

On Feb 22, 12:48 pm, [e-mail] wrote:

Just gearing up to do a complex series of update queries on a terrible
set of data.

I need this to work:

----------
Public Sub blah()

Dim Result As String
Result = 9
DoCmd.OpenQuery "NewQuery"

End Sub
-----------

Where the query criteria is [Result].

Currently the query asks me for 'Result' in a Parameter pop up, so my
parameter is not being passed. I also tried [Module1]![Result] and
[blah]![Result]. Still not working.

Thanks,

Christian

You'll want to use a querydef object, e.g.

Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("NewQuery")
qdf.Parameters("Result") = 9
qdf.Execute

Hope this helps,
Bruce

[christianlott1]

Thanks storrboy and Bruce for your help.

Now how do I pass a parameter with embedded quotes?

--
SubmidType = "(Not Like "CL" & "*" And Not Like "EP" & "*" And Not
Like "FQ" & "*" And Not Like "*" & "AR" & "*")"
--

Or just:

Not Like "CL" & "*" And Not Like "EP" & "*" And Not Like "FQ" & "*"
And Not Like "*" & "AR" & "*"

---------

I know I should just make this a subquery but it will need to change
as the program iterates. I didn't want to make a bunch of named
queries.

[storrboy]

On Feb 22, 2:22 pm, [e-mail] wrote:

Thanks storrboy and Bruce for your help.

Now how do I pass a parameter with embedded quotes?

--
SubmidType = "(Not Like "CL" & "*" And Not Like "EP" & "*" And Not
Like "FQ" & "*" And Not Like "*" & "AR" & "*")"
--

Or just:

Not Like "CL" & "*" And Not Like "EP" & "*" And Not Like "FQ" & "*"
And Not Like "*" & "AR" & "*"

---------

I know I should just make this a subquery but it will need to change
as the program iterates. I didn't want to make a bunch of named
queries.

See the help file for "Using Quotes In Strings" o some such thing.
There are many examples and will depend on the method you chose to
use.

[christianlott1]

Unrelated question, what's wrong with this?

-------------
SepRank = Null

If Not IsNull(Entered) Then
j = InStr(1, Entered, "#")
If j <0 Then
SepRank = Mid(Entered, j + 1, 1)
End If
End If
--------------

It's supposed to separate out -

from - "asdasd #1"
to - "1"

(Please God, help these poor people in their completely screwed up
database!)

Thanks in advance.

[christianlott1]

On Feb 22, 2:19 pm, [e-mail] wrote:

-------------
SepRank = Null

If Not IsNull(Entered) Then
j = InStr(1, Entered, "#")
If j <0 Then
SepRank = Mid(Entered, j + 1, 1)
End If
End If
--------------

Answering my own questions..

When I took out 'SepRank=Null' it ran fine.