Query will not get values from form

[Cpowell]

Hi,

I am sort of new at this. In my office we are still using Access 2003 and I was asked to add some features to a database. I currently have a query which should be getting its parameter values from a form but will not. When I run the query by itsself and enter the values at the prompts the query works perfectly, but when I enter the same values in the appropriate form field no records are returned at all.

Thanks for any help you can give me.

 

[pbaldy]

Make sure focus has left the textbox where you enter values. If you just enter a value and then run the query from the database window, the Value property of the textbox will not yet have been updated with the value you typed in.

 

[Cpowell]

I use a command button to generate a report for the records that are (or should be) returned would that not update it? (I apologize for my ignorance)

 

[pbaldy]

Yes, that would do it. Is the control on the form a combo box, which might be returning a different value than shown? Can you post the SQL of the query, or even a sample db?

 

[Cpowell]

The form has three combo boxes and two text boxes. I need to be able to narrow the records returned by those combined. Here is the SQL for the query I know its bad but it was the best I could come up with.

PARAMETERS [Forms]![Customer Form 1]![Start] DateTime, [Forms]![Customer Form 1]![End] DateTime, [Forms]![Customer Form 1]![Country] Text ( 255 ), [Forms]![Customer Form 1]![Region] Text ( 255 ), [Forms]![Customer Form 1]![Continent] Text ( 255 );
SELECT [Tbl1 Query].[Customer Number], [Tbl1 Query].[Date], [Tbl1 Query].Country, [Tbl1 Query].Region, [Tbl1 Query].Continent
FROM Tbl1 INNER JOIN [Tbl1 Query] ON Tbl1.[Customer Number] = [Tbl1 Query].[Customer Number]
WHERE ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start] And ([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Continent])) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start] And ([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![Continent]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![Continent]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null)) OR ((([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![Continent]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null)) OR ((([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![Continent]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null)) OR ((([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![Continent]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null)) OR ((([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Continent]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start]) AND (([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![Continent]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start]) AND (([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![Continent]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start]) AND (([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Continent]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start]) AND (([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Continent]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start]) AND (([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Continent]) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start]) AND (([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Continent]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null)) OR ((([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![Continent]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null)) OR ((([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![Continent]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null)) OR ((([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Continent]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null)) OR ((([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Continent]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null)) OR ((([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Continent]) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null)) OR ((([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Continent]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start] And ([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![Continent]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start] And ([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![Continent]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start] And ([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![Region]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start] And ([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Continent]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start] And ([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Region]) Is Null)) OR ((([Tbl1 Query].[Date])>=[Forms]![Customer Form 1]![Start] And ([Tbl1 Query].[Date])<=[Forms]![Customer Form 1]![End]) AND (([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Country]) Is Null)) OR ((([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Continent]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null)) OR ((([Tbl1 Query].Country)=[Forms]![Customer Form 1]![Country]) AND (([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Region]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null)) OR ((([Tbl1 Query].Region)=[Forms]![Customer Form 1]![Region]) AND (([Tbl1 Query].Continent)=[Forms]![Customer Form 1]![Region]) AND (([Forms]![Customer Form 1]![Country]) Is Null) AND (([Forms]![Customer Form 1]![End]) Is Null) AND (([Forms]![Customer Form 1]![Start]) Is Null));

 

[pbaldy]

Holy guacamole!

That's hard to follow. Have you tried a simple query that gets its criteria from the form? Actually, you said it worked if you typed in the values, right? Are any of the form controls combo boxes?

 

[Cpowell]

Indeed three of the form controls are combo boxes. Country, Region, and Continent. Also I forgot to mention that the query worked from the form until I added the parameters line so it would show properly as a report.

Revision
I tried removing the parameters line and it worked, but now my report wont open ::sigh::