I am trying to find the next available date that is not a holiday.
The holiday dates are in a table.

The code works for the first pass through but once the date has been found
and 1 has been added to give the next date to evaluate I get the error
invalid use of Null.
When I hover over the variable a valid date appears.

Here is the code snippet

Do
blnFound = (DLookup("HolidayDays", "tblHolidayDates",
"[HolidayDays]=#" & dtmTemp & "#")) ' this is where error occurs
If blnFound Then
dtmTemp = dtmTemp + intIncrement
End If
Loop Until Not blnFound

In my table I have all the dates from 1st June to 4th June. If I send a
date of 1st June blnFound returns true and dtmTemp is incremented by 1 to
give dtmTemp a value of 2nd June. When trying to evaluate blnFound for the
2nd time the error 94 Invalid Use of Null is returned.

Where am I going wrong?
All help greatly appreciated

When DLookup doesn't find the date in tblHolidayDates it returns a null. If
blnFound is in fact a Boolean that is where the error is occurring. You can
not Assign a Null to a Boolean variable. Try wrapping your DLookup in a
NZ() like this

blnFound = NZ(DLookup("HolidayDays", "tblHolidayDates", "[HolidayDays]=#" &
dtmTemp & "#")False)

Ron W

"Newbie" <(E-Mail Removed)> wrote in message
news:u3$(E-Mail Removed)...
> I am trying to find the next available date that is not a holiday.
> The holiday dates are in a table.
>
> The code works for the first pass through but once the date has been found
> and 1 has been added to give the next date to evaluate I get the error
> invalid use of Null.
> When I hover over the variable a valid date appears.
>
> Here is the code snippet
>
> Do
> blnFound = (DLookup("HolidayDays", "tblHolidayDates",
> "[HolidayDays]=#" & dtmTemp & "#")) ' this is where error occurs
> If blnFound Then
> dtmTemp = dtmTemp + intIncrement
> End If
> Loop Until Not blnFound
>
> In my table I have all the dates from 1st June to 4th June. If I send a
> date of 1st June blnFound returns true and dtmTemp is incremented by 1 to
> give dtmTemp a value of 2nd June. When trying to evaluate blnFound for
the
> 2nd time the error 94 Invalid Use of Null is returned.
>
> Where am I going wrong?
> All help greatly appreciated
>
>

Try replacing

dtmTemp = dtmTemp + intIncrement

With

dtmTemp = dateadd("d", intIncrement, dtmTemp)


Ron W
"news.microsoft.com" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks for this - it will fix the error when there truely is no record but
> it is returning null when the date is in the table.
> For some reason it seems that when adding 1 to the dtmTemp date it is
> changing the way that it is seeing the date.
>
> How can I get round this?
>
> Thanks again for your help
> "Ron Weiner" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > When DLookup doesn't find the date in tblHolidayDates it returns a null.
> If
> > blnFound is in fact a Boolean that is where the error is occurring. You
> can
> > not Assign a Null to a Boolean variable. Try wrapping your DLookup in a
> > NZ() like this
> >
> > blnFound = NZ(DLookup("HolidayDays", "tblHolidayDates",
"[HolidayDays]=#"
> &
> > dtmTemp & "#")False)
> >
> > Ron W
> >
> > "Newbie" <(E-Mail Removed)> wrote in message
> > news:u3$(E-Mail Removed)...
> > > I am trying to find the next available date that is not a holiday.
> > > The holiday dates are in a table.
> > >
> > > The code works for the first pass through but once the date has been
> found
> > > and 1 has been added to give the next date to evaluate I get the error
> > > invalid use of Null.
> > > When I hover over the variable a valid date appears.
> > >
> > > Here is the code snippet
> > >
> > > Do
> > > blnFound = (DLookup("HolidayDays", "tblHolidayDates",
> > > "[HolidayDays]=#" & dtmTemp & "#")) ' this is where error occurs
> > > If blnFound Then
> > > dtmTemp = dtmTemp + intIncrement
> > > End If
> > > Loop Until Not blnFound
> > >
> > > In my table I have all the dates from 1st June to 4th June. If I send
a
> > > date of 1st June blnFound returns true and dtmTemp is incremented by 1
> to
> > > give dtmTemp a value of 2nd June. When trying to evaluate blnFound
for
> > the
> > > 2nd time the error 94 Invalid Use of Null is returned.
> > >
> > > Where am I going wrong?
> > > All help greatly appreciated
> > >
> > >
> >
> >
>
>

