Changing the parameters of a form

[Amin]

I have a form that has a start date, end date, and calculates the days in
between.
The control source for the calculated days is the following:
=WorkingDays2([StartDate],[EndDate])


What I would like to do is put that day in a record next to each person from
my personnel roster:

Ted 3
Bill 3
Steven 3
....

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

This query produces each persons name and a square 0 in the "Available"
field for each person, regardless of the number of days. When I tried to
change the parameters like so:

PARAMETERS Forms!Switchie!Days Number;
SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

The "Available" field becomes null. Is there a way that this field will
populate without having to mess with the VBA code?

Thank you!
Amin

 

[Golfinray]

try workingdays2:datediff("d",[startdate],[enddate])

 

[Beetle]

WorkingDays2 appears to be a global function, so why not just use
it directly in your query, using references to your form controls as
arguments.

 

[Amin]

No, unfortunately, that resulted in my days textbox to be fixed on #Name?

try workingdays2:datediff("d",[startdate],[enddate])

I have a form that has a start date, end date, and calculates the days in
between.
The control source for the calculated days is the following:
=WorkingDays2([StartDate],[EndDate])


What I would like to do is put that day in a record next to each person from
my personnel roster:

Ted 3
Bill 3
Steven 3
...

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

This query produces each persons name and a square 0 in the "Available"
field for each person, regardless of the number of days. When I tried to
change the parameters like so:

PARAMETERS Forms!Switchie!Days Number;
SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

The "Available" field becomes null. Is there a way that this field will
populate without having to mess with the VBA code?

Thank you!
Amin

 

[Klatuu]

There are a couple of things to consider here.
First, it appears WorkingDays2 is a function that returns the number of work
days between the two day. That is good.
Now, it also appears your expression is in the control source of a text box
on your form and that [StartDate] and [EndDate] are either fields in your
table, controls on your form, or both.

One of the basic rules of database normalization is you do not store values
in a tabe that can be calculated from available static data. That is, if
StartDate and EndDate are fields in your table, then they should not be
stored at all, but displayed with a caculated control as you are doing now.

On the other hand, if the dates are unbound values you enter in the form but
are not stored in the table, then you do want to store the value. But,
since the control source is used to display the calculation, you will have
to populate the text box another way and bind the control to the field in
your table that stores the number of days.

An easy way to do that would be to write a function that does the
calculation and populates the control and call it from the AfterUpdate event
of both date controls. It would look like this:

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function

Note, it will not update until both dates are entered.

Now, to use it, you put the following expression directly in the AfterUpdate
event text box in the properties dialog for both date controls.

=CalcWorkDays()

That will cause the function to execute whenver the user enters a value in
either control, but the control to display and store the work days will not
populate until both have a date.

 

[Amin]

Yes, WorkingDays2 is a function that returns the number of work days between
two days. The dates are unbound values tied to a form. This is my code from
an access website:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************

Are you saying I should paste your code into another module? And then change
the AfterUpdate property of each textbox? Before I do this, I want to be
clear about what I'm trying to achieve. My form right now successfully
calculates the working days between two unbound dates in a form. Now this
"Days" textbox works and I can use to divide the total number of records
between two dates by the number of days to get records per day (and other
operations like this). What I could not get it to do was populate as in the
example I wrote? Will this accomplish that? Forgive me being dense, I am
adding more tools to my knowledge of SQL and VBA, but sometimes I get lost. I
don't understand why I can use this calculated textbox and divide it by other
fields, but I can't populate it as a constant number in the example I showed.

Thank you very much,
Amin



Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function

There are a couple of things to consider here.
First, it appears WorkingDays2 is a function that returns the number of work
days between the two day. That is good.
Now, it also appears your expression is in the control source of a text box
on your form and that [StartDate] and [EndDate] are either fields in your
table, controls on your form, or both.

One of the basic rules of database normalization is you do not store values
in a tabe that can be calculated from available static data. That is, if
StartDate and EndDate are fields in your table, then they should not be
stored at all, but displayed with a caculated control as you are doing now.

On the other hand, if the dates are unbound values you enter in the form but
are not stored in the table, then you do want to store the value. But,
since the control source is used to display the calculation, you will have
to populate the text box another way and bind the control to the field in
your table that stores the number of days.

An easy way to do that would be to write a function that does the
calculation and populates the control and call it from the AfterUpdate event
of both date controls. It would look like this:

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function

Note, it will not update until both dates are entered.

Now, to use it, you put the following expression directly in the AfterUpdate
event text box in the properties dialog for both date controls.

=CalcWorkDays()

That will cause the function to execute whenver the user enters a value in
either control, but the control to display and store the work days will not
populate until both have a date.

 

[Klatuu]

What I posted is exactly what you need. The function I provided can go in
the form's module. This method will call you WorkDays2 function and
populate a control that is bound to the field you want to store the number
of days in. You will have to modify the function I posted to use the realy
names of your controls.

Yes, WorkingDays2 is a function that returns the number of work days
between
two days. The dates are unbound values tied to a form. This is my code
from
an access website:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************

Are you saying I should paste your code into another module? And then
change
the AfterUpdate property of each textbox? Before I do this, I want to be
clear about what I'm trying to achieve. My form right now successfully
calculates the working days between two unbound dates in a form. Now this
"Days" textbox works and I can use to divide the total number of records
between two dates by the number of days to get records per day (and other
operations like this). What I could not get it to do was populate as in
the
example I wrote? Will this accomplish that? Forgive me being dense, I am
adding more tools to my knowledge of SQL and VBA, but sometimes I get
lost. I
don't understand why I can use this calculated textbox and divide it by
other
fields, but I can't populate it as a constant number in the example I
showed.

Thank you very much,
Amin



Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function

There are a couple of things to consider here.
First, it appears WorkingDays2 is a function that returns the number of
work
days between the two day. That is good.
Now, it also appears your expression is in the control source of a text
box
on your form and that [StartDate] and [EndDate] are either fields in your
table, controls on your form, or both.

One of the basic rules of database normalization is you do not store
values
in a tabe that can be calculated from available static data. That is, if
StartDate and EndDate are fields in your table, then they should not be
stored at all, but displayed with a caculated control as you are doing
now.

On the other hand, if the dates are unbound values you enter in the form
but
are not stored in the table, then you do want to store the value. But,
since the control source is used to display the calculation, you will
have
to populate the text box another way and bind the control to the field in
your table that stores the number of days.

An easy way to do that would be to write a function that does the
calculation and populates the control and call it from the AfterUpdate
event
of both date controls. It would look like this:

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function

Note, it will not update until both dates are entered.

Now, to use it, you put the following expression directly in the
AfterUpdate
event text box in the properties dialog for both date controls.

=CalcWorkDays()

That will cause the function to execute whenver the user enters a value
in
either control, but the control to display and store the work days will
not
populate until both have a date.

 

[Amin]

OK, so I am following your advice. My controls are called StartDate, EndDate,
and Days, and the name of my function was called WorkingDays2. I modified
your code to the following, and pasted it below the function WorkingDays2.

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.Days = WorkingDays2(Me.StartDate, Me.EndDate)
End If

