Creating Calculated Field

[bagia]

Hi,

I'm trying to create a calculated field in a form and not
sure where to start. I have a water reading database.
What I want to do is to come up with today's water meter
reading. I want to subtract yesterday's reading (from the
previous record)from today's reading (today number field).

Water reading = [today number]- how can i pull previous
record info into this function?

Any suggestions or help would be greatly appreciated!

 

[Shawn]

Use the "DLookup" fucntion. You will need some sort of
unique identifier for each day.

 

[Allen Browne]

This function will get the value from the previous row of the form,
regardless of how the form is sorted or filtered.

To use it in a form, set the Control Source of a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

 

[bagia]

Allen,

Thanks so much for helping. I set teh Control Source for
a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

BUT I'm not sure where to place the function. Where should
I place the function? Do I place it in the Control Source
as well? Sorry I don't know anything about programming.

-----Original Message-----
This function will get the value from the previous row of the form,
regardless of how the form is sorted or filtered.

To use it in a form, set the Control Source of a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I'm trying to create a calculated field in a form and not
sure where to start. I have a water reading database.
What I want to do is to come up with today's water meter
reading. I want to subtract yesterday's reading (from the
previous record)from today's reading (today number field).

Water reading = [today number]- how can i pull previous
record info into this function?

Any suggestions or help would be greatly appreciated!

.

 

[Allen Browne]

1. In the Database window, select the Module tab.
2. Click New
3. Paste the code.
4. Save the module. "Module1" will do for a name.

You can now use this function anywhere in your program, just like Date() or
any of the built-in functions.

--

Allen,

Thanks so much for helping. I set teh Control Source for
a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

BUT I'm not sure where to place the function. Where should
I place the function? Do I place it in the Control Source
as well? Sorry I don't know anything about programming.

-----Original Message-----
This function will get the value from the previous row of the form,
regardless of how the form is sorted or filtered.

To use it in a form, set the Control Source of a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I'm trying to create a calculated field in a form and not
sure where to start. I have a water reading database.
What I want to do is to come up with today's water meter
reading. I want to subtract yesterday's reading (from the
previous record)from today's reading (today number field).

Water reading = [today number]- how can i pull previous
record info into this function?

Any suggestions or help would be greatly appreciated!

 

[bagia]

Allen,

Sorry for being so ignorant about the this. I went ot the
Module tab and created a new one with the code...copied
and pasted.

I open the form and received an error msg...it brought up
the MS Visual Basic window "Compile Error: User-defined
type not defined". "rs As DAO.Recordset" is highlighted.

Is there a way for me to send you the database and have
you take a look at it if it's easier for you to see what
i'm doing wrong?

-----Original Message-----
1. In the Database window, select the Module tab.
2. Click New
3. Paste the code.
4. Save the module. "Module1" will do for a name.

You can now use this function anywhere in your program, just like Date() or
any of the built-in functions.

--

Allen,

Thanks so much for helping. I set teh Control Source for
a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

BUT I'm not sure where to place the function. Where should
I place the function? Do I place it in the Control Source
as well? Sorry I don't know anything about programming.

-----Original Message-----
This function will get the value from the previous row

of
the form,

regardless of how the form is sorted or filtered.

To use it in a form, set the Control Source of a text

box
to:

=GetPreviousValue([Form], "NameOfYourFieldHere")

Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row

of
the form.

Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I'm trying to create a calculated field in a form and not
sure where to start. I have a water reading database.
What I want to do is to come up with today's water meter
reading. I want to subtract yesterday's reading

(from
the

previous record)from today's reading (today number field).

Water reading = [today number]- how can i pull previous
record info into this function?

Any suggestions or help would be greatly appreciated!

.

 

[Allen Browne]

The message means you do not have a reference to the DAO library.

From the code window (where you got the error), choose References from the
Tools menu. Then check the box beside:
Microsoft DAO 3.6

To check everything is okay, choose Compile from the Debug menu.

If you want more information about references, what they are, and which ones
you need see:
http://allenbrowne.com/ser-38.html

Allen,

Sorry for being so ignorant about the this. I went ot the
Module tab and created a new one with the code...copied
and pasted.

I open the form and received an error msg...it brought up
the MS Visual Basic window "Compile Error: User-defined
type not defined". "rs As DAO.Recordset" is highlighted.

Is there a way for me to send you the database and have
you take a look at it if it's easier for you to see what
i'm doing wrong?

-----Original Message-----
1. In the Database window, select the Module tab.
2. Click New
3. Paste the code.
4. Save the module. "Module1" will do for a name.

You can now use this function anywhere in your program, just like Date() or
any of the built-in functions.

--

Allen,

Thanks so much for helping. I set teh Control Source for
a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

BUT I'm not sure where to place the function. Where should
I place the function? Do I place it in the Control Source
as well? Sorry I don't know anything about programming.



-----Original Message-----
This function will get the value from the previous row of
the form,
regardless of how the form is sorted or filtered.

To use it in a form, set the Control Source of a text box
to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

Function GetPreviousValue(frm As Form, strField As
String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of
the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I'm trying to create a calculated field in a form and
not
sure where to start. I have a water reading database.
What I want to do is to come up with today's water meter
reading. I want to subtract yesterday's reading (from
the
previous record)from today's reading (today number
field).

Water reading = [today number]- how can i pull previous
record info into this function?

Any suggestions or help would be greatly appreciated!

 

[bagia]

Thank you very much Allen!

-----Original Message-----
The message means you do not have a reference to the DAO library.

From the code window (where you got the error), choose References from the
Tools menu. Then check the box beside:
Microsoft DAO 3.6

To check everything is okay, choose Compile from the Debug menu.

If you want more information about references, what they are, and which ones
you need see:
http://allenbrowne.com/ser-38.html

Allen,

Sorry for being so ignorant about the this. I went ot the
Module tab and created a new one with the code...copied
and pasted.

I open the form and received an error msg...it brought up
the MS Visual Basic window "Compile Error: User-defined
type not defined". "rs As DAO.Recordset" is highlighted.

Is there a way for me to send you the database and have
you take a look at it if it's easier for you to see what
i'm doing wrong?

-----Original Message-----
1. In the Database window, select the Module tab.
2. Click New
3. Paste the code.
4. Save the module. "Module1" will do for a name.

You can now use this function anywhere in your program, just like Date() or
any of the built-in functions.

--
Allen,

Thanks so much for helping. I set teh Control Source for
a text box to:
=GetPreviousValue ([Form], "NameOfYourFieldHere")

BUT I'm not sure where to place the function. Where should
I place the function? Do I place it in the Control Source
as well? Sorry I don't know anything about programming.



-----Original Message-----
This function will get the value from the previous

row
of

the form,
regardless of how the form is sorted or filtered.

To use it in a form, set the Control Source of a

text
box

to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

Function GetPreviousValue(frm As Form, strField As
String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous

row
of

the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I'm trying to create a calculated field in a form and
not
sure where to start. I have a water reading database.
What I want to do is to come up with today's water meter
reading. I want to subtract yesterday's reading (from
the
previous record)from today's reading (today number
field).

Water reading = [today number]- how can i pull previous
record info into this function?

Any suggestions or help would be greatly

appreciated!


.