Invalid use of null

R

RobertG

Hi all,

I am receiving "Error 93: Invalid use of null" when trying to run a query
with criteria in a certain field; the query runs fine without the criteria,
but not so much when I try to specify one.

The expression of the field in question is:

Period: CStr(IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy"),5
& DatePart("yyyy",[TrainingDate])+3)))

The full SQL is:

SELECT tblTraining.StaffID, tblStaff.LName & ", " & tblStaff.FName AS
StaffName, tblTrainingType.TrainingType, tblTraining.TrainingType,
tblFrequency.Frequency, tblStaff.AnniversaryDate, tblTraining.TrainingDate,
IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),DateSerial(Year([TrainingDate])+3,12,31)))
AS DueDate, CStr(IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy"),5
& DatePart("yyyy",[TrainingDate])+3))) AS Period
FROM (tblFrequency RIGHT JOIN tblTrainingType ON tblFrequency.FrequencyID =
tblTrainingType.Frequency) RIGHT JOIN (tblStaff LEFT JOIN tblTraining ON
tblStaff.StaffID = tblTraining.StaffID) ON tblTrainingType.TrainingTypeID =
tblTraining.TrainingType
WHERE (((IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),DateSerial(Year([TrainingDate])+3,12,31))))>=Date())
AND ((CStr(IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy"),5
& DatePart("yyyy",[TrainingDate])+3))))=fPeriod()) AND
((tblFrequency.FrequencyID)=2)) OR (((IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),DateSerial(Year([TrainingDate])+3,12,31))))>=Date())
AND ((CStr(IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy"),5
& DatePart("yyyy",[TrainingDate])+3))))=fPeriod()) AND
((tblFrequency.FrequencyID)=3))
ORDER BY tblTraining.StaffID;

Any ideas why this query will run under one circumstance, but not another?

Feel free to ask any questions or request more info, if need. Your help is
greatly appreciated!

Thanks,

Robert
 
V

Vsn

You might want to leave the false option to set it to Null, what is the use
to set it to Null again, if Null was TRUE already.

Further I would use the function 'IsNull(field_x)' instead of 'field_x Is
Null'

This might do for you.

Ludovic
 
J

John Spencer

This is going to error if AnniversaryDate is null or if TrainingDate Is null.
CStr(Null) will error everytime and since you are going to give it null when
trainingDate is null, you will have an error.

Month(Null) will return Null and DateSerial will error if one of its arguments
is not a valid number.

So DROP CStr. If you want a zero length string then change the Null to "".

PERHAPS what you want is the following.

Period: IIf([TrainingDate] Is Null OR AnniversaryDate Is Null,
Null,
IIf([FrequencyID]=2,
Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy")
,"5" & DatePart("yyyy",[TrainingDate])+3))

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hi all,

I am receiving "Error 93: Invalid use of null" when trying to run a query
with criteria in a certain field; the query runs fine without the criteria,
but not so much when I try to specify one.

The expression of the field in question is:

Period: CStr(IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy"),5
& DatePart("yyyy",[TrainingDate])+3)))

The full SQL is:

SELECT tblTraining.StaffID, tblStaff.LName & ", " & tblStaff.FName AS
StaffName, tblTrainingType.TrainingType, tblTraining.TrainingType,
tblFrequency.Frequency, tblStaff.AnniversaryDate, tblTraining.TrainingDate,
IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),DateSerial(Year([TrainingDate])+3,12,31)))
AS DueDate, CStr(IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy"),5
& DatePart("yyyy",[TrainingDate])+3))) AS Period
FROM (tblFrequency RIGHT JOIN tblTrainingType ON tblFrequency.FrequencyID =
tblTrainingType.Frequency) RIGHT JOIN (tblStaff LEFT JOIN tblTraining ON
tblStaff.StaffID = tblTraining.StaffID) ON tblTrainingType.TrainingTypeID =
tblTraining.TrainingType
WHERE (((IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),DateSerial(Year([TrainingDate])+3,12,31))))>=Date())
AND ((CStr(IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy"),5
& DatePart("yyyy",[TrainingDate])+3))))=fPeriod()) AND
((tblFrequency.FrequencyID)=2)) OR (((IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),DateSerial(Year([TrainingDate])+3,12,31))))>=Date())
AND ((CStr(IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy"),5
& DatePart("yyyy",[TrainingDate])+3))))=fPeriod()) AND
((tblFrequency.FrequencyID)=3))
ORDER BY tblTraining.StaffID;

Any ideas why this query will run under one circumstance, but not another?

Feel free to ask any questions or request more info, if need. Your help is
greatly appreciated!

Thanks,

Robert
Click to expand...
 
M

Michel Walsh

With JET, IsNull requires an external call to VBA, while IS NULL is the SQL
way of testing for a null. So, assuming the computation is performed inside
a query, IS NULL is preferable to IsNull, although the difference, in
execution time, may be irrelevant.


Vanderghast, Access MVP


Vsn said:
You might want to leave the false option to set it to Null, what is the
use to set it to Null again, if Null was TRUE already.

Further I would use the function 'IsNull(field_x)' instead of 'field_x Is
Null'

This might do for you.

Ludovic

RobertG said:
Hi all,

I am receiving "Error 93: Invalid use of null" when trying to run a query
with criteria in a certain field; the query runs fine without the
criteria,
but not so much when I try to specify one.

The expression of the field in question is:

Period: CStr(IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy"),5
& DatePart("yyyy",[TrainingDate])+3)))

The full SQL is:

SELECT tblTraining.StaffID, tblStaff.LName & ", " & tblStaff.FName AS
StaffName, tblTrainingType.TrainingType, tblTraining.TrainingType,
tblFrequency.Frequency, tblStaff.AnniversaryDate,
tblTraining.TrainingDate,
IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),DateSerial(Year([TrainingDate])+3,12,31)))
AS DueDate, CStr(IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy"),5
& DatePart("yyyy",[TrainingDate])+3))) AS Period
FROM (tblFrequency RIGHT JOIN tblTrainingType ON tblFrequency.FrequencyID
=
tblTrainingType.Frequency) RIGHT JOIN (tblStaff LEFT JOIN tblTraining ON
tblStaff.StaffID = tblTraining.StaffID) ON tblTrainingType.TrainingTypeID
=
tblTraining.TrainingType
WHERE (((IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),DateSerial(Year([TrainingDate])+3,12,31))))>=Date())
AND ((CStr(IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy"),5
& DatePart("yyyy",[TrainingDate])+3))))=fPeriod()) AND
((tblFrequency.FrequencyID)=2)) OR (((IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),DateSerial(Year([TrainingDate])+3,12,31))))>=Date())
AND ((CStr(IIf([TrainingDate] Is
Null,Null,IIf([FrequencyID]=2,Format(DateSerial(Year([TrainingDate])+1,Month([AnniversaryDate]),Day([AnniversaryDate])),"qyyyy"),5
& DatePart("yyyy",[TrainingDate])+3))))=fPeriod()) AND
((tblFrequency.FrequencyID)=3))
ORDER BY tblTraining.StaffID;

Any ideas why this query will run under one circumstance, but not
another?

Feel free to ask any questions or request more info, if need. Your help
is
greatly appreciated!

Thanks,

Robert
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

Query/Function/Parameter Troubles... 2
payroll weeks / varying first day and week of the year 2
SQL help 1
Update query stopped working 1
Peculiar Behavior of Query 2
Access shuts down, when i try to save this query 12
Problem with Nested IIF 2
Return all row headings in crosstab query 3

Top