End Function

I follow the logic of the code there. I then inputted =CalcWorkDays() in the
AfterEvent field of my textboxes. I deleted the "Days" control source code
of: =WorkingDays2([StartDate],[EndDate]).

But now nothing will populate, the textbox remains empty. What step am I
missing?

Thanks,
Amin

What I posted is exactly what you need. The function I provided can go in
the form's module. This method will call you WorkDays2 function and
populate a control that is bound to the field you want to store the number
of days in. You will have to modify the function I posted to use the realy
names of your controls.

Yes, WorkingDays2 is a function that returns the number of work days
between
two days. The dates are unbound values tied to a form. This is my code
from
an access website:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************

Are you saying I should paste your code into another module? And then
change
the AfterUpdate property of each textbox? Before I do this, I want to be
clear about what I'm trying to achieve. My form right now successfully
calculates the working days between two unbound dates in a form. Now this
"Days" textbox works and I can use to divide the total number of records
between two dates by the number of days to get records per day (and other
operations like this). What I could not get it to do was populate as in
the
example I wrote? Will this accomplish that? Forgive me being dense, I am
adding more tools to my knowledge of SQL and VBA, but sometimes I get
lost. I
don't understand why I can use this calculated textbox and divide it by
other
fields, but I can't populate it as a constant number in the example I
showed.

Thank you very much,
Amin



Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function

There are a couple of things to consider here.
First, it appears WorkingDays2 is a function that returns the number of
work
days between the two day. That is good.
Now, it also appears your expression is in the control source of a text
box
on your form and that [StartDate] and [EndDate] are either fields in your
table, controls on your form, or both.

One of the basic rules of database normalization is you do not store
values
in a tabe that can be calculated from available static data. That is, if
StartDate and EndDate are fields in your table, then they should not be
stored at all, but displayed with a caculated control as you are doing
now.

On the other hand, if the dates are unbound values you enter in the form
but
are not stored in the table, then you do want to store the value. But,
since the control source is used to display the calculation, you will
have
to populate the text box another way and bind the control to the field in
your table that stores the number of days.

An easy way to do that would be to write a function that does the
calculation and populates the control and call it from the AfterUpdate
event
of both date controls. It would look like this:

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function

Note, it will not update until both dates are entered.

Now, to use it, you put the following expression directly in the
AfterUpdate
event text box in the properties dialog for both date controls.

=CalcWorkDays()

That will cause the function to execute whenver the user enters a value
in
either control, but the control to display and store the work days will
not
populate until both have a date.

 

[Beetle]

A couple of things to note here;

It looks like you may have placed the function Klatuu provided into
a global module (which is probably where your other function is).
It needs to go in your Form's module to work correctly (at least, as
it is written).

The other thing is that the Control Source of your Days control should
not be blank, it should be the field in the recordsource where you want the
value stored.

--
_________

Sean Bailey

OK, so I am following your advice. My controls are called StartDate, EndDate,
and Days, and the name of my function was called WorkingDays2. I modified
your code to the following, and pasted it below the function WorkingDays2.

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.Days = WorkingDays2(Me.StartDate, Me.EndDate)
End If

End Function

I follow the logic of the code there. I then inputted =CalcWorkDays() in the
AfterEvent field of my textboxes. I deleted the "Days" control source code
of: =WorkingDays2([StartDate],[EndDate]).

But now nothing will populate, the textbox remains empty. What step am I
missing?

Thanks,
Amin

What I posted is exactly what you need. The function I provided can go in
the form's module. This method will call you WorkDays2 function and
populate a control that is bound to the field you want to store the number
of days in. You will have to modify the function I posted to use the realy
names of your controls.

Yes, WorkingDays2 is a function that returns the number of work days
between
two days. The dates are unbound values tied to a form. This is my code
from
an access website:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************

Are you saying I should paste your code into another module? And then
change
the AfterUpdate property of each textbox? Before I do this, I want to be
clear about what I'm trying to achieve. My form right now successfully
calculates the working days between two unbound dates in a form. Now this
"Days" textbox works and I can use to divide the total number of records
between two dates by the number of days to get records per day (and other
operations like this). What I could not get it to do was populate as in
the
example I wrote? Will this accomplish that? Forgive me being dense, I am
adding more tools to my knowledge of SQL and VBA, but sometimes I get
lost. I
don't understand why I can use this calculated textbox and divide it by
other
fields, but I can't populate it as a constant number in the example I
showed.

Thank you very much,
Amin



Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function



:

There are a couple of things to consider here.
First, it appears WorkingDays2 is a function that returns the number of
work
days between the two day. That is good.
Now, it also appears your expression is in the control source of a text
box
on your form and that [StartDate] and [EndDate] are either fields in your
table, controls on your form, or both.

One of the basic rules of database normalization is you do not store
values
in a tabe that can be calculated from available static data. That is, if
StartDate and EndDate are fields in your table, then they should not be
stored at all, but displayed with a caculated control as you are doing
now.

On the other hand, if the dates are unbound values you enter in the form
but
are not stored in the table, then you do want to store the value. But,
since the control source is used to display the calculation, you will
have
to populate the text box another way and bind the control to the field in
your table that stores the number of days.

An easy way to do that would be to write a function that does the
calculation and populates the control and call it from the AfterUpdate
event
of both date controls. It would look like this:

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function

Note, it will not update until both dates are entered.

Now, to use it, you put the following expression directly in the
AfterUpdate
event text box in the properties dialog for both date controls.

=CalcWorkDays()

That will cause the function to execute whenver the user enters a value
in
either control, but the control to display and store the work days will
not
populate until both have a date.

 

[Amin]

Thank you Sean, you were right, I had put the code in a global module and not
the forms module. The field now populates correctly.

But as your second point predicted, I am still having the same problem as
before. My query is still producing a square zero when I have the control
source as blank. My query which is called [Availability] is this at the
moment:

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

Since you stated the control source of my "Days' textbox "should be the
field in the recordsource where you want the value stored", I changed the
control source to
this:

[Availability]!["Available"]

But this messes up the population of the textbox "Days"

What is the last thing I'm missing?

Thanks,
Amin

A couple of things to note here;

It looks like you may have placed the function Klatuu provided into
a global module (which is probably where your other function is).
It needs to go in your Form's module to work correctly (at least, as
it is written).

The other thing is that the Control Source of your Days control should
not be blank, it should be the field in the recordsource where you want the
value stored.

--
_________

Sean Bailey

OK, so I am following your advice. My controls are called StartDate, EndDate,
and Days, and the name of my function was called WorkingDays2. I modified
your code to the following, and pasted it below the function WorkingDays2.

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.Days = WorkingDays2(Me.StartDate, Me.EndDate)
End If

End Function

I follow the logic of the code there. I then inputted =CalcWorkDays() in the
AfterEvent field of my textboxes. I deleted the "Days" control source code
of: =WorkingDays2([StartDate],[EndDate]).

But now nothing will populate, the textbox remains empty. What step am I
missing?

Thanks,
Amin

