Aagh! Why doesn't this code work?

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

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
 
Try replacing

dtmTemp = dtmTemp + intIncrement

With

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


Ron W
news.microsoft.com said:
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 said:
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",
Click to expand...
"[HolidayDays]=#"
&
dtmTemp & "#")False)

Ron W

Newbie said:
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
Click to expand...
for
the
2nd time the error 94 Invalid Use of Null is returned.

Where am I going wrong?
All help greatly appreciated
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
Try replacing

dtmTemp = dtmTemp + intIncrement

With

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


Ron W
news.microsoft.com said:
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 said:
When DLookup doesn't find the date in tblHolidayDates it returns a
Click to expand...
null.
If
blnFound is in fact a Boolean that is where the error is occurring.
Click to expand...
You
can
not Assign a Null to a Boolean variable. Try wrapping your DLookup in a
NZ() like this

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

Ron W

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
Click to expand...
Click to expand...
send
a 1
to
Click to expand...
Click to expand...
 
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
Click to expand...
("HolidayDays", "tblHolidayDates", "[HolidayDays]=" &
Format$(dtmTemp, jetdatefmt))), False)

Thanks for your help so far
Try replacing

dtmTemp = dtmTemp + intIncrement

With

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


Ron W
Click to expand...
truely is no record
but tblHolidayDates it returns a
null. error is occurring.
You wrapping your DLookup in
a
NZ() like this

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

Ron W

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
Click to expand...
Click to expand...
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
Click to expand...
Click to expand...
to 4th June. If I
send
a
Click to expand...
dtmTemp is incremented by
1
to evaluate blnFound
for
Click to expand...


.
Click to expand...
 
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.
Click to expand...

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
 
When I hover over the variable a valid date appears.
Click to expand...

But is it the right date?
Here is the code snippet

Do
blnFound = DLookup( _
"HolidayDays", _
"tblHolidayDates", _
"[HolidayDays]=#" & dtmTemp & "#"))
' This is where error occurs
Click to expand...

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
Click to expand...
("HolidayDays", "tblHolidayDates", "[HolidayDays]=" &
 
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 said:
When I hover over the variable a valid date appears.
Click to expand...

But is it the right date?
Here is the code snippet

Do
blnFound = DLookup( _
"HolidayDays", _
"tblHolidayDates", _
"[HolidayDays]=#" & dtmTemp & "#"))
' This is where error occurs
Click to expand...

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
Click to expand...
 
No. Literal dates are different in SQLServer SQL syntax.

You need to check the SQLServer Books-On-Line for proper syntax in
SQLServer.
 
Do I take it that from now on I should always format the dates using the
format
Format(dtmTemp, "\#mm\/dd\/yyyy\#")?
Click to expand...

Well, I have a personal distaste for this USian hegemony, and prefer the
ISO version, but, yes you should.
Does the same apply if I am sending date parameters via ADO to stored
procedures in SQL Server?
Click to expand...

As Van says, there is quite a lot of information on writing international
applications and date formatting in particular in the SQLBOL.CHM help file
-- look up "Dates, entering".

Hope that helps


Tim F
 
Back
Top