IIF issues and Null value

L

Larry Hodges

Running Access 2002.

I have a query on one form's combo box that's looking to another form,
frmCustomerLumberTally, for criteria. The field BundleQuant it's
referencing is Long Integer, and it's the same table on both forms.

If there is data in BundleQuant, everything is fine and the query returns
results. If the field is Null, there no results are returned. So I've
tried qualifying using another field named TallyType. In other words, if
TallyType = "Bundle", there will be a value in BundleQuant. This is what I
have and doesn't work:

IIf([Forms]![frmCustomerLumberTally]![TallyType]="Bundle",[Forms]![frmCustomerLumberTally]![BundleQuant])

Am I making this too complicated? Is there another way to deal with a Null
value in BundleQuant?

Thanks in advance for your help,
-Larry
 
L

Larry Hodges

Oh yeah...sorry...

SELECT tblTallySheetSummary.TallySheetNumber,
tblTallySheetSummary.TallySheetID, tblTallySheetSummary.BundleQuant
FROM tblTallySheetSummary
WHERE
(((tblTallySheetSummary.TallySheetNumber)<>[Forms]![frmCustomerLumberTally]![TallySheetNumber])
AND
((tblTallySheetSummary.BundleQuant)=IIf([Forms]![frmCustomerLumberTally]![TallyType]="Bundle",[Forms]![frmCustomerLumberTally]![BundleQuant]))
AND ((tblTallySheetSummary.Grade)=[Forms]![frmCustomerLumberTally]![Grade])
AND
((tblTallySheetSummary.Species)=[Forms]![frmCustomerLumberTally]![Species])
AND
((tblTallySheetSummary.Thickness)=[Forms]![frmCustomerLumberTally]![Thickness])
AND ((tblTallySheetSummary.Width)=[Forms]![frmCustomerLumberTally]![Width])
AND
((tblTallySheetSummary.TallyType)=[Forms]![frmCustomerLumberTally]![TallyType]));


KARL DEWEY said:
Post your SQL.

Larry Hodges said:
Running Access 2002.

I have a query on one form's combo box that's looking to another form,
frmCustomerLumberTally, for criteria. The field BundleQuant it's
referencing is Long Integer, and it's the same table on both forms.

If there is data in BundleQuant, everything is fine and the query returns
results. If the field is Null, there no results are returned. So I've
tried qualifying using another field named TallyType. In other words, if
TallyType = "Bundle", there will be a value in BundleQuant. This is what
I
have and doesn't work:

IIf([Forms]![frmCustomerLumberTally]![TallyType]="Bundle",[Forms]![frmCustomerLumberTally]![BundleQuant])

Am I making this too complicated? Is there another way to deal with a
Null
value in BundleQuant?

Thanks in advance for your help,
-Larry
Click to expand...
Click to expand...
 
L

Larry Hodges

Actually, to make it simpler, I took out the other criteria. I've left just
the one that has the issues...


SELECT tblTallySheetSummary.TallySheetNumber,
tblTallySheetSummary.TallySheetID, tblTallySheetSummary.BundleQuant
FROM tblTallySheetSummary
WHERE
(((tblTallySheetSummary.BundleQuant)=IIf([Forms]![frmCustomerLumberTally]![TallyType]="Bundle",[Forms]![frmCustomerLumberTally]![BundleQuant])));



KARL DEWEY said:
Post your SQL.

Larry Hodges said:
Running Access 2002.

I have a query on one form's combo box that's looking to another form,
frmCustomerLumberTally, for criteria. The field BundleQuant it's
referencing is Long Integer, and it's the same table on both forms.

If there is data in BundleQuant, everything is fine and the query returns
results. If the field is Null, there no results are returned. So I've
tried qualifying using another field named TallyType. In other words, if
TallyType = "Bundle", there will be a value in BundleQuant. This is what
I
have and doesn't work:

IIf([Forms]![frmCustomerLumberTally]![TallyType]="Bundle",[Forms]![frmCustomerLumberTally]![BundleQuant])