What I posted is exactly what you need. The function I provided can go in
the form's module. This method will call you WorkDays2 function and
populate a control that is bound to the field you want to store the number
of days in. You will have to modify the function I posted to use the realy
names of your controls.

Yes, WorkingDays2 is a function that returns the number of work days
between
two days. The dates are unbound values tied to a form. This is my code
from
an access website:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************

Are you saying I should paste your code into another module? And then
change
the AfterUpdate property of each textbox? Before I do this, I want to be
clear about what I'm trying to achieve. My form right now successfully
calculates the working days between two unbound dates in a form. Now this
"Days" textbox works and I can use to divide the total number of records
between two dates by the number of days to get records per day (and other
operations like this). What I could not get it to do was populate as in
the
example I wrote? Will this accomplish that? Forgive me being dense, I am
adding more tools to my knowledge of SQL and VBA, but sometimes I get
lost. I
don't understand why I can use this calculated textbox and divide it by
other
fields, but I can't populate it as a constant number in the example I
showed.

Thank you very much,
Amin



Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function



:

There are a couple of things to consider here.
First, it appears WorkingDays2 is a function that returns the number of
work
days between the two day. That is good.
Now, it also appears your expression is in the control source of a text
box
on your form and that [StartDate] and [EndDate] are either fields in your
table, controls on your form, or both.

One of the basic rules of database normalization is you do not store
values
in a tabe that can be calculated from available static data. That is, if
StartDate and EndDate are fields in your table, then they should not be
stored at all, but displayed with a caculated control as you are doing
now.

On the other hand, if the dates are unbound values you enter in the form
but
are not stored in the table, then you do want to store the value. But,
since the control source is used to display the calculation, you will
have
to populate the text box another way and bind the control to the field in
your table that stores the number of days.

An easy way to do that would be to write a function that does the
calculation and populates the control and call it from the AfterUpdate
event
of both date controls. It would look like this:

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function

Note, it will not update until both dates are entered.

Now, to use it, you put the following expression directly in the
AfterUpdate
event text box in the properties dialog for both date controls.

=CalcWorkDays()

That will cause the function to execute whenver the user enters a value
in
either control, but the control to display and store the work days will
not
populate until both have a date.

 

[Klatuu]

Lets go over it one more time.

Control named Days
Should contain the name of the field in the form's record source in
which you want to store the results of the calculation. If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

Thank you Sean, you were right, I had put the code in a global module and
not
the forms module. The field now populates correctly.

But as your second point predicted, I am still having the same problem as
before. My query is still producing a square zero when I have the control
source as blank. My query which is called [Availability] is this at the
moment:

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

Since you stated the control source of my "Days' textbox "should be the
field in the recordsource where you want the value stored", I changed the
control source to
this:

[Availability]!["Available"]

But this messes up the population of the textbox "Days"

What is the last thing I'm missing?

Thanks,
Amin

A couple of things to note here;

It looks like you may have placed the function Klatuu provided into
a global module (which is probably where your other function is).
It needs to go in your Form's module to work correctly (at least, as
it is written).

The other thing is that the Control Source of your Days control should
not be blank, it should be the field in the recordsource where you want
the
value stored.

--
_________

Sean Bailey

OK, so I am following your advice. My controls are called StartDate,
EndDate,
and Days, and the name of my function was called WorkingDays2. I
modified
your code to the following, and pasted it below the function
WorkingDays2.

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.Days = WorkingDays2(Me.StartDate, Me.EndDate)
End If

End Function

I follow the logic of the code there. I then inputted =CalcWorkDays()
in the
AfterEvent field of my textboxes. I deleted the "Days" control source
code
of: =WorkingDays2([StartDate],[EndDate]).

But now nothing will populate, the textbox remains empty. What step am
I
missing?

Thanks,
Amin




:

What I posted is exactly what you need. The function I provided can
go in
the form's module. This method will call you WorkDays2 function and
populate a control that is bound to the field you want to store the
number
of days in. You will have to modify the function I posted to use the
realy
names of your controls.

Yes, WorkingDays2 is a function that returns the number of work
days
between
two days. The dates are unbound values tied to a form. This is my
code
from
an access website:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between
them
' Note that this function has been modified to account for
holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <>
vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************

Are you saying I should paste your code into another module? And
then
change
the AfterUpdate property of each textbox? Before I do this, I want
to be
clear about what I'm trying to achieve. My form right now
successfully
calculates the working days between two unbound dates in a form.
Now this
"Days" textbox works and I can use to divide the total number of
records
between two dates by the number of days to get records per day (and
other
operations like this). What I could not get it to do was populate
as in
the
example I wrote? Will this accomplish that? Forgive me being dense,
I am
adding more tools to my knowledge of SQL and VBA, but sometimes I
get
lost. I
don't understand why I can use this calculated textbox and divide
it by
other
fields, but I can't populate it as a constant number in the example
I
showed.

Thank you very much,
Amin



Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function



:

There are a couple of things to consider here.
First, it appears WorkingDays2 is a function that returns the
number of
work
days between the two day. That is good.
Now, it also appears your expression is in the control source of a
text
box
on your form and that [StartDate] and [EndDate] are either fields
in your
table, controls on your form, or both.

One of the basic rules of database normalization is you do not
store
values
in a tabe that can be calculated from available static data. That
is, if
StartDate and EndDate are fields in your table, then they should
not be
stored at all, but displayed with a caculated control as you are
doing
now.

On the other hand, if the dates are unbound values you enter in
the form
but
are not stored in the table, then you do want to store the value.
But,
since the control source is used to display the calculation, you
will
have
to populate the text box another way and bind the control to the
field in
your table that stores the number of days.

An easy way to do that would be to write a function that does the
calculation and populates the control and call it from the
AfterUpdate
event
of both date controls. It would look like this:

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function

Note, it will not update until both dates are entered.

Now, to use it, you put the following expression directly in the
AfterUpdate
event text box in the properties dialog for both date controls.

=CalcWorkDays()

That will cause the function to execute whenver the user enters a
value
in
either control, but the control to display and store the work days
will
not
populate until both have a date.

 

[Amin]

David, I thank you for all your help, for I can tell that I'm definitely
close to the answer. I thought I had implied this in my last post, but I do
not exactly know what "form's record source" means.

If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

I wrote in my last post that I had done the above, but when I put this into
the Control Source text box, my textbox populated with #Name?.

