Calculated fields in Form - Design view

[pjb]

I'm trying to add two fields to a form in Design view. The first one is just
a field for today's date; and I would like to include a label called "Today's
Date."

The second is a bit more detailed. This is going to be an appointment/date
book, keeping several records based on hourly appointments. What I would
like to do is have a field based on a calculation: the count of the number
of appointments already scheduled for that time period. I've had some
training in Oracle and SQL, so I know the query will be something like:

SELECT COUNT(*) from [Appt_book].[Appts] WHERE [Date]=(field read from form)
AND [Time]=(field read from form)

I want the field to simply read as empty until the time and date are
entered, then when both are entered, I would like a field on the screen to
indicate the count for the number of appointments already scheduled on the
same date and time.

Also, I forget how to add calculated fields to forms in general. I know
there's a way... but I forget how!

Thanks in advance for the help.

 

[Klatuu]

I will assume that the data and time fields in your table are two different
fields. They really should be one, but that is another story. So, lets call
the time control txtApptTime and the date txtApptDate and the count
txtTotAppts

First, to accomodate existing records and display the count, we need to put
this the Current event of the form:

Me.txtTotAppts = Nz(DCount("*", "Appt_Book","[Date] = #" & Me.txtApptDate &
"# AND [Time] = #" & Me.txtApptTime & "#"),0)

The Nz avoid getting a null in your form if there are no appointments.
If you really have fields named Date and Time, change them. Those are
Access reserved words and will buy you trouble.
It is necessary you format date and time correctly. I would suggest using
your input mask properties of the controls to do that. The Time formatting
is critical if you pass it #11:15:00# is will assume 11:15 AM. The time is
based on a 24 hour clock(See Help for more details)

Now, for new records, this is tricky because the date and time are in
different controls. The question is when to do the count, because one or
both may be empty. What I usually do in a case like this, is write a
function that I would call from the Before Update event of txtApptDate and
txtApptTime controls:
(Pass it the name of the control you are calling from so it knows what to do.)

For the Date control:
Me.txtTotAppts = CountAppointments("txtApptDate")
For the Time control:
Me.txtTotAppts = CountAppointments("txtApptTimee")

Function CountAppointments (txtCalledFrom as String) as Variant
If txtCalledFrom = "txtApptDate" Then
If Not IsDate(Me.txtApptDate) Then
MsgBox "Not a Valid Date"
CountAppointments = Null
ElseIf Not IsDate(Me.txtApptTime) Then
CountAppointments = Null
Else
CountAppointments = Nz(DCount("*", "Appt_Book"," _
[Date] = #" & Me.txtApptDate & _
"# AND [Time] = #" & Me.txtApptTime & "#"),0)
End If
Else
If Not IsDate(Me.txtApptTime) Then
MsgBox "Not a Valid Time"
CountAppointments = Null
ElseIf Not IsDate(Me.txtApptDate) Then
CountAppointments = Null
Else
CountAppointments = Nz(DCount("*", "Appt_Book"," _
[Date] = #" & Me.txtApptDate & _
"# AND [Time] = #" & Me.txtApptTime & "#"),0)
End If
End If
End Function

Notice that if you call it from the Date control, it validates the date and
checks for a valid time. It warns of a bad date, but ignores bad times and
returns null because we don't know if the user entered the time yet. The
same is true for the Time. If both date and time are valid, it does the
count.

Be aware this is UNTEST AIR CODE and may need debugging, but the method is
sound.

I'm trying to add two fields to a form in Design view. The first one is just
a field for today's date; and I would like to include a label called "Today's
Date."

The second is a bit more detailed. This is going to be an appointment/date
book, keeping several records based on hourly appointments. What I would
like to do is have a field based on a calculation: the count of the number
of appointments already scheduled for that time period. I've had some
training in Oracle and SQL, so I know the query will be something like:

SELECT COUNT(*) from [Appt_book].[Appts] WHERE [Date]=(field read from form)
AND [Time]=(field read from form)

I want the field to simply read as empty until the time and date are
entered, then when both are entered, I would like a field on the screen to
indicate the count for the number of appointments already scheduled on the
same date and time.

