Access changing Query

[FeS2]

In Access 2003, I make a query in design view and enter criteria nearly all the fields. After typing it in, i can view it in SQL view and design view, and it runs perfectly. When i close the query editor and then go back into it, it is all scrambled and fails to pull the correct data. Basically this query will be run from a form that the user fills in what they are searching for or leaves it blank and hits the search button. if the field is blank i want the query to ingnor that criteria. I haven't had this run from the form but ran it directly and filled the parameters. here is what the SQL code looks like:

SELECT [E-mail/Call tracking].Method, [E-mail/Call tracking].[Date E-mail sent], [E-mail/Call tracking].[Time E-mail sent], [E-mail/Call tracking].[E-mail Box], [E-mail/Call tracking].[Rep Initials], [E-mail/Call tracking].Team, [E-mail/Call tracking].[ACS Group], [E-mail/Call tracking].[Date of Issue], [E-mail/Call tracking].[Open Order], [E-mail/Call tracking].ANI, [E-mail/Call tracking].[Order #], [E-mail/Call tracking].[Reason / Complaint], [E-mail/Call tracking].Error, [E-mail/Call tracking].[Results / Comments]
FROM [E-mail/Call tracking]
WHERE ((([E-mail/Call tracking].Method)=[Forms]![Query Form]![method] Or [Forms]![Query Form]![method] Is Null) AND (([E-mail/Call tracking].[Date E-mail sent])=[Forms]![Query Form]![date e-mail sent] Or [Forms]![Query Form]![date e-mail sent] Is Null) AND (([E-mail/Call tracking].[E-mail Box])=[Forms]![Query Form]![e-mail box] Or [Forms]![Query Form]![e-mail box] Is Null) AND (([E-mail/Call tracking].[Rep Initials])=[Forms]![Query Form]![rep initials] Or [Forms]![Query Form]![rep initials] Is Null) AND (([E-mail/Call tracking].Team)=[Forms]![Query Form]![team] Or [Forms]![Query Form]![team] Is Null) AND (([E-mail/Call tracking].[ACS Group])=[Forms]![Query Form]![acs group] Or [Forms]![Query Form]![acs group] Is Null) AND (([E-mail/Call tracking].[Date of Issue])=[Forms]![Query Form]![date of issue] Or [Forms]![Query Form]![date of issue] Is Null) AND (([E-mail/Call tracking].ANI)=[Forms]![Query Form]![ani] Or [Forms]![Query Form]![ani] Is Null) AND (([E-mail/Call tracking].[Order #])=[Forms]![Query Form]![order #] Or [Forms]![Query Form]![order #] Is Null) AND (([E-mail/Call tracking].Error)=[Forms]![Query Form]![error] Or [Forms]![Query Form]![error] Is Null))
ORDER BY [E-mail/Call tracking].Method;

I know that since i am using Access, the code could be streamlined a bit, but that is beyond my current skill. What i need to do is make this able to be saved and run more than once from the form.

 

[PHV]

Have tried something like this ?
FROM [E-mail/Call tracking] A
WHERE Nz(A.Method)=Nz([Forms]![Query Form]![method],Nz(A.Method))
AND Nz([Date E-mail sent])=Nz([Forms]![Query Form]![date e-mail sent],Nz([Date E-mail sent]))
AND ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FeS2]

Questions
What is the A?
What is Nz?

 

[lespaul]

'A' is an alias for 'E-mail/Call Tracking', so PHV didn't have to keep typing : 'E-mail/Call Tracking', he only had to type 'A' and that means the same thing.

Nz is a function that allows you determine if the field is null. Search the Access help for more info.



Leslie

 

[FeS2]

YAY!! I got this to work now. What I did was get rid of everything right after the SELECT and before FROM and replaced it with *. Then created a new blank query in ACCESS and went straight to SQL view and pasted it in. After several weeks of running it still works great. Thanks for everyones help.