I have googled "form's record source" to figure out what that means, and
Access Help says you can view it from the "View--> Field list," but the
"View--> Field list" will not toggle (as in, it does not give me the option
to click it, it's gray) so I can see what the form's record source is.

Let me restate what I have, and can someone please tell me what I should do:

I have three textboxes called StartDate, EndDate, and Days. Using what David
told me, Days calculates the number of work days between StartDate and
EndDate using a function called CalcWorkDays.

The problem is that I want to call this number in a query called
"Availability", and I guess because "Days" is unbound at the moment, it
produces a square zero. David is telling me I need to store this value in a
field, and I do this in the control source of "Days". I tried to do this by
putting the name of the field in "Availability" (using the ... box so I did
not misspell it), but this causes "Days" to produce this error, #Name?. I
thought maybe the problem was using a query, so I created a new table with
only one field, but when I put this table.fieldname in my Days Control
Source, I got the same error of #Name?

I also tried to creating a new textbox, and making that control source equal
to "Days", and calling that from my query Availability, but that did not work
either. Can someone please clarify what I am doing wrong?

Thank you again,
Amin

Lets go over it one more time.

Control named Days
Should contain the name of the field in the form's record source in
which you want to store the results of the calculation. If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

Thank you Sean, you were right, I had put the code in a global module and
not
the forms module. The field now populates correctly.

But as your second point predicted, I am still having the same problem as
before. My query is still producing a square zero when I have the control
source as blank. My query which is called [Availability] is this at the
moment:

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

Since you stated the control source of my "Days' textbox "should be the
field in the recordsource where you want the value stored", I changed the
control source to
this:

[Availability]!["Available"]

But this messes up the population of the textbox "Days"

What is the last thing I'm missing?

Thanks,
Amin

A couple of things to note here;

It looks like you may have placed the function Klatuu provided into
a global module (which is probably where your other function is).
It needs to go in your Form's module to work correctly (at least, as
it is written).

The other thing is that the Control Source of your Days control should
not be blank, it should be the field in the recordsource where you want
the
value stored.

--
_________

Sean Bailey


:

OK, so I am following your advice. My controls are called StartDate,
EndDate,
and Days, and the name of my function was called WorkingDays2. I
modified
your code to the following, and pasted it below the function
WorkingDays2.

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.Days = WorkingDays2(Me.StartDate, Me.EndDate)
End If

End Function

I follow the logic of the code there. I then inputted =CalcWorkDays()
in the
AfterEvent field of my textboxes. I deleted the "Days" control source
code
of: =WorkingDays2([StartDate],[EndDate]).

But now nothing will populate, the textbox remains empty. What step am
I
missing?

Thanks,
Amin




:

What I posted is exactly what you need. The function I provided can
go in
the form's module. This method will call you WorkDays2 function and
populate a control that is bound to the field you want to store the
number
of days in. You will have to modify the function I posted to use the
realy
names of your controls.

Yes, WorkingDays2 is a function that returns the number of work
days
between
two days. The dates are unbound values tied to a form. This is my
code
from
an access website:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between
them
' Note that this function has been modified to account for
holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <>
vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************

Are you saying I should paste your code into another module? And
then
change
the AfterUpdate property of each textbox? Before I do this, I want
to be
clear about what I'm trying to achieve. My form right now
successfully
calculates the working days between two unbound dates in a form.
Now this
"Days" textbox works and I can use to divide the total number of
records
between two dates by the number of days to get records per day (and
other
operations like this). What I could not get it to do was populate
as in
the
example I wrote? Will this accomplish that? Forgive me being dense,
I am
adding more tools to my knowledge of SQL and VBA, but sometimes I
get
lost. I
don't understand why I can use this calculated textbox and divide
it by
other
fields, but I can't populate it as a constant number in the example
I
showed.

Thank you very much,
Amin



Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function



:

There are a couple of things to consider here.
First, it appears WorkingDays2 is a function that returns the
number of
work
days between the two day. That is good.
Now, it also appears your expression is in the control source of a
text
box
on your form and that [StartDate] and [EndDate] are either fields
in your
table, controls on your form, or both.

One of the basic rules of database normalization is you do not
store
values
in a tabe that can be calculated from available static data. That
is, if
StartDate and EndDate are fields in your table, then they should
not be
stored at all, but displayed with a caculated control as you are
doing
now.

On the other hand, if the dates are unbound values you enter in
the form
but
are not stored in the table, then you do want to store the value.
But,
since the control source is used to display the calculation, you
will
have
to populate the text box another way and bind the control to the
field in
your table that stores the number of days.

An easy way to do that would be to write a function that does the
calculation and populates the control and call it from the
AfterUpdate
event
of both date controls. It would look like this:

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function

Note, it will not update until both dates are entered.

Now, to use it, you put the following expression directly in the
AfterUpdate
event text box in the properties dialog for both date controls.

=CalcWorkDays()

That will cause the function to execute whenver the user enters a
value
in
either control, but the control to display and store the work days
will
not
populate until both have a date.

 

[Beetle]

Maybe we should take a step back here and try to clarify what
you currently have and what you want, then we can try again to
point you in the right direction.

First, a forms record source is the table or query that the form is
based on. However, a form can also be unbound, which means it
has no record source. I suspect that since your field list is not
selectable, your form is unbound (you can verify this by opening
the properties sheet for your form and looking at the Record Source
property under the Data tab - if your form is unbound it will be blank).

If the form is unbound then any data entered into it is not being
store in any table in your application, unless you are writing the data
to the table indirectly using VBA code. It doesn't really sound like
this is the case. Which begs the question - what is the purpose of this
form? Did you create it just to try to get your query to work?

My next question - because I'm still not clear - is what is your ultimate
goal? Do you want the number of working days stored in a table or
do you just want to display it in a query?

Lastly, just to reiterate, as Dave stated previously if the values for
StartDate and EndDate are stored somewhere in a table in your
application, then it is not necessary (nor recommended) to store the
working days in a table. It can always be calculated as needed based
on the existing Start and End Date data. On the other hand, if the Start
and End dates are not stored anywhere, then it may be legitimate to store
the working days.

--
_________

Sean Bailey

David, I thank you for all your help, for I can tell that I'm definitely
close to the answer. I thought I had implied this in my last post, but I do
not exactly know what "form's record source" means.

If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

I wrote in my last post that I had done the above, but when I put this into
the Control Source text box, my textbox populated with #Name?.

I have googled "form's record source" to figure out what that means, and
Access Help says you can view it from the "View--> Field list," but the
"View--> Field list" will not toggle (as in, it does not give me the option
to click it, it's gray) so I can see what the form's record source is.

Let me restate what I have, and can someone please tell me what I should do:

I have three textboxes called StartDate, EndDate, and Days. Using what David
told me, Days calculates the number of work days between StartDate and
EndDate using a function called CalcWorkDays.

The problem is that I want to call this number in a query called
"Availability", and I guess because "Days" is unbound at the moment, it
produces a square zero. David is telling me I need to store this value in a
field, and I do this in the control source of "Days". I tried to do this by
putting the name of the field in "Availability" (using the ... box so I did
not misspell it), but this causes "Days" to produce this error, #Name?. I
thought maybe the problem was using a query, so I created a new table with
only one field, but when I put this table.fieldname in my Days Control
Source, I got the same error of #Name?

I also tried to creating a new textbox, and making that control source equal
to "Days", and calling that from my query Availability, but that did not work
either. Can someone please clarify what I am doing wrong?

Thank you again,
Amin

Lets go over it one more time.

Control named Days
Should contain the name of the field in the form's record source in
which you want to store the results of the calculation. If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

Thank you Sean, you were right, I had put the code in a global module and
not
the forms module. The field now populates correctly.

But as your second point predicted, I am still having the same problem as
before. My query is still producing a square zero when I have the control
source as blank. My query which is called [Availability] is this at the
moment:

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

Since you stated the control source of my "Days' textbox "should be the
field in the recordsource where you want the value stored", I changed the
control source to
this:

[Availability]!["Available"]

But this messes up the population of the textbox "Days"

What is the last thing I'm missing?

Thanks,
Amin

:

A couple of things to note here;

It looks like you may have placed the function Klatuu provided into
a global module (which is probably where your other function is).
It needs to go in your Form's module to work correctly (at least, as
it is written).

