In([variable])

[undrline via AccessMonster.com]

I know that In (123,456,789) will work in my query criteria. But I want the
user to be able to type in *any* set of ids. I don't want to give them a
hundred prompts, if they only want two ids, and I don't want the user who
needs a bunch of ids to be limited by the number of prompts. So, there must
be a way of doing this . . .

I've tried:
In([Type your comma-delimited IDs])
In(""&[Type your comma-delimited IDs]&"")
[Type your IDs as 123 Or 456 Or 789]
[Type your IDs as "123" Or "456" Or "789"]

Very frustrating. Please help.

 

[John Vinson]

I know that In (123,456,789) will work in my query criteria. But I want the
user to be able to type in *any* set of ids. I don't want to give them a
hundred prompts, if they only want two ids, and I don't want the user who
needs a bunch of ids to be limited by the number of prompts. So, there must
be a way of doing this . . .

I've tried:
In([Type your comma-delimited IDs])
In(""&[Type your comma-delimited IDs]&"")
[Type your IDs as 123 Or 456 Or 789]
[Type your IDs as "123" Or "456" Or "789"]

Very frustrating. Please help.

It is frustrating, because the Parameter process simply doesn't allow
you to do this. You can input values - 123 or 456 - but you cannot
input OPERATORS such as the comma or the word OR.

You'll need to actually construct the entire SQL query in code, adding
the ID's input by the user:

Dim strSQL As String
strSQL = "SELECT blah, blah, blah FROM sometable WHERE ID IN(" _
& Forms!MyForm!txtCriteria & ");"

for example.

John W. Vinson[MVP]

 

[undrline via AccessMonster.com]

Ouch. That's no good. Thank you for the response.

So, currently, the query is a backend to a report. The user clicks a button,
and is prompted. It generates something that can be saved as snp that has
friendly formatting.

So . . . in the in the Open Event of the report . . .

Dim strInput As String
Dim strSQL As String

strInput = InputBox("Enter your IDs, comma-delimited.","")
strSQL = "put my query in SQL here" _
"WHERE ((([Data Entry].Record) In (" + strInput +"))) ORDER BY [Data Entry].
Determination;"

Me.RecordSource = strSQL

Am I doing that right? I think I'm doing the last line wrong.

I know that In (123,456,789) will work in my query criteria. But I want the
user to be able to type in *any* set of ids. I don't want to give them a

[quoted text clipped - 9 lines]

Very frustrating. Please help.

It is frustrating, because the Parameter process simply doesn't allow
you to do this. You can input values - 123 or 456 - but you cannot
input OPERATORS such as the comma or the word OR.

You'll need to actually construct the entire SQL query in code, adding
the ID's input by the user:

Dim strSQL As String
strSQL = "SELECT blah, blah, blah FROM sometable WHERE ID IN(" _
& Forms!MyForm!txtCriteria & ");"

for example.

John W. Vinson[MVP]

 

[undrline via AccessMonster.com]

I tried it, and it seems like it *will* work, once I get my code straight.
Right now, it's telling me "Join expression not supported."

Private Sub Report_Open(Cancel As Integer)
Dim strInput As String
Dim strSQL As String