Am I making this too complicated? Is there another way to deal with a
Null
value in BundleQuant?

Thanks in advance for your help,
-Larry
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
SELECT tblTallySheetSummary.TallySheetNumber,
tblTallySheetSummary.TallySheetID, tblTallySheetSummary.BundleQuant
FROM tblTallySheetSummary
WHERE
tblTallySheetSummary.BundleQuant=[Forms]![frmCustomerLumberTally]![BundleQuant] Or tblTallySheetSummary.BundleQuant Is Null;


Larry Hodges said:
Actually, to make it simpler, I took out the other criteria. I've left just
the one that has the issues...


SELECT tblTallySheetSummary.TallySheetNumber,
tblTallySheetSummary.TallySheetID, tblTallySheetSummary.BundleQuant
FROM tblTallySheetSummary
WHERE
(((tblTallySheetSummary.BundleQuant)=IIf([Forms]![frmCustomerLumberTally]![TallyType]="Bundle",[Forms]![frmCustomerLumberTally]![BundleQuant])));



KARL DEWEY said:
Post your SQL.

Larry Hodges said:
Running Access 2002.

I have a query on one form's combo box that's looking to another form,
frmCustomerLumberTally, for criteria. The field BundleQuant it's
referencing is Long Integer, and it's the same table on both forms.

If there is data in BundleQuant, everything is fine and the query returns
results. If the field is Null, there no results are returned. So I've
tried qualifying using another field named TallyType. In other words, if
TallyType = "Bundle", there will be a value in BundleQuant. This is what
I
have and doesn't work:

IIf([Forms]![frmCustomerLumberTally]![TallyType]="Bundle",[Forms]![frmCustomerLumberTally]![BundleQuant])

Am I making this too complicated? Is there another way to deal with a
Null
value in BundleQuant?

Thanks in advance for your help,
-Larry
Click to expand...
Click to expand...
Click to expand...
 
L

Larry Hodges

Thanks! That did it. I really appreciate the help Karl.

-Larry

KARL DEWEY said:
Try this --
SELECT tblTallySheetSummary.TallySheetNumber,
tblTallySheetSummary.TallySheetID, tblTallySheetSummary.BundleQuant
FROM tblTallySheetSummary
WHERE
tblTallySheetSummary.BundleQuant=[Forms]![frmCustomerLumberTally]![BundleQuant]
Or tblTallySheetSummary.BundleQuant Is Null;


Larry Hodges said:
Actually, to make it simpler, I took out the other criteria. I've left
just
the one that has the issues...


SELECT tblTallySheetSummary.TallySheetNumber,
tblTallySheetSummary.TallySheetID, tblTallySheetSummary.BundleQuant
FROM tblTallySheetSummary
WHERE
(((tblTallySheetSummary.BundleQuant)=IIf([Forms]![frmCustomerLumberTally]![TallyType]="Bundle",[Forms]![frmCustomerLumberTally]![BundleQuant])));



KARL DEWEY said:
Post your SQL.

:

Running Access 2002.

I have a query on one form's combo box that's looking to another form,
frmCustomerLumberTally, for criteria. The field BundleQuant it's
referencing is Long Integer, and it's the same table on both forms.

If there is data in BundleQuant, everything is fine and the query
returns
results. If the field is Null, there no results are returned. So
I've
tried qualifying using another field named TallyType. In other words,
if
TallyType = "Bundle", there will be a value in BundleQuant. This is
what
I
have and doesn't work:

IIf([Forms]![frmCustomerLumberTally]![TallyType]="Bundle",[Forms]![frmCustomerLumberTally]![BundleQuant])

Am I making this too complicated? Is there another way to deal with a
Null
value in BundleQuant?

Thanks in advance for your help,
-Larry
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

Null value issue in Criteria 2
Criteria to return all records if selection from form is null 4
iif function syntax problem 5
Parameters And Null Values 2
Query driven by form field 1
IIF Statement In Query Doesn't Work Right 3
Null Criteria Problem 4
If statement that returns all entries or one 2

Top