Also, I forget how to add calculated fields to forms in general. I know
there's a way... but I forget how!

Thanks in advance for the help.

 

[pjb]

Thanks for your information. I did think that the date and time fields would
be better served as a single input field; however, what I need is for each of
these bits of information to be displayed individually in the form, and in a
report that will be generated from a query built on the original table. I
want to build the report so that it requests the date as a parameter, then
builds the report grouping by hour.

Is there a way to make it easier to input the time/date/DOW information so
that they can be displayed separately and used separately as data (e.g., as
calculated fields elsewhere on the form), or am I on the right track starting
with them as separate fields to begin with? If they would be better as one
field, what would be the input mask needed to assure that only hours can be
input?

Thanks again!

I will assume that the data and time fields in your table are two different
fields. They really should be one, but that is another story. So, lets call
the time control txtApptTime and the date txtApptDate and the count
txtTotAppts

First, to accomodate existing records and display the count, we need to put
this the Current event of the form:

Me.txtTotAppts = Nz(DCount("*", "Appt_Book","[Date] = #" & Me.txtApptDate &
"# AND [Time] = #" & Me.txtApptTime & "#"),0)

The Nz avoid getting a null in your form if there are no appointments.
If you really have fields named Date and Time, change them. Those are
Access reserved words and will buy you trouble.
It is necessary you format date and time correctly. I would suggest using
your input mask properties of the controls to do that. The Time formatting
is critical if you pass it #11:15:00# is will assume 11:15 AM. The time is
based on a 24 hour clock(See Help for more details)

Now, for new records, this is tricky because the date and time are in
different controls. The question is when to do the count, because one or
both may be empty. What I usually do in a case like this, is write a
function that I would call from the Before Update event of txtApptDate and
txtApptTime controls:
(Pass it the name of the control you are calling from so it knows what to do.)

For the Date control:
Me.txtTotAppts = CountAppointments("txtApptDate")
For the Time control:
Me.txtTotAppts = CountAppointments("txtApptTimee")

Function CountAppointments (txtCalledFrom as String) as Variant
If txtCalledFrom = "txtApptDate" Then
If Not IsDate(Me.txtApptDate) Then
MsgBox "Not a Valid Date"
CountAppointments = Null
ElseIf Not IsDate(Me.txtApptTime) Then
CountAppointments = Null
Else
CountAppointments = Nz(DCount("*", "Appt_Book"," _
[Date] = #" & Me.txtApptDate & _
"# AND [Time] = #" & Me.txtApptTime & "#"),0)
End If
Else
If Not IsDate(Me.txtApptTime) Then
MsgBox "Not a Valid Time"
CountAppointments = Null
ElseIf Not IsDate(Me.txtApptDate) Then
CountAppointments = Null
Else
CountAppointments = Nz(DCount("*", "Appt_Book"," _
[Date] = #" & Me.txtApptDate & _
"# AND [Time] = #" & Me.txtApptTime & "#"),0)
End If
End If
End Function

Notice that if you call it from the Date control, it validates the date and
checks for a valid time. It warns of a bad date, but ignores bad times and
returns null because we don't know if the user entered the time yet. The
same is true for the Time. If both date and time are valid, it does the
count.

Be aware this is UNTEST AIR CODE and may need debugging, but the method is
sound.

I'm trying to add two fields to a form in Design view. The first one is just
a field for today's date; and I would like to include a label called "Today's
Date."

The second is a bit more detailed. This is going to be an appointment/date
book, keeping several records based on hourly appointments. What I would
like to do is have a field based on a calculation: the count of the number
of appointments already scheduled for that time period. I've had some
training in Oracle and SQL, so I know the query will be something like:

SELECT COUNT(*) from [Appt_book].[Appts] WHERE [Date]=(field read from form)
AND [Time]=(field read from form)

I want the field to simply read as empty until the time and date are
entered, then when both are entered, I would like a field on the screen to
indicate the count for the number of appointments already scheduled on the
same date and time.

Also, I forget how to add calculated fields to forms in general. I know
there's a way... but I forget how!

Thanks in advance for the help.

 

[Klatuu]

