USING THE "IN CLAUSE"

[Guest]

Here is my Query. I need to pull more than one company number from the
[co-number] field.

SELECT
ALL_AGINGS_ALL.[co-number], ALL_AGINGS_ALL.[divn-number],
ALL_AGINGS_ALL.[cust-number], ALL_AGINGS_ALL.[cust-name],
ALL_AGINGS_ALL.TranType, ALL_AGINGS_ALL.[ref-number],
ALL_AGINGS_ALL.[as-of-date], ALL_AGINGS_ALL.[due-date],
ALL_AGINGS_ALL.[item-amount], ALL_AGINGS_ALL.Today,
DateDiff("d",[Due-Date],[Today]) AS DSO, ALL_AGINGS_ALL.Current,
ALL_AGINGS_ALL.[1-30], ALL_AGINGS_ALL.[31-60], ALL_AGINGS_ALL.[61-90],
ALL_AGINGS_ALL.[91-180], ALL_AGINGS_ALL.[181-360], ALL_AGINGS_ALL.[360+],
ALL_AGINGS_ALL.CA, ALL_AGINGS_ALL.CM, ALL_AGINGS_ALL.[Internal/External],

(ALL_AGINGS_ALL.[co-number] IN (1, 2))


FROM ALL_AGINGS_ALL


WHERE ((([ALL_AGINGS_ALL].[co-number])=[Which Branch do you want data for?])
And (([ALL_AGINGS_ALL].[co-number])=[Enter Branch number]));

 

[Rick Brandt]

Here is my Query. I need to pull more than one company number from the
[co-number] field.

You cannot use the IN clause with a self-prompting parameter. Anything the
user types will always be treated as ONE value in the IN clause as opposed
to a list of values.

Self-prompting parameter queries are very limited and are seldom used in
"production quality" applications. Better is to provide a form where the
users makes entries and then modify the query appropriately to use the
values entered by the user into the form.

 

[Guest]

Rick,

This query is exported to an excel file. I need to have the ablilty the
Credit Managers to pull the two different co-numbers, then export to Excel.
Got any ideas?

Here is my Query. I need to pull more than one company number from the
[co-number] field.

You cannot use the IN clause with a self-prompting parameter. Anything the
user types will always be treated as ONE value in the IN clause as opposed
to a list of values.

Self-prompting parameter queries are very limited and are seldom used in
"production quality" applications. Better is to provide a form where the
users makes entries and then modify the query appropriately to use the
values entered by the user into the form.

 

[Rick Brandt]

Rick,

This query is exported to an excel file. I need to have the ablilty
the Credit Managers to pull the two different co-numbers, then export
to Excel. Got any ideas?

A way that dosn't require using code to modify the query would be to have a
small table that is included in the query (joined to by co-number) and in
that table you can enter the co-numbers you want included in the query.
Then you just provide a small form for making entries into this table and a
button to export the query. When the form is closed you coud run a delete
query to clear the small table out again.

 

[david epsom dot com dot au]

WHERE ((([ALL_AGINGS_ALL].[co-number])=[Which Branch do you want

WHERE Instr([Which Branch do you want data for?],
[ALL_AGINGS_ALL].[co-number])

works for single digit numbers

(david)