Grouping Information in a Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to Access 97. I have a database that tracks quality information
about parts at different stages of production.

First I want a to identify (via bold, italic, etc.) the parts that do not
meet the specification for any of the data. (I want the data input to the
database, even though the data makes the part unacceptable.)

Second I want to automatically generate "NO" in another field to indicate
the part is not acceptable.

Third, I would like to enter the data from a previous record to the next
record automatically. (e.g. the date the part is being processed)

I would like an explanation - I'm unsure where all of the coding goes into
the database that I see exhibited in these chats.

Thanks so much.
 
Hi Glenda.

Access 2000 and later have a feature called Conditional Formatting, which is
not available in A97. The best you can do is A97 is to add a small text box
beside the main one, and include a symbol that changes color.

For this example, we assume that you have a field named TestResult. It has
values from 1 to 10, and anything below 5 is not up to standard.

Add a text box with properties like these:
Control Source =([TestResult] < 5)
Format [Blue]"Fine"; [Red]"Failed"

The control source yields an expression that is True or False (or null).
In Access, True is -1, and False is 0.
The Format expression intrepets these values as numbers.
The first expression applies to positive numbers, so shows for False.
The 2nd expression applies to negative numbers, so shows for True.


Regarding getting data from the previous record, see:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/?id=210504


Alternatively, if you are trying to do this in the context of a form, you
could add a text box with this ControlSource:
=GetPreviousValue([Form], "NameOfYourFieldHere")
and past the function below into a standard module (Modules tab of Database
window):

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
 
Thanks so much, everything works as you stated.

Allen Browne said:
Hi Glenda.

Access 2000 and later have a feature called Conditional Formatting, which is
not available in A97. The best you can do is A97 is to add a small text box
beside the main one, and include a symbol that changes color.

For this example, we assume that you have a field named TestResult. It has
values from 1 to 10, and anything below 5 is not up to standard.

Add a text box with properties like these:
Control Source =([TestResult] < 5)
Format [Blue]"Fine"; [Red]"Failed"

The control source yields an expression that is True or False (or null).
In Access, True is -1, and False is 0.
The Format expression intrepets these values as numbers.
The first expression applies to positive numbers, so shows for False.
The 2nd expression applies to negative numbers, so shows for True.


Regarding getting data from the previous record, see:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/?id=210504


Alternatively, if you are trying to do this in the context of a form, you
could add a text box with this ControlSource:
=GetPreviousValue([Form], "NameOfYourFieldHere")
and past the function below into a standard module (Modules tab of Database
window):

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.

Glenda said:
I am new to Access 97. I have a database that tracks quality information
about parts at different stages of production.

First I want a to identify (via bold, italic, etc.) the parts that do not
meet the specification for any of the data. (I want the data input to the
database, even though the data makes the part unacceptable.)

Second I want to automatically generate "NO" in another field to indicate
the part is not acceptable.

Third, I would like to enter the data from a previous record to the next
record automatically. (e.g. the date the part is being processed)

I would like an explanation - I'm unsure where all of the coding goes into
the database that I see exhibited in these chats.

Thanks so much.
Click to expand...
Click to expand...
 
Back
Top