based on what you said, the separate fields is probably the way to go. To
combine them into one field and manipulate them for display would take extra
work.
Input mask for the date 00\/00\/0000
Input mask for the time field 99:00 >LL

Thanks for your information. I did think that the date and time fields would
be better served as a single input field; however, what I need is for each of
these bits of information to be displayed individually in the form, and in a
report that will be generated from a query built on the original table. I
want to build the report so that it requests the date as a parameter, then
builds the report grouping by hour.

Is there a way to make it easier to input the time/date/DOW information so
that they can be displayed separately and used separately as data (e.g., as
calculated fields elsewhere on the form), or am I on the right track starting
with them as separate fields to begin with? If they would be better as one
field, what would be the input mask needed to assure that only hours can be
input?

Thanks again!

I will assume that the data and time fields in your table are two different
fields. They really should be one, but that is another story. So, lets call
the time control txtApptTime and the date txtApptDate and the count
txtTotAppts

First, to accomodate existing records and display the count, we need to put
this the Current event of the form:

Me.txtTotAppts = Nz(DCount("*", "Appt_Book","[Date] = #" & Me.txtApptDate &
"# AND [Time] = #" & Me.txtApptTime & "#"),0)

The Nz avoid getting a null in your form if there are no appointments.
If you really have fields named Date and Time, change them. Those are
Access reserved words and will buy you trouble.
It is necessary you format date and time correctly. I would suggest using
your input mask properties of the controls to do that. The Time formatting
is critical if you pass it #11:15:00# is will assume 11:15 AM. The time is
based on a 24 hour clock(See Help for more details)

Now, for new records, this is tricky because the date and time are in
different controls. The question is when to do the count, because one or
both may be empty. What I usually do in a case like this, is write a
function that I would call from the Before Update event of txtApptDate and
txtApptTime controls:
(Pass it the name of the control you are calling from so it knows what to do.)

For the Date control:
Me.txtTotAppts = CountAppointments("txtApptDate")
For the Time control:
Me.txtTotAppts = CountAppointments("txtApptTimee")

Function CountAppointments (txtCalledFrom as String) as Variant
If txtCalledFrom = "txtApptDate" Then
If Not IsDate(Me.txtApptDate) Then
MsgBox "Not a Valid Date"
CountAppointments = Null
ElseIf Not IsDate(Me.txtApptTime) Then
CountAppointments = Null
Else
CountAppointments = Nz(DCount("*", "Appt_Book"," _
[Date] = #" & Me.txtApptDate & _
"# AND [Time] = #" & Me.txtApptTime & "#"),0)
End If
Else
If Not IsDate(Me.txtApptTime) Then
MsgBox "Not a Valid Time"
CountAppointments = Null
ElseIf Not IsDate(Me.txtApptDate) Then
CountAppointments = Null
Else
CountAppointments = Nz(DCount("*", "Appt_Book"," _
[Date] = #" & Me.txtApptDate & _
"# AND [Time] = #" & Me.txtApptTime & "#"),0)
End If
End If
End Function

Notice that if you call it from the Date control, it validates the date and
checks for a valid time. It warns of a bad date, but ignores bad times and
returns null because we don't know if the user entered the time yet. The
same is true for the Time. If both date and time are valid, it does the
count.

Be aware this is UNTEST AIR CODE and may need debugging, but the method is
sound.

I'm trying to add two fields to a form in Design view. The first one is just
a field for today's date; and I would like to include a label called "Today's
Date."

The second is a bit more detailed. This is going to be an appointment/date
book, keeping several records based on hourly appointments. What I would
like to do is have a field based on a calculation: the count of the number
of appointments already scheduled for that time period. I've had some
training in Oracle and SQL, so I know the query will be something like:

SELECT COUNT(*) from [Appt_book].[Appts] WHERE [Date]=(field read from form)
AND [Time]=(field read from form)

I want the field to simply read as empty until the time and date are
entered, then when both are entered, I would like a field on the screen to
indicate the count for the number of appointments already scheduled on the
same date and time.

Also, I forget how to add calculated fields to forms in general. I know
there's a way... but I forget how!

Thanks in advance for the help.