Table Data As Selection Criteria

[Nick hfrupn]

I enter selection data into a sub form, frmMainWorkCentreInclude, with a
field MainWorkCentre. This data is stored in a table, tbMainWorkCentreInclude
and this table is used as the selection criteria for a query on another
table. I have posted the SQL below.
This works fine when there is data in the table. My question is how can I
get the query to return all data when the table is blank and only the data
specified when the table is not blank.

Regards
Nick

SELECT qOrder19SystemStatusOp.Order, qOrder19SystemStatusOp.[Order Type],
qOrder19SystemStatusOp.[Bas# start date], qOrder19SystemStatusOp.[Short
text], qOrder19SystemStatusOp.FunctLocation, qOrder19SystemStatusOp.[Planner
group], qOrder19SystemStatusOp.[Main WorkCtr],
qOrder19SystemStatusOp.Priority, qOrder19SystemStatusOp.[Entered by],
qOrder19SystemStatusOp.tbHeader.SystemCondition,
qOrder19SystemStatusOp.Revision, qOrder19SystemStatusOp.tbHeader.[System
Status], qOrder19SystemStatusOp.[User status],
qOrder19SystemStatusOp.[Oper#/Act#], qOrder19SystemStatusOp.[Control key],
qOrder19SystemStatusOp.[Work Center], qOrder19SystemStatusOp.[Opr# short
text], qOrder19SystemStatusOp.[Normal duration],
qOrder19SystemStatusOp.Number, qOrder19SystemStatusOp.Work,
qOrder19SystemStatusOp.tbOperations.SystemCondition,
qOrder19SystemStatusOp.[Earl#start date],
qOrder19SystemStatusOp.tbOperations.[System Status],
qOrder19SystemStatusOp.Recipient, qOrder19SystemStatusOp.[Std text key]
FROM qOrder19SystemStatusOp
WHERE (((qOrder19SystemStatusOp.[Main WorkCtr]) In (SELECT [MainWorkCentre]
FROM [tbMainWorkCentreInclude])));

 

[KARL DEWEY]

UNTESTED but try this for your WHERE statement --
[qOrder19SystemStatusOp].[Main
WorkCtr]=IIf([tbMainWorkCentreInclude].[MainWorkCentre] Is
Null,"*",[tbMainWorkCentreInclude].[MainWorkCentre])

 

[Nick hfrupn]

Thanks for your reply Karl.
But no luck, the “*†does not return any data.

Regards
Nick

UNTESTED but try this for your WHERE statement --
[qOrder19SystemStatusOp].[Main
WorkCtr]=IIf([tbMainWorkCentreInclude].[MainWorkCentre] Is
Null,"*",[tbMainWorkCentreInclude].[MainWorkCentre])

I enter selection data into a sub form, frmMainWorkCentreInclude, with a
field MainWorkCentre. This data is stored in a table, tbMainWorkCentreInclude
and this table is used as the selection criteria for a query on another
table. I have posted the SQL below.
This works fine when there is data in the table. My question is how can I
get the query to return all data when the table is blank and only the data
specified when the table is not blank.

Regards
Nick

SELECT qOrder19SystemStatusOp.Order, qOrder19SystemStatusOp.[Order Type],
qOrder19SystemStatusOp.[Bas# start date], qOrder19SystemStatusOp.[Short
text], qOrder19SystemStatusOp.FunctLocation, qOrder19SystemStatusOp.[Planner
group], qOrder19SystemStatusOp.[Main WorkCtr],
qOrder19SystemStatusOp.Priority, qOrder19SystemStatusOp.[Entered by],
qOrder19SystemStatusOp.tbHeader.SystemCondition,
qOrder19SystemStatusOp.Revision, qOrder19SystemStatusOp.tbHeader.[System
Status], qOrder19SystemStatusOp.[User status],
qOrder19SystemStatusOp.[Oper#/Act#], qOrder19SystemStatusOp.[Control key],
qOrder19SystemStatusOp.[Work Center], qOrder19SystemStatusOp.[Opr# short
text], qOrder19SystemStatusOp.[Normal duration],
qOrder19SystemStatusOp.Number, qOrder19SystemStatusOp.Work,
qOrder19SystemStatusOp.tbOperations.SystemCondition,
qOrder19SystemStatusOp.[Earl#start date],
qOrder19SystemStatusOp.tbOperations.[System Status],
qOrder19SystemStatusOp.Recipient, qOrder19SystemStatusOp.[Std text key]
FROM qOrder19SystemStatusOp
WHERE (((qOrder19SystemStatusOp.[Main WorkCtr]) In (SELECT [MainWorkCentre]
FROM [tbMainWorkCentreInclude])));

 

[KARL DEWEY]

Try putting LIKE in front of "*" and if that does not work try LIKE in front
of IIF. And again replace the equal ( = ) with LIKE.

Thanks for your reply Karl.
But no luck, the “*†does not return any data.

Regards
Nick

UNTESTED but try this for your WHERE statement --
[qOrder19SystemStatusOp].[Main
WorkCtr]=IIf([tbMainWorkCentreInclude].[MainWorkCentre] Is
Null,"*",[tbMainWorkCentreInclude].[MainWorkCentre])

I enter selection data into a sub form, frmMainWorkCentreInclude, with a
field MainWorkCentre. This data is stored in a table, tbMainWorkCentreInclude
and this table is used as the selection criteria for a query on another
table. I have posted the SQL below.
This works fine when there is data in the table. My question is how can I
get the query to return all data when the table is blank and only the data
specified when the table is not blank.

Regards
Nick

SELECT qOrder19SystemStatusOp.Order, qOrder19SystemStatusOp.[Order Type],
qOrder19SystemStatusOp.[Bas# start date], qOrder19SystemStatusOp.[Short
text], qOrder19SystemStatusOp.FunctLocation, qOrder19SystemStatusOp.[Planner
group], qOrder19SystemStatusOp.[Main WorkCtr],
qOrder19SystemStatusOp.Priority, qOrder19SystemStatusOp.[Entered by],
qOrder19SystemStatusOp.tbHeader.SystemCondition,
qOrder19SystemStatusOp.Revision, qOrder19SystemStatusOp.tbHeader.[System
Status], qOrder19SystemStatusOp.[User status],
qOrder19SystemStatusOp.[Oper#/Act#], qOrder19SystemStatusOp.[Control key],
qOrder19SystemStatusOp.[Work Center], qOrder19SystemStatusOp.[Opr# short
text], qOrder19SystemStatusOp.[Normal duration],
qOrder19SystemStatusOp.Number, qOrder19SystemStatusOp.Work,
qOrder19SystemStatusOp.tbOperations.SystemCondition,
qOrder19SystemStatusOp.[Earl#start date],
qOrder19SystemStatusOp.tbOperations.[System Status],
qOrder19SystemStatusOp.Recipient, qOrder19SystemStatusOp.[Std text key]
FROM qOrder19SystemStatusOp
WHERE (((qOrder19SystemStatusOp.[Main WorkCtr]) In (SELECT [MainWorkCentre]
FROM [tbMainWorkCentreInclude])));

 

[Nick hfrupn]

I tried every combination you suggested and some but still no lick.

Regards
Nick

Try putting LIKE in front of "*" and if that does not work try LIKE in front
of IIF. And again replace the equal ( = ) with LIKE.

Thanks for your reply Karl.
But no luck, the “*†does not return any data.

Regards
Nick

UNTESTED but try this for your WHERE statement --
[qOrder19SystemStatusOp].[Main
WorkCtr]=IIf([tbMainWorkCentreInclude].[MainWorkCentre] Is
Null,"*",[tbMainWorkCentreInclude].[MainWorkCentre])

:

I enter selection data into a sub form, frmMainWorkCentreInclude, with a
field MainWorkCentre. This data is stored in a table, tbMainWorkCentreInclude
and this table is used as the selection criteria for a query on another
table. I have posted the SQL below.
This works fine when there is data in the table. My question is how can I
get the query to return all data when the table is blank and only the data
specified when the table is not blank.

Regards
Nick

SELECT qOrder19SystemStatusOp.Order, qOrder19SystemStatusOp.[Order Type],
qOrder19SystemStatusOp.[Bas# start date], qOrder19SystemStatusOp.[Short
text], qOrder19SystemStatusOp.FunctLocation, qOrder19SystemStatusOp.[Planner
group], qOrder19SystemStatusOp.[Main WorkCtr],
qOrder19SystemStatusOp.Priority, qOrder19SystemStatusOp.[Entered by],
qOrder19SystemStatusOp.tbHeader.SystemCondition,
qOrder19SystemStatusOp.Revision, qOrder19SystemStatusOp.tbHeader.[System
Status], qOrder19SystemStatusOp.[User status],
qOrder19SystemStatusOp.[Oper#/Act#], qOrder19SystemStatusOp.[Control key],
qOrder19SystemStatusOp.[Work Center], qOrder19SystemStatusOp.[Opr# short
text], qOrder19SystemStatusOp.[Normal duration],
qOrder19SystemStatusOp.Number, qOrder19SystemStatusOp.Work,
qOrder19SystemStatusOp.tbOperations.SystemCondition,
qOrder19SystemStatusOp.[Earl#start date],
qOrder19SystemStatusOp.tbOperations.[System Status],
qOrder19SystemStatusOp.Recipient, qOrder19SystemStatusOp.[Std text key]
FROM qOrder19SystemStatusOp
WHERE (((qOrder19SystemStatusOp.[Main WorkCtr]) In (SELECT [MainWorkCentre]
FROM [tbMainWorkCentreInclude])));

 

[Neil Sunderland]

I enter selection data into a sub form, frmMainWorkCentreInclude, with a
field MainWorkCentre. This data is stored in a table, tbMainWorkCentreInclude
and this table is used as the selection criteria for a query on another
table. I have posted the SQL below.
This works fine when there is data in the table. My question is how can I
get the query to return all data when the table is blank and only the data
specified when the table is not blank.

Change your WHERE clause to:
WHERE
qOrder19SystemStatusOp.[Main WorkCtr] IN
(SELECT [MainWorkCentre] FROM [tbMainWorkCentreInclude])
OR NOT EXISTS
(SELECT [MainWorkCentre] FROM [tbMainWorkCentreInclude]);

 

[Nick hfrupn]

This now give the correct result when tbMainWorkCentreInclude is blank but
when there is data in the table the database crashes with this message

Microsoft Office Access has encountered a problem and needs to close.

regards
Nick

I enter selection data into a sub form, frmMainWorkCentreInclude, with a
field MainWorkCentre. This data is stored in a table, tbMainWorkCentreInclude
and this table is used as the selection criteria for a query on another
table. I have posted the SQL below.
This works fine when there is data in the table. My question is how can I
get the query to return all data when the table is blank and only the data
specified when the table is not blank.

Change your WHERE clause to:
WHERE
qOrder19SystemStatusOp.[Main WorkCtr] IN
(SELECT [MainWorkCentre] FROM [tbMainWorkCentreInclude])
OR NOT EXISTS
(SELECT [MainWorkCentre] FROM [tbMainWorkCentreInclude]);

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

 

[Neil Sunderland]

Change your WHERE clause to:
WHERE
qOrder19SystemStatusOp.[Main WorkCtr] IN
(SELECT [MainWorkCentre] FROM [tbMainWorkCentreInclude])
OR NOT EXISTS
(SELECT [MainWorkCentre] FROM [tbMainWorkCentreInclude]);

This now give the correct result when tbMainWorkCentreInclude is blank but
when there is data in the table the database crashes with this message

Microsoft Office Access has encountered a problem and needs to close.

Hmmm. I've tested this in Access 2000 and it works fine with a pair of
small tables.

You could try doing a Compact and Repair on your database and see if
that makes things any better.

If that doesn't help, try changing the WHERE clause to:
WHERE
qOrder19SystemStatusOp.[Main WorkCtr] IN
(SELECT [MainWorkCentre] FROM [tbMainWorkCentreInclude])
OR NOT EXISTS
(SELECT * FROM [tbMainWorkCentreInclude]);

....although I should point out that I'm clutching at straws!

 

[Nick hfrupn]

Maybe I am going about things the wrong way. Worked as your previous
suggestion, with no data all is returned but locks up with data. I have put
the SQL below in case I’m doing something wrong.

Regards
Nick
PS Is it worth sending you the data base?

SELECT qOrder19SystemStatusOp.Order, qOrder19SystemStatusOp.[Order Type],
qOrder19SystemStatusOp.[Bas# start date], qOrder19SystemStatusOp.[Short
text], qOrder19SystemStatusOp.FunctLocation, qOrder19SystemStatusOp.[Planner
group], qOrder19SystemStatusOp.[Main WorkCtr],
qOrder19SystemStatusOp.Priority, qOrder19SystemStatusOp.[Entered by],
qOrder19SystemStatusOp.tbHeader.SystemCondition,
qOrder19SystemStatusOp.Revision, qOrder19SystemStatusOp.tbHeader.[System
Status], qOrder19SystemStatusOp.[User status],
qOrder19SystemStatusOp.[Oper#/Act#], qOrder19SystemStatusOp.[Control key],
qOrder19SystemStatusOp.[Work Center], qOrder19SystemStatusOp.[Opr# short
text], qOrder19SystemStatusOp.[Normal duration],
qOrder19SystemStatusOp.Number, qOrder19SystemStatusOp.Work,
qOrder19SystemStatusOp.tbOperations.SystemCondition,
qOrder19SystemStatusOp.[Earl#start date],
qOrder19SystemStatusOp.tbOperations.[System Status],
qOrder19SystemStatusOp.Recipient, qOrder19SystemStatusOp.[Std text key]
FROM qOrder19SystemStatusOp
WHERE qOrder19SystemStatusOp.[Main WorkCtr] IN (SELECT [MainWorkCentre] FROM
[tbMainWorkCentreInclude]) OR NOT EXISTS (SELECT * FROM
[tbMainWorkCentreInclude]);

 

[Neil Sunderland]

Maybe I am going about things the wrong way. Worked as your previous
suggestion, with no data all is returned but locks up with data. I have put
the SQL below in case I’m doing something wrong.

There are some odd-looking fields in that query:
qOrder19SystemStatusOp.tbHeader.SystemCondition,
qOrder19SystemStatusOp.tbHeader.[System Status]
qOrder19SystemStatusOp.tbOperations.SystemCondition
qOrder19SystemStatusOp.tbOperations.[System Status]

Are these correct?

PS Is it worth sending you the data base?

No - it'll never make it past my firewall intact.

 

[Nick hfrupn]

Yes, the field names are correct.
I download two spreadsheets. These sheets pickup the first row as the column
headers and when I transfer the sheets into Access they become the field
names of the two related tables, tbHeader & tbOperations. I then combine the
data in the query. Because of fields in both tables being named the same,
Access adds the table name to the field name in the query. If I was to change
the names there would be a conflict when I do the transfer. As the header row
of the spreadsheets comes from the data of another program.

Nick

Maybe I am going about things the wrong way. Worked as your previous
suggestion, with no data all is returned but locks up with data. I have put
the SQL below in case I’m doing something wrong.

There are some odd-looking fields in that query:
qOrder19SystemStatusOp.tbHeader.SystemCondition,
qOrder19SystemStatusOp.tbHeader.[System Status]
qOrder19SystemStatusOp.tbOperations.SystemCondition
qOrder19SystemStatusOp.tbOperations.[System Status]

Are these correct?

PS Is it worth sending you the data base?

No - it'll never make it past my firewall intact.

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

 

[Neil Sunderland]

Yes, the field names are correct.

OK, just checking!

Last throw of the dice: re-order the two parts of the where clause to:
WHERE
(NOT EXISTS (SELECT * FROM [tbMainWorkCentreInclude]))
OR (qOrder19SystemStatusOp.[Main WorkCtr] IN
(SELECT [MainWorkCentre] FROM [tbMainWorkCentreInclude]))

If that doesn't work then I'm afraid out of ideas.

 

[Nick hfrupn]

Neil,
No luck.
Thanks for your efforts I really appreciate it.
If you have any afterthoughts let me know.

Regards
Nick

Yes, the field names are correct.

OK, just checking!

Last throw of the dice: re-order the two parts of the where clause to:
WHERE
(NOT EXISTS (SELECT * FROM [tbMainWorkCentreInclude]))
OR (qOrder19SystemStatusOp.[Main WorkCtr] IN
(SELECT [MainWorkCentre] FROM [tbMainWorkCentreInclude]))

If that doesn't work then I'm afraid out of ideas.

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address