Query based on list box value

  • Thread starter Thread starter Dkline
  • Start date Start date
D

Dkline

I'm trying to set the Criteria of a report from the value selected in a list
box.

Basic question - when do I use a SELECT statement vs. using the OpenReport?

Second question - why isn't the below working.

The strWhere_PolicyNumber ends up being:
((([Policy Number].PolicyNumber) = 'VL300204'))"

I get the report but there is no selection - I get ALL the records.

What am I missing?

Here's the code at the moment:
strValue_PolicyNumber = Me.lstPolNum.Value
MsgBox (strValue_PolicyNumber)
strWhere_PolicyNumber = "((([Policy Inventory].PolicyNumber) = "
MsgBox (strWhere_PolicyNumber)
strWhere_PolicyNumber = strWhere_PolicyNumber & "'" & strValue_PolicyNumber
& "'" & "))"
MsgBox (strWhere_PolicyNumber)
DoCmd.OpenReport "rptFinalBalanceFile", acViewNormal, ,
strWhere_PolicyNumber
'DoCmd.OpenReport "rptFinalBalanceFile", acViewNormal,
"qryFinalBalanceFile", strWhere_PolicyNumber

This is the SELECT statement.
'SELECT [Policy Inventory].PolicyNumber, [Final Balance File].[Investment
Account Name], [Final Balance File].Site, [Final Balance File].[Ending
Balance 12/03], [Final Balance File].Manager
'FROM [Policy Inventory] INNER JOIN [Final Balance File] ON [Policy
Inventory].PolicyNumber = [Final Balance File].[Policy Number]
'WHERE ((([Policy Inventory].PolicyNumber) = "VL300204"))
'ORDER BY [Policy Inventory].PolicyNumber;

Don
 
Don't use the table name in your where clause.
strWhere_PolicyNumber = "PolicyNumber = """ & Me.lstPolNum & """"
DoCmd.OpenReport "rptFinalBalanceFile", acViewNormal, ,
strWhere_PolicyNumber
This assumes the field is text, not numeric.
 
Thanks. Up and running!

Duane Hookom said:
Don't use the table name in your where clause.
strWhere_PolicyNumber = "PolicyNumber = """ & Me.lstPolNum & """"
DoCmd.OpenReport "rptFinalBalanceFile", acViewNormal, ,
strWhere_PolicyNumber
This assumes the field is text, not numeric.

--
Duane Hookom
MS Access MVP


Dkline said:
I'm trying to set the Criteria of a report from the value selected in a list
box.

Basic question - when do I use a SELECT statement vs. using the OpenReport?

Second question - why isn't the below working.

The strWhere_PolicyNumber ends up being:
((([Policy Number].PolicyNumber) = 'VL300204'))"

I get the report but there is no selection - I get ALL the records.

What am I missing?

Here's the code at the moment:
strValue_PolicyNumber = Me.lstPolNum.Value
MsgBox (strValue_PolicyNumber)
strWhere_PolicyNumber = "((([Policy Inventory].PolicyNumber) = "
MsgBox (strWhere_PolicyNumber)
strWhere_PolicyNumber = strWhere_PolicyNumber & "'" & strValue_PolicyNumber
& "'" & "))"
MsgBox (strWhere_PolicyNumber)
DoCmd.OpenReport "rptFinalBalanceFile", acViewNormal, ,
strWhere_PolicyNumber
'DoCmd.OpenReport "rptFinalBalanceFile", acViewNormal,
"qryFinalBalanceFile", strWhere_PolicyNumber

This is the SELECT statement.
'SELECT [Policy Inventory].PolicyNumber, [Final Balance File].[Investment
Account Name], [Final Balance File].Site, [Final Balance File].[Ending
Balance 12/03], [Final Balance File].Manager
'FROM [Policy Inventory] INNER JOIN [Final Balance File] ON [Policy
Inventory].PolicyNumber = [Final Balance File].[Policy Number]
'WHERE ((([Policy Inventory].PolicyNumber) = "VL300204"))
'ORDER BY [Policy Inventory].PolicyNumber;

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