DlookUp Help

[wrightlife11]

I am trying to use a date for comparison in a do while loop. The Loop works
great! No problems there.... but I need to find out what is wrong with the
dlookup I am tring to use. It always returns null. causing the rest of the
function to bomb.

Any Ideas?
Option Compare Database
Option Explicit

Public Function WorkDaysAdj() As Variant
'Required Inputs:StartDate As Date, AdjDays As Integer
'....................................................................
On Error GoTo Err_WorkDaysAdj

Dim AdjDays As Integer
Dim StartDate As Variant
Dim NewDate As Variant
Dim HDay As Variant


StartDate = #9/1/2008#
AdjDays = 12
NewDate = StartDate

'I tried to do a DLookUp that looked like this but got an error "invalid use
of null"
HDay = DLookup("[Holiday]", "tblHolidays", "[Holiday]=" & NewDate)
Debug.Print HDay & " Holiday"

' Here I tried to keep the DLookUp from returnging a null.
HDay = IIf(DLookup("[Holiday]", "tblHolidays", "[Holiday]=" & NewDate) <
#1/1/2001#, #1/1/2001#, DLookup("[Holiday]", "tblHolidays", "[Holiday]=" &
NewDate))
Debug.Print HDay & " Holiday"
Exit_WorkDaysAdj:
Exit Function


Err_WorkDaysAdj:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkDaysAdj
End Select

End Function

 

[Michael Conroy]

Dates require the pound sign around it.
DLookup("[Holiday]", "tblHolidays", "[Holiday]= #" & NewDate &"#)"
Try that.

 

[Marshall Barton]

I am trying to use a date for comparison in a do while loop. The Loop works
great! No problems there.... but I need to find out what is wrong with the
dlookup I am tring to use. It always returns null. causing the rest of the
function to bomb.

Any Ideas?
Option Compare Database
Option Explicit

Public Function WorkDaysAdj() As Variant
'Required Inputs:StartDate As Date, AdjDays As Integer
'....................................................................
On Error GoTo Err_WorkDaysAdj

Dim AdjDays As Integer
Dim StartDate As Variant
Dim NewDate As Variant
Dim HDay As Variant


StartDate = #9/1/2008#
AdjDays = 12
NewDate = StartDate

'I tried to do a DLookUp that looked like this but got an error "invalid use
of null"
HDay = DLookup("[Holiday]", "tblHolidays", "[Holiday]=" & NewDate)
Debug.Print HDay & " Holiday"

If Holiday is a Date type field, then you should use
something like:

DLookup("[Holiday]", "tblHolidays", "[Holiday]=" &
Format(NewDate, "\#yyyy-m-d\#")

' Here I tried to keep the DLookUp from returnging a null.
HDay = IIf(DLookup("[Holiday]", "tblHolidays", "[Holiday]=" & NewDate) <
#1/1/2001#, #1/1/2001#, DLookup("[Holiday]", "tblHolidays", "[Holiday]=" &
NewDate))

"[Holiday]=" & NewDate) <> #1/1/2001#
doesn't make sense. I can't be sure what you were hoping it
would do, but maybe you want:

