running query using parameters

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I'm invoking a query
using 'DoCmd.OpenQuery "Q_PoliceServiceCode", ,'. The
query SQL code is:
SELECT [PoliceServiceCode].[PoliceService]
FROM PoliceServiceCode
WHERE ((([PoliceServiceCode].[PoliceServiceCode])=[]));

I'm trying to pass the PoliceServiceCOde via a variable &
would like to have the result (PoliceService) placed in
another variable. What would I need to do to get this
accomplished using the above code or is there a better way
to do it?

Thanks in advance
 
Bill said:
I'm invoking a query
using 'DoCmd.OpenQuery "Q_PoliceServiceCode", ,'. The
query SQL code is:
SELECT [PoliceServiceCode].[PoliceService]
FROM PoliceServiceCode
WHERE ((([PoliceServiceCode].[PoliceServiceCode])=[]));

I'm trying to pass the PoliceServiceCOde via a variable &
would like to have the result (PoliceService) placed in
another variable. What would I need to do to get this
accomplished using the above code or is there a better way
to do it?
Click to expand...

Since you want a single value, you can use the DLookup
function:

xx = DLookup("PoliceService", "PoliceServiceCode", _
"PoliceServiceCode = " & thevariable)
 
Thanks for the lead. I defined XX as variant and inserted
my variable name for 'thevariable'. Upon running I get the
following message:

The expression you entered as a query parameter produced
this error: 'The object doesn't contain the Automation
object 'ON064000."

ON064000 is the value inside my selection criteria
var1able. I looked at the Dlookup example in VBA Help & it
had [] brackets around the return field value & the
selection criteria. The revised code looks like:

PoliceServiceName = DLookup
("[PoliceService]", "PoliceServiceCode", _
"[PoliceServiceCode] = " & pcPoliceServiceIDcode)

Thanks again for any further help.......bill
-----Original Message-----
Bill said:
I'm invoking a query
using 'DoCmd.OpenQuery "Q_PoliceServiceCode", ,'. The
query SQL code is:
SELECT [PoliceServiceCode].[PoliceService]
FROM PoliceServiceCode
WHERE ((([PoliceServiceCode].[PoliceServiceCode])=[]));

I'm trying to pass the PoliceServiceCOde via a variable &
would like to have the result (PoliceService) placed in
another variable. What would I need to do to get this
accomplished using the above code or is there a better way
to do it?
Click to expand...

Since you want a single value, you can use the DLookup
function:

xx = DLookup("PoliceService", "PoliceServiceCode", _
"PoliceServiceCode = " & thevariable)
Click to expand...
 
Thanks for the lead. I defined XX as variant and inserted
my variable name for 'thevariable'. Upon running I get the
following message:

The expression you entered as a query parameter produced
this error: 'The object doesn't contain the Automation
object 'ON064000."

ON064000 is the value inside my selection criteria
var1able. I looked at the Dlookup example in VBA Help & it
had [] brackets around the return field value & the
selection criteria. The revised code looks like:

PoliceServiceName = DLookup
("[PoliceService]", "PoliceServiceCode", _
"[PoliceServiceCode] = " & pcPoliceServiceIDcode)
Click to expand...


Ugh, what an unhelpful error message.

I had interpreted the name ...IDcode to indicate that it was
a number, but, since the value you posted here is clearly a
text string, you need to enclose it in quotes:

