running query using parameters

[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

 

[Marshall Barton]

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?

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

xx = DLookup("PoliceService", "PoliceServiceCode", _
"PoliceServiceCode = " & thevariable)

 

[Guest]

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-----

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?

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

xx = DLookup("PoliceService", "PoliceServiceCode", _
"PoliceServiceCode = " & thevariable)

 

[Marshall Barton]

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 & """")

 

[Bill]

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)

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 & """")

 

[Bill]

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)

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 & """")

 

[Marshall Barton]

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

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 & """")

 

[Bill]

Oh Oh. I should have known that! Thanks for your patience
& expertise.......bill

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

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

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 & """")

.

 

[Marshall Barton]

Oh Oh. I should have known that! Thanks for your patience
& expertise.......bill

<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-----

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

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 & """")

.