strInput = InputBox("Enter your IDs, comma-delimited.", "")
strSQL = "SELECT [Data Entry].Record AS ID, [Data Entry].Group, " _
& "IIf(tblMember!Pt_First_Name Is Null,[First Name],tblMember!Pt_First_Name)
AS Pt_First, " _
& "IIf(tblMember_1!Pt_Last_Name Is Null,[Last Name],tblMember_1!Pt_Last_Name)
AS Pt_Last, " _
& "IIf(tblMember_2!Pt_Member_ID Is Null,[Member Number],tblMember_2!
Pt_Member_ID) AS [Member ID], " _
& "[Data Entry].Case, [Data Entry].[Case Type], [Data Entry].[UBH Received],
[Data Entry].Acknowledged, " _
& "[Data Entry].Determination, [Data Entry].[Due Date], [Data Entry].
Explanation, " _
& "tlkpUser!User_First_Name+' '+tlkpUser_1!User_Last_Name AS Reviewer1, " _
& "tlkpUser_2!User_First_Name+' '+tlkpUser_3!User_Last_Name AS Reviewer2, " _
& "tlkpUser_4!User_First_Name+' '+tlkpUser_5!User_Last_Name AS Reviewer3, " _
& "[Data Entry].[To Change - Case], [Data Entry].[To Change - Ackno], [Data
Entry].[To Change - Det], " _
& "[Data Entry].[To Change - Reviewer], [Data Entry].[Violated Standard], " _
& "[Data Entry].[Due Date Ackno] " _
& "FROM (((((((([Data Entry] LEFT JOIN tlkpUser ON [Data Entry].[1 Reviewer
First] = tlkpUser.User_ID) LEFT JOIN tlkpUser AS tlkpUser_1 ON [Data Entry].
[1 Reviewer Last] = tlkpUser_1.User_ID) LEFT JOIN tlkpUser AS tlkpUser_2 ON
[Data Entry].[2 Reviewer First] = tlkpUser_2.User_ID) LEFT JOIN tlkpUser AS
tlkpUser_3 ON [Data Entry].[2 Reviewer Last] = tlkpUser_3.User_ID) LEFT JOIN
tlkpUser AS tlkpUser_4 ON [Data Entry].[3 Reviewer First] = tlkpUser_4.
User_ID) LEFT JOIN tlkpUser AS tlkpUser_5 ON [Data Entry].[3 Reviewer Last] =
tlkpUser_5.User_ID) LEFT JOIN tblMember ON [Data Entry].[First Name] =
tblMember.Patient_ID) LEFT JOIN tblMember AS tblMember_1 ON [Data Entry].
[Last Name] = tblMember_1.Patient_ID) LEFT JOIN tblMember AS tblMember_2 ON
[Data Entry].[Member Number] = tblMember_2.Patient_ID" _
& "WHERE ((([Data Entry].Record) In (" & strInput & "))) ORDER BY [Data Entry]
Determination;"

Me.RecordSource = strSQL

End Sub

Ouch. That's no good. Thank you for the response.

So, currently, the query is a backend to a report. The user clicks a button,
and is prompted. It generates something that can be saved as snp that has
friendly formatting.

So . . . in the in the Open Event of the report . . .

Dim strInput As String
Dim strSQL As String

strInput = InputBox("Enter your IDs, comma-delimited.","")
strSQL = "put my query in SQL here" _
"WHERE ((([Data Entry].Record) In (" + strInput +"))) ORDER BY [Data Entry].
Determination;"

Me.RecordSource = strSQL

Am I doing that right? I think I'm doing the last line wrong.
[quoted text clipped - 16 lines]

John W. Vinson[MVP]

 

[John Vinson]

Ouch. That's no good. Thank you for the response.

So, currently, the query is a backend to a report. The user clicks a button,
and is prompted. It generates something that can be saved as snp that has
friendly formatting.

So . . . in the in the Open Event of the report . . .

Dim strInput As String
Dim strSQL As String

strInput = InputBox("Enter your IDs, comma-delimited.","")
strSQL = "put my query in SQL here" _
"WHERE ((([Data Entry].Record) In (" + strInput +"))) ORDER BY [Data Entry].
Determination;"

Me.RecordSource = strSQL

Am I doing that right? I think I'm doing the last line wrong.

The code above appears to be missing an ampersand and a blank: try

strSQL = "put my query in SQL here " _
& "WHERE ((([Data Entry].Record) In (" _
& strInput & "))) ORDER BY [Data Entry].Determination;"

I'm using & rather than + to concatenate the strings; if there are no
NULLS involved either operator will work.

I'd be inclined to offer the user a Form control to enter the criteria
rather than an InputBox but again, either should work.

John W. Vinson[MVP]

 

[John Vinson]

I tried it, and it seems like it *will* work, once I get my code straight.
Right now, it's telling me "Join expression not supported."

Ow.

Sorry, but your table design is not properly normalized. If you have
fields Reviewer1, Reviewer2, etc. then you have a "repeating group" -
you're embedding a one (report?) to many relationship in each record,
rather than properly using two tables in a one to many relationship.

I suspect the Join Expression error may be because you have also
fallen victim to Microsoft's Lookup Wizard misfeature: it looks like
you're trying to join a name to a (concealed) numeric ID. Without
knowing the structure of your tables, I can't say for sure though!

John W. Vinson[MVP]

 

[raskew via AccessMonster.com]

Hi -

The InParam() function described in the MSKB
http://support.microsoft.com/kb/100131/en-us may be what you're looking for.
It allows the user to enter comma-separated parameters.

HTH - Bob

