How do I associate two fields in Access.

[Guest]

How do I associate multiple fields in Access so that changing one will change
the info in another. Example: If I enter the date as 01/01/04 in one field I
would want another field to display "Tuesday" another display "January"
another display "01" and another field display "2004" automatically.

I am using Access to edit Mail Merge fields in legal documents. please advise.

 

[Mike Painter]

How do I associate multiple fields in Access so that changing one
will change the info in another. Example: If I enter the date as
01/01/04 in one field I would want another field to display "Tuesday"
another display "January" another display "01" and another field
display "2004" automatically.

I am using Access to edit Mail Merge fields in legal documents.
please advise.

Use a query and calculated fields to format the date in the manner you wish.

 

[Roger Carlson]

You would NOT do this in a table. You do it in a query. Something like
this:

SELECT Format([TheDateField],"dddd") AS DayOfWeek,
Format([TheDateField],"mmmm") AS [Month],
Format([TheDateField],"dd") AS [Day],
Format([TheDateField],"yyyy") AS [Year]
FROM TheTable;

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

How do I associate multiple fields in Access so that changing one will change
the info in another. Example: If I enter the date as 01/01/04 in one field I
would want another field to display "Tuesday" another display "January"
another display "01" and another field display "2004" automatically.

I am using Access to edit Mail Merge fields in legal documents. please

advise.

 

[Guest]

Assume you have two fields txt1 and txt2, both being text boxes. If you are
keying the date into txt1 as mm/dd/yy and want the four digit year in txt2 do
the following:

Click on txt2
Open the properties box and click on the data tab
for control source enter = format(txt1, "YYYY")

When you enter data into txt1 and leave the field, txt2 will update. You
can pick the date apart whichever way you need to in whichever fields.

Hope that helps

 

[Guest]

Could you please direct me to a tutorial for this procedure.

You would NOT do this in a table. You do it in a query. Something like
this:

SELECT Format([TheDateField],"dddd") AS DayOfWeek,
Format([TheDateField],"mmmm") AS [Month],
Format([TheDateField],"dd") AS [Day],
Format([TheDateField],"yyyy") AS [Year]
FROM TheTable;

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

How do I associate multiple fields in Access so that changing one will change
the info in another. Example: If I enter the date as 01/01/04 in one field I
would want another field to display "Tuesday" another display "January"
another display "01" and another field display "2004" automatically.

I am using Access to edit Mail Merge fields in legal documents. please

advise.

 

[Roger Carlson]

I was showing you the SQL statement because it is a little hard to show the
Query Builder here. You need to find a good book about Access querying.
However, perhaps I can describe it.

1) Create a new query.
2) Add your table to the top panel
3) In the bottom panel, add any fields you want to show in your resultset
4) In a blank column (in the bottom panel) add the following to the Field
row:
DayOfWeek:Format([TheDateField],"dddd")
5) Repeat for the other fields (each in their own column)
Month: Format([TheDateField],"mmmm")
Day: Format([TheDateField],"dd")
Year: TheDateField],"yyyy") AS [Year]

If you go to the SQL View, you will see that you have a query very much like
what I wrote below.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Could you please direct me to a tutorial for this procedure.

You would NOT do this in a table. You do it in a query. Something like
this:

SELECT Format([TheDateField],"dddd") AS DayOfWeek,
Format([TheDateField],"mmmm") AS [Month],
Format([TheDateField],"dd") AS [Day],
Format([TheDateField],"yyyy") AS [Year]
FROM TheTable;

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

How do I associate multiple fields in Access so that changing one will change
the info in another. Example: If I enter the date as 01/01/04 in one

field
I

would want another field to display "Tuesday" another display "January"
another display "01" and another field display "2004" automatically.

I am using Access to edit Mail Merge fields in legal documents. please

advise.

 

[Roger Carlson]

Sorry, minor correction. The last one should be:
Year: Format([TheDateField],"yyyy")

Of course, you would replace TheDateField in all of these with your actual
date field.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I was showing you the SQL statement because it is a little hard to show the
Query Builder here. You need to find a good book about Access querying.
However, perhaps I can describe it.

1) Create a new query.
2) Add your table to the top panel
3) In the bottom panel, add any fields you want to show in your resultset
4) In a blank column (in the bottom panel) add the following to the Field
row:
DayOfWeek:Format([TheDateField],"dddd")
5) Repeat for the other fields (each in their own column)
Month: Format([TheDateField],"mmmm")
Day: Format([TheDateField],"dd")
Year: TheDateField],"yyyy") AS [Year]

If you go to the SQL View, you will see that you have a query very much like
what I wrote below.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Could you please direct me to a tutorial for this procedure.

You would NOT do this in a table. You do it in a query. Something like
this:

SELECT Format([TheDateField],"dddd") AS DayOfWeek,
Format([TheDateField],"mmmm") AS [Month],
Format([TheDateField],"dd") AS [Day],
Format([TheDateField],"yyyy") AS [Year]
FROM TheTable;

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


How do I associate multiple fields in Access so that changing one will
change
the info in another. Example: If I enter the date as 01/01/04 in one field
I
would want another field to display "Tuesday" another display "January"
another display "01" and another field display "2004" automatically.

I am using Access to edit Mail Merge fields in legal documents. please
advise.

 

[Mike Painter]

I was showing you the SQL statement because it is a little hard to
show the Query Builder here. You need to find a good book about
Access querying. However, perhaps I can describe it.

1) Create a new query.
2) Add your table to the top panel
3) In the bottom panel, add any fields you want to show in your
resultset 4) In a blank column (in the bottom panel) add the
following to the Field row:
DayOfWeek:Format([TheDateField],"dddd")
5) Repeat for the other fields (each in their own column)
Month: Format([TheDateField],"mmmm")
Day: Format([TheDateField],"dd")
Year: TheDateField],"yyyy") AS [Year]

If you go to the SQL View, you will see that you have a query very
much like what I wrote below.

If you are lazy like I am you can also clean up and place the query in the
SQL view then switch to design.

 

[Guest]

Thanks for the help.

Assume you have two fields txt1 and txt2, both being text boxes. If you are
keying the date into txt1 as mm/dd/yy and want the four digit year in txt2 do
the following:

Click on txt2
Open the properties box and click on the data tab
for control source enter = format(txt1, "YYYY")

When you enter data into txt1 and leave the field, txt2 will update. You
can pick the date apart whichever way you need to in whichever fields.

Hope that helps