REPOST Re: Problem running the example in MS KB Article 304428 - How to Use the Query by Form (QBF)

M

Mike Webb

I worked with your solution and got it to work on the Northwinds example. I
next tried to take that principle to my database. I am working on the
query, but it returns all records no matter what criteria is set. Can
someone tell me where I went wrong?

Background: Using Access 2K2
Skill Level: Beginner
Table in query:
~tblAddressList - contains all info on a contact (i.e., name, address,
email, etc.)
~tblAddressListCategories - contains 89 different categories a contact
can fall into, such as "donor", "major donor", "US Fish and Wildlife
Service", etc. Each contact may have 1 or as many as 9 categories that fall
into.
~tblJoinAddressListAndCategories - my join table, contains the other 2
tables' PK's as FK's.

SQL of the query (I created the query in Design view);

SELECT tblAddressList.TitlePrefix, [LastName] & ", " & [FirstName] & " " &
[MiddleInitialName] AS FullName, tblAddressList.TitleSuffix,
tblAddressList.Organization, tblAddressList.Address, [City] & " " & [State]
& " " & [ZipCode] AS CityStateZip, tblAddressListCategories.Description
FROM tblAddressList INNER JOIN (tblAddressListCategories INNER JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE ((([LastName] & ", " & [FirstName] & " " &
[MiddleInitialName])=[Forms]![QBF_Query]![FullName])) OR
(((tblAddressListCategories.Description)=[Forms]![QBF_Query]![Description])
AND (([Forms]![QBF_Query]![FullName]) Is Null)) OR
((([Forms]![QBF_Query]![Description]) Is Null))
ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitialName],
tblAddressListCategories.Description;

TIA, Mike
============================================================================

Michel Walsh said:
Hi,


That should work, but I suspect the second OR should be an AND... at
least, as common sense dictates what we commonly do with a criteria.

WHERE (((Orders.CustomerID)=[Forms]![QBF_Form]![WhatCustomer])) OR
((([Forms]![QBF_Form]![WhatCustomer]) Is Null)) AND
(((Orders.EmployeeID)=[Forms]![QBF_Form]![WhatEmployee])) OR
((([Forms]![QBF_Form]![WhatEmployee]) Is Null))



The query has to be run for a RecordSource, or for a RowSource. It would
complain about missing parameters is you open it through CurrentDb, as in

Set rst=CurrentDb.OpenRecordset( " ... the SQL statement here...")


What is the exact error message, or problem, you have got? Note that an
SQL statement should be mathematically right, x=5 OR x=6 is
mathematically right. x=5 OR 6 is not. The later is close to the human
speech, and the query designer let you write a criteria: = 5 OR 6, but
the query designer KNOWS it have to translate it in a mathematically correct
statement, and does it. That is what SHOULD be, it is not an "error". It is
a conversion from human speech to mathematical notation.


Hoping it may help,
Vanderghast, Access MVP



Mike Webb said:
I am a newbie; use Access 2K2 on WinXP for a non-profit. Trying to
Click to expand...
learn
as
I go.

