USING THE "IN CLAUSE"

  • Thread starter Thread starter Guest
  • Start date Start date
G

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]));
 
tamxwell said:
Here is my Query. I need to pull more than one company number from the
[co-number] field.
Click to expand...

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,

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?

Rick Brandt said:
tamxwell said:
Here is my Query. I need to pull more than one company number from the
[co-number] field.
Click to expand...

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.
Click to expand...
 
tamxwell said:
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?
Click to expand...

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.
 
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)
 
Back
Top