Table Data As Selection Criteria

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

Guest

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])));
 
UNTESTED but try this for your WHERE statement --
[qOrder19SystemStatusOp].[Main
WorkCtr]=IIf([tbMainWorkCentreInclude].[MainWorkCentre] Is
Null,"*",[tbMainWorkCentreInclude].[MainWorkCentre])
 
Thanks for your reply Karl.
But no luck, the “*†does not return any data.

Regards
Nick


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

Nick hfrupn said:
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])));
Click to expand...
Click to expand...
 
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.

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

Regards
Nick


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

Nick hfrupn said:
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])));
Click to expand...
Click to expand...
Click to expand...
 
I tried every combination you suggested and some but still no lick.

Regards
Nick

KARL DEWEY said:
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.

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

Regards
Nick


KARL DEWEY said:
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])));
Click to expand...
Click to expand...
Click to expand...
 
Nick said:
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.
Click to expand...

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.

regards
Nick

Neil Sunderland said:
Nick said:
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.
Click to expand...

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
Click to expand...
 
Neil said:
Change your WHERE clause to:
WHERE
qOrder19SystemStatusOp.[Main WorkCtr] IN
(SELECT [MainWorkCentre] FROM [tbMainWorkCentreInclude])
OR NOT EXISTS
(SELECT [MainWorkCentre] FROM [tbMainWorkCentreInclude]);
Click to expand...
Click to expand...

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

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!
 
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]);
 
Nick said:
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.
Click to expand...

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

No - it'll never make it past my firewall intact.
 
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

Neil Sunderland said:
Nick said:
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.
Click to expand...

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

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

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address
Click to expand...
 
Nick said:
Yes, the field names are correct.
Click to expand...

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,
No luck.
Thanks for your efforts I really appreciate it.
If you have any afterthoughts let me know.

Regards
Nick


Neil Sunderland said:
Nick said:
Yes, the field names are correct.
Click to expand...

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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I would like to select query criteria from data in another table 2
Extract data from SQL Server table into excel using Listbox Selection as criteria 0
'Data Type Mismatch In Criteria expression' error when make-table query runs 4
Automatically set the data type as date for a Make Table Query 3

Back
Top