[AccessD] Date issues in Access

The dates in Question are datDay1 and datDay2 ,
Some background here... I am a newly appointed Analyst programmer and am
trying to troubleshoot thsi application which was written in 1995 by
someone who is long gone from the organization.

The formula involved is to calculate the number of days between two values.
I get an error on the result of the number of longBusiness days,  as
follows:


Public Function DiffBusinessDays_TSB(datDay1 As Date, datDay2 As Date,
strHolidayTbl As String, strHolidayField As String) As Long
  ' Comments  : Returns the number of business days between two dates
  '             The days are rounded down -- it takes 24 hours to make a
day.
  '             Weekend dates (Saturday and Sunday) and holidays are not
counted.
  ' Parameters: datDay1 - first (earlier) date/time (subtracted from
datDay2)
  '             datDay2 - second (later) date/time
  '             strHolidayTbl - name of holiday table
  '             strHolidayField - field name of holiday dates in the
holiday table
  ' Returns   : Number of whole business days between two dates
  '             (Returns negative days if datDay1 is after datDay2)
  '

  Dim db As Database
  Dim rst As Recordset
  Dim strSQL As String
  Dim strField As String
  Dim lngWeekdays As Long
  Dim lngBusinessDays As Long


  ' Calculate number of weekdays between two dates:
  lngWeekdays = DiffWeekdays_TSB(datDay1, datDay2)

  datDay1 = Format$(datDay1, "dd/mm/yy")
  datDay2 = Format$(datDay2, "dd/mm/yy")

  Set db = CurrentDb()
    strField = "[" & strHolidayTbl & "].[" & strHolidayField & "]"
    strSQL = "SELECT DISTINCTROW Count(" & strField & ") AS Count"
    strSQL = strSQL & " FROM " & strHolidayTbl
    strSQL = strSQL & " WHERE ((" & strField & " "
    If datDay1 <= datDay2 Then
      strSQL = strSQL & ">=#" & datDay1 & "# And "
      strSQL = strSQL & strField & "<#" & datDay2 & "#));"
    Else
      strSQL = strSQL & ">=#" & datDay2 & "# And "
      strSQL = strSQL & strField & "<#" & datDay1 & "#));"
    End If
     msgbox "long Week Days is " & lngWeekdays
     msgbox "Start Date is " & datDay1 Returns correct value
     msgbox "End Date is " & datDay2 Returns correct value
    Set rst = db.OpenRecordset(strSQL)
      lngBusinessDays = rst![Count]
    rst.Close
  db.Close

 msgbox "long Business Days is " & lngBusinessDays Returns incorrect value


  DiffBusinessDays_TSB = lngWeekdays - lngBusinessDays

End Function


Connie Kamrowski

Analyst/Programmer
Information Technology
NSW Agriculture
Orange

Ph: 02 6391 3250
Fax:02 6391 3290



|---------+------------------------------------>
|         |           "Stuart McLachlan"       |
|         |           <stuart at lexacorp.com.pg> |
|         |           Sent by:                 |
|         |           accessd-bounces at databasea|
|         |           dvisors.com              |
|         |                                    |
|         |                                    |
|         |           19/09/03 01:31 PM        |
|         |           Please respond to Access |
|         |           Developers discussion and|
|         |           problem solving          |
|         |                                    |
|---------+------------------------------------>
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       Access Developers discussion and problem solving <accessd at databaseadvisors.com>              |
  |       cc:                                                                                                    |
  |       Subject:  Re: [AccessD] Date issues in Access                                                          |
  >--------------------------------------------------------------------------------------------------------------|




On 19 Sep 2003 at 13:22, connie.kamrowski at agric.nsw.go wrote:

>
> That works for display of the data but it is still returning incorrect
> results from the formula because the date is passed incorrectly if it is
> before the 12th of  the month
>
You only asked about displaying it in a message box :-)

If you show us how you are currently using it in  a formula, we can
probably help there too.


--
Stuart McLachlan
Lexacorp Ltd
Application Development,  IT Consultancy
http://www.lexacorp.com.pg

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





This message is intended for the addressee named and may contain
confidential information. If you are not the intended recipient or received
it in error, please delete the message and notify sender. Views expressed
are those of the individual sender and are not necessarily the views of
their organisation.