Ouch. That's no good. Thank you for the response.

[quoted text clipped - 15 lines]

Am I doing that right? I think I'm doing the last line wrong.

The code above appears to be missing an ampersand and a blank: try

strSQL = "put my query in SQL here " _
& "WHERE ((([Data Entry].Record) In (" _
& strInput & "))) ORDER BY [Data Entry].Determination;"

I'm using & rather than + to concatenate the strings; if there are no
NULLS involved either operator will work.

I'd be inclined to offer the user a Form control to enter the criteria
rather than an InputBox but again, either should work.

John W. Vinson[MVP]

 

[John Vinson]

Hi -

The InParam() function described in the MSKB
http://support.microsoft.com/kb/100131/en-us may be what you're looking for.
It allows the user to enter comma-separated parameters.

HTH - Bob

Thanks, Bob! I hadn't encountered that nifty little trick, or if I
had, I'd forgotten it.

John W. Vinson[MVP]

 

[raskew via AccessMonster.com]

Hi John,

I just ran accross that a couple of weeks ago. Seems to work well.

Best Wishes - Bob

Hi -

[quoted text clipped - 3 lines]

HTH - Bob

Thanks, Bob! I hadn't encountered that nifty little trick, or if I
had, I'd forgotten it.

John W. Vinson[MVP]

 

[undrline via AccessMonster.com]

Though, if I were to continue along the first lines, I'm sure it would've
worked . . . that InParam module worked so much easier. Thanks. And, you
made it worth the while for John to stick with me.

Thank you both.

Hi John,

I just ran accross that a couple of weeks ago. Seems to work well.

Best Wishes - Bob
[quoted text clipped - 6 lines]

John W. Vinson[MVP]

 

[undrline via AccessMonster.com]

I didn't understand most of this. I'm familiar with the whole concealed id
thing; it's horrible. I try not to use wizards. The query works without any
parameters: I built it in Design View (where it works fine with concrete
parameters), changed to SQL view, copied to wordpad and replaced all double-
quotes with single quotes. So, I know the joins must be correct.

 

[undrline via AccessMonster.com]

I didn't try this, because of the later post . . . but it may have worked. I
think I was using + instead of & because I mostly code in JavaScript and
Hyperion Intelligence JavaScript. Sometimes hard to keep the small things
straight.

Ouch. That's no good. Thank you for the response.

[quoted text clipped - 15 lines]

Am I doing that right? I think I'm doing the last line wrong.

The code above appears to be missing an ampersand and a blank: try

strSQL = "put my query in SQL here " _
& "WHERE ((([Data Entry].Record) In (" _
& strInput & "))) ORDER BY [Data Entry].Determination;"

I'm using & rather than + to concatenate the strings; if there are no
NULLS involved either operator will work.

I'd be inclined to offer the user a Form control to enter the criteria
rather than an InputBox but again, either should work.

John W. Vinson[MVP]

 

[raskew via AccessMonster.com]

Last time I looked, this was a MS Access forum.

Bob

I didn't try this, because of the later post . . . but it may have worked. I
think I was using + instead of & because I mostly code in JavaScript and
Hyperion Intelligence JavaScript. Sometimes hard to keep the small things
straight.
[quoted text clipped - 15 lines]

John W. Vinson[MVP]

 

[Marshall Barton]

The InParam() function described in the MSKB
http://support.microsoft.com/kb/100131/en-us may be what you're looking for.
It allows the user to enter comma-separated parameters.

If the values in the list are numbers (e.g. 234,456,789),
then you could use:

Eval(Cstr(ID) & " IN(" & Forms!theform.thetextbox) &")" )

The same sort of thing will work for a list of text items,
but the items would need to be quoted (e.g.
"abc","def","ghj")

Eval("""" & ID & """ IN(" & Forms!theform.thetextbox) &")" )

 

[undrline via AccessMonster.com]

Yes, I know that this is MS Access. That was meant to be a reply to John,
"I'm using & rather than + to concatenate the strings; if there are no NULLS
involved either operator will work." Saying that I mixed up the two, because
I'm not as used to VBA as I am to JS.

Your link to the module worked perfectly and solved my issue. I appreciate
it, and thank you for assisting me.

Last time I looked, this was a MS Access forum.

Bob

I didn't try this, because of the later post . . . but it may have worked. I
think I was using + instead of & because I mostly code in JavaScript and

[quoted text clipped - 6 lines]