Running Param Qry from Form; not filtering...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm running a parameter query from a control on a form, with the form setting
9 parameters. I've got the query running beautifully when I run it without
the form, but when i try to run it with the form, it doesn't filter out the
unwanted records, and I can't figure out why. I've got it set up so that if
the user leaves any of the fields/parameters blank, then the query should
return all values that meet the remaining parameters. (Yes, Tom, and John -
this is the same project from before - it's working except for this little
thing!) Any help would be greatly appreciated...here's the SQL:

SELECT [Product Keys].[Content/Project File Name], [Product Keys].[File
Location], [Product Keys].[Source Origination], [Product Keys].[Information
Product Type], [Product Keys].[CMS Product Version], [Product Keys].[Product
Line], [Product Keys].[User Profile(s)]
FROM [Product Keys]
WHERE ((([Content/Project File
Name]=[Forms]![FormToRunQuery]![Content/Project File Name] Or
Nz([Forms]![FormToRunQuery]![Content/Project File Name],"")="")<>False) AND
(([Source Origination]=[Forms]![FormToRunQuery]![Source Origination] Or
Nz([Forms]![FormToRunQuery]![Source Origination],"")="")<>False) AND
(([Information Product Type]=[Forms]![FormToRunQuery]![Information Product
Type] Or Nz([Forms]![FormToRunQuery]![Information Product
Type],"")="")<>False) AND (([CMS Product
Version]=[Forms]![FormToRunQuery]![CMS Product Version] Or
Nz([Forms]![FormToRunQuery]![CMS Product Version],"")="")<>False) AND
(([Product Line]=[Forms]![FormToRunQuery]![Product Line] Or
Nz([Forms]![FormToRunQuery]![Product Line],"")="")<>False) AND (([User
Profile(s)]=[Forms]![FormToRunQuery]![User Profile(s)] Or
Nz([Forms]![FormToRunQuery]![User Profile(s)],"")="")<>False));
 
Sorry - this is the wrong SQL - i copied it after running it, so it's got
results in it I think instead of what it's looking for. Here's the 'real
thing' below, and I also posted a question as to why it seems to be deleting
part of a single record from my table when I run it (but not every time, just
sometimes, and not the entire record, it seems to be just the fields I left
blank on the form when I ran it - very interesting...)

SELECT [Product Keys].[Content/Project File Name], [Product Keys].[File
Location], [Product Keys].[Source Origination], [Product Keys].[Information
Product Type], [Product Keys].[CMS Product Version], [Product Keys].[Product
Line], [Product Keys].[User Profile(s)]
FROM [Product Keys]
WHERE (([Content/Project File Name]=Forms!FormToRunQuery![Content/Project
File Name] Or Nz(Forms!FormToRunQuery![Content/Project File Name],"")=""))
And (([Source Origination]=Forms!FormToRunQuery![Source Origination] Or
Nz(Forms!FormToRunQuery![Source Origination],"")="")) And (([Information
Product Type]=Forms!FormToRunQuery![Information Product Type] Or
Nz(Forms!FormToRunQuery![Information Product Type],"")="")) And (([CMS
Product Version]=Forms!FormToRunQuery![CMS Product Version] Or
Nz(Forms!FormToRunQuery![CMS Product Version],"")="")) And (([Product
Line]=Forms!FormToRunQuery![Product Line] Or Nz(Forms!FormToRunQuery![Product
Line],"")="")) And (([User Profile(s)]=Forms!FormToRunQuery![User Profile(s)]
Or Nz(Forms!FormToRunQuery![User Profile(s)],"")=""));


Lorian said:
I'm running a parameter query from a control on a form, with the form setting
9 parameters. I've got the query running beautifully when I run it without
the form, but when i try to run it with the form, it doesn't filter out the
unwanted records, and I can't figure out why. I've got it set up so that if
the user leaves any of the fields/parameters blank, then the query should
return all values that meet the remaining parameters. (Yes, Tom, and John -
this is the same project from before - it's working except for this little
thing!) Any help would be greatly appreciated...here's the SQL:

SELECT [Product Keys].[Content/Project File Name], [Product Keys].[File
Location], [Product Keys].[Source Origination], [Product Keys].[Information
Product Type], [Product Keys].[CMS Product Version], [Product Keys].[Product
Line], [Product Keys].[User Profile(s)]
FROM [Product Keys]
WHERE ((([Content/Project File
Name]=[Forms]![FormToRunQuery]![Content/Project File Name] Or
Nz([Forms]![FormToRunQuery]![Content/Project File Name],"")="")<>False) AND
(([Source Origination]=[Forms]![FormToRunQuery]![Source Origination] Or
Nz([Forms]![FormToRunQuery]![Source Origination],"")="")<>False) AND
(([Information Product Type]=[Forms]![FormToRunQuery]![Information Product
Type] Or Nz([Forms]![FormToRunQuery]![Information Product
Type],"")="")<>False) AND (([CMS Product
Version]=[Forms]![FormToRunQuery]![CMS Product Version] Or
Nz([Forms]![FormToRunQuery]![CMS Product Version],"")="")<>False) AND
(([Product Line]=[Forms]![FormToRunQuery]![Product Line] Or
Nz([Forms]![FormToRunQuery]![Product Line],"")="")<>False) AND (([User
Profile(s)]=[Forms]![FormToRunQuery]![User Profile(s)] Or
Nz([Forms]![FormToRunQuery]![User Profile(s)],"")="")<>False));
Click to expand...
 
OK, I've been able to narrow it down - the Information Product Type field is
working as a filter for the results. That is, when I enter a value in the
Info Product Type field of the form and leave the rest blank, I get all the
records that have that Info Product Type, regardless of the values of the
rest of the fields. But the other fields don't seem to be filtering. I
haven't tested them all yet, but that's what I've found so far. So I'll look
at the design and see if I can see a difference in the way that field is set
up compared to the others, but in the meantime if anyone has any other ideas,
they'd be very appreciated! Thanks in advance...
Lorian
 
Back
Top