I am learning QBF (trying to) and found this KB article. The problem is
that when I put in query criteria as in the article (i.e.
"[Forms]![QBF_Form]![WhatCustomer] Or [Forms]![QBF_Form]![WhatCustomers] Is
Null" -- for which I directed to place on all one line), MS Access changes
it on Saving the query. The SQL of it is below:

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderID,
Orders.OrderDate
FROM Orders
WHERE (((Orders.CustomerID)=[Forms]![QBF_Form]![WhatCustomer])) OR
((([Forms]![QBF_Form]![WhatCustomer]) Is Null)) OR
(((Orders.EmployeeID)=[Forms]![QBF_Form]![WhatEmployee])) OR
((([Forms]![QBF_Form]![WhatEmployee]) Is Null))
ORDER BY Orders.CustomerID, Orders.EmployeeID;

I get different results when running some of the query parameters than the
article says I should.

Although the article implies this works on Access 2K2, I think it may not,
at least as written.

What went wrong, and what SHOULD it have been?

TIA,
Mike
Click to expand...
Click to expand...
 
G

Gary Walter

Mike Webb said:
I worked with your solution and got it to work on the Northwinds example. I
next tried to take that principle to my database. I am working on the
query, but it returns all records no matter what criteria is set. Can
someone tell me where I went wrong?
Click to expand...
SELECT tblAddressList.TitlePrefix, [LastName] & ", " & [FirstName] & " " &
[MiddleInitialName] AS FullName, tblAddressList.TitleSuffix,
tblAddressList.Organization, tblAddressList.Address, [City] & " " & [State]
& " " & [ZipCode] AS CityStateZip, tblAddressListCategories.Description
FROM tblAddressList INNER JOIN (tblAddressListCategories INNER JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE
( par1 ---------------------------------
( par2 |
([LastName] & ", " & [FirstName] |
& " " & [MiddleInitialName]) |
=[Forms]![QBF_Query]![FullName] |
) close par2 |
) close par1 ---------------------------
OR
( par3 ----------------------------------
( par4 |
(tblAddressListCategories.Description) |
=[Forms]![QBF_Query]![Description] |
) close par4 |
AND |
( par5 |
([Forms]![QBF_Query]![FullName]) |
Is Null |
) close par5 |
) close par3 ----------------------------
OR
( par6 ----------------------------------
( par7 |
([Forms]![QBF_Query]![Description]) |
Is Null |
) close par7 |
) close par6 -----------------------------
ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitialName],
tblAddressListCategories.Description;

It looks like you have 3 conditions (separated by OR's)
to return records.

My guess is that it is the third condition
that could "screw up" and always give
you all records.

What happens if you change the name
of the text box from "Description" to
something else (on your form and in your
query)....say "txtDescription"

Just a *guess*...

Gary Walter
 
G

Gary Walter

Also....(mean no offense)
I suppose the last condition
would always be true if the
form was not open?.....
(had to ask)

Gary Walter said:
Mike Webb said:
I worked with your solution and got it to work on the Northwinds example. I
next tried to take that principle to my database. I am working on the
query, but it returns all records no matter what criteria is set. Can
someone tell me where I went wrong?
Click to expand...
SELECT tblAddressList.TitlePrefix, [LastName] & ", " & [FirstName] & " " &
[MiddleInitialName] AS FullName, tblAddressList.TitleSuffix,
tblAddressList.Organization, tblAddressList.Address, [City] & " " & [State]
& " " & [ZipCode] AS CityStateZip, tblAddressListCategories.Description
FROM tblAddressList INNER JOIN (tblAddressListCategories INNER JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE
( par1 ---------------------------------
( par2 |
([LastName] & ", " & [FirstName] |
& " " & [MiddleInitialName]) |
=[Forms]![QBF_Query]![FullName] |
) close par2 |
) close par1 ---------------------------
OR
( par3 ----------------------------------
( par4 |
(tblAddressListCategories.Description) |
=[Forms]![QBF_Query]![Description] |
) close par4 |
AND |
( par5 |
([Forms]![QBF_Query]![FullName]) |
Is Null |
) close par5 |
) close par3 ----------------------------
OR
( par6 ----------------------------------
( par7 |
([Forms]![QBF_Query]![Description]) |
Is Null |
) close par7 |
) close par6 -----------------------------
ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitialName],
tblAddressListCategories.Description;

It looks like you have 3 conditions (separated by OR's)
to return records.

My guess is that it is the third condition
that could "screw up" and always give
you all records.

What happens if you change the name
of the text box from "Description" to
something else (on your form and in your
query)....say "txtDescription"

Just a *guess*...

Gary Walter
Click to expand...
 
G

Gary Walter

I think this is what you really wanted:
(if form is open and changed name
textbox on form to "txtDescription")

SELECT tblAddressList.TitlePrefix, [LastName] & ", " & [FirstName] & " " &
[MiddleInitialName] AS FullName, tblAddressList.TitleSuffix,
tblAddressList.Organization, tblAddressList.Address, [City] & " " & [State]
& " " & [ZipCode] AS CityStateZip, tblAddressListCategories.Description
FROM tblAddressList INNER JOIN (tblAddressListCategories INNER JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE
( par1 -----------------------------------
( par2 |
([LastName] & ", " & [FirstName] |
& " " & [MiddleInitialName]) |
=[Forms]![QBF_Query]![FullName] |
) close par2 |
OR |
( par3 |
([Forms]![QBF_Query]![FullName]) |
Is Null |
) close par3 |
) close par1 ------------------------------
AND
( par4 -------------------------------------
( par5 |
(tblAddressListCategories.Description) |
=[Forms]![QBF_Query]![txtDescription] |
) close par5 |
OR |
( par6 |
([Forms]![QBF_Query]![txtDescription]) |
Is Null |
) close par6 |
) close par4 -------------------------------

ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitialName],
tblAddressListCategories.Description;

Of course remove the "parx" and "close parx"
and "-'s" and "|'s"
and "close up" the parentheses.

Good luck,

Gary Walter

Mike Webb said:
I worked with your solution and got it to work on the Northwinds example. I
next tried to take that principle to my database. I am working on the
query, but it returns all records no matter what criteria is set. Can
someone tell me where I went wrong?

Background: Using Access 2K2
Skill Level: Beginner
Table in query:
~tblAddressList - contains all info on a contact (i.e., name, address,
email, etc.)
~tblAddressListCategories - contains 89 different categories a contact
can fall into, such as "donor", "major donor", "US Fish and Wildlife
Service", etc. Each contact may have 1 or as many as 9 categories that fall
into.
~tblJoinAddressListAndCategories - my join table, contains the other 2
tables' PK's as FK's.

SQL of the query (I created the query in Design view);

SELECT tblAddressList.TitlePrefix, [LastName] & ", " & [FirstName] & " " &
[MiddleInitialName] AS FullName, tblAddressList.TitleSuffix,
tblAddressList.Organization, tblAddressList.Address, [City] & " " & [State]
& " " & [ZipCode] AS CityStateZip, tblAddressListCategories.Description
FROM tblAddressList INNER JOIN (tblAddressListCategories INNER JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE ((([LastName] & ", " & [FirstName] & " " &
[MiddleInitialName])=[Forms]![QBF_Query]![FullName])) OR
(((tblAddressListCategories.Description)=[Forms]![QBF_Query]![Description])
AND (([Forms]![QBF_Query]![FullName]) Is Null)) OR
((([Forms]![QBF_Query]![Description]) Is Null))
ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitialName],
tblAddressListCategories.Description;

TIA, Mike
============================================================================

Michel Walsh said:
Hi,


That should work, but I suspect the second OR should be an AND... at
least, as common sense dictates what we commonly do with a criteria.

WHERE (((Orders.CustomerID)=[Forms]![QBF_Form]![WhatCustomer])) OR
((([Forms]![QBF_Form]![WhatCustomer]) Is Null)) AND
(((Orders.EmployeeID)=[Forms]![QBF_Form]![WhatEmployee])) OR
((([Forms]![QBF_Form]![WhatEmployee]) Is Null))



The query has to be run for a RecordSource, or for a RowSource. It would
complain about missing parameters is you open it through CurrentDb, as in

Set rst=CurrentDb.OpenRecordset( " ... the SQL statement here...")


What is the exact error message, or problem, you have got? Note that an
SQL statement should be mathematically right, x=5 OR x=6 is
mathematically right. x=5 OR 6 is not. The later is close to the human
speech, and the query designer let you write a criteria: = 5 OR 6, but
the query designer KNOWS it have to translate it in a mathematically correct
statement, and does it. That is what SHOULD be, it is not an "error". It is
a conversion from human speech to mathematical notation.


Hoping it may help,
Vanderghast, Access MVP



Mike Webb said:
I am a newbie; use Access 2K2 on WinXP for a non-profit. Trying to
Click to expand...
learn
as
I go.

I am learning QBF (trying to) and found this KB article. The problem is
that when I put in query criteria as in the article (i.e.
"[Forms]![QBF_Form]![WhatCustomer] Or [Forms]![QBF_Form]![WhatCustomers] Is
Null" -- for which I directed to place on all one line), MS Access changes
it on Saving the query. The SQL of it is below:

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderID,
Orders.OrderDate
FROM Orders
WHERE (((Orders.CustomerID)=[Forms]![QBF_Form]![WhatCustomer])) OR
((([Forms]![QBF_Form]![WhatCustomer]) Is Null)) OR
(((Orders.EmployeeID)=[Forms]![QBF_Form]![WhatEmployee])) OR
((([Forms]![QBF_Form]![WhatEmployee]) Is Null))
ORDER BY Orders.CustomerID, Orders.EmployeeID;

I get different results when running some of the query parameters than the
article says I should.

Although the article implies this works on Access 2K2, I think it may not,
at least as written.

What went wrong, and what SHOULD it have been?

TIA,
Mike
Click to expand...
Click to expand...
Click to expand...
 
M

Mike Webb

Thanks! I'll give it a try.
Gary Walter said:
I think this is what you really wanted:
(if form is open and changed name
textbox on form to "txtDescription")

SELECT tblAddressList.TitlePrefix, [LastName] & ", " & [FirstName] & " " &
[MiddleInitialName] AS FullName, tblAddressList.TitleSuffix,
tblAddressList.Organization, tblAddressList.Address, [City] & " " & [State]
& " " & [ZipCode] AS CityStateZip, tblAddressListCategories.Description
FROM tblAddressList INNER JOIN (tblAddressListCategories INNER JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE
( par1 -----------------------------------
( par2 |
([LastName] & ", " & [FirstName] |
& " " & [MiddleInitialName]) |
=[Forms]![QBF_Query]![FullName] |
) close par2 |
OR |
( par3 |
([Forms]![QBF_Query]![FullName]) |
Is Null |
) close par3 |
) close par1 ------------------------------
AND
( par4 -------------------------------------
( par5 |
(tblAddressListCategories.Description) |
=[Forms]![QBF_Query]![txtDescription] |
) close par5 |
OR |
( par6 |
([Forms]![QBF_Query]![txtDescription]) |
Is Null |
) close par6 |
) close par4 -------------------------------

ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitialName],
tblAddressListCategories.Description;

Of course remove the "parx" and "close parx"
and "-'s" and "|'s"
and "close up" the parentheses.

Good luck,

Gary Walter

Mike Webb said:
I worked with your solution and got it to work on the Northwinds example. I
next tried to take that principle to my database. I am working on the
query, but it returns all records no matter what criteria is set. Can
someone tell me where I went wrong?

Background: Using Access 2K2
Skill Level: Beginner
Table in query:
~tblAddressList - contains all info on a contact (i.e., name, address,
email, etc.)
~tblAddressListCategories - contains 89 different categories a contact
can fall into, such as "donor", "major donor", "US Fish and Wildlife
Service", etc. Each contact may have 1 or as many as 9 categories that fall
into.
~tblJoinAddressListAndCategories - my join table, contains the other 2
tables' PK's as FK's.

SQL of the query (I created the query in Design view);

SELECT tblAddressList.TitlePrefix, [LastName] & ", " & [FirstName] & " " &
[MiddleInitialName] AS FullName, tblAddressList.TitleSuffix,
tblAddressList.Organization, tblAddressList.Address, [City] & " " & [State]
& " " & [ZipCode] AS CityStateZip, tblAddressListCategories.Description
FROM tblAddressList INNER JOIN (tblAddressListCategories INNER JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE ((([LastName] & ", " & [FirstName] & " " &
[MiddleInitialName])=[Forms]![QBF_Query]![FullName])) OR
(((tblAddressListCategories.Description)=[Forms]![QBF_Query]![Description])
AND (([Forms]![QBF_Query]![FullName]) Is Null)) OR
((([Forms]![QBF_Query]![Description]) Is Null))
ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitialName],
tblAddressListCategories.Description;

TIA, Mike
============================================================================

Michel Walsh said:
Hi,


That should work, but I suspect the second OR should be an AND... at
least, as common sense dictates what we commonly do with a criteria.

WHERE (((Orders.CustomerID)=[Forms]![QBF_Form]![WhatCustomer])) OR
((([Forms]![QBF_Form]![WhatCustomer]) Is Null)) AND
(((Orders.EmployeeID)=[Forms]![QBF_Form]![WhatEmployee])) OR
((([Forms]![QBF_Form]![WhatEmployee]) Is Null))



The query has to be run for a RecordSource, or for a RowSource. It would
complain about missing parameters is you open it through CurrentDb, as in

Set rst=CurrentDb.OpenRecordset( " ... the SQL statement here...")


What is the exact error message, or problem, you have got? Note
Click to expand...
that
an
SQL statement should be mathematically right, x=5 OR x=6 is
mathematically right. x=5 OR 6 is not. The later is close to the human
speech, and the query designer let you write a criteria: = 5 OR 6, but
the query designer KNOWS it have to translate it in a mathematically correct
statement, and does it. That is what SHOULD be, it is not an "error".
Click to expand...
It
is
a conversion from human speech to mathematical notation.


Hoping it may help,
Vanderghast, Access MVP



I am a newbie; use Access 2K2 on WinXP for a non-profit. Trying to learn
as
I go.

I am learning QBF (trying to) and found this KB article. The problem is
that when I put in query criteria as in the article (i.e.
"[Forms]![QBF_Form]![WhatCustomer] Or [Forms]![QBF_Form]![WhatCustomers]
Is
Null" -- for which I directed to place on all one line), MS Access changes
it on Saving the query. The SQL of it is below:

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderID,
Orders.OrderDate
FROM Orders
WHERE (((Orders.CustomerID)=[Forms]![QBF_Form]![WhatCustomer])) OR
((([Forms]![QBF_Form]![WhatCustomer]) Is Null)) OR
(((Orders.EmployeeID)=[Forms]![QBF_Form]![WhatEmployee])) OR
((([Forms]![QBF_Form]![WhatEmployee]) Is Null))
ORDER BY Orders.CustomerID, Orders.EmployeeID;

I get different results when running some of the query parameters
Click to expand...
than
the
article says I should.

Although the article implies this works on Access 2K2, I think it
Click to expand...
may
not,
at least as written.

What went wrong, and what SHOULD it have been?

TIA,
Mike
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem running the example in MS KB Article 304428 - How to Use the Query by Form (QBF) Technique 2
Problem with Query By Form 1
Query by Form (QBF) 2
QBF Technique 3
Multiple criteria in one field- query by form 1
Query by Form (QBF) date problem 2
Query by Form "blows-up" when query is closed and re-opened 1
Query By Form - Questions 2

Top