PoliceServiceName = DLookup("[PoliceService]", _
"PoliceServiceCode", _
"[PoliceServiceCode] = """ & pcPoliceServiceIDcode & """")
 
Again I appreciate your assistance. I copied your code and
receive the following error:

Compile Error: Wrong number of arguments or invalid
property assignment.

Here's the complete Subroutine:

Sub DLookup()

Dim PoliceServiceName
Dim pcPoliceServiceIDcode As String

pcPoliceServiceIDcode = "ON064000"

PoliceServiceName = DLookup("[PoliceService]", _
"PoliceServiceCode", _
"[PoliceServiceCode] = """ & pcPoliceServiceIDcode & """")

MsgBox PoliceServiceName
End Sub
-----Original Message-----
Thanks for the lead. I defined XX as variant and inserted
my variable name for 'thevariable'. Upon running I get the
following message:

The expression you entered as a query parameter produced
this error: 'The object doesn't contain the Automation
object 'ON064000."

ON064000 is the value inside my selection criteria
var1able. I looked at the Dlookup example in VBA Help & it
had [] brackets around the return field value & the
selection criteria. The revised code looks like:

PoliceServiceName = DLookup
("[PoliceService]", "PoliceServiceCode", _
"[PoliceServiceCode] = " & pcPoliceServiceIDcode)
Click to expand...


Ugh, what an unhelpful error message.

I had interpreted the name ...IDcode to indicate that it was
a number, but, since the value you posted here is clearly a
text string, you need to enclose it in quotes:

PoliceServiceName = DLookup("[PoliceService]", _
"PoliceServiceCode", _
"[PoliceServiceCode] = """ & pcPoliceServiceIDcode & """")
Click to expand...
 
In my previous reply/post I indicated that I had
successfully used DCount. I had also played with DFirst &
DLast & realized that these last two functions retrieved
the information I needed. I think I will be able to
replace the DLookUp with DFirst as long as I give the
criteria.

I still would be interested in finding out why the DLookUp
function responded with the error messages.

Thanks again for your assistance................bill
-----Original Message-----
Thanks for the lead. I defined XX as variant and inserted
my variable name for 'thevariable'. Upon running I get the
following message:

The expression you entered as a query parameter produced
this error: 'The object doesn't contain the Automation
object 'ON064000."

ON064000 is the value inside my selection criteria
var1able. I looked at the Dlookup example in VBA Help & it
had [] brackets around the return field value & the
selection criteria. The revised code looks like:

PoliceServiceName = DLookup
("[PoliceService]", "PoliceServiceCode", _
"[PoliceServiceCode] = " & pcPoliceServiceIDcode)
Click to expand...


Ugh, what an unhelpful error message.

I had interpreted the name ...IDcode to indicate that it was
a number, but, since the value you posted here is clearly a
text string, you need to enclose it in quotes:

PoliceServiceName = DLookup("[PoliceService]", _
"PoliceServiceCode", _
"[PoliceServiceCode] = """ & pcPoliceServiceIDcode & """")
Click to expand...
 
Bill said:
Again I appreciate your assistance. I copied your code and
receive the following error:

Compile Error: Wrong number of arguments or invalid
property assignment.

Here's the complete Subroutine:

Sub DLookup()

Dim PoliceServiceName
Dim pcPoliceServiceIDcode As String

pcPoliceServiceIDcode = "ON064000"

PoliceServiceName = DLookup("[PoliceService]", _
"PoliceServiceCode", _
"[PoliceServiceCode] = """ & pcPoliceServiceIDcode & """")

MsgBox PoliceServiceName
End Sub
Click to expand...


Billll, Biiilllll, you can't create a Sub named DLookup
without preempting Access's built-in function with the same
name.

Just rename your pocedure to something else such as
GetServiceName()
--
Marsh
MVP [MS Access]


-----Original Message-----
Thanks for the lead. I defined XX as variant and inserted
my variable name for 'thevariable'. Upon running I get the
following message:

The expression you entered as a query parameter produced
this error: 'The object doesn't contain the Automation
object 'ON064000."

ON064000 is the value inside my selection criteria
var1able. I looked at the Dlookup example in VBA Help & it
had [] brackets around the return field value & the
selection criteria. The revised code looks like:

PoliceServiceName = DLookup
("[PoliceService]", "PoliceServiceCode", _
"[PoliceServiceCode] = " & pcPoliceServiceIDcode)
Click to expand...


Ugh, what an unhelpful error message.

I had interpreted the name ...IDcode to indicate that it was
a number, but, since the value you posted here is clearly a
text string, you need to enclose it in quotes:

PoliceServiceName = DLookup("[PoliceService]", _
"PoliceServiceCode", _
"[PoliceServiceCode] = """ & pcPoliceServiceIDcode & """")
Click to expand...
Click to expand...
 
Oh Oh. I should have known that! Thanks for your patience
& expertise.......bill
-----Original Message-----
Bill said:
Again I appreciate your assistance. I copied your code and
receive the following error:

Compile Error: Wrong number of arguments or invalid
property assignment.

Here's the complete Subroutine:

Sub DLookup()

Dim PoliceServiceName
Dim pcPoliceServiceIDcode As String

pcPoliceServiceIDcode = "ON064000"

PoliceServiceName = DLookup("[PoliceService]", _
"PoliceServiceCode", _
"[PoliceServiceCode] = """ & pcPoliceServiceIDcode & """")

MsgBox PoliceServiceName
End Sub
Click to expand...


Billll, Biiilllll, you can't create a Sub named DLookup
without preempting Access's built-in function with the same
name.

Just rename your pocedure to something else such as
GetServiceName()
--
Marsh
MVP [MS Access]


-----Original Message-----

Thanks for the lead. I defined XX as variant and inserted
my variable name for 'thevariable'. Upon running I get the
following message:

The expression you entered as a query parameter produced
this error: 'The object doesn't contain the Automation
object 'ON064000."

ON064000 is the value inside my selection criteria
var1able. I looked at the Dlookup example in VBA Help
Click to expand...
&
it
had [] brackets around the return field value & the
selection criteria. The revised code looks like:

PoliceServiceName = DLookup
("[PoliceService]", "PoliceServiceCode", _
"[PoliceServiceCode] = " & pcPoliceServiceIDcode)


Ugh, what an unhelpful error message.

I had interpreted the name ...IDcode to indicate that
Click to expand...
it
was
a number, but, since the value you posted here is
Click to expand...
clearly
a
text string, you need to enclose it in quotes:

PoliceServiceName = DLookup("[PoliceService]", _
"PoliceServiceCode", _
"[PoliceServiceCode] = """ & pcPoliceServiceIDcode & """")
Click to expand...
Click to expand...

.
Click to expand...
 
Bill said:
Oh Oh. I should have known that! Thanks for your patience
& expertise.......bill
Click to expand...

<grin> Don't worry about it, sooner or later we all do the
same kind of thing. Aren't you glad you've gotten it out of
your system and will never make that mistake again <gdr>
--
Marsh
MVP [MS Access]


-----Original Message-----
Bill said:
Again I appreciate your assistance. I copied your code and
receive the following error:

Compile Error: Wrong number of arguments or invalid
property assignment.

Here's the complete Subroutine:

Sub DLookup()

Dim PoliceServiceName
Dim pcPoliceServiceIDcode As String

pcPoliceServiceIDcode = "ON064000"

PoliceServiceName = DLookup("[PoliceService]", _
"PoliceServiceCode", _
"[PoliceServiceCode] = """ & pcPoliceServiceIDcode & """")

MsgBox PoliceServiceName
End Sub
Click to expand...


Billll, Biiilllll, you can't create a Sub named DLookup
without preempting Access's built-in function with the same
name.

Just rename your pocedure to something else such as
GetServiceName()
--
Marsh
MVP [MS Access]


-----Original Message-----

Thanks for the lead. I defined XX as variant and
inserted
my variable name for 'thevariable'. Upon running I get
the
following message:

The expression you entered as a query parameter produced
this error: 'The object doesn't contain the Automation
object 'ON064000."

ON064000 is the value inside my selection criteria
var1able. I looked at the Dlookup example in VBA Help &
it
had [] brackets around the return field value & the
selection criteria. The revised code looks like:

PoliceServiceName = DLookup
("[PoliceService]", "PoliceServiceCode", _
"[PoliceServiceCode] = " & pcPoliceServiceIDcode)


Ugh, what an unhelpful error message.

I had interpreted the name ...IDcode to indicate that it
was
a number, but, since the value you posted here is clearly
a
text string, you need to enclose it in quotes:

PoliceServiceName = DLookup("[PoliceService]", _
"PoliceServiceCode", _
"[PoliceServiceCode] = """ & pcPoliceServiceIDcode & """")
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top