Search Query Re-Writing itself!

[XelaIrodavlas]

Hi all,

I have written a query which allows the user to search records of a table of incident information based on a number of criteria (for example sake let's limit that to PersonnelID, SiteID and TrendID).

I've done this simply by specifying that each criteria equals a control on a form, or that control is null (so it isn't a required field). eg.:

Expr = Forms!MyForm!Criteria1 OR Forms!MyForm!Criteria1 Is Null

This works fine, the problem occurs once i save and close the criteria. If i try to open it again I find the SQL has rewritten itself and become enormous (it seems to be all the possible combinations of the criteria written one at a time). This makes it much harder to add new fields or ammend the criteria, and if there are too many fields can crash Access.

My code was written like this:

SELECT AIRHONMReports.ObsMisID, AIRHONMReports.SiteID, AIRHONMReports.ReportingPersonnelID, AIRHONMReports.TrendID

FROM AIRHONMReports

WHERE (((AIRHONMReports.SiteID)=[Forms]![ReportsFrm]![SiteID] Or [Forms]![ReportsFrm]![SiteID] Is Null) 
AND ((AIRHONMReports.ReportingPersonnelID)=[Forms]![ReportsFrm]![PersonnelID] Or [Forms]![ReportsFrm]![PersonnelID] Is Null) 
AND ((AIRHONMReports.TrendID)=[Forms]![ReportsFrm]![TrendID] Or [Forms]![ReportsFrm]![TrendID] Is Null));

After saving closing and re-opening it it looks like this:

SELECT AIRHONMReports.ObsMisID, AIRHONMReports.SiteID, AIRHONMReports.ReportingPersonnelID, AIRHONMReports.TrendID

FROM AIRHONMReports

WHERE (((AIRHONMReports.SiteID)=[Forms]![ReportsFrm]![SiteID]) 
AND ((AIRHONMReports.ReportingPersonnelID)=[Forms]![ReportsFrm]![PersonnelID]) 
AND ((AIRHONMReports.TrendID)=[Forms]![ReportsFrm]![TrendID])) OR (((AIRHONMReports.ReportingPersonnelID)=[Forms]![ReportsFrm]![PersonnelID]) 
AND ((AIRHONMReports.TrendID)=[Forms]![ReportsFrm]![TrendID]) 
AND (([Forms]![ReportsFrm]![SiteID]) Is Null)) OR (((AIRHONMReports.SiteID)=[Forms]![ReportsFrm]![SiteID]) 
AND ((AIRHONMReports.TrendID)=[Forms]![ReportsFrm]![TrendID]) 
AND (([Forms]![ReportsFrm]![PersonnelID]) Is Null)) OR (((AIRHONMReports.TrendID)=[Forms]![ReportsFrm]![TrendID]) 
AND (([Forms]![ReportsFrm]![SiteID]) Is Null) 
AND (([Forms]![ReportsFrm]![PersonnelID]) Is Null)) OR (((AIRHONMReports.SiteID)=[Forms]![ReportsFrm]![SiteID]) 
AND ((AIRHONMReports.ReportingPersonnelID)=[Forms]![ReportsFrm]![PersonnelID]) 
AND (([Forms]![ReportsFrm]![TrendID]) Is Null)) OR (((AIRHONMReports.ReportingPersonnelID)=[Forms]![ReportsFrm]![PersonnelID]) 
AND (([Forms]![ReportsFrm]![SiteID]) Is Null) 
AND (([Forms]![ReportsFrm]![TrendID]) Is Null)) OR (((AIRHONMReports.SiteID)=[Forms]![ReportsFrm]![SiteID]) 
AND (([Forms]![ReportsFrm]![PersonnelID]) Is Null) 
AND (([Forms]![ReportsFrm]![TrendID]) Is Null)) OR ((([Forms]![ReportsFrm]![SiteID]) Is Null) 
AND (([Forms]![ReportsFrm]![PersonnelID]) Is Null) 
AND (([Forms]![ReportsFrm]![TrendID]) Is Null));

You'll notice it has now tripled in size and is going to be much harder to edit, but both do the same thing!

Does anyone know why this is happening? Or is there a better way to get what I'm trying to achieve, that is, to create user-friendly on demand queries?

All ideas appreciated

Thanks,

 

[pr2-eugin]

Try this Query.

SELECT AIRHONMReports.ObsMisID, AIRHONMReports.SiteID, AIRHONMReports.ReportingPersonnelID, AIRHONMReports.TrendID
FROM AIRHONMReports
WHERE (((AIRHONMReports.SiteID = [Forms]![ReportsFrm]![SiteID]) Or ([Forms]![ReportsFrm]![SiteID] Is Null)) 
AND ((AIRHONMReports.ReportingPersonnelID = [Forms]![ReportsFrm]![PersonnelID]) Or ([Forms]![ReportsFrm]![PersonnelID] Is Null)) 
AND ((AIRHONMReports.TrendID = [Forms]![ReportsFrm]![TrendID]) Or ([Forms]![ReportsFrm]![TrendID] Is Null)));

 

[XelaIrodavlas]

ah the extra set of brackets trick... I tried this before and found it didn't help, but now having tried it again I can see it does!

I've been playing around with it, it seems the code only changes if it is saved and reopened in the design mode (rather than SQL view) my guess is Access struggles to represent this in design view so it goes a little bit crazy...

Just something to watch out for I suppose...

Thanks Paul!

 

[pr2-eugin]

Although the SQL Query wizard/Builder makes life a bit easier, it sometimes is frustrating. Specially with brackets. I would simply write Queries by hand. then copy it into the QBE, to make things pretty. Lol.

Glad to help ! Good luck !