How do I use a field created in VBA in a query

[Lynniebud]

I've written the code below to calculate a field called fulldayhours, but
when I try to show this field in a query design it doesn't work. This could
either be because the code is wrong, because you can't do this in access or
because I'm referring to it incorrectly in the query! I'm not a programmer,
so don't judge the code too harshly if it's doing completely the wrong
thing...

Function fulldayhours() As Integer

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim days As Integer
Dim i As Integer
Dim n As Integer
Dim n1 As Integer

Set rst = CurrentDb.OpenRecordset("tblData_Capture2")

While Not rst.EOF

' Calculate how many full days are between the two dates
If (IsNull(rst.Fields("Date_Keyed")) Or
IsNull(rst.Fields("Application_Date"))) Then
GoTo skiprecord
Else
days = (rst!Date_Keyed - rst!Application_Date) - 1
End If


' If there are 1 or more days between the dates then identify how many hours
that constitutes
If days > 0 Then

For i = 1 To days

If WeekDay(rst!Date_Keyed - i, vbMonday) < 6 Then
n = 12
ElseIf WeekDay(rst!Date_Keyed - i, vbMonday) = 6 Then
n = 9
ElseIf WeekDay(rst!Date_Keyed - i, vbMonday) = 7 Then
n = 8
End If

n1 = n + n1

Next i


rst.MoveNext

Else

n1 = 0
rst.MoveNext
End If

skiprecord:
rst.MoveNext

fulldayhours = n1

Wend


End Function

 

[Stefan Hoffmann]

This could
either be because the code is wrong, because you can't do this in access or
because I'm referring to it incorrectly in the query!

For the use in a query, you normally need parameters, e.g.:

Public Function FullDayHours(AStart As Date, AEnd As Date) As Long

FullDayHours = Int(AEnd - AStart) * 24

End Function

Function fulldayhours() As Integer

It must be declared in a standard module as Public.

days = (rst!Date_Keyed - rst!Application_Date) - 1

The integer part is the number of days:

days = Int(rst!Date_Keyed - rst!Application_Date)

If days is negative, then you may consider using Fix() instead of Int().

' If there are 1 or more days between the dates then identify how many hours
that constitutes

What do you exactly want to caluclate?
The number of hours is part of the fractional part:

hours = Int((rst!Date_Keyed - rst!Application_Date) / (1/24))


mfG
--> stefan <--

 

[Lynniebud]

What I'm trying to do is calculate the number of hours my business is open in
the full days between an application being made and the information being
keyed onto our system - i'll give an example to explain better!

If an application is made on Thursday 2nd, and is keyed onto the system on
Monday 6th, then I want to know how many hours the business was open on Fri,
Sat and Sun, but the office is open for a different length of time on these
days. (12hrs per weekday, 9 on a Sat and 8 on a Sun.) I therefore need to
work out (a) how many full days there are between the two dates and (b) what
days of the week they are. I then want to be able to pull this field into a
query using the design view.

Does this make sense?

Lynniebud

 

[Stefan Hoffmann]

hi,

If an application is made on Thursday 2nd, and is keyed onto the system on
Monday 6th, then I want to know how many hours the business was open on Fri,
Sat and Sun, but the office is open for a different length of time on these
days. (12hrs per weekday, 9 on a Sat and 8 on a Sun.) I therefore need to
work out (a) how many full days there are between the two dates and (b) what
days of the week they are. I then want to be able to pull this field into a
query using the design view.

You only count the days in between, so the application is made today and
keyed today or tomorrow, then the result is 0 hours?

Store this in a standard module:

Option Compare Database
Option Explicit

Public Function FullDayHours(AStart As Date, AEnd As Date) As Long

Dim Count As Long
Dim Result As Long

Result = 0
For Count = 0 To CInt(AEnd - AStart) - 2
Select Case WeekDay(CDate(AStart + Count + 1), vbMonday)
Case Is = 6
Result = Result + 9
Case Is = 7
Result = Result + 8
Case Else
Result = Result + 12
End Select
Next Count

FullDayHours = Result

End Function


You can use it in a query like

SELECT FullDayHours([Application_Date], [Date_Keyed]) AS Hours
FROM <yourTable>

or place it into the field row in the designer:

Hours: FullDayHours([Application_Date], [Date_Keyed])


mfG
--> stefan <--

 

[Lynniebud]

Fantastic - thanks!!!

Lynniebud

hi,

If an application is made on Thursday 2nd, and is keyed onto the system on
Monday 6th, then I want to know how many hours the business was open on Fri,
Sat and Sun, but the office is open for a different length of time on these
days. (12hrs per weekday, 9 on a Sat and 8 on a Sun.) I therefore need to
work out (a) how many full days there are between the two dates and (b) what
days of the week they are. I then want to be able to pull this field into a
query using the design view.

You only count the days in between, so the application is made today and
keyed today or tomorrow, then the result is 0 hours?

Store this in a standard module:

Option Compare Database
Option Explicit

Public Function FullDayHours(AStart As Date, AEnd As Date) As Long

Dim Count As Long
Dim Result As Long

Result = 0
For Count = 0 To CInt(AEnd - AStart) - 2
Select Case WeekDay(CDate(AStart + Count + 1), vbMonday)
Case Is = 6
Result = Result + 9
Case Is = 7
Result = Result + 8
Case Else
Result = Result + 12
End Select
Next Count

FullDayHours = Result

End Function


You can use it in a query like

SELECT FullDayHours([Application_Date], [Date_Keyed]) AS Hours
FROM <yourTable>

or place it into the field row in the designer:

Hours: FullDayHours([Application_Date], [Date_Keyed])


mfG
--> stefan <--

 

[wietze van osch]

I've written the code below to calculate a field called fulldayhours, but
when I try to show this field in a query design it doesn't work. This
could
either be because the code is wrong, because you can't do this in access
or
because I'm referring to it incorrectly in the query! I'm not a
programmer,
so don't judge the code too harshly if it's doing completely the wrong
thing...

Function fulldayhours() As Integer

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim days As Integer
Dim i As Integer
Dim n As Integer
Dim n1 As Integer

Set rst = CurrentDb.OpenRecordset("tblData_Capture2")

While Not rst.EOF

' Calculate how many full days are between the two dates
If (IsNull(rst.Fields("Date_Keyed")) Or
IsNull(rst.Fields("Application_Date"))) Then
GoTo skiprecord
Else
days = (rst!Date_Keyed - rst!Application_Date) - 1
End If


' If there are 1 or more days between the dates then identify how many
hours
that constitutes
If days > 0 Then

For i = 1 To days

If WeekDay(rst!Date_Keyed - i, vbMonday) < 6 Then
n = 12
ElseIf WeekDay(rst!Date_Keyed - i, vbMonday) = 6 Then
n = 9
ElseIf WeekDay(rst!Date_Keyed - i, vbMonday) = 7 Then
n = 8
End If

n1 = n + n1

Next i


rst.MoveNext

Else

n1 = 0
rst.MoveNext
End If

skiprecord:
rst.MoveNext

fulldayhours = n1

Wend


End Function

hello internet en netwerk van mail is misslukt

 

[Stefan Hoffmann]

hi,

Fantastic - thanks!!!

There is a little error in this line:
Change CInt() To Int() without the C.


mfG
--> stefan <--