Using colour in tables

[Guest]

I use a large data base, set up by others, for which we only view the data in
the table, or occasionally use a query. I am fairly new to using Access.

In one field we enter either A, R or W followed by a date I would like
entries starting with R to show in a different colour so that they are
instantly visible. Is there a way or should I introduce another field for the
letter alone.

If I do the latter can I validate it to show on the table in a colour?

 

[Craig Alexander Morrison]

Use Excel!

or

Use Forms and Conditional Formatting.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

 

[Craig Alexander Morrison]

Another alternative rather than colour is to use a query to return only the
records with an "A", "R" or "W".

SELECT TableName.*
FROM TableName
WHERE (((TableName.AlphaLetter)="A"));

Paste the above in the SQL Window in the Query Designer and rename the
TableName to your table and the AlphaLetter to your field.

But if you really need a colour cue then my last post are the options.

 

[Craig Alexander Morrison]

Another alternative rather than colour is to use a query to return only the
records with an "A", "R" or "W".

SELECT TableName.*
FROM TableName
WHERE (((TableName.AlphaLetter)="A"));

Paste the above in the SQL Window in the Query Designer and rename the
TableName to your table and the AlphaLetter to your field.

But if you really need a colour cue then my last post contains the options
available.

 

[Guest]

Jo:

Access includes a conditional formatting facility for situations such as
this, but to see the data in datasheet view and make use of this facility
you'd need to design a form (a simple autoform would do) and set its
DefaultView property to Datasheet. Having done that do as follows:

Select the text box control in question while in form design view. Then
select 'Conditional Formatting' from the Format menu. In the combo box on
the left select 'Expression is'. In the text box to the right of this enter
the following expression, substituting the name of your field for 'MyField'

Left([MyField],1)="A" Or Left([MyField],1)="R" Or Left([MyField],1)="W"

If you want the text to change colour select the forecolor by clicking the
button with the 'A' on it; if you want the background to the control to
change select the backcolor by clicking the button with a tilted bucket on
it. You can use either or both of these in conjunction.

I would recommend, however, that you use forms for viewing/entering/editing
data rather than a raw datasheet, whether this be of a table or query. Forms
provide control over the formatting, data validation etc which a datasheet
does not. Moreover if you use single or continuous form view rather than
datasheet, you can include unbound controls such as command buttons, search
combo boxes etc. to enhance the functionality of the application.

As regards the question of splitting the field into letter and date,
regardless of the current requirement, for which its not necessary, that
would nevertheless probably be a good idea. For one thing it would allow you
to use a true date/time data type for the dates rather than their being part
of a string as at present. It’s a trivial task to join values from two or
more fields, but not always easy to separate them. In your case it sounds
like the letter and date are separate attributes in any case, in which case
they should have their own fields as a matter of good basic database design.
Getting existing data from your one field into two would be a simple task
with an update query once the two new fields are added to the table design,
following which the original field could be deleted.

Ken Sheridan
Stafford, England