allowing null entries

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

Guest

I recently developed a custom parameter dialog per the excellent tutorial
found at www.fontstuff.com/access/acctut08.htm

I've followed everything to the letter but am getting odd data returns. Here
is my SQL:

SELECT tblFinishedGoods.txtProfileID, tblFinishedGoods.Brand,
tblFinishedGoods.Claim, tblFinishedGoods.FlavorStyle,
tblFinishedGoods.Category, tblFinishedGoods.CountryLanguage,
tblFinishedGoods.UnitCount, tblFinishedGoods.SubUnitCount,
tblFinishedGoods.UnitSize, tblFinishedGoods.UnitUOM,
qryProfilesAssociations.ProfilesAssociations,
qryProfilesAssociations.Description
FROM tblFinishedGoods LEFT JOIN qryProfilesAssociations ON
tblFinishedGoods.txtProfileID = qryProfilesAssociations.txtProfileID
WHERE
(((tblFinishedGoods.UnitCount)=[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitCount]
Or (tblFinishedGoods.UnitCount) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitCount] Is Null) AND
((tblFinishedGoods.SubUnitCount)=[Forms]![frmQueryFGUnitCountUnitSize]![cbSubUnitCount]
Or (tblFinishedGoods.SubUnitCount) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbSubUnitCount] Is Null) AND
((tblFinishedGoods.UnitSize)=[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitSize]
Or (tblFinishedGoods.UnitSize) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitSize] Is Null) AND
((tblFinishedGoods.UnitUOM)=[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitUOM]
Or (tblFinishedGoods.UnitUOM) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitUOM] Is Null))
ORDER BY tblFinishedGoods.txtProfileID;

Keep in mind this is the SQL BEFORE running the query, therefore, the Or
Like and Is Null criteria havn't been broken down by Access.

When I do run this and Access breaks down the criteria I don't get the data
return I'm expecting. For example if I type in "857" in cbUnitCount the query
returns lots of records but all of them have blank UnitCounts - "857" doesn't
display, either!

I'm stumped. Does anyone see something wrong with what I'm doing?

Thanks for your time and assistance!
 
JohnLute said:
I recently developed a custom parameter dialog per the excellent
tutorial found at www.fontstuff.com/access/acctut08.htm

I've followed everything to the letter but am getting odd data
returns. Here is my SQL:

SELECT tblFinishedGoods.txtProfileID, tblFinishedGoods.Brand,
tblFinishedGoods.Claim, tblFinishedGoods.FlavorStyle,
tblFinishedGoods.Category, tblFinishedGoods.CountryLanguage,
tblFinishedGoods.UnitCount, tblFinishedGoods.SubUnitCount,
tblFinishedGoods.UnitSize, tblFinishedGoods.UnitUOM,
qryProfilesAssociations.ProfilesAssociations,
qryProfilesAssociations.Description
FROM tblFinishedGoods LEFT JOIN qryProfilesAssociations ON
tblFinishedGoods.txtProfileID = qryProfilesAssociations.txtProfileID
WHERE
(((tblFinishedGoods.UnitCount)=[Forms]![frmQueryFGUnitCountUnitSize]![cb
UnitCount]
Or (tblFinishedGoods.UnitCount) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitCount] Is Null) AND
((tblFinishedGoods.SubUnitCount)=[Forms]![frmQueryFGUnitCountUnitSize]![
cbSubUnitCount]
Or (tblFinishedGoods.SubUnitCount) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbSubUnitCount] Is Null) AND
((tblFinishedGoods.UnitSize)=[Forms]![frmQueryFGUnitCountUnitSize]![cbUn
itSize]
Or (tblFinishedGoods.UnitSize) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitSize] Is Null) AND
((tblFinishedGoods.UnitUOM)=[Forms]![frmQueryFGUnitCountUnitSize]![cbUni
tUOM]
Or (tblFinishedGoods.UnitUOM) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitUOM] Is Null))
ORDER BY tblFinishedGoods.txtProfileID;

Keep in mind this is the SQL BEFORE running the query, therefore, the
Or Like and Is Null criteria havn't been broken down by Access.

When I do run this and Access breaks down the criteria I don't get
the data return I'm expecting. For example if I type in "857" in
cbUnitCount the query returns lots of records but all of them have
blank UnitCounts - "857" doesn't display, either!

I'm stumped. Does anyone see something wrong with what I'm doing?

Thanks for your time and assistance!
Click to expand...

John, the WHERE-clause criteria in this form ...


(tblFinishedGoods.UnitCount)=[Forms]![frmQueryFGUnitCountUnitSize]![cbUn
itCount]
Or (tblFinishedGoods.UnitCount) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitCount] Is Null

.... don't make sense to me. I would expect something along the lines of


(tblFinishedGoods.UnitCount)=[Forms]![frmQueryFGUnitCountUnitSize]![cbUn
itCount]
Or [Forms]![frmQueryFGUnitCountUnitSize]![cbUnitCount] Is Null

That would say, "If [Forms]![frmQueryFGUnitCountUnitSize]![cbUnitCount]
is not Null, records to be returned must match it in the UnitCount
field; otherwise, the UnitCount field doesn't matter."