Thanks for this - it will fix the error when there truely is no record but
it is returning null when the date is in the table.
For some reason it seems that when adding 1 to the dtmTemp date it is
changing the way that it is seeing the date.

How can I get round this?

Thanks again for your help
"Ron Weiner" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> When DLookup doesn't find the date in tblHolidayDates it returns a null.
If
> blnFound is in fact a Boolean that is where the error is occurring. You
can
> not Assign a Null to a Boolean variable. Try wrapping your DLookup in a
> NZ() like this
>
> blnFound = NZ(DLookup("HolidayDays", "tblHolidayDates", "[HolidayDays]=#"
&
> dtmTemp & "#")False)
>
> Ron W
>
> "Newbie" <(E-Mail Removed)> wrote in message
> news:u3$(E-Mail Removed)...
> > I am trying to find the next available date that is not a holiday.
> > The holiday dates are in a table.
> >
> > The code works for the first pass through but once the date has been
found
> > and 1 has been added to give the next date to evaluate I get the error
> > invalid use of Null.
> > When I hover over the variable a valid date appears.
> >
> > Here is the code snippet
> >
> > Do
> > blnFound = (DLookup("HolidayDays", "tblHolidayDates",
> > "[HolidayDays]=#" & dtmTemp & "#")) ' this is where error occurs
> > If blnFound Then
> > dtmTemp = dtmTemp + intIncrement
> > End If
> > Loop Until Not blnFound
> >
> > In my table I have all the dates from 1st June to 4th June. If I send a
> > date of 1st June blnFound returns true and dtmTemp is incremented by 1
to
> > give dtmTemp a value of 2nd June. When trying to evaluate blnFound for
> the
> > 2nd time the error 94 Invalid Use of Null is returned.
> >
> > Where am I going wrong?
> > All help greatly appreciated
> >
> >
>
>

Thanks but that doesn't work either.
I have found that the following does work but I don't know why
could you explain?

Dim jetdatefmt As String
jetdatefmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l"

blnFound = Nz((DLookup("HolidayDays", "tblHolidayDates", "[HolidayDays]=" &
Format$(dtmTemp, jetdatefmt))), False)

Thanks for your help so far
"Ron Weiner" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Try replacing
>
> dtmTemp = dtmTemp + intIncrement
>
> With
>
> dtmTemp = dateadd("d", intIncrement, dtmTemp)
>
>
> Ron W
> "news.microsoft.com" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Thanks for this - it will fix the error when there truely is no record
but
> > it is returning null when the date is in the table.
> > For some reason it seems that when adding 1 to the dtmTemp date it is
> > changing the way that it is seeing the date.
> >
> > How can I get round this?
> >
> > Thanks again for your help
> > "Ron Weiner" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > When DLookup doesn't find the date in tblHolidayDates it returns a
null.
> > If
> > > blnFound is in fact a Boolean that is where the error is occurring.
You
> > can
> > > not Assign a Null to a Boolean variable. Try wrapping your DLookup in
a
> > > NZ() like this
> > >
> > > blnFound = NZ(DLookup("HolidayDays", "tblHolidayDates",
> "[HolidayDays]=#"
> > &
> > > dtmTemp & "#")False)
> > >
> > > Ron W
> > >
> > > "Newbie" <(E-Mail Removed)> wrote in message
> > > news:u3$(E-Mail Removed)...
> > > > I am trying to find the next available date that is not a holiday.
> > > > The holiday dates are in a table.
> > > >
> > > > The code works for the first pass through but once the date has been
> > found
> > > > and 1 has been added to give the next date to evaluate I get the
error
> > > > invalid use of Null.
> > > > When I hover over the variable a valid date appears.
> > > >
> > > > Here is the code snippet
> > > >
> > > > Do
> > > > blnFound = (DLookup("HolidayDays", "tblHolidayDates",
> > > > "[HolidayDays]=#" & dtmTemp & "#")) ' this is where error occurs
> > > > If blnFound Then
> > > > dtmTemp = dtmTemp + intIncrement
> > > > End If
> > > > Loop Until Not blnFound
> > > >
> > > > In my table I have all the dates from 1st June to 4th June. If I
send
> a
> > > > date of 1st June blnFound returns true and dtmTemp is incremented by
1
> > to
> > > > give dtmTemp a value of 2nd June. When trying to evaluate blnFound
> for
> > > the
> > > > 2nd time the error 94 Invalid Use of Null is returned.
> > > >
> > > > Where am I going wrong?
> > > > All help greatly appreciated
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Following is a copy of my post to this thread in the
queries group:

Hi guys,

