bind data to anothermfield. Date field to Month field.

[Guest]

My database has a date field; 'Birthdate.'
I have added another field to the table; 'Birthmonth.'

I want a user to be able to order the table view for browsing by month.

How do I get the Month from the 'Birthdate' field to automatically enter the
'birthmonth' field.

pg

 

[Al Camp]

You don't...
Since you've already captured BirthDate, there's no need to also
capture/save BirthMonth to a table field. BirthMonth can always be
redetermined "on the fly" in any query, form, or report.

I strongly recommend that you not allow users to "browse" in table view.
You should create a form, where you can control what they can and can't do
with the data.

But, if you must... show the user a query based on your table, and just add
a calculated field to the query that always displays the BirthMonth.
BirthMonth : Month(BirthDate)
Now BirthMonth (a "bound" field) shows up in the query datasheet view, and
can be filtered, sorted, etc... just like a real table field.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

Al, Thank you for your advice re user access.
This is a small business. The ONLY other user is my boss.
I'm going to study your response regarding queries and publish a report for
the next month's birthdays a week or so before.

 

[Larry Linson]

My database has a date field; 'Birthdate.'
I have added another field to the table; 'Birthmonth.'

I want a user to be able to order the
table view for browsing by month.

How do I get the Month from the
'Birthdate' field to automatically enter the
'birthmonth' field.

You don't, because you would violate relational database design principles
by having redundant data in the Table. What you do is eliminate the
Birthmonth Field from the Table, but calculate it from Birthdate in Queries
that you use to access the data from Forms, Feports, or for other purposes
such as browsing in datasheet view.

In the top line of the grid in the Query Builder, the Calculated Field would
read:

Birthmonth: Month([Birthdate]), or perhaps

Birthmonth: Format([Birthdate, mmm]) , or maybe

Birthmonth: Format([Birthdate],mmmm)

depending on whether you wanted the number, the abbreviation, or the name of
the month.

Larry Linson
Microsoft Access MVP

 

[John Vinson]

I'm going to study your response regarding queries and publish a report for
the next month's birthdays a week or so before.

The simplest way to do this is to use a Query using the date of birth
field (DOB let's say): put a calculated field in the query

HappyHappy: DateSerial(Year(Date()), Month([DOB]), Day([DOB]))

This will contain this year's birthday anniversary. As a criterion on
the field put

= DateSerial(Year(Date()), Month(Date()) + 1, 1) AND < DateSerial(Year(Date()), Month(Date()) + 2, 1)

to get just the coming month's birthdays.

John W. Vinson[MVP]

 

[Guest]

Larry it's not working for me.

I have a field in my table ; Birthday 1

I make a query in design view.
From my table 'pgFeb15' ;
I add Fields ; F. Name, L. Name, Birthday 1, in the usual way to produce
a table with the query results.

In the next Column. on the row headed 'field' I adapt your advice to state:

B1 Month: Format ([Birthday 1, mmm])

I have a another field in my table; Birthday 2 etc

why doesn't it produce the desired result in the query results table.

I have copied my query screen to a word doc if you would like to see it.
-------------------------------------------------------------------------

My database has a date field; 'Birthdate.'
I have added another field to the table; 'Birthmonth.'

I want a user to be able to order the
table view for browsing by month.

How do I get the Month from the
'Birthdate' field to automatically enter the
'birthmonth' field.

You don't, because you would violate relational database design principles
by having redundant data in the Table. What you do is eliminate the
Birthmonth Field from the Table, but calculate it from Birthdate in Queries
that you use to access the data from Forms, Feports, or for other purposes
such as browsing in datasheet view.

In the top line of the grid in the Query Builder, the Calculated Field would
read:

Birthmonth: Month([Birthdate]), or perhaps

Birthmonth: Format([Birthdate, mmm]) , or maybe

Birthmonth: Format([Birthdate],mmmm)

depending on whether you wanted the number, the abbreviation, or the name of
the month.

Larry Linson
Microsoft Access MVP

 

[John Vinson]

In the next Column. on the row headed 'field' I adapt your advice to state:

B1 Month: Format ([Birthday 1, mmm])

Syntax error: the field name itself should be delimited with square
brackets. You don't have a field named "Birthday 1, mmm"!

Use

B1 Month: Format([Birthday 1], "mmm")

which is what was suggested...

Also note that a table with fields Birthday 1, Birthday 2, Birthday 3
etc. is SIMPLY WRONG. If you have many people or many birthdays, use
*one* record each and use multiple records, not multiple fields.

John W. Vinson[MVP]