Parameter Query with Criteria from Combo boxes on form

[Poduska]

The method I am trying seems to work sometimes and others with errors, I don't understand.
Is my method valid or is there a better way? XP Pro, Access 97 (2003 also installed but must use 97)

I am attempting to make a parameter query using criteria from Combo boxes on a form.
User selects the combo boxes to limit the resulting records on the form, clicks a command button to export the records, sounds simple.

I have read many posts and sites telling to use this format, I have pasted the sample here from the page I was following.

Field: [FieldName]=[Forms]![FormName]![ControlName] or [Forms]![FormName]![ControlName] Is Null

Show: uncheck

Criteria: True
-------------------------------------

I tried the code below and it appears to "Sometimes" work, I had 4 combo boxes working last night and then all of a sudden none work and I receive the message "Too complex, or typed incorrectly..."? message, yesterday no error but sometimes no records returned. I thought that it might be caused by some of the table fields having Lookups but the one documented here is just a text field.

(line breaks just for readablility) The joins etc all work, only when I add the columns for the criteria does start to fail

SELECT tblAllModels.ProjectID

FROM tblDepartment 

INNER JOIN (((tblAllModels INNER JOIN tblSection ON tblAllModels.TestSection = tblSection.txtSectCode) 

INNER JOIN tblActiveProjects ON tblAllModels.ProjectID = tblActiveProjects.ProjectID) 

LEFT JOIN tblEmployee ON tblAllModels.TestEngineer = tblEmployee.txtEmpID) ON tblDepartment.txtDeptCode = tblSection.txtDeptCode

WHERE ((([tblAllModels]![ProjectID]=[Forms]![frmDesignPrediction]![cboProjectIDFilter] 
Or [Forms]![frmDesignPrediction]![cboProjectIDFilter] Is Null)=True));

I tried to use this as a Criteria but it does not pick up Null values in my data

Like "*" & [Forms]![frmDesignPrediction]![cboProjectIDFilter]

I'm pretty sure the problem lies between the keyboard and the seat....

 

[genomon]

[Forms]![frmDesignPrediction]![cboProjectIDFilter] Is Null)=True

Should be

[Forms]![frmDesignPrediction]![cboProjectIDFilter] Is Null
[/code

"Teamwork means never having to take all the blame yourself."

 

[PHV]

Replace this:
WHERE ((([tblAllModels]![ProjectID]=[Forms]![frmDesignPrediction]![cboProjectIDFilter]
Or [Forms]![frmDesignPrediction]![cboProjectIDFilter] Is Null)=True));

with this:
WHERE tblAllModels.ProjectID=[Forms]![frmDesignPrediction]![cboProjectIDFilter]
Or [Forms]![frmDesignPrediction]![cboProjectIDFilter] Is Null;

Or this:
WHERE Nz(tblAllModels.ProjectID,'') Like '*' & [Forms]![frmDesignPrediction]![cboProjectIDFilter];

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886