Hope you don't mind if I join in. In looking at the past
posts I was wondering if the problem may lie with the #
sign date delimiters in the Dlookup function. These are
needed if the criteria is being specified as a date
literal string, but I don't believe it is needed if the
date is being specified as a number representing the date
value.

I actually can't remember off hand how the VBA date data
type provides the values (as a string literal or as the
date value), but I would have expected it to represent
the value. If so, this could be the problem.

Not sure if this will help, but thought I would offer it.

-Ted Allen
>-----Original Message-----
>Thanks but that doesn't work either.
>I have found that the following does work but I don't
know why
>could you explain?
>
>Dim jetdatefmt As String
> jetdatefmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l"
>
>blnFound = Nz((DLookup
("HolidayDays", "tblHolidayDates", "[HolidayDays]=" &
>Format$(dtmTemp, jetdatefmt))), False)
>
>Thanks for your help so far
>"Ron Weiner" <(E-Mail Removed)> wrote in
message
>news:(E-Mail Removed)...
>> Try replacing
>>
>> dtmTemp = dtmTemp + intIncrement
>>
>> With
>>
>> dtmTemp = dateadd("d", intIncrement, dtmTemp)
>>
>>
>> Ron W
>> "news.microsoft.com" <(E-Mail Removed)> wrote in
message
>> news:(E-Mail Removed)...
>> > Thanks for this - it will fix the error when there
truely is no record
>but
>> > it is returning null when the date is in the table.
>> > For some reason it seems that when adding 1 to the
dtmTemp date it is
>> > changing the way that it is seeing the date.
>> >
>> > How can I get round this?
>> >
>> > Thanks again for your help
>> > "Ron Weiner" <(E-Mail Removed)> wrote in
message
>> > news:(E-Mail Removed)...
>> > > When DLookup doesn't find the date in
tblHolidayDates it returns a
>null.
>> > If
>> > > blnFound is in fact a Boolean that is where the
error is occurring.
>You
>> > can
>> > > not Assign a Null to a Boolean variable. Try
wrapping your DLookup in
>a
>> > > NZ() like this
>> > >
>> > > blnFound = NZ(DLookup
("HolidayDays", "tblHolidayDates",
>> "[HolidayDays]=#"
>> > &
>> > > dtmTemp & "#")False)
>> > >
>> > > Ron W
>> > >
>> > > "Newbie" <(E-Mail Removed)> wrote in message
>> > > news:u3$(E-Mail Removed)...
>> > > > I am trying to find the next available date that
is not a holiday.
>> > > > The holiday dates are in a table.
>> > > >
>> > > > The code works for the first pass through but
once the date has been
>> > found
>> > > > and 1 has been added to give the next date to
evaluate I get the
>error
>> > > > invalid use of Null.
>> > > > When I hover over the variable a valid date
appears.
>> > > >
>> > > > Here is the code snippet
>> > > >
>> > > > Do
>> > > > blnFound = (DLookup
("HolidayDays", "tblHolidayDates",
>> > > > "[HolidayDays]=#" & dtmTemp & "#")) ' this is
where error occurs
>> > > > If blnFound Then
>> > > > dtmTemp = dtmTemp + intIncrement
>> > > > End If
>> > > > Loop Until Not blnFound
>> > > >
>> > > > In my table I have all the dates from 1st June
to 4th June. If I
>send
>> a
>> > > > date of 1st June blnFound returns true and
dtmTemp is incremented by
>1
>> > to
>> > > > give dtmTemp a value of 2nd June. When trying
to evaluate blnFound
>> for
>> > > the
>> > > > 2nd time the error 94 Invalid Use of Null is
returned.
>> > > >
>> > > > Where am I going wrong?
>> > > > All help greatly appreciated
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>>
>
>
>.
>

"Ted Allen" <(E-Mail Removed)> wrote in
news:196e601c44d68$4a20af10$(E-Mail Removed):

> I was wondering if the problem may lie with the #
> sign date delimiters in the Dlookup function. These are
> needed if the criteria is being specified as a date
> literal string, but I don't believe it is needed if the
> date is being specified as a number representing the date
> value.

Well, it's an either-or isn't it? This may help to explain:-

Dim dtSomeDate As Date
Dim strWhere As String
Dim dwResult As Long

dtSomeDate = "1999-10-20"
strWhere = "Onset = " & Format(dtSomeDate, "\#yyyy\-mm\-dd\#")
MsgBox strWhere
dwResult = DLookup("IndexID", "Temp", strWhere)

