Null Date parameter

[Guest]

Hi everyone,

I am getting an error : expression too complex to be evaluted on a query
that has a date parameter. It occurs when the date field is left blank on my
form. It works when the date is set.

SELECT o.EventID, o.EventDate, o.Customer, o.[Event Title],
o.EVENTCONFIRMATION, o.EVENTSTATUS, o.TotalNum, o.Pos, c.IsConfirmed,
IIf(TotalNum>IsConfirmed,totalNum-IsConfirmed,0) AS Unconfirmed,
IIf(IsConfirmed>TotalNum,IsConfirmed-TotalNum,0) AS Over
FROM qryEventNumStaffOrdered AS o LEFT JOIN qrySumBookingConfirmed AS c ON
(o.Pos = c.Pos) AND (o.EventID = c.EventID)
WHERE (o.EventDate=[Forms]![EventFinder]![EventDate] or o.EventDate is null)

Any suggestions?

Thanks,
David

 

[Ken Snell [MVP]]

What do you want the query to do if the date control on your form is left
blank? Should it return all records? Or no records?

Your answer to the questions will let us provide suggestions.

 

[Guest]

sorry,
I also have a field called EventID

I want the user to have the choice to pass eventID or EventDate

so WHERE (o.EventDate=[Forms]![EventFinder]![EventDate] or
o.EventID=[Forms]![EventFinder]![EventID]

What do you want the query to do if the date control on your form is left
blank? Should it return all records? Or no records?

Your answer to the questions will let us provide suggestions.
--

Ken Snell
<MS ACCESS MVP>

Hi everyone,

I am getting an error : expression too complex to be evaluted on a query
that has a date parameter. It occurs when the date field is left blank on
my
form. It works when the date is set.

SELECT o.EventID, o.EventDate, o.Customer, o.[Event Title],
o.EVENTCONFIRMATION, o.EVENTSTATUS, o.TotalNum, o.Pos, c.IsConfirmed,
IIf(TotalNum>IsConfirmed,totalNum-IsConfirmed,0) AS Unconfirmed,
IIf(IsConfirmed>TotalNum,IsConfirmed-TotalNum,0) AS Over
FROM qryEventNumStaffOrdered AS o LEFT JOIN qrySumBookingConfirmed AS c ON
(o.Pos = c.Pos) AND (o.EventID = c.EventID)
WHERE (o.EventDate=[Forms]![EventFinder]![EventDate] or o.EventDate is
null)

Any suggestions?

Thanks,
David

 

[Ken Snell [MVP]]

You did not answer the question clearly. Are you saying that the user will
enter either an eventID value or an eventdate? So that you want the query to
handle the two situations?

If yes, then choose a value for "no entry made" to be used for each value,
and then the Nz function can be used. In this example, I'm using January 1,
1900 as the date if none is entered, and zero as the eventID if none is
entered.

WHERE (o.EventDate=Nz([Forms]![EventFinder]![EventDate], #1/1/1900#) or
o.EventID=Nz([Forms]![EventFinder]![EventID], 0)


--

Ken Snell
<MS ACCESS MVP>

sorry,
I also have a field called EventID

I want the user to have the choice to pass eventID or EventDate

so WHERE (o.EventDate=[Forms]![EventFinder]![EventDate] or
o.EventID=[Forms]![EventFinder]![EventID]

What do you want the query to do if the date control on your form is left
blank? Should it return all records? Or no records?

Your answer to the questions will let us provide suggestions.
--

Ken Snell
<MS ACCESS MVP>

Hi everyone,

I am getting an error : expression too complex to be evaluted on a
query
that has a date parameter. It occurs when the date field is left blank
on
my
form. It works when the date is set.

SELECT o.EventID, o.EventDate, o.Customer, o.[Event Title],
o.EVENTCONFIRMATION, o.EVENTSTATUS, o.TotalNum, o.Pos, c.IsConfirmed,
IIf(TotalNum>IsConfirmed,totalNum-IsConfirmed,0) AS Unconfirmed,
IIf(IsConfirmed>TotalNum,IsConfirmed-TotalNum,0) AS Over
FROM qryEventNumStaffOrdered AS o LEFT JOIN qrySumBookingConfirmed AS c
ON
(o.Pos = c.Pos) AND (o.EventID = c.EventID)
WHERE (o.EventDate=[Forms]![EventFinder]![EventDate] or o.EventDate is
null)

Any suggestions?

Thanks,
David

 

[peregenem]

choose a value for "no entry made" to be used for each value,
and then the Nz function can be used.

A missing value in SQL is represented by NULL. The usual trick to
handle a missing parameter value is to return the column in the
expression when the parameter value is NULL. In standard SQL we use
COALESCE, in the non-compliant Jet we use IIF (NZ is limited to the
Access UI)

MyTable.EventDate = IIF(paramEventDate IS NULL, MyTable.EventDate,
paramEventDate)
AND
MyTable.EventID = IIF(paramEventID IS NULL, MyTable.EventID,
paramEventID)

 

[Guest]

Hi Ken:

Thanks for your help so far.
I am sorry I did not answer the question properly, I did not think of those
factors.

Yes I want it to behave as you mentioned.

I did try your suggestion. It did work only if the EventDate was not null.
If it was null it threw the same error, expression too complex.

However, when I took out the [EventFinder]![EventDate] and just put a simple
parameter
Nz([EventDate],#1/1/1900# it worked great.

Any idea why this would occur. The form does pass the proper parameter when
it is not null, so I know its not the control itself...

Thanks for any insight.

Cheers,
DAvid

You did not answer the question clearly. Are you saying that the user will
enter either an eventID value or an eventdate? So that you want the query to
handle the two situations?

If yes, then choose a value for "no entry made" to be used for each value,
and then the Nz function can be used. In this example, I'm using January 1,
1900 as the date if none is entered, and zero as the eventID if none is
entered.

WHERE (o.EventDate=Nz([Forms]![EventFinder]![EventDate], #1/1/1900#) or
o.EventID=Nz([Forms]![EventFinder]![EventID], 0)


--

Ken Snell
<MS ACCESS MVP>

sorry,
I also have a field called EventID

I want the user to have the choice to pass eventID or EventDate

so WHERE (o.EventDate=[Forms]![EventFinder]![EventDate] or
o.EventID=[Forms]![EventFinder]![EventID]

What do you want the query to do if the date control on your form is left
blank? Should it return all records? Or no records?

Your answer to the questions will let us provide suggestions.
--

Ken Snell
<MS ACCESS MVP>



Hi everyone,

I am getting an error : expression too complex to be evaluted on a
query
that has a date parameter. It occurs when the date field is left blank
on
my
form. It works when the date is set.

SELECT o.EventID, o.EventDate, o.Customer, o.[Event Title],
o.EVENTCONFIRMATION, o.EVENTSTATUS, o.TotalNum, o.Pos, c.IsConfirmed,
IIf(TotalNum>IsConfirmed,totalNum-IsConfirmed,0) AS Unconfirmed,
IIf(IsConfirmed>TotalNum,IsConfirmed-TotalNum,0) AS Over
FROM qryEventNumStaffOrdered AS o LEFT JOIN qrySumBookingConfirmed AS c
ON
(o.Pos = c.Pos) AND (o.EventID = c.EventID)
WHERE (o.EventDate=[Forms]![EventFinder]![EventDate] or o.EventDate is
null)

Any suggestions?

Thanks,
David

 

[Ken Snell [MVP]]

peregenem's suggestion is probably the way to make this work for your
situation:

WHERE (o.EventDate=IIf([Forms]![EventFinder]![EventDate] Is Null,
#1/1/1900#, [Forms]![EventFinder]![EventDate]) or
o.EventID=IIf([Forms]![EventFinder]![EventID] Is Null, 0,
[Forms]![EventFinder]![EventID])

--

Ken Snell
<MS ACCESS MVP>

Hi Ken:

Thanks for your help so far.
I am sorry I did not answer the question properly, I did not think of
those
factors.

Yes I want it to behave as you mentioned.

I did try your suggestion. It did work only if the EventDate was not
null.
If it was null it threw the same error, expression too complex.

However, when I took out the [EventFinder]![EventDate] and just put a
simple
parameter
Nz([EventDate],#1/1/1900# it worked great.

Any idea why this would occur. The form does pass the proper parameter
when
it is not null, so I know its not the control itself...

Thanks for any insight.

Cheers,
DAvid

You did not answer the question clearly. Are you saying that the user
will
enter either an eventID value or an eventdate? So that you want the query
to
handle the two situations?

If yes, then choose a value for "no entry made" to be used for each
value,
and then the Nz function can be used. In this example, I'm using January
1,
1900 as the date if none is entered, and zero as the eventID if none is
entered.

WHERE (o.EventDate=Nz([Forms]![EventFinder]![EventDate], #1/1/1900#) or
o.EventID=Nz([Forms]![EventFinder]![EventID], 0)


--

Ken Snell
<MS ACCESS MVP>

sorry,
I also have a field called EventID

I want the user to have the choice to pass eventID or EventDate

so WHERE (o.EventDate=[Forms]![EventFinder]![EventDate] or
o.EventID=[Forms]![EventFinder]![EventID]

:

What do you want the query to do if the date control on your form is
left
blank? Should it return all records? Or no records?

Your answer to the questions will let us provide suggestions.
--

Ken Snell
<MS ACCESS MVP>



Hi everyone,

I am getting an error : expression too complex to be evaluted on a
query
that has a date parameter. It occurs when the date field is left
blank
on
my
form. It works when the date is set.

SELECT o.EventID, o.EventDate, o.Customer, o.[Event Title],
o.EVENTCONFIRMATION, o.EVENTSTATUS, o.TotalNum, o.Pos,
c.IsConfirmed,
IIf(TotalNum>IsConfirmed,totalNum-IsConfirmed,0) AS Unconfirmed,
IIf(IsConfirmed>TotalNum,IsConfirmed-TotalNum,0) AS Over
FROM qryEventNumStaffOrdered AS o LEFT JOIN qrySumBookingConfirmed
AS c
ON
(o.Pos = c.Pos) AND (o.EventID = c.EventID)
WHERE (o.EventDate=[Forms]![EventFinder]![EventDate] or o.EventDate
is
null)

Any suggestions?

Thanks,
David

 

[Guest]

oooohhh, I hate nulls especially in vb

You know what it was- it didnt like the null fields
so i tried

WHERE (((o.EventDate)=IIf(Len([Forms]![EventFinder]![EventDate] &
"")=0,#1/1/1900#,[Forms]![EventFinder]![EventDate]))) OR
(((o.EventID)=IIf(Len([Forms]![EventFinder]![EventID] &
"")=0,0,[Forms]![EventFinder]![EventID])));


it worked beautifully.

i have noticed this issue in vba programming if checking for field is null,
its always the safest to use Len(field & "") = 0 or > 0

Yay, problem solved.

thanks evevyone

peregenem's suggestion is probably the way to make this work for your
situation:

WHERE (o.EventDate=IIf([Forms]![EventFinder]![EventDate] Is Null,
#1/1/1900#, [Forms]![EventFinder]![EventDate]) or
o.EventID=IIf([Forms]![EventFinder]![EventID] Is Null, 0,
[Forms]![EventFinder]![EventID])

--

Ken Snell
<MS ACCESS MVP>

Hi Ken:

Thanks for your help so far.
I am sorry I did not answer the question properly, I did not think of
those
factors.

Yes I want it to behave as you mentioned.

I did try your suggestion. It did work only if the EventDate was not
null.
If it was null it threw the same error, expression too complex.

However, when I took out the [EventFinder]![EventDate] and just put a
simple
parameter
Nz([EventDate],#1/1/1900# it worked great.

Any idea why this would occur. The form does pass the proper parameter
when
it is not null, so I know its not the control itself...

Thanks for any insight.

Cheers,
DAvid

You did not answer the question clearly. Are you saying that the user
will
enter either an eventID value or an eventdate? So that you want the query
to
handle the two situations?

If yes, then choose a value for "no entry made" to be used for each
value,
and then the Nz function can be used. In this example, I'm using January
1,
1900 as the date if none is entered, and zero as the eventID if none is
entered.

WHERE (o.EventDate=Nz([Forms]![EventFinder]![EventDate], #1/1/1900#) or
o.EventID=Nz([Forms]![EventFinder]![EventID], 0)


--

Ken Snell
<MS ACCESS MVP>


sorry,
I also have a field called EventID

I want the user to have the choice to pass eventID or EventDate

so WHERE (o.EventDate=[Forms]![EventFinder]![EventDate] or
o.EventID=[Forms]![EventFinder]![EventID]

:

What do you want the query to do if the date control on your form is
left
blank? Should it return all records? Or no records?

Your answer to the questions will let us provide suggestions.
--

Ken Snell
<MS ACCESS MVP>



Hi everyone,

I am getting an error : expression too complex to be evaluted on a
query
that has a date parameter. It occurs when the date field is left
blank
on
my
form. It works when the date is set.

SELECT o.EventID, o.EventDate, o.Customer, o.[Event Title],
o.EVENTCONFIRMATION, o.EVENTSTATUS, o.TotalNum, o.Pos,
c.IsConfirmed,
IIf(TotalNum>IsConfirmed,totalNum-IsConfirmed,0) AS Unconfirmed,
IIf(IsConfirmed>TotalNum,IsConfirmed-TotalNum,0) AS Over
FROM qryEventNumStaffOrdered AS o LEFT JOIN qrySumBookingConfirmed
AS c
ON
(o.Pos = c.Pos) AND (o.EventID = c.EventID)
WHERE (o.EventDate=[Forms]![EventFinder]![EventDate] or o.EventDate
is
null)

Any suggestions?

Thanks,
David

 

[peregenem]

oooohhh, I hate nulls

Dates are an excellent example of why we need them in SQL. Using
#1900-01-01# to signify a missing value is flawed because you have hard
coded an assumption (not even a business rule). Another issue is
#1900-01-01# is a valid date value e.g. you can see how this would not
work for date of birth. And nulls are inevitable e.g. when performing
an OUTER JOIN.

I hate nulls especially in vb

Use the Variant data type, which may be set to (and tested for) a null
value. I see a lot of code where a nullable INTEGER column in the
database is represented by a Long or Integer in VBA i.e. cannot
differentiate between zero and null.