HDay = Nz(DLookup("[Holiday]", "tblHolidays", "[Holiday]=" &
Format(NewDate, "\#yyyy-m-d\#") , #1/1/2001#)

 

[wrightlife11]

Thank you for your quick reply.
Tried it like you have it and the output is "5.60258964143426E-04" Which is
neither a true output. Also it appears with similar numbers for every record
not just those where Date specified in the Holiday table = the "NewDate".


Any other ideas?

Matthew

Dates require the pound sign around it.
DLookup("[Holiday]", "tblHolidays", "[Holiday]= #" & NewDate &"#)"
Try that.
--
Michael Conroy
Stamford, CT

I am trying to use a date for comparison in a do while loop. The Loop works
great! No problems there.... but I need to find out what is wrong with the
dlookup I am tring to use. It always returns null. causing the rest of the
function to bomb.

Any Ideas?
Option Compare Database
Option Explicit

Public Function WorkDaysAdj() As Variant
'Required Inputs:StartDate As Date, AdjDays As Integer
'....................................................................
On Error GoTo Err_WorkDaysAdj

Dim AdjDays As Integer
Dim StartDate As Variant
Dim NewDate As Variant
Dim HDay As Variant


StartDate = #9/1/2008#
AdjDays = 12
NewDate = StartDate

'I tried to do a DLookUp that looked like this but got an error "invalid use
of null"
HDay = DLookup("[Holiday]", "tblHolidays", "[Holiday]=" & NewDate)
Debug.Print HDay & " Holiday"

' Here I tried to keep the DLookUp from returnging a null.
HDay = IIf(DLookup("[Holiday]", "tblHolidays", "[Holiday]=" & NewDate) <
#1/1/2001#, #1/1/2001#, DLookup("[Holiday]", "tblHolidays", "[Holiday]=" &
NewDate))
Debug.Print HDay & " Holiday"
Exit_WorkDaysAdj:
Exit Function


Err_WorkDaysAdj:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkDaysAdj
End Select

End Function

 

[david]

HDay = DLookup("[Holiday]", "tblHolidays", "[Holiday]=" & NewDate)

Possibly not a good example. If you know that Holiday = NewDate, why
look it up? Just write

HDay = NewDate

(david)

 

[Dale Fye]

What is it you are actually trying to do here?

When you cut out the rest of your code, you made it difficult to determine
what you are doing. Are you trying to find the first work day after StartDay
+ AdjDays, and want to make sure that holidays are excluded and weekends are
excluded, so that you only count workdays?

What does the rest of the code in the function look like?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[wrightlife11]

I would like to thank you all for your input. I have it working now and
think it is right. What do you think?

I am trying to count backwards from a given date to get a new date. I only
want to count valid working days. This is king of like networkdays in excel,
except it allows you to identify the parameter of the number of working days.
I need this to assist in scheduling backwards so we can meet customer
expectations.

Please check to code for any errors.

Public Function WorkDaysAdj(StartDate As Date, AdjDays As Integer) As Variant
'Required Inputs:StartDate As Date, AdjDays As Integer
'Required: Table called Holidays.
'Output: The Calculated date = to the number of working days(AdjDays) less
than the Start Date.
'Author: Matthew Wright dtd 9/12/2008
'....................................................................
On Error GoTo Err_WorkDaysAdj

'Declare your variables

Dim intCount As Integer 'Counter
Dim HDay As Variant 'Holidays

'Set your variables

intCount = 0


'Begin evaluation...... so the AdjDays is evaluated against the number of
valid iterations.
'Valid iterations are working days Monday thru Friday and excludes Holidays.
Do While intCount < AdjDays
'Use <= to include the start date and the last day. Use < to only
include the last day.

HDay = DLookup("[Holiday]", "tblHolidays", "[Holiday]= #" & StartDate &
"#") ' Is it a holiday?

If HDay = StartDate Then
intCount = intCount 'if it is a Holiday No Iteration.
Else 'otherwise Select Case.

Select Case Weekday(StartDate) 'checks the current date for day of
the week.

Case 1, 7 'Is the day of the week Sunday(1) or
Saturday(7).
intCount = intCount 'No iteration.

Case 2, 3, 4, 5, 6 'Is the day of the week Monday(2) thru
Friday(6).
intCount = intCount + 1 'Iterate.

End Select 'End check to see the day of the week.

End If 'End the Holiday check for this iteration.

StartDate = StartDate - 1 'Subtract 1 day.....To make it count
forward make this +1.

Loop 'Loop through the dates until the
evaluation is no longer valid.

WorkDaysAdj = StartDate + 1 'Identify the output. Since it subtracts
a date on the
'last iteration take the last on back.

Exit_WorkDaysAdj:
Exit Function

Err_WorkDaysAdj:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkDaysAdj
End Select

End Function

What is it you are actually trying to do here?

When you cut out the rest of your code, you made it difficult to determine
what you are doing. Are you trying to find the first work day after StartDay
+ AdjDays, and want to make sure that holidays are excluded and weekends are
excluded, so that you only count workdays?

What does the rest of the code in the function look like?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I am trying to use a date for comparison in a do while loop. The Loop works
great! No problems there.... but I need to find out what is wrong with the
dlookup I am tring to use. It always returns null. causing the rest of the
function to bomb.

Any Ideas?
Option Compare Database
Option Explicit

Public Function WorkDaysAdj() As Variant
'Required Inputs:StartDate As Date, AdjDays As Integer
'....................................................................
On Error GoTo Err_WorkDaysAdj

Dim AdjDays As Integer
Dim StartDate As Variant
Dim NewDate As Variant
Dim HDay As Variant


StartDate = #9/1/2008#
AdjDays = 12
NewDate = StartDate

'I tried to do a DLookUp that looked like this but got an error "invalid use
of null"
HDay = DLookup("[Holiday]", "tblHolidays", "[Holiday]=" & NewDate)
Debug.Print HDay & " Holiday"

' Here I tried to keep the DLookUp from returnging a null.
HDay = IIf(DLookup("[Holiday]", "tblHolidays", "[Holiday]=" & NewDate) <
#1/1/2001#, #1/1/2001#, DLookup("[Holiday]", "tblHolidays", "[Holiday]=" &
NewDate))
Debug.Print HDay & " Holiday"
Exit_WorkDaysAdj:
Exit Function


Err_WorkDaysAdj:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkDaysAdj
End Select

End Function