Pass-Through Query Prompting for ID#

[IrishRed]

Hello,
I have a pass-through query in which the SQL looks like this:

select Field A, Field B, Field C
from Table1
where MemberID = '1245%'
order by memberid

I was looking to allow users to run this and receive a prompt to enter a
value for the memberID field. What is the best way to do this ? I do not have
much experience using forms or VB code and I think this may be what is
needed.

Also, is it possible being that I need the wildcard after the entered value
to remain? MemberID is incremented for each member in the group so I need to
pull back any records for the group, regardless of whether it is member 01,
02, 03, etc...

Thanks for any suggestions.

 

[Stefan Hoffmann]

hi,

I was looking to allow users to run this and receive a prompt to enter a
value for the memberID field.
What is the best way to do this ?

This is not possible with out some VBA programming. You need a form or a
InputBox to query your parameter, then you need to rewrite your query on
the fly:

Public Sub RewritePassthrough(AMemberID As String, _
AQueryName As String = "yourQueryNameHereAsConstantParameter")

Const SQL As String = "SELECT fieldList " & _
"FROM yourTableName " & _
"WHERE MemberID = '@%' " & _
"ORDER BY MemberID;"

Dim qd As DAO.QueryDef

Set qd = CurrentDbC.QueryDefs.Item(AQueryName)

AMemberID = Replace(AMemberID, "'", "''")
qd.Statement = Replace(SQL, "@", AMemberID)

Set qd = Nothing

End Sub


mfG
--> stefan <--

 

[Jerry Whittle]

What system are you hitting? The wildcard % works in Oracle, but * may be
used for other databases.

 

[Lynn Trapp]

The '%' wildcard also works in SQL Server, although the OP's use of it is
suspect. I would think the LIKE operator is needed.

 

[IrishRed]

Yes, the query is actually where MemberID Like '1245%' I just typed it
incorrectly. Thanks.
I will take a stab at implementing the VB code suggested by Stefan. Thanks
for the responses.