Selection Criteria

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I am using a select query for the record source of a data
sheet form. For the criteria I have used the following;
Like [qyWorkCentreListCombine]![WClist] to get this to
work I had to include the query the
union "qyWorkCentreListCombine".
The problem is when I open the form it returns the correct
data, the only problem is I can't edit this data.
Is there any to achieve the same result and also be able
to edit the data.

Hoping for good news and all help appreciated

Regards
Nick
 
You MIGHT be able to get this to work using a subquery.

SELECT *
FROM YOURTable
WHERE YourTable.SomeField IN
(SELECT WCList FROM qyWorkCentreListCombine)

IF you want a more detailed answer, you need to post the SQL text of your query.

Tell us why you are using LIKE so we can see if there is a workaround to using Like

Give us a sample of the value(s) being returned by qyWorkCentreListCombine.
 
Hi John,
I'm not sure what you mean by a sub-query so I'll try to
explain what I am trying to do.
I have a main form called "Daily Jobs", record source -
tbDefaults,
combo box "Work", control source - Work_Centre,
text box "Start_Plan_date", record source - Date_From,
text box "Finish_Plan_Date", record source - Date_To,
This is ok for a single Work Centre but in cases where I
need more than one Work Centre I have a button to open
another form "WorkCentreList" record source -
tbWorkCentreList, where I can enter any numbers of Work
Centres in a data sheet field "Wclist", control source -
Wclist.

I combine the fields from both tables in a union
query, "qytbWorkCentreList-Combine"
SELECT tbWorkCentreList.[WClist]
FROM tbWorkCentreList

UNION SELECT tbDefaults.[Work_Centre]
FROM tbDefaults;

The data sheet sub-form "Daily Job 1", record source -
qDaily_Job_Sheets, SQL below. This is where I use "Like
[qytbWorkCentreListCombine]![WClist]" which gives me a
result from the single entry in the combo box, or the list
from the form WorkCentreList, or both. (I hope this is
making sense) The results from the query are what I want
but I can't edit anything.