strWhere = "Onset = " & Format(dtSomeDate, "0")
MsgBox strWhere
dwResult = DLookup("IndexID", "Temp", strWhere)


There is a bad bug in the VB Editor, however, when a date literal typed in
like

dtSomeDate = #20/09/2000#

gets reformatted to

dtSomeDate = #9/20/2000#

which could lead to some very nasty bugs. I have no idea where this
particular date format comes from -- is it legal anywhere in the world? --
and certainly does not help in a d/m/y country! It is possible that the
original poster is actually passing the wrong date value.

B Wishes


Tim F

"Newbie" <(E-Mail Removed)> wrote in
news:u3$(E-Mail Removed):

> When I hover over the variable a valid date appears.

But is it the right date?

> Here is the code snippet
>
> Do
> blnFound = DLookup( _
> "HolidayDays", _
> "tblHolidayDates", _
> "[HolidayDays]=#" & dtmTemp & "#"))
> ' This is where error occurs
>

Letting VBA parse your dates into SQL strings is asking for trouble --
there are some dreadful gotchas and, IMV, some bugs too. You simply have to
take control and format the date explicitly. These are legal:

Format(dtmTemp, "\#mm\/dd\/yyyy\#") ' USian format
Format(dtmTemp, "\#yyyy\-mm\-dd\#") ' ISO 8601

Note: the latter has the advantage of being an internationally agreed
format, and is also unambiguous on first sight. The backslashes are
neccessary because some national settings change the use of / and - as date
separators.

Finally, see my post downthread if you are entering dates into your code.

All the best


Tim F

Try something like (assuming blnFound is a Boolean
variable)

****Untested****
Do
blnFound = DCount("*", "tblHolidayDates", _
"[HolidayDays]= " & _
Format(dtmTemp & "\#mm/dd/yyyy\#") ) > 0
If blnFound Then
dtmTemp = DateAdd( "d", intIncrement, dtmTemp )
End If
Loop Until blnFound = False
****

JET (the default database engine in Access) expect literal
date values in the format mm/dd/yyyy enclosed in hashes
(#). Hence the Format() function I used as above.

If you don't use this format, the date value will be
converted to string according to your regional settings
which may be incorrect (e.g. mine is dd/mm/yyyy). Worse
still, there are cases that Access may not recognise what
being passed as date. After all, "1/6/2004" can be
interpreted as 1 divided by 6 (then) divided by 2004!

BTW, your jetdatefmt is wrong. It should have a max of 4
sections (separated by semi-colons), not 5 sections (as
per AXP Help.

HTH
Van T. Dinh
MVP (Access)




>-----Original Message-----
>Thanks but that doesn't work either.
>I have found that the following does work but I don't
know why
>could you explain?
>
>Dim jetdatefmt As String
> jetdatefmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l"
>
>blnFound = Nz((DLookup
("HolidayDays", "tblHolidayDates", "[HolidayDays]=" &
>Format$(dtmTemp, jetdatefmt))), False)
>
>Thanks for your help so far

Thanks for all the help.
The function is working now.
Do I take it that from now on I should always format the dates using the
format
Format(dtmTemp, "\#mm\/dd\/yyyy\#")?
Does the same apply if I am sending date parameters via ADO to stored
procedures in SQL Server?

Thanks again for the help
"Tim Ferguson" <(E-Mail Removed)> wrote in message
news:Xns9502B5D0A4001garbleme4455656@207.46.248.16...
> "Newbie" <(E-Mail Removed)> wrote in
> news:u3$(E-Mail Removed):
>
> > When I hover over the variable a valid date appears.
>
> But is it the right date?
>
> > Here is the code snippet
> >
> > Do
> > blnFound = DLookup( _
> > "HolidayDays", _
> > "tblHolidayDates", _
> > "[HolidayDays]=#" & dtmTemp & "#"))
> > ' This is where error occurs
> >
>
> Letting VBA parse your dates into SQL strings is asking for trouble --
> there are some dreadful gotchas and, IMV, some bugs too. You simply have
to
> take control and format the date explicitly. These are legal:
>
> Format(dtmTemp, "\#mm\/dd\/yyyy\#") ' USian format
> Format(dtmTemp, "\#yyyy\-mm\-dd\#") ' ISO 8601
>
> Note: the latter has the advantage of being an internationally agreed
> format, and is also unambiguous on first sight. The backslashes are
> neccessary because some national settings change the use of / and - as
date
> separators.
>
> Finally, see my post downthread if you are entering dates into your code.
>
> All the best
>
>
> Tim F
>