By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Sub Passing Variables to Query

P: n/a
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

Feb 22 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Feb 22, 12:48 pm, christianlo...@yahoo.com 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

Feb 22 '07 #2

P: n/a
On Feb 22, 12:48 pm, christianlo...@yahoo.com 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
Feb 22 '07 #3

P: n/a
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.

Feb 22 '07 #4

P: n/a
On Feb 22, 2:22 pm, christianlo...@yahoo.com 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.
Feb 22 '07 #5

P: n/a
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.

Feb 22 '07 #6

P: n/a
On Feb 22, 2:19 pm, christianlo...@yahoo.com 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.

Feb 22 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.