Calc Business Days

[end922]

Reference Thread thread705-1453739

Greetings, I am using CK1999's code I keep getting a debug error when executed. The error says there is Syntax error in from clause. The VBA code errors out at line
Set rst = dbs.OpenRecordset(strSql)

I don't get it. I've checked my tabel name, field name everything I can think of with no luck.
Any suggestions would greatly help.

my holiday table name is tbl_H and field name is HDate all date formats are the same in all my tables.

Public Function basDlyHrs(StDt As Date, EndDt As Date) As Double

'Michhael Red 1/1/2003 Working Hours?
'Tek-Tips thread705-449121 for "Chargers"

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim Idx As Long
Dim StrtTim As Date
Dim EndTim As Date
Dim dtSt As Date
Dim dtEnd As Date
Dim TheDate As Date
Dim DlyHrs As Single

Dim strCriteria As String
Dim strSql As String

StrtTim = #7:00:00 AM#
EndTim = #4:00:00 PM#
DlyHrs = DateDiff("n", StrtTim, EndTim) / 60

dtSt = Format(StDt, "Short Date")
dtEnd = Format(EndDt, "Short Date")

'Create an Array to hold the Time for Each Day
' Dim MyDates() As MyDtHrsType
'Resize array for each DAY
' ReDim MyDates(dtEnd - dtSt + 1)

'Get Holidates
Set dbs = CurrentDb

strCriteria = "(HDate Between " & Chr(35) & dtSt & Chr(35) & _
" AND " & Chr(35) & dtEnd & Chr(35) & ")"

strSql = "Select HDate "
strSql = strSql & "from tbl_H"
strSql = strSql & "Where "
strSql = strSql & strCriteria & ";"

Set rst = dbs.OpenRecordset(strSql)

Dim holidaycount
holidaycount = 0
rst.MoveFirst
Do While Not rst.EOF
If (Weekday(rst("HDate"), vbMonday) > 5) Then
holidaycount = holidaycount + 1
End If
rst.MoveNext
Loop

' next line from

http://www.pacificdb.com.au/MVP/Code/Dates.htm

datecount = DateDiff("d", dtSt, dtEnd) - DateDiff("ww", dtSt, dtEnd, 1) * 2 - IIf(Weekday(dtEnd, 1) = 7, IIf(Weekday(dtSt, 1) = 7, 0, 1), IIf(Weekday(dtSt, 1) = 7, -1, 0)) + 1

datecount = datecount - holidaycount

basDlyHrs = datecount * DlyHrs

Set dbs = Nothing

End Function

 

[MajP]

put in a
debug.print strSql
before this line. And see what is wrong with your SQL statement. Post the result. Most likely your table or field names are different than Mike's

 

[TheAceMan1]

How are ya end922 . . .

First there's a spacing error here:

[blue]    strSql = strSql & "from tbl_H"
should be :
    strSql = strSql & "from tbl_H[COLOR=blue red] [/color]"[/blue]

Despite that try the following:

[blue]strSQL = "Select HDate " & _
         "From tbl_H " & _
         "Where (HDate Between #" & dtSt & "# AND #" & dtEnd & "#)"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[end922]

Changes I made
Added in Debug.Print
Added in the space after tbl_H
Saw where I was missing part of the original str for the below
Set rst = dbs.OpenRecordset(strSql)
Changed it to
Set rst = dbs.OpenRecordset(strSql, dbopenDynaset)

The immediate window displays
Select HDate from tbl_H Where (HDate Between #9/24/2008# and #9/26/2008#)

debug error now says "No current record" code errors out on line
rst.MoveFirst


I certainly appreciate both of you assistance.


Eric

 

[PHV]

Get rid of the MoveFirst line as you've just opened the recordset.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[end922]

Thanks did that

When I mouse over these fields all of them say 12:00:00 AM except for StDt and EndDT those show the correct times
Dlyhrs = 0 when I mouse over it when it should say 9
StrtTim = #7:00:00 AM#
EndTim = #4:00:00 PM#
DlyHrs = DateDiff("n", #7:00:00 AM#, #4:00:00 PM#) / 60
dtSt = Format(StDt, "General Date")
dtEnd = Format(EndDt, "General Date")
I tried to set DlyHrs = 9 but it still says 0 when I mouse over.

I am getting results back but not correct ones. I am not getting any more errors when I execute.

AppRecDate = 9/26/2008 11:06:00 AM
AssignedDate = 9/26/2008 3:06:00 PM
I would expect to get back a value of 4 since it was assigned in 4 hours. I get back 9

Thanks
E

 

[MichaelRed]

Why would you do a calculation, when you expect a constant to be returned?

DlyHrs = DateDiff("n", #7:00:00 AM#, #4:00:00 PM#) / 60

and

Dlyhrs = 0 when I mouse over it when it should say 9

Much the same could be said of StartTim and EndTim

refer to thread705-1207464 and please realize that many of the posts of code in these fora are intended as a starting point for learning about the functions and approaches to specific elements of the subject(s).

I believe there is also one or more faq's on this topic which should have more complete treatments.



MichaelRed