- Joined
- Feb 26, 2013
- Messages
- 12
- Reaction score
- 0
Hi guys I have an access query where I have created a union join to join fields (of the same heading) in two different tables (live table and an archive table) and what I want from the query is to bring back information based on a number (record under one of the columns) and also based on dates written in text boxes on a form.
below is my code
<code>
SELECT Cutter, CutterName, PartName, LotNumber, Status, CutDate FROM Employees_Performance
WHERE Cutter LIKE [Forms]![INSPECTOR PERFORMANCE]![txtBNo]
AND CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtMon] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtTues] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtWed] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtThur] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtFri] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtSat] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtSun] & "*"
UNION ALL SELECT Cutter, CutterName, PartName, LotNumber, Status, CutDate FROM ARCHIVE_Employees_Performance
WHERE Cutter LIKE [Forms]![INSPECTOR PERFORMANCE]![txtBNo]
AND CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtMon] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtTues] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtWed] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtThur] & "*" Or .CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtFri] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtSat] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtSun] & "*";
</code>
when this is run it says there are missing operators in the code below
<code>
Cutter LIKE [Forms]![INSPECTOR PERFORMANCE]![txtBNo]
AND CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtMon] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtTues] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtWed] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtThur] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtFri] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtSat] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtSun] & "*"
</code>
it works perfectly fine when I take out the parameters for CutDate - any ideas where I am going wrong.
Also I should mention that if i do the same with just one of the tables instead of the two tables (without the join and the second part of the sql after the union syntax) it works fine.
I understand that the information is quite vague but I have to abide by an IT policy at work.
Thank you
below is my code
<code>
SELECT Cutter, CutterName, PartName, LotNumber, Status, CutDate FROM Employees_Performance
WHERE Cutter LIKE [Forms]![INSPECTOR PERFORMANCE]![txtBNo]
AND CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtMon] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtTues] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtWed] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtThur] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtFri] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtSat] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtSun] & "*"
UNION ALL SELECT Cutter, CutterName, PartName, LotNumber, Status, CutDate FROM ARCHIVE_Employees_Performance
WHERE Cutter LIKE [Forms]![INSPECTOR PERFORMANCE]![txtBNo]
AND CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtMon] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtTues] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtWed] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtThur] & "*" Or .CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtFri] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtSat] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtSun] & "*";
</code>
when this is run it says there are missing operators in the code below
<code>
Cutter LIKE [Forms]![INSPECTOR PERFORMANCE]![txtBNo]
AND CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtMon] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtTues] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtWed] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtThur] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtFri] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtSat] & "*" Or CutDate Like [Forms]![INSPECTOR PERFORMANCE]![txtSun] & "*"
</code>
it works perfectly fine when I take out the parameters for CutDate - any ideas where I am going wrong.
Also I should mention that if i do the same with just one of the tables instead of the two tables (without the join and the second part of the sql after the union syntax) it works fine.
I understand that the information is quite vague but I have to abide by an IT policy at work.
Thank you