Auto fillin of field

C

C Tate

I have a table called 'client'. In it is a field called dateclosed. There's
also another one called 'casestatus'. When someone enters a date into the
'dateclosed' field I would like the 'casestatus' field to automatically be
filled in with the 'closed' option which is already in that field (closed
and open are the two options in the 'casestatus' field).

Any advice with step by step instructions would be much appreciated! Thanks
in advance.
 
R

Rick B

This seems kind of redundant. If there is a datclosed, then the case is
closed. Why do you need a separate field to store that status as well?

Instead, only store the date closed. In your queries, reports, and forms,
you can display the string "Status = closed" if an entry exists in the data
field, or "status= open" of the field is null. No reason to store this data
though.

For more information, you might do a search on "database normalization" and
"redundant" data.
 
C

C Tate

Incidentally I'm not sure what you mean by 'you can display the string
'Status = closed' if an entry exists in the data field. Do you mean that if
I rely solely on a date being present in the dateclosed field that I can
still display the status of the case (ie, closed) in a query? This is new to
me. Please explain! I'm quite new to Access.
 
T

Tom Wickerath

Create a new query. Dismiss the Add Table dialog, without adding any tables
(or you can add your Client table if you want). In query design view, click
on View > SQL View. Replace the word SELECT with the following SQL
(Structured Query Language) statement:

SELECT *, IIf(IsNull([DateClosed]),"Open","Closed") AS [Case Status]
FROM Client;

You can now switch to the more familiar query design view, by using the View
menu. In this example, the wildcard, *, is selecting ALL fields in the Client
table. You may want to limit your query to just a few selected fields.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
C

C Tate

Many thanks! That's really useful.
Tom Wickerath said:
Create a new query. Dismiss the Add Table dialog, without adding any
tables
(or you can add your Client table if you want). In query design view,
click
on View > SQL View. Replace the word SELECT with the following SQL
(Structured Query Language) statement:

SELECT *, IIf(IsNull([DateClosed]),"Open","Closed") AS [Case Status]
FROM Client;

You can now switch to the more familiar query design view, by using the
View
menu. In this example, the wildcard, *, is selecting ALL fields in the
Client
table. You may want to limit your query to just a few selected fields.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

C Tate said:
Incidentally I'm not sure what you mean by 'you can display the string
'Status = closed' if an entry exists in the data field. Do you mean that
if
I rely solely on a date being present in the dateclosed field that I can
still display the status of the case (ie, closed) in a query? This is new
to
me. Please explain! I'm quite new to Access.
 
T

Tom Wickerath

You're welcome. Good luck on your project.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

C Tate said:
Many thanks! That's really useful. __________________________________________

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
Create a new query. Dismiss the Add Table dialog, without adding any
tables
(or you can add your Client table if you want). In query design view,
click
on View > SQL View. Replace the word SELECT with the following SQL
(Structured Query Language) statement:

SELECT *, IIf(IsNull([DateClosed]),"Open","Closed") AS [Case Status]
FROM Client;

You can now switch to the more familiar query design view, by using the
View
menu. In this example, the wildcard, *, is selecting ALL fields in the
Client
table. You may want to limit your query to just a few selected fields.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

C Tate said:
Incidentally I'm not sure what you mean by 'you can display the string
'Status = closed' if an entry exists in the data field. Do you mean that
if
I rely solely on a date being present in the dateclosed field that I can
still display the status of the case (ie, closed) in a query? This is new
to
me. Please explain! I'm quite new to Access.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top