Access Access SQL - union joins with parameters

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