Access 2007: Dynamic SQL to be run when opening a report

Question

I'm trying to have some SQL execute when I open a report. This works fine when I try to match on a column that's an integer with an integer, but when I try to match on a "text" column, it keeps popping up a dialog asking for what you want to filter on.

Here's a somple query:

select person_phone_numbers.person_id from person_phone_numbers where phone_number = '444-444-4444'

This is actually a sub-query I'm trying to use, but this is where the problem is. If I change it to this it works fine:

select person_phone_numbers.person_id from person_phone_numbers where phone_id = 2

I put this in the OnOpen event and I'm assigning it to Me.RecordSource if that makes a difference. My goal here is to have a form accept query parameter(s) and have it open a report with the results.

EDIT: Full code snippet:

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "SELECT person.original_name, person.normalized_name, phone_number.full_number, phone_number.type, person.person_id " _
                & "FROM phone_number INNER JOIN (person INNER JOIN person_phone_numbers ON person.person_id = person_phone_numbers.person_id) " _
                & "ON phone_number.full_number = person_phone_numbers.full_number where person.person_id IN " _
                & "(select person_phone_numbers.person_id from person_phone_numbers where phone_number = '444-444-4444')"
End Sub

Any thoughts on why it wants to ask for a parameter vs. just running the query the way I have it?

Answer 1

is the phone # field actually storing the hash marks?

from what you have supplied for code, i do not see anything that would cause a parameter prompt - please post the entire query