The expression is typed incorrectly. What am I doing wrong?

[Loc]

You have seen this a lot but you guys have come through time and time again for me. Here is what I am doing

I am making a query using 4 linked tables, I am using a form has start date, end date and the rest of the information is entered via combo boxes. If one combo box was left empty then I wanted to return all the data from that box.



I am not versed in SQL so I used the design view and I first started were you needed to enter all the fields in order to get a return. Then I entered "or Like[...] Is Null" to my Criteria and access populated the rest.

SELECT SellTable.Date, Equipment.Department, Equipment.PrimaryMachine, Equipment.Machine, Employee.Last, Parts.PartNo, Parts.Description
FROM Parts INNER JOIN (Equipment INNER JOIN (Employee INNER JOIN SellTable ON Employee.EmpID = SellTable.EmpID) ON Equipment.EqupID = SellTable.EqupID) ON Parts.PartID = SellTable.PartID
WHERE (((SellTable.Date) Between [Forms]![RepairSearch].[startDate] And [Forms]![RepairSearch].[endDate]) AND 
((Equipment.Department)=[Forms]![RepairSearch].[cboDepartment]) AND ((Equipment.PrimaryMachine)=[Forms]![RepairSearch].[cboPrimary] Or 
(Equipment.PrimaryMachine) Like [Forms]![RepairSearch].[cboPrimary]) AND ((Equipment.Machine)=[Forms]![RepairSearch].[cboMachine]) AND 
((Employee.EmpID)=[Forms]![RepairSearch].[cboEmployee] Or (Employee.EmpID) Like [Forms]![RepairSearch].[cboEmployee])) OR 
(((SellTable.Date) Between [Forms]![RepairSearch].[startDate] And [Forms]![RepairSearch].[endDate]) AND 
((Equipment.PrimaryMachine)=[Forms]![RepairSearch].[cboPrimary] Or (Equipment.PrimaryMachine) Like [Forms]![RepairSearch].[cboPrimary]) AND 
((Equipment.Machine)=[Forms]![RepairSearch].[cboMachine]) AND ((Employee.EmpID)=[Forms]![RepairSearch].[cboEmployee] Or 
(Employee.EmpID) Like [Forms]![RepairSearch].[cboEmployee]) AND ((([Equipment].[Department]) 
Like [Forms]![RepairSearch].[cboDepartment]) Is Null)) OR (((SellTable.Date) Between [Forms]![RepairSearch].[startDate] And 
[Forms]![RepairSearch].[endDate]) AND ((Equipment.Department)=[Forms]![RepairSearch].[cboDepartment]) AND 
((Equipment.PrimaryMachine)=[Forms]![RepairSearch].[cboPrimary] Or (Equipment.PrimaryMachine) Like [Forms]![RepairSearch].[cboPrimary]) AND 
((Employee.EmpID)=[Forms]![RepairSearch].[cboEmployee] Or (Employee.EmpID) Like [Forms]![RepairSearch].[cboEmployee]) AND 
((([Equipment].[Machine]) Like [Forms]![RepairSearch].[cboMachine]) Is Null)) OR (((SellTable.Date) Between 
[Forms]![RepairSearch].[startDate] And [Forms]![RepairSearch].[endDate]) AND ((Equipment.PrimaryMachine)=[Forms]![RepairSearch].[cboPrimary] Or
 (Equipment.PrimaryMachine) Like [Forms]![RepairSearch].[cboPrimary]) AND ((Employee.EmpID)=[Forms]![RepairSearch].[cboEmployee] Or 
(Employee.EmpID) Like [Forms]![RepairSearch].[cboEmployee]) AND ((([Equipment].[Department]) Like [Forms]![RepairSearch].[cboDepartment]) Is Null) AND 
((([Equipment].[Machine]) Like [Forms]![RepairSearch].[cboMachine]) Is Null))
ORDER BY SellTable.Date;

Is there am easier way to do this.

[alansidman]

Try changing the lines similar to this:

Like [Forms]![RepairSearch].[cboMachine] Is Null

to

Like [Forms]![RepairSearch].[cboMachine] & "*"

[Loc]

I have seen this but I wanted exact matches or all if left blank.

Ex...

I am looking for just "Dog" and I will also return "Dog Catcher" I do not want the "Dog Catcher" in my report.

[June7]

LIKE and Is Null together is nonsense. LIKE is useful only with wildcard.

Remove LIKE and don't use wildcard.

[Loc]

It gave me no results by removing like and no wiled card.

[Perceptus]

I think the OP should look into MS Access sub queries and the use of Where Exists() or Where not exists()

[June7]

Did query builder put = sign in where LIKE was?

Without the wildcard will need a value in all of the criteria inputs.

If you use wildcard then input of "Dog" will return records with "Dog catcher".

Alternative is instead of a parameterized query, use VBA code to dynamically construct filter string with only those controls that have inputs.

Review:
http://allenbrowne.com/ser-62code.html
http://allenbrowne.com/ser-50.html

[Loc]

My problem is I am using 4 tables to get this information, I am not sure how to combine this information and it might get complex. But what I think I am going to do is combine VBA with the query. I will make several types of querys and depending on the information they enter will result on what query will run.