The other thing is that the Control Source of your Days control should
not be blank, it should be the field in the recordsource where you want
the
value stored.

--
_________

Sean Bailey


:

OK, so I am following your advice. My controls are called StartDate,
EndDate,
and Days, and the name of my function was called WorkingDays2. I
modified
your code to the following, and pasted it below the function
WorkingDays2.

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.Days = WorkingDays2(Me.StartDate, Me.EndDate)
End If

End Function

I follow the logic of the code there. I then inputted =CalcWorkDays()
in the
AfterEvent field of my textboxes. I deleted the "Days" control source
code
of: =WorkingDays2([StartDate],[EndDate]).

But now nothing will populate, the textbox remains empty. What step am
I
missing?

Thanks,
Amin




:

What I posted is exactly what you need. The function I provided can
go in
the form's module. This method will call you WorkDays2 function and
populate a control that is bound to the field you want to store the
number
of days in. You will have to modify the function I posted to use the
realy
names of your controls.

Yes, WorkingDays2 is a function that returns the number of work
days
between
two days. The dates are unbound values tied to a form. This is my
code
from
an access website:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between
them
' Note that this function has been modified to account for
holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <>
vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************

Are you saying I should paste your code into another module? And
then
change
the AfterUpdate property of each textbox? Before I do this, I want
to be
clear about what I'm trying to achieve. My form right now
successfully
calculates the working days between two unbound dates in a form.
Now this
"Days" textbox works and I can use to divide the total number of
records
between two dates by the number of days to get records per day (and
other
operations like this). What I could not get it to do was populate
as in
the
example I wrote? Will this accomplish that? Forgive me being dense,
I am
adding more tools to my knowledge of SQL and VBA, but sometimes I
get
lost. I
don't understand why I can use this calculated textbox and divide
it by
other
fields, but I can't populate it as a constant number in the example
I
showed.

Thank you very much,
Amin



Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function



:

There are a couple of things to consider here.
First, it appears WorkingDays2 is a function that returns the
number of
work
days between the two day. That is good.
Now, it also appears your expression is in the control source of a
text
box
on your form and that [StartDate] and [EndDate] are either fields
in your
table, controls on your form, or both.

One of the basic rules of database normalization is you do not
store
values
in a tabe that can be calculated from available static data. That
is, if
StartDate and EndDate are fields in your table, then they should
not be
stored at all, but displayed with a caculated control as you are
doing
now.

On the other hand, if the dates are unbound values you enter in
the form
but
are not stored in the table, then you do want to store the value.
But,
since the control source is used to display the calculation, you
will
have
to populate the text box another way and bind the control to the
field in
your table that stores the number of days.

An easy way to do that would be to write a function that does the
calculation and populates the control and call it from the
AfterUpdate
event
of both date controls. It would look like this:

 

[Amin]

Thanks Sean, you are right, my form is unbound. So I have a table with over
100,000 records over a period of 4 months that does have a field with dates.

I am using the form so that a user can simply select a StartDate and EndDate
that they are interested in, and my form will produce multiple reports during
that time-frame.

I first tried to do this with parametric queries, but I have queries based
on queries, and when I tried to input the StartDate and EndDate using a
parametric query, I was prompted multiple times for the StartDate and
EndDate. I was told in a different thread that the only way around this was
to use an unbound form.

Now, the reason I need working days is so that I can normalize whatever date
range the user inputs to produce daily averages.

Using the form accomplishes all of these things (as in it works for my those
reports), but when I try to call it in this specific query:

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

To produce this table:

Ted 3
Bill 3
Steven 3

I unfortunately get this table (only with square zeros)

Ted 0
Bill 0
Steven 0

This is literally the last thing I need to be finished with this project,
but I do not know how to do it.

Sorry to everyone for all the time this is taking up,
Amin

Maybe we should take a step back here and try to clarify what
you currently have and what you want, then we can try again to
point you in the right direction.

First, a forms record source is the table or query that the form is
based on. However, a form can also be unbound, which means it
has no record source. I suspect that since your field list is not
selectable, your form is unbound (you can verify this by opening
the properties sheet for your form and looking at the Record Source
property under the Data tab - if your form is unbound it will be blank).

If the form is unbound then any data entered into it is not being
store in any table in your application, unless you are writing the data
to the table indirectly using VBA code. It doesn't really sound like
this is the case. Which begs the question - what is the purpose of this
form? Did you create it just to try to get your query to work?

My next question - because I'm still not clear - is what is your ultimate
goal? Do you want the number of working days stored in a table or
do you just want to display it in a query?

Lastly, just to reiterate, as Dave stated previously if the values for
StartDate and EndDate are stored somewhere in a table in your
application, then it is not necessary (nor recommended) to store the
working days in a table. It can always be calculated as needed based
on the existing Start and End Date data. On the other hand, if the Start
and End dates are not stored anywhere, then it may be legitimate to store
the working days.

--
_________

Sean Bailey

David, I thank you for all your help, for I can tell that I'm definitely
close to the answer. I thought I had implied this in my last post, but I do
not exactly know what "form's record source" means.

If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

I wrote in my last post that I had done the above, but when I put this into
the Control Source text box, my textbox populated with #Name?.

I have googled "form's record source" to figure out what that means, and
Access Help says you can view it from the "View--> Field list," but the
"View--> Field list" will not toggle (as in, it does not give me the option
to click it, it's gray) so I can see what the form's record source is.

Let me restate what I have, and can someone please tell me what I should do:

I have three textboxes called StartDate, EndDate, and Days. Using what David
told me, Days calculates the number of work days between StartDate and
EndDate using a function called CalcWorkDays.

The problem is that I want to call this number in a query called
"Availability", and I guess because "Days" is unbound at the moment, it
produces a square zero. David is telling me I need to store this value in a
field, and I do this in the control source of "Days". I tried to do this by
putting the name of the field in "Availability" (using the ... box so I did
not misspell it), but this causes "Days" to produce this error, #Name?. I
thought maybe the problem was using a query, so I created a new table with
only one field, but when I put this table.fieldname in my Days Control
Source, I got the same error of #Name?

I also tried to creating a new textbox, and making that control source equal
to "Days", and calling that from my query Availability, but that did not work
either. Can someone please clarify what I am doing wrong?

Thank you again,
Amin

Lets go over it one more time.

Control named Days
Should contain the name of the field in the form's record source in
which you want to store the results of the calculation. If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

Thank you Sean, you were right, I had put the code in a global module and
not
the forms module. The field now populates correctly.

But as your second point predicted, I am still having the same problem as
before. My query is still producing a square zero when I have the control
source as blank. My query which is called [Availability] is this at the
moment:

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

Since you stated the control source of my "Days' textbox "should be the
field in the recordsource where you want the value stored", I changed the
control source to
this:

[Availability]!["Available"]

But this messes up the population of the textbox "Days"

What is the last thing I'm missing?

Thanks,
Amin

:

A couple of things to note here;

It looks like you may have placed the function Klatuu provided into
a global module (which is probably where your other function is).
It needs to go in your Form's module to work correctly (at least, as
it is written).

