connie.kamrowski at agric.nsw.gov.au
connie.kamrowski at agric.nsw.gov.au
Sun Sep 21 20:46:01 CDT 2003
This worked a treat with some minor tweaking.
Thanks Gustav!
Connie Kamrowski
Analyst/Programmer
Information Technology
NSW Agriculture
Orange
|---------+------------------------------------>
| | Gustav Brock |
| | <gustav at cactus.dk> |
| | Sent by: |
| | accessd-bounces at databasea|
| | dvisors.com |
| | |
| | |
| | 20/09/03 12:52 AM |
| | 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 |
>--------------------------------------------------------------------------------------------------------------|
Hi Connie
Newbie or not, you've hit a sensitive topic of this list:
Internationalization (look up the archive) ...
Your basic problem is that this crappy function is (1) clumsy, (2) for
US developers only. If you google on "DiffBusinessDays_TSB" you'll see
some even more crappy variations on this at Experts-Exchange where it
has been ghosting for a couple of years.
First, as Stuart already has pointed out, dates in SQL code must be
formatted as US style strings; no exceptions.
Second, DateDiff() should be used for comparing date values. However,
in SQL it is much easier to use Between .. And as this is symmetric
(it doesn't matter wether the newest date is first or last) if you
take care of that it will include both the start and end date.
Thus, first, you'll have to modify your code to use US formatted date
strings ("The Lembit method" as our fellow lister Lembit was the first
to point this issue out and how to solve it). This function will help
if you do not want to mess up your SQL string with the formatting
expressions:
<code>
Public Function StrDateSQL(ByVal dat As Date) As String
' Formats full string of date/time in US format for SQL.
' Overrides local (non US) settings for date/time separators.
' Example output:
'
' #08/16/1998 04:03:36 PM#
'
' 1999-10-21. Cactus Data ApS, CPH.
StrDateSQL = Format(dat, "\#mm\/dd\/yyyy hh\:nn\:ss AM/PM\#")
End Function
</code
For your purpose you could modify strDateSQL to
StrDateSQL = Format(dat, "\#mm\/dd\/yyyy\#")
Note that it includes the hash marks which must wrap date strings in
SQL.
Now your code can be modified as this (don't know about the
DiffWeekdays_TSB() function):
<code snip>
' Calculate number of weekdays between two dates:
lngWeekdays = DiffWeekdays_TSB(datDay1, datDay2)
Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim strDay1 As String
Dim strDay2 As String
Dim strField As String
Dim lngWeekdays As Long
Dim lngBusinessDays As Long
strDay1 = StrDateSQL(datDay1)
strDay2 = StrDateSQL(datDay2)
Set db = CurrentDb()
strField = "[" & strHolidayField & "]"
strSQL = "SELECT Count("*") - 1 AS Count "
strSQL = strSQL & "FROM " & strHolidayTbl & " "
strSQL = strSQL & "WHERE (" & strField & " "
strSQL = strSQL & "BETWEEN " & strDay1 & " "
strSQL = strSQL & "AND " & strDay2 & ");"
MsgBox "Long Week Days is " & lngWeekdays
MsgBox "Start Date is " & datDay1
MsgBox "End Date is " & datDay2
Set rst = db.OpenRecordset(strSQL)
lngBusinessDays = rst![Count]
rst.Close
db.Close
MsgBox "Long Business Days is " & lngBusinessDays
...
Set rst = Nothing
Set db = Nothing
DiffBusinessDays_TSB = lngWeekdays - lngBusinessDays
</code snip>
This is not tested as I don't have these tables so corrections may be
needed but you probably get the idea.
/gustav
> 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
_______________________________________________
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.