DLookup function in form yields inaccurate results 1

[THWatson]

I have a table, tblThursdaysInFiscalYears.
The table has 3 fields - StartDate, EndDate and NbrThursdays.

The StartDate is always 10/1/Year and the EndDate is always 9/30/following Year. A fiscal year.

On a form called frmDateSelector there are 2 text box controls, txtStartDate and txtEndDate.

I have placed an additional text box control, in which I want to use a DLookup function to look at the table and find the number of Thursdays in the fiscal year. I have tried

=DLookUp("[NbrThursdays]","[tblThursdaysInFiscalYears]","[StartDate]=Forms!frmDateSelector!txtStartDate And [EndDate] =Forms!frmDateSelector!txtEndDate")

and

=DLookUp("[NbrThursdays]","[tblThursdaysInFiscalYears]","Forms!frmDateSelector!txtStartDate Between [StartDate] and [EndDate] And [Forms]![frmDateSelector]![txtEndDate] Between [StartDate] and [EndDate]")

I have also tried using the Between operator.

Should it not work?

Tom

 

[MajP]

This may be helpful. I think it works correctly for any day, you might want to verify the results

Public Function getDaysInFY(lngYear As Integer, strDay As String) As Integer
  Dim startDay As Date
  Dim endDay As Date
  Dim intWeekDay As Integer
  Dim tempDay As Date
  startDay = DateSerial(lngYear - 1, 10, 1)
  endDay = DateSerial(lngYear, 9, 30)
  Select Case strDay
  Case "Sunday", "Sun"
   intWeekDay = 1
  Case "Monday", "Mon"
   intWeekDay = 2
  Case "Tuesday", "Tues"
   intWeekDay = 3
  Case "Wednesday", "Wed"
   intWeekDay = 4
  Case "Thursday", "Thurs"
   intWeekDay = 5
  Case "Friday", "Fri"
   intWeekDay = 6
  Case "Saturday", "Sat"
   intWeekDay = 7
  End Select
  tempDay = startDay
  If Weekday(startDay) = intWeekDay Then getDaysInFY = 1
  Do
    tempDay = tempDay + 1
    If Weekday(tempDay) = intWeekDay Then getDaysInFY = getDaysInFY + 1
  Loop Until tempDay = endDay
End Function

?getDaysInFY(2009,"Thurs")
52
?getDaysInFY(2008,"Thurs")
52
?getDaysInFY(2009,"Wed")
53
?getDaysInFY(2010,"Thurs")
53

 

[THWatson]

MajP
I wonder if I steered you in a wrong direction. Or not have been sufficiently descriptive of the situation.

I only want the # of Thursdays in the year...excluding Thursdays when Christmas or New Years fall on that day. For example, using this function, there are 50 Thursdays between 10/1/08 and 9/30/09, and 52 between 10/1/07 and 9/30/08.

I have a function that does the job as a control on a form.

Function RetThur(dteStart As Date, dteEnd As Date)
Dim i As Integer
Dim intT As Integer
Dim dteTest As Date
Dim ny As Date
Dim xm As Date

   On Error GoTo RetThur_Error

    If dteStart >= dteEnd Then
        'Problem
        RetThur = "Null"
    End If
    
    For i = 0 To (dteEnd - dteStart)
        dteTest = dteStart + i
        ny = DateSerial(Year(dteTest), 1, 1)
        xm = DateSerial(Year(dteTest), 12, 25)
        
        If Weekday(dteTest) = 5 And dteTest <> ny And dteTest <> xm Then
            intT = intT + 1
        End If
    Next
    
    RetThur = intT

   On Error GoTo 0
   Exit Function

RetThur_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RetThur of Module modGetThursdays"
    
End Function

The table is based on an Excel sheet which uses a formula to get the proper # of Thursdays. You plug in the Year in A1. B1 has 10/1/Year. C1 has 9/30/Year+1. D1 has the following construction

=52+OR(WEEKDAY(DATE(A75,10,1))=5,WEEKDAY(DATE(A75+1,9,30))=5)-2*(WEEKDAY(DATE(A75,12,25))=5)

I can get the Thursdays in a selected fiscal year by using a text box control which has the following

=RetThur([txtStartDate],[txtEndDate])

However, I wanted to use the DLookup as it uses stored data, rather than basing the form on code.

Tom

 

[pjm]

The criteria part of your dlookup is wrong. You need to replace the string you have put in:

"[StartDate]=Forms!frmDateSelector!txtStartDate And [EndDate] =Forms!frmDateSelector!txtEndDate"

with the values you actually want:

"[StartDate]=" & format (forms!frmDateSelector!txtStartDate, "\#mm/dd/yyyy\#") & " And [EndDate]=" _
& format (forms!frmDateSelector!txtEndDate, "\#mm/dd/yyyy\#")

HTH
pjm

 

[THWatson]

pjm
Thanks. With a slight bit of tweaking...

"[StartDate]=" & Format([Forms]![frmDateSelector]![txtStartDate],"\#mm/dd/yyyy#") & " And [EndDate]=" & Format([Forms]![frmDateSelector]![txtEndDate],"\#mm/dd/yyyy#"))

Tom