The other thing is that the Control Source of your Days control should
not be blank, it should be the field in the recordsource where you want
the
value stored.

--
_________

Sean Bailey


:

OK, so I am following your advice. My controls are called StartDate,
EndDate,
and Days, and the name of my function was called WorkingDays2. I
modified
your code to the following, and pasted it below the function
WorkingDays2.

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.Days = WorkingDays2(Me.StartDate, Me.EndDate)
End If

End Function

I follow the logic of the code there. I then inputted =CalcWorkDays()
in the
AfterEvent field of my textboxes. I deleted the "Days" control source
code
of: =WorkingDays2([StartDate],[EndDate]).

But now nothing will populate, the textbox remains empty. What step am
I
missing?

Thanks,
Amin




:

What I posted is exactly what you need. The function I provided can
go in
the form's module. This method will call you WorkDays2 function and
populate a control that is bound to the field you want to store the
number
of days in. You will have to modify the function I posted to use the
realy
names of your controls.

Yes, WorkingDays2 is a function that returns the number of work
days
between
two days. The dates are unbound values tied to a form. This is my
code
from
an access website:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between
them
' Note that this function has been modified to account for
holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <>
vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************

Are you saying I should paste your code into another module? And
then
change
the AfterUpdate property of each textbox? Before I do this, I want
to be
clear about what I'm trying to achieve. My form right now
successfully
calculates the working days between two unbound dates in a form.
Now this
"Days" textbox works and I can use to divide the total number of
records
between two dates by the number of days to get records per day (and
other
operations like this). What I could not get it to do was populate
as in
the
example I wrote? Will this accomplish that? Forgive me being dense,
I am
adding more tools to my knowledge of SQL and VBA, but sometimes I
get
lost. I
don't understand why I can use this calculated textbox and divide
it by
other
fields, but I can't populate it as a constant number in the example
I
showed.

Thank you very much,
Amin



Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function



:

There are a couple of things to consider here.
First, it appears WorkingDays2 is a function that returns the
number of
work

 

[Beetle]