SELECT [tbWorkorders].[Complete], [tbWorkorders].[Work
center], [tbWorkorders].[OrderID], [tbWorkorders].
[Earl#start date], [tbWorkorders].[Order type],
[tbWorkorders].[Order], [tbWorkorders].[Operation],
[tbWorkorders].[Control key], [tbWorkorders].[Planner
group], [tbWorkorders].[Opr# short text], [tbWorkorders].
[Normal duration], [tbWorkorders].[Number], [tbWorkorders].
[Work], [tbWorkorders].[SystemCondition], [tbWorkorders].
[Purch#req#], [tbWorkorders].[FunctLocation],
[tbWorkorders].[Description], [tbWorkorders].[Priority],
[tbWorkorders].[System status], [tbWorkorders].
[Equipment], [tbWorkorders].[Recipient], [tbWorkorders].
[Std text key], [tbWorkorders].[Notes]

FROM tbWorkorders, [qytbWorkCentreList-Combine]

WHERE ((([tbWorkorders].[Work center]) Like
[qytbWorkCentreList-Combine]![WClist]) And
(([tbWorkorders].[Earl#start date])>=[Forms]![Daily Jobs]!
[Start_Plan_date] And ([tbWorkorders].[Earl#start date])<=
[Forms]![Daily Jobs]![Finish_Plan_Date]))

ORDER BY [tbWorkorders].[Work center], [tbWorkorders].
[Earl#start date], [tbWorkorders].[Order type] DESC ,
[tbWorkorders].[Order], [tbWorkorders].[Operation];

SELECT tbWorkCentreList.[WClist]
FROM tbWorkCentreList

This is probably a big ask and I appreciate your assistance

Regards
Nick
 
Looking at your SQL statement, I think the following modification may work.
Note that I removed [qytbWorkCentreList-Combine] from the FROM clause and set up
a condition in the where clause that matches tblWorkorders.[Work center] if the
value is in the WCList field of the query [qytbWorkCentreList-Combine].

UNTESTED and it may fail. If it does, try renaming the union query to
qytbWorkCentreListCombine so the brackets are no longer needed and use that in
the sub-query in the where clause. IF it still fails, post back and perhaps I
or someone else can come up with an alternative query. Watch out for newreader
line wraps. I think I've taken care of it, but ...

SELECT [tbWorkorders].[Complete],
[tbWorkorders].[Work center],
[tbWorkorders].[OrderID],
[tbWorkorders].[Earl#start date],
[tbWorkorders].[Order type],
[tbWorkorders].[Order],
[tbWorkorders].[Operation],
[tbWorkorders].[Control key],
[tbWorkorders].[Planner group],
[tbWorkorders].[Opr# short text],
[tbWorkorders].[Normal duration],
[tbWorkorders].[Number],
[tbWorkorders].[Work],
[tbWorkorders].[SystemCondition],
[tbWorkorders].[Purch#req#],
[tbWorkorders].[FunctLocation],
[tbWorkorders].[Description],
[tbWorkorders].[Priority],
[tbWorkorders].[System status],
[tbWorkorders].[Equipment],
[tbWorkorders].[Recipient],
[tbWorkorders].[Std text key],
[tbWorkorders].[Notes]
FROM tbWorkorders
WHERE [tbWorkorders].[Work center] In
(SELECT WClist FROM [qytbWorkCentreList-Combine])
And [tbWorkorders].[Earl#start date]>=
[Forms]![Daily Jobs]![Start_Plan_date]
And [tbWorkorders].[Earl#start date]<=
[Forms]![Daily Jobs]![Finish_Plan_Date]
ORDER BY [tbWorkorders].[Work center],
[tbWorkorders].[Earl#start date],
[tbWorkorders].[Order type] DESC ,
[tbWorkorders].[Order],
[tbWorkorders].[Operation];
Hi John,
I'm not sure what you mean by a sub-query so I'll try to
explain what I am trying to do.
I have a main form called "Daily Jobs", record source -
tbDefaults,
combo box "Work", control source - Work_Centre,
text box "Start_Plan_date", record source - Date_From,
text box "Finish_Plan_Date", record source - Date_To,
This is ok for a single Work Centre but in cases where I
need more than one Work Centre I have a button to open
another form "WorkCentreList" record source -
tbWorkCentreList, where I can enter any numbers of Work
Centres in a data sheet field "Wclist", control source -
Wclist.

I combine the fields from both tables in a union
query, "qytbWorkCentreList-Combine"
SELECT tbWorkCentreList.[WClist]
FROM tbWorkCentreList

UNION SELECT tbDefaults.[Work_Centre]
FROM tbDefaults;

The data sheet sub-form "Daily Job 1", record source -
qDaily_Job_Sheets, SQL below. This is where I use "Like
[qytbWorkCentreListCombine]![WClist]" which gives me a
result from the single entry in the combo box, or the list
from the form WorkCentreList, or both. (I hope this is
making sense) The results from the query are what I want
but I can't edit anything.

SELECT [tbWorkorders].[Complete], [tbWorkorders].[Work
center], [tbWorkorders].[OrderID], [tbWorkorders].
[Earl#start date], [tbWorkorders].[Order type],
[tbWorkorders].[Order], [tbWorkorders].[Operation],
[tbWorkorders].[Control key], [tbWorkorders].[Planner
group], [tbWorkorders].[Opr# short text], [tbWorkorders].
[Normal duration], [tbWorkorders].[Number], [tbWorkorders].
[Work], [tbWorkorders].[SystemCondition], [tbWorkorders].
[Purch#req#], [tbWorkorders].[FunctLocation],
[tbWorkorders].[Description], [tbWorkorders].[Priority],
[tbWorkorders].[System status], [tbWorkorders].
[Equipment], [tbWorkorders].[Recipient], [tbWorkorders].
[Std text key], [tbWorkorders].[Notes]

FROM tbWorkorders, [qytbWorkCentreList-Combine]

WHERE ((([tbWorkorders].[Work center]) Like
[qytbWorkCentreList-Combine]![WClist]) And
(([tbWorkorders].[Earl#start date])>=[Forms]![Daily Jobs]!
[Start_Plan_date] And ([tbWorkorders].[Earl#start date])<=
[Forms]![Daily Jobs]![Finish_Plan_Date]))

ORDER BY [tbWorkorders].[Work center], [tbWorkorders].
[Earl#start date], [tbWorkorders].[Order type] DESC ,
[tbWorkorders].[Order], [tbWorkorders].[Operation];

SELECT tbWorkCentreList.[WClist]
FROM tbWorkCentreList

This is probably a big ask and I appreciate your assistance

Regards
Nick
Click to expand...
 
John,
Thankyou very much.
Your assistance with the query worked a treat. I didn't
have to change a thing, it pasted straight in.
You enabled me to finish my project and without your help
I couldn't see this happening.
Thanks again!

Regards
Nick
-----Original Message-----
Looking at your SQL statement, I think the following modification may work.
Note that I removed [qytbWorkCentreList-Combine] from the FROM clause and set up
a condition in the where clause that matches
Click to expand...
tblWorkorders.[Work center] if the
value is in the WCList field of the query [qytbWorkCentreList-Combine].

UNTESTED and it may fail. If it does, try renaming the union query to
qytbWorkCentreListCombine so the brackets are no longer needed and use that in
the sub-query in the where clause. IF it still fails, post back and perhaps I
or someone else can come up with an alternative query. Watch out for newreader
line wraps. I think I've taken care of it, but ...

SELECT [tbWorkorders].[Complete],
[tbWorkorders].[Work center],
[tbWorkorders].[OrderID],
[tbWorkorders].[Earl#start date],
[tbWorkorders].[Order type],
[tbWorkorders].[Order],
[tbWorkorders].[Operation],
[tbWorkorders].[Control key],
[tbWorkorders].[Planner group],
[tbWorkorders].[Opr# short text],
[tbWorkorders].[Normal duration],
[tbWorkorders].[Number],
[tbWorkorders].[Work],
[tbWorkorders].[SystemCondition],
[tbWorkorders].[Purch#req#],
[tbWorkorders].[FunctLocation],
[tbWorkorders].[Description],
[tbWorkorders].[Priority],
[tbWorkorders].[System status],
[tbWorkorders].[Equipment],
[tbWorkorders].[Recipient],
[tbWorkorders].[Std text key],
[tbWorkorders].[Notes]
FROM tbWorkorders
WHERE [tbWorkorders].[Work center] In
(SELECT WClist FROM [qytbWorkCentreList-Combine])
And [tbWorkorders].[Earl#start date]>=
[Forms]![Daily Jobs]![Start_Plan_date]
And [tbWorkorders].[Earl#start date]<=
[Forms]![Daily Jobs]![Finish_Plan_Date]
ORDER BY [tbWorkorders].[Work center],
[tbWorkorders].[Earl#start date],
[tbWorkorders].[Order type] DESC ,
[tbWorkorders].[Order],
[tbWorkorders].[Operation];
Hi John,
I'm not sure what you mean by a sub-query so I'll try to
explain what I am trying to do.
I have a main form called "Daily Jobs", record source -
tbDefaults,
combo box "Work", control source - Work_Centre,
text box "Start_Plan_date", record source - Date_From,
text box "Finish_Plan_Date", record source - Date_To,
This is ok for a single Work Centre but in cases where I
need more than one Work Centre I have a button to open
another form "WorkCentreList" record source -
tbWorkCentreList, where I can enter any numbers of Work
Centres in a data sheet field "Wclist", control source -
Wclist.

I combine the fields from both tables in a union
query, "qytbWorkCentreList-Combine"
SELECT tbWorkCentreList.[WClist]
FROM tbWorkCentreList

UNION SELECT tbDefaults.[Work_Centre]
FROM tbDefaults;

The data sheet sub-form "Daily Job 1", record source -
qDaily_Job_Sheets, SQL below. This is where I use "Like
[qytbWorkCentreListCombine]![WClist]" which gives me a
result from the single entry in the combo box, or the list
from the form WorkCentreList, or both. (I hope this is
making sense) The results from the query are what I want
but I can't edit anything.

SELECT [tbWorkorders].[Complete], [tbWorkorders].[Work
center], [tbWorkorders].[OrderID], [tbWorkorders].
[Earl#start date], [tbWorkorders].[Order type],
[tbWorkorders].[Order], [tbWorkorders].[Operation],
[tbWorkorders].[Control key], [tbWorkorders].[Planner
group], [tbWorkorders].[Opr# short text], [tbWorkorders].
[Normal duration], [tbWorkorders].[Number], [tbWorkorders].
[Work], [tbWorkorders].[SystemCondition], [tbWorkorders].
[Purch#req#], [tbWorkorders].[FunctLocation],
[tbWorkorders].[Description], [tbWorkorders].[Priority],
[tbWorkorders].[System status], [tbWorkorders].
[Equipment], [tbWorkorders].[Recipient], [tbWorkorders].
[Std text key], [tbWorkorders].[Notes]

FROM tbWorkorders, [qytbWorkCentreList-Combine]

WHERE ((([tbWorkorders].[Work center]) Like
[qytbWorkCentreList-Combine]![WClist]) And
(([tbWorkorders].[Earl#start date])>=[Forms]![Daily Jobs]!
[Start_Plan_date] And ([tbWorkorders].[Earl#start date]) <=
[Forms]![Daily Jobs]![Finish_Plan_Date]))

ORDER BY [tbWorkorders].[Work center], [tbWorkorders].
[Earl#start date], [tbWorkorders].[Order type] DESC ,
[tbWorkorders].[Order], [tbWorkorders].[Operation];

SELECT tbWorkCentreList.[WClist]
FROM tbWorkCentreList

This is probably a big ask and I appreciate your assistance

Regards
Nick
Click to expand...
.
Click to expand...
 
John,
Thankyou very much.
Your assistance with the query worked a treat. I didn't
have to change a thing, it pasted straight in.
You enabled me to finish my project and without your help
I couldn't see this happening.
Thanks again!

Regards
Nick
-----Original Message-----
Looking at your SQL statement, I think the following modification may work.
Note that I removed [qytbWorkCentreList-Combine] from the FROM clause and set up
a condition in the where clause that matches
Click to expand...
tblWorkorders.[Work center] if the
value is in the WCList field of the query [qytbWorkCentreList-Combine].

UNTESTED and it may fail. If it does, try renaming the union query to
qytbWorkCentreListCombine so the brackets are no longer needed and use that in
the sub-query in the where clause. IF it still fails, post back and perhaps I
or someone else can come up with an alternative query. Watch out for newreader
line wraps. I think I've taken care of it, but ...

SELECT [tbWorkorders].[Complete],
[tbWorkorders].[Work center],
[tbWorkorders].[OrderID],
[tbWorkorders].[Earl#start date],
[tbWorkorders].[Order type],
[tbWorkorders].[Order],
[tbWorkorders].[Operation],
[tbWorkorders].[Control key],
[tbWorkorders].[Planner group],
[tbWorkorders].[Opr# short text],
[tbWorkorders].[Normal duration],
[tbWorkorders].[Number],
[tbWorkorders].[Work],
[tbWorkorders].[SystemCondition],
[tbWorkorders].[Purch#req#],
[tbWorkorders].[FunctLocation],
[tbWorkorders].[Description],
[tbWorkorders].[Priority],
[tbWorkorders].[System status],
[tbWorkorders].[Equipment],
[tbWorkorders].[Recipient],
[tbWorkorders].[Std text key],
[tbWorkorders].[Notes]
FROM tbWorkorders
WHERE [tbWorkorders].[Work center] In
(SELECT WClist FROM [qytbWorkCentreList-Combine])
And [tbWorkorders].[Earl#start date]>=
[Forms]![Daily Jobs]![Start_Plan_date]
And [tbWorkorders].[Earl#start date]<=
[Forms]![Daily Jobs]![Finish_Plan_Date]
ORDER BY [tbWorkorders].[Work center],
[tbWorkorders].[Earl#start date],
[tbWorkorders].[Order type] DESC ,
[tbWorkorders].[Order],
[tbWorkorders].[Operation];
Hi John,
I'm not sure what you mean by a sub-query so I'll try to
explain what I am trying to do.
I have a main form called "Daily Jobs", record source -
tbDefaults,
combo box "Work", control source - Work_Centre,
text box "Start_Plan_date", record source - Date_From,
text box "Finish_Plan_Date", record source - Date_To,
This is ok for a single Work Centre but in cases where I
need more than one Work Centre I have a button to open
another form "WorkCentreList" record source -
tbWorkCentreList, where I can enter any numbers of Work
Centres in a data sheet field "Wclist", control source -
Wclist.

I combine the fields from both tables in a union
query, "qytbWorkCentreList-Combine"
SELECT tbWorkCentreList.[WClist]
FROM tbWorkCentreList

UNION SELECT tbDefaults.[Work_Centre]
FROM tbDefaults;

The data sheet sub-form "Daily Job 1", record source -
qDaily_Job_Sheets, SQL below. This is where I use "Like
[qytbWorkCentreListCombine]![WClist]" which gives me a
result from the single entry in the combo box, or the list
from the form WorkCentreList, or both. (I hope this is
making sense) The results from the query are what I want
but I can't edit anything.

SELECT [tbWorkorders].[Complete], [tbWorkorders].[Work
center], [tbWorkorders].[OrderID], [tbWorkorders].
[Earl#start date], [tbWorkorders].[Order type],
[tbWorkorders].[Order], [tbWorkorders].[Operation],
[tbWorkorders].[Control key], [tbWorkorders].[Planner
group], [tbWorkorders].[Opr# short text], [tbWorkorders].
[Normal duration], [tbWorkorders].[Number], [tbWorkorders].
[Work], [tbWorkorders].[SystemCondition], [tbWorkorders].
[Purch#req#], [tbWorkorders].[FunctLocation],
[tbWorkorders].[Description], [tbWorkorders].[Priority],
[tbWorkorders].[System status], [tbWorkorders].
[Equipment], [tbWorkorders].[Recipient], [tbWorkorders].
[Std text key], [tbWorkorders].[Notes]

FROM tbWorkorders, [qytbWorkCentreList-Combine]

WHERE ((([tbWorkorders].[Work center]) Like
[qytbWorkCentreList-Combine]![WClist]) And
(([tbWorkorders].[Earl#start date])>=[Forms]![Daily Jobs]!
[Start_Plan_date] And ([tbWorkorders].[Earl#start date]) <=
[Forms]![Daily Jobs]![Finish_Plan_Date]))

ORDER BY [tbWorkorders].[Work center], [tbWorkorders].
[Earl#start date], [tbWorkorders].[Order type] DESC ,
[tbWorkorders].[Order], [tbWorkorders].[Operation];

SELECT tbWorkCentreList.[WClist]
FROM tbWorkCentreList

This is probably a big ask and I appreciate your assistance

Regards
Nick
Click to expand...
.
Click to expand...
 
Back
Top