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

[Mike Webb]

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 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

 

[Michel Walsh]

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]

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
============================================================================

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

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 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