OK, so if the Start and End dates are stored in a table then you don't
need to (and shouldn't) store the Working Days. Just calculate it when
you need to. As I said in my very first post, you can use your function
directly in your query using the undound StartDate and EndDate controls
on your form as variables. The SQL might look like;

SELECT UserID, WorkingDays2(CDate([Forms]![Switchie]![StartDate]),
CDate([Forms]![Switchie]![EndDate])) As Available
From [Personnel Roster];

I have used the CDate function in the above example to typecast the
values from the form controls to ensure that the function recognizes them
as dates. As long as the form is open, and there are date values in the
two contols, the query should return what you want.

--
_________

Sean Bailey

Thanks Sean, you are right, my form is unbound. So I have a table with over
100,000 records over a period of 4 months that does have a field with dates.

I am using the form so that a user can simply select a StartDate and EndDate
that they are interested in, and my form will produce multiple reports during
that time-frame.

I first tried to do this with parametric queries, but I have queries based
on queries, and when I tried to input the StartDate and EndDate using a
parametric query, I was prompted multiple times for the StartDate and
EndDate. I was told in a different thread that the only way around this was
to use an unbound form.

Now, the reason I need working days is so that I can normalize whatever date
range the user inputs to produce daily averages.

Using the form accomplishes all of these things (as in it works for my those
reports), but when I try to call it in this specific query:

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

To produce this table:

Ted 3
Bill 3
Steven 3

I unfortunately get this table (only with square zeros)

Ted 0
Bill 0
Steven 0

This is literally the last thing I need to be finished with this project,
but I do not know how to do it.

Sorry to everyone for all the time this is taking up,
Amin

Maybe we should take a step back here and try to clarify what
you currently have and what you want, then we can try again to
point you in the right direction.

First, a forms record source is the table or query that the form is
based on. However, a form can also be unbound, which means it
has no record source. I suspect that since your field list is not
selectable, your form is unbound (you can verify this by opening
the properties sheet for your form and looking at the Record Source
property under the Data tab - if your form is unbound it will be blank).

If the form is unbound then any data entered into it is not being
store in any table in your application, unless you are writing the data
to the table indirectly using VBA code. It doesn't really sound like
this is the case. Which begs the question - what is the purpose of this
form? Did you create it just to try to get your query to work?

My next question - because I'm still not clear - is what is your ultimate
goal? Do you want the number of working days stored in a table or
do you just want to display it in a query?

Lastly, just to reiterate, as Dave stated previously if the values for
StartDate and EndDate are stored somewhere in a table in your
application, then it is not necessary (nor recommended) to store the
working days in a table. It can always be calculated as needed based
on the existing Start and End Date data. On the other hand, if the Start
and End dates are not stored anywhere, then it may be legitimate to store
the working days.

--
_________

Sean Bailey

David, I thank you for all your help, for I can tell that I'm definitely
close to the answer. I thought I had implied this in my last post, but I do
not exactly know what "form's record source" means.

If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

I wrote in my last post that I had done the above, but when I put this into
the Control Source text box, my textbox populated with #Name?.

I have googled "form's record source" to figure out what that means, and
Access Help says you can view it from the "View--> Field list," but the
"View--> Field list" will not toggle (as in, it does not give me the option
to click it, it's gray) so I can see what the form's record source is.

Let me restate what I have, and can someone please tell me what I should do:

I have three textboxes called StartDate, EndDate, and Days. Using what David
told me, Days calculates the number of work days between StartDate and
EndDate using a function called CalcWorkDays.

The problem is that I want to call this number in a query called
"Availability", and I guess because "Days" is unbound at the moment, it
produces a square zero. David is telling me I need to store this value in a
field, and I do this in the control source of "Days". I tried to do this by
putting the name of the field in "Availability" (using the ... box so I did
not misspell it), but this causes "Days" to produce this error, #Name?. I
thought maybe the problem was using a query, so I created a new table with
only one field, but when I put this table.fieldname in my Days Control
Source, I got the same error of #Name?

I also tried to creating a new textbox, and making that control source equal
to "Days", and calling that from my query Availability, but that did not work
either. Can someone please clarify what I am doing wrong?

Thank you again,
Amin


:

Lets go over it one more time.

Control named Days
Should contain the name of the field in the form's record source in
which you want to store the results of the calculation. If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

Thank you Sean, you were right, I had put the code in a global module and
not
the forms module. The field now populates correctly.

But as your second point predicted, I am still having the same problem as
before. My query is still producing a square zero when I have the control
source as blank. My query which is called [Availability] is this at the
moment:

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

Since you stated the control source of my "Days' textbox "should be the
field in the recordsource where you want the value stored", I changed the
control source to
this:

[Availability]!["Available"]

But this messes up the population of the textbox "Days"

What is the last thing I'm missing?

Thanks,
Amin

:

A couple of things to note here;

It looks like you may have placed the function Klatuu provided into
a global module (which is probably where your other function is).
It needs to go in your Form's module to work correctly (at least, as
it is written).

The other thing is that the Control Source of your Days control should
not be blank, it should be the field in the recordsource where you want
the
value stored.

--
_________

Sean Bailey


:

OK, so I am following your advice. My controls are called StartDate,
EndDate,
and Days, and the name of my function was called WorkingDays2. I
modified
your code to the following, and pasted it below the function
WorkingDays2.

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.Days = WorkingDays2(Me.StartDate, Me.EndDate)
End If

End Function

I follow the logic of the code there. I then inputted =CalcWorkDays()
in the
AfterEvent field of my textboxes. I deleted the "Days" control source
code
of: =WorkingDays2([StartDate],[EndDate]).

But now nothing will populate, the textbox remains empty. What step am
I
missing?

Thanks,
Amin




:

What I posted is exactly what you need. The function I provided can
go in
the form's module. This method will call you WorkDays2 function and
populate a control that is bound to the field you want to store the
number
of days in. You will have to modify the function I posted to use the
realy
names of your controls.

Yes, WorkingDays2 is a function that returns the number of work
days
between
two days. The dates are unbound values tied to a form. This is my
code
from
an access website:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between
them
' Note that this function has been modified to account for
holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <>
vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************

Are you saying I should paste your code into another module? And
then
change
the AfterUpdate property of each textbox? Before I do this, I want
to be
clear about what I'm trying to achieve. My form right now
successfully

 

[Amin]

Thank you so much! I should have realized that you can call the working days
module from SQL. Lesson learned, thank you again!

OK, so if the Start and End dates are stored in a table then you don't
need to (and shouldn't) store the Working Days. Just calculate it when
you need to. As I said in my very first post, you can use your function
directly in your query using the undound StartDate and EndDate controls
on your form as variables. The SQL might look like;

SELECT UserID, WorkingDays2(CDate([Forms]![Switchie]![StartDate]),
CDate([Forms]![Switchie]![EndDate])) As Available
From [Personnel Roster];

I have used the CDate function in the above example to typecast the
values from the form controls to ensure that the function recognizes them
as dates. As long as the form is open, and there are date values in the
two contols, the query should return what you want.

--
_________

Sean Bailey

Thanks Sean, you are right, my form is unbound. So I have a table with over
100,000 records over a period of 4 months that does have a field with dates.

I am using the form so that a user can simply select a StartDate and EndDate
that they are interested in, and my form will produce multiple reports during
that time-frame.

I first tried to do this with parametric queries, but I have queries based
on queries, and when I tried to input the StartDate and EndDate using a
parametric query, I was prompted multiple times for the StartDate and
EndDate. I was told in a different thread that the only way around this was
to use an unbound form.

Now, the reason I need working days is so that I can normalize whatever date
range the user inputs to produce daily averages.

Using the form accomplishes all of these things (as in it works for my those
reports), but when I try to call it in this specific query:

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

To produce this table:

Ted 3
Bill 3
Steven 3

I unfortunately get this table (only with square zeros)

Ted 0
Bill 0
Steven 0

This is literally the last thing I need to be finished with this project,
but I do not know how to do it.

Sorry to everyone for all the time this is taking up,
Amin

Maybe we should take a step back here and try to clarify what
you currently have and what you want, then we can try again to
point you in the right direction.

First, a forms record source is the table or query that the form is
based on. However, a form can also be unbound, which means it
has no record source. I suspect that since your field list is not
selectable, your form is unbound (you can verify this by opening
the properties sheet for your form and looking at the Record Source
property under the Data tab - if your form is unbound it will be blank).

If the form is unbound then any data entered into it is not being
store in any table in your application, unless you are writing the data
to the table indirectly using VBA code. It doesn't really sound like
this is the case. Which begs the question - what is the purpose of this
form? Did you create it just to try to get your query to work?

My next question - because I'm still not clear - is what is your ultimate
goal? Do you want the number of working days stored in a table or
do you just want to display it in a query?

Lastly, just to reiterate, as Dave stated previously if the values for
StartDate and EndDate are stored somewhere in a table in your
application, then it is not necessary (nor recommended) to store the
working days in a table. It can always be calculated as needed based
on the existing Start and End Date data. On the other hand, if the Start
and End dates are not stored anywhere, then it may be legitimate to store
the working days.

--
_________

Sean Bailey


:

David, I thank you for all your help, for I can tell that I'm definitely
close to the answer. I thought I had implied this in my last post, but I do
not exactly know what "form's record source" means.

If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

I wrote in my last post that I had done the above, but when I put this into
the Control Source text box, my textbox populated with #Name?.

I have googled "form's record source" to figure out what that means, and
Access Help says you can view it from the "View--> Field list," but the
"View--> Field list" will not toggle (as in, it does not give me the option
to click it, it's gray) so I can see what the form's record source is.

Let me restate what I have, and can someone please tell me what I should do:

I have three textboxes called StartDate, EndDate, and Days. Using what David
told me, Days calculates the number of work days between StartDate and
EndDate using a function called CalcWorkDays.

The problem is that I want to call this number in a query called
"Availability", and I guess because "Days" is unbound at the moment, it
produces a square zero. David is telling me I need to store this value in a
field, and I do this in the control source of "Days". I tried to do this by
putting the name of the field in "Availability" (using the ... box so I did
not misspell it), but this causes "Days" to produce this error, #Name?. I
thought maybe the problem was using a query, so I created a new table with
only one field, but when I put this table.fieldname in my Days Control
Source, I got the same error of #Name?

I also tried to creating a new textbox, and making that control source equal
to "Days", and calling that from my query Availability, but that did not work
either. Can someone please clarify what I am doing wrong?

Thank you again,
Amin


:

Lets go over it one more time.

Control named Days
Should contain the name of the field in the form's record source in
which you want to store the results of the calculation. If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

Thank you Sean, you were right, I had put the code in a global module and
not
the forms module. The field now populates correctly.

But as your second point predicted, I am still having the same problem as
before. My query is still producing a square zero when I have the control
source as blank. My query which is called [Availability] is this at the
moment:

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

Since you stated the control source of my "Days' textbox "should be the
field in the recordsource where you want the value stored", I changed the
control source to
this:

[Availability]!["Available"]

But this messes up the population of the textbox "Days"

What is the last thing I'm missing?

Thanks,
Amin

:

A couple of things to note here;

It looks like you may have placed the function Klatuu provided into
a global module (which is probably where your other function is).
It needs to go in your Form's module to work correctly (at least, as
it is written).

The other thing is that the Control Source of your Days control should
not be blank, it should be the field in the recordsource where you want
the
value stored.

--
_________

Sean Bailey


:

OK, so I am following your advice. My controls are called StartDate,
EndDate,
and Days, and the name of my function was called WorkingDays2. I
modified
your code to the following, and pasted it below the function
WorkingDays2.

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.Days = WorkingDays2(Me.StartDate, Me.EndDate)
End If

End Function

I follow the logic of the code there. I then inputted =CalcWorkDays()
in the
AfterEvent field of my textboxes. I deleted the "Days" control source
code
of: =WorkingDays2([StartDate],[EndDate]).

But now nothing will populate, the textbox remains empty. What step am
I
missing?

Thanks,
Amin




:

What I posted is exactly what you need. The function I provided can
go in
the form's module. This method will call you WorkDays2 function and
populate a control that is bound to the field you want to store the
number
of days in. You will have to modify the function I posted to use the
realy
names of your controls.

Yes, WorkingDays2 is a function that returns the number of work
days
between
two days. The dates are unbound values tied to a form. This is my
code
from
an access website:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between
them
' Note that this function has been modified to account for
holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <>
vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

 

[Klatuu]

Sorry, Amin, I didn't pick up on that.
It is the table or query identified in the form's properties dialog box that
provides that data for the form.

I am assuming you are using a bound form. That is a form with a record
source specified as I described, but you can have an unbound form which has
no record source and you do all your data manipulation using VBA.

David, I thank you for all your help, for I can tell that I'm definitely
close to the answer. I thought I had implied this in my last post, but I
do
not exactly know what "form's record source" means.

If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

I wrote in my last post that I had done the above, but when I put this
into
the Control Source text box, my textbox populated with #Name?.

I have googled "form's record source" to figure out what that means, and
Access Help says you can view it from the "View--> Field list," but the
"View--> Field list" will not toggle (as in, it does not give me the
option
to click it, it's gray) so I can see what the form's record source is.

Let me restate what I have, and can someone please tell me what I should
do:

I have three textboxes called StartDate, EndDate, and Days. Using what
David
told me, Days calculates the number of work days between StartDate and
EndDate using a function called CalcWorkDays.

The problem is that I want to call this number in a query called
"Availability", and I guess because "Days" is unbound at the moment, it
produces a square zero. David is telling me I need to store this value in
a
field, and I do this in the control source of "Days". I tried to do this
by
putting the name of the field in "Availability" (using the ... box so I
did
not misspell it), but this causes "Days" to produce this error, #Name?. I
thought maybe the problem was using a query, so I created a new table with
only one field, but when I put this table.fieldname in my Days Control
Source, I got the same error of #Name?

I also tried to creating a new textbox, and making that control source
equal
to "Days", and calling that from my query Availability, but that did not
work
either. Can someone please clarify what I am doing wrong?

Thank you again,
Amin

Lets go over it one more time.

Control named Days
Should contain the name of the field in the form's record source in
which you want to store the results of the calculation. If the field is
named Available, just put Available in the Control Source text box. You
can drop the Control Source box down and select the name.

Thank you Sean, you were right, I had put the code in a global module
and
not
the forms module. The field now populates correctly.

But as your second point predicted, I am still having the same problem
as
before. My query is still producing a square zero when I have the
control
source as blank. My query which is called [Availability] is this at the
moment:

SELECT USERID, Forms!Switchie!Days AS ["Available"]
FROM [Personnel Roster];

Since you stated the control source of my "Days' textbox "should be the
field in the recordsource where you want the value stored", I changed
the
control source to
this:

[Availability]!["Available"]

But this messes up the population of the textbox "Days"

What is the last thing I'm missing?

Thanks,
Amin

:

A couple of things to note here;

It looks like you may have placed the function Klatuu provided into
a global module (which is probably where your other function is).
It needs to go in your Form's module to work correctly (at least, as
it is written).

The other thing is that the Control Source of your Days control should
not be blank, it should be the field in the recordsource where you
want
the
value stored.

--
_________

Sean Bailey


:

OK, so I am following your advice. My controls are called StartDate,
EndDate,
and Days, and the name of my function was called WorkingDays2. I
modified
your code to the following, and pasted it below the function
WorkingDays2.

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.Days = WorkingDays2(Me.StartDate, Me.EndDate)
End If

End Function

I follow the logic of the code there. I then inputted
=CalcWorkDays()
in the
AfterEvent field of my textboxes. I deleted the "Days" control
source
code
of: =WorkingDays2([StartDate],[EndDate]).

But now nothing will populate, the textbox remains empty. What step
am
I
missing?

Thanks,
Amin




:

What I posted is exactly what you need. The function I provided
can
go in
the form's module. This method will call you WorkDays2 function
and
populate a control that is bound to the field you want to store
the
number
of days in. You will have to modify the function I posted to use
the
realy
names of your controls.

Yes, WorkingDays2 is a function that returns the number of work
days
between
two days. The dates are unbound values tied to a form. This is
my
code
from
an access website:

Public Function WorkingDays2(StartDate As Date, EndDate As Date)
As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between
them
' Note that this function has been modified to account for
holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM
tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <>
vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End **************

Are you saying I should paste your code into another module? And
then
change
the AfterUpdate property of each textbox? Before I do this, I
want
to be
clear about what I'm trying to achieve. My form right now
successfully
calculates the working days between two unbound dates in a form.
Now this
"Days" textbox works and I can use to divide the total number of
records
between two dates by the number of days to get records per day
(and
other
operations like this). What I could not get it to do was
populate
as in
the
example I wrote? Will this accomplish that? Forgive me being
dense,
I am
adding more tools to my knowledge of SQL and VBA, but sometimes
I
get
lost. I
don't understand why I can use this calculated textbox and
divide
it by
other
fields, but I can't populate it as a constant number in the
example
I
showed.

Thank you very much,
Amin



Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function



:

There are a couple of things to consider here.
First, it appears WorkingDays2 is a function that returns the
number of
work
days between the two day. That is good.
Now, it also appears your expression is in the control source
of a
text
box
on your form and that [StartDate] and [EndDate] are either
fields
in your
table, controls on your form, or both.

One of the basic rules of database normalization is you do not
store
values
in a tabe that can be calculated from available static data.
That
is, if
StartDate and EndDate are fields in your table, then they
should
not be
stored at all, but displayed with a caculated control as you
are
doing
now.

On the other hand, if the dates are unbound values you enter in
the form
but
are not stored in the table, then you do want to store the
value.
But,
since the control source is used to display the calculation,
you
will
have
to populate the text box another way and bind the control to
the
field in
your table that stores the number of days.

An easy way to do that would be to write a function that does
the
calculation and populates the control and call it from the
AfterUpdate
event
of both date controls. It would look like this:

Private Function CalcWorkDays()

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
Me.WorkDays = WorkDays2(Me.StartDate, Me.EndDate)
End If

End Function

Note, it will not update until both dates are entered.

Now, to use it, you put the following expression directly in
the
AfterUpdate
event text box in the properties dialog for both date controls.

=CalcWorkDays()

That will cause the function to execute whenver the user enters
a
value
in
either control, but the control to display and store the work
days
will
not
populate until both have a date.