Your use of "Like [Forms]![frmQueryFGUnitCountUnitSize]![cbUnitCount] Is
Null" amounts to either "Like 0" or "Like -1", depending on whether the
form control is Null or not.
 
Dirk:

As always you are correct! The tutorial I followed is very helpful but it's
code is either written incorrectly or I'm not reading it properly.

Your correction makes it sing! Muchas Gracias, Mi Amigo! (I'm part Colombian
by association as my wife is Colombian).

So when are you going to write your own Access book? You've got a "tough"
name so how about something devious like: Dirk's Dirty Secret Codes.

That would be sure to draw attention in the computer section of the
bookstores!

--
www.Marzetti.com


Dirk Goldgar said:
JohnLute said:
I recently developed a custom parameter dialog per the excellent
tutorial found at www.fontstuff.com/access/acctut08.htm

I've followed everything to the letter but am getting odd data
returns. Here is my SQL:

SELECT tblFinishedGoods.txtProfileID, tblFinishedGoods.Brand,
tblFinishedGoods.Claim, tblFinishedGoods.FlavorStyle,
tblFinishedGoods.Category, tblFinishedGoods.CountryLanguage,
tblFinishedGoods.UnitCount, tblFinishedGoods.SubUnitCount,
tblFinishedGoods.UnitSize, tblFinishedGoods.UnitUOM,
qryProfilesAssociations.ProfilesAssociations,
qryProfilesAssociations.Description
FROM tblFinishedGoods LEFT JOIN qryProfilesAssociations ON
tblFinishedGoods.txtProfileID = qryProfilesAssociations.txtProfileID
WHERE
(((tblFinishedGoods.UnitCount)=[Forms]![frmQueryFGUnitCountUnitSize]![cb
UnitCount]
Or (tblFinishedGoods.UnitCount) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitCount] Is Null) AND
((tblFinishedGoods.SubUnitCount)=[Forms]![frmQueryFGUnitCountUnitSize]![
cbSubUnitCount]
Or (tblFinishedGoods.SubUnitCount) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbSubUnitCount] Is Null) AND
((tblFinishedGoods.UnitSize)=[Forms]![frmQueryFGUnitCountUnitSize]![cbUn
itSize]
Or (tblFinishedGoods.UnitSize) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitSize] Is Null) AND
((tblFinishedGoods.UnitUOM)=[Forms]![frmQueryFGUnitCountUnitSize]![cbUni
tUOM]
Or (tblFinishedGoods.UnitUOM) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitUOM] Is Null))
ORDER BY tblFinishedGoods.txtProfileID;

Keep in mind this is the SQL BEFORE running the query, therefore, the
Or Like and Is Null criteria havn't been broken down by Access.

When I do run this and Access breaks down the criteria I don't get
the data return I'm expecting. For example if I type in "857" in
cbUnitCount the query returns lots of records but all of them have
blank UnitCounts - "857" doesn't display, either!

I'm stumped. Does anyone see something wrong with what I'm doing?

Thanks for your time and assistance!
Click to expand...

John, the WHERE-clause criteria in this form ...


(tblFinishedGoods.UnitCount)=[Forms]![frmQueryFGUnitCountUnitSize]![cbUn
itCount]
Or (tblFinishedGoods.UnitCount) Like
[Forms]![frmQueryFGUnitCountUnitSize]![cbUnitCount] Is Null

.... don't make sense to me. I would expect something along the lines of


(tblFinishedGoods.UnitCount)=[Forms]![frmQueryFGUnitCountUnitSize]![cbUn
itCount]
Or [Forms]![frmQueryFGUnitCountUnitSize]![cbUnitCount] Is Null

That would say, "If [Forms]![frmQueryFGUnitCountUnitSize]![cbUnitCount]
is not Null, records to be returned must match it in the UnitCount
field; otherwise, the UnitCount field doesn't matter."

Your use of "Like [Forms]![frmQueryFGUnitCountUnitSize]![cbUnitCount] Is
Null" amounts to either "Like 0" or "Like -1", depending on whether the
form control is Null or not.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
JohnLute said:
Dirk:

As always you are correct!
Click to expand...

Your correction makes it sing! Muchas Gracias, Mi Amigo! (I'm part
Colombian by association as my wife is Colombian).
Click to expand...

I'd try to reply in Spanish, but I don't trust Babelfish.
So when are you going to write your own Access book? You've got a
"tough" name so how about something devious like: Dirk's Dirty Secret
Codes.
Click to expand...

LOL! Yeah, that would be a big seller. :-/

One of these days I'm going to have to put some tips up on my website,
like everybody else. I've got a few of my favorite tricks tucked away
in a folder.
 
Dirk Goldgar said:
<g> Well, I wouldn't say "always" ...
Click to expand...

Personally, I can't think of a time where you gave me wrong advice. Whenever
I see your name in response to something I've posted my immediate thought is,
"Well, looks like I'll have this resolved in a flash!"
I'd try to reply in Spanish, but I don't trust Babelfish.
Click to expand...

Like the old saying goes: "Trust in God - All Others Please Bring Data."

:)
One of these days I'm going to have to put some tips up on my website,
like everybody else. I've got a few of my favorite tricks tucked away
in a folder.
Click to expand...

I'm sure whatever tips you have will be of great help to everyone!
 
Back
Top