Automatic Update Query?

[Guest]

I maintain a database for a non-profit and am self-taught. One of the
monthly reports is titled Birthdays. The birthday field is a text field
(mmdd). Previously, the user would enter the beginning and ending mmdd to
include (so 0101 and 0131). They've now asked that they just be able to
enter the month name rather than the range of dates.

I've performed an update query to add a field titled bday month and converts
the dates to month name. The query and report work fine when the user types
in the month name.

The Question: how do I automatically update the bday month field each time a
new record is entered with only the birthdate?

And, since I'm self-taught, I don't know VB.

Any help would be appreciated. Thanks!

 

[Rick B]

You are duplicating data here. You have a field with month and date, and
another field with month. Why not just have two fields? One with month,
and one with date? Or better, use a real date field?

Using date fields allows yo uto utilize built-in functions to pull out the
month or the date and allows "between" logic to be used.

Rick B

 

[Guest]

Is there an easy way to convert the existing text fields to a date field
without losing data. The data was maintained in Excel previously and so when
it was converted to Access, it was converted as text.

 

[LeAnne]

Is there an easy way to convert the existing text fields to a date field
without losing data. The data was maintained in Excel previously and so when
it was converted to Access, it was converted as text.

PMFJI -

Use the DateValue() function. I've found that the easiest way to do this
is to :

- rename the old date field in your table (something like "TextDate")
- create a new field in your table, of Date/Time datatype, named
something like "TrueDate"
- create an Update Query to populate the TrueDate field:
UPDATE MyTable
SET TrueDate=DateValue([TextDate])
WHERE TrueDate IS NULL;

hth,

LeAnne

 

[Rick B]

I would build an append query (I just tested this) and put the following in
the "update to" field....

Left([somedate],2) & "/" & Right([somedate],2) & "/1900"

After running it, all the fields will be updated to the correct format and
will have a year of 1900 (since you don't have the birth year, you will have
to pick an arbitrary year and always use it).

Then, open your table in design-view and change the field type to
date/teime.


In your data entry form, you will want to make sure that all future entries
use 1900 as the year (for consistency).

I'm not sure this is the absolute best way to do this, since you would
normally store the year as well.

You might wait and see if you get any other comments from the MVPs before
you process that query.

Rick B

 

[Rick B]

LeAnne:

Will that work if you only have a four-digit entry? That was my first
thoght, but did not think it would know what to do with 0906 or 1002.

Rick B

Is there an easy way to convert the existing text fields to a date field
without losing data. The data was maintained in Excel previously and so when
it was converted to Access, it was converted as text.

PMFJI -

Use the DateValue() function. I've found that the easiest way to do this
is to :

- rename the old date field in your table (something like "TextDate")
- create a new field in your table, of Date/Time datatype, named
something like "TrueDate"
- create an Update Query to populate the TrueDate field:
UPDATE MyTable
SET TrueDate=DateValue([TextDate])
WHERE TrueDate IS NULL;

hth,

LeAnne

 

[Guest]

Thanks both Rick B and LeAnne. As I recall, that might have been another
reason that the date was converted as text ---- just the month and day, no
year. I'll see if anyone else has any suggestions.

I would build an append query (I just tested this) and put the following in
the "update to" field....

Left([somedate],2) & "/" & Right([somedate],2) & "/1900"

After running it, all the fields will be updated to the correct format and
will have a year of 1900 (since you don't have the birth year, you will have
to pick an arbitrary year and always use it).

Then, open your table in design-view and change the field type to
date/teime.


In your data entry form, you will want to make sure that all future entries
use 1900 as the year (for consistency).

I'm not sure this is the absolute best way to do this, since you would
normally store the year as well.

You might wait and see if you get any other comments from the MVPs before
you process that query.

Rick B

Is there an easy way to convert the existing text fields to a date field
without losing data. The data was maintained in Excel previously and so when
it was converted to Access, it was converted as text.

 

[John Spencer (MVP)]

Try the following as the criteria against the Birthday field. Easiest would be
to enter just the month number

Criteria: Between Format(DateSerial(Year(Date()), [Month Number],1),"ddmm")
AND Format(DateSerial(Year(Date()), [Month Number]+1,0),"ddmm")


If you really need to use the month name then it is a lot more complicated. It
would be something like the following untested criteria.
Field: Birthday
Criteria: Between Format(CDate([Month Name] & " 1, " & Year(Date())),"mmdd")
AND Format(DateSerial(Year(CDate([Month Name] & "1, " & Year(Date()))),
Month(CDate([Month Name] & "1, " & Year(Date()))) +1,0)),"mmdd")

 

[tina]

PMFJI...

i agree with Rick about adding an additional field to the table with a
"month" value - redundant data; and the additional problem you ran into -
how to enter a value in that field when a record is added (or the birthday
value is changed in an existing record). if you do want to change your
birthday field to a date/time data type, then Rick, LeAnne and the MVPs will
come up with a solution for you, i'm certain.

if you want to stick with the birthday field as mmdd text, then how about
adding a calculated control to the query that underlies the monthly report,
as

BdayMonth:
Choose(CInt(Left([BirthdayFieldName],2)),"January","February","March",<conti
nuing on to>,"December")

then set the criteria on this calculated field.

hth

 

[LeAnne]

LeAnne:

Will that work if you only have a four-digit entry? That was my first
thoght, but did not think it would know what to do with 0906 or 1002.

Rick B

Ah. Thanks, I didn't spot that part; guess I should have read the
original post more closely. In that case, DB could use DateSerial() to
parse the string into an actual date. Now, DateSerial still requires
year, month, AND day as arguments (it's that double-precision
floating-point requirement).Something like:

DateSerial(Year(Date()),Left([TextDate],2),Right([TextDate],2)

....which would give each birthdate the current year (I'm assuming that
either birth year is immaterial, and/or the actual birth years were not
included when the date was changed to text & imported). But I agree
with your earlier observation that just a plain Date field would be a
better choice for the table. Month and day can easily be extracted via a
query using, say, DatePart().

LeAnne

 

[Guest]

Tina - great suggestion! I placed the following in the field name for the
query underlying my birthday report.

Month: Choose(CInt(Left([Member
Information]![Bday],2)),"January","February","March","April","May","June","July","August","September","October","November","December")

It worked like a charm but...... I've got [Enter Month] in the Criteria
field so that I can select which birthdays I want to include. I get a return
message that says "Invalid Use of Null". Any suggestions?

PMFJI...

i agree with Rick about adding an additional field to the table with a
"month" value - redundant data; and the additional problem you ran into -
how to enter a value in that field when a record is added (or the birthday
value is changed in an existing record). if you do want to change your
birthday field to a date/time data type, then Rick, LeAnne and the MVPs will
come up with a solution for you, i'm certain.

if you want to stick with the birthday field as mmdd text, then how about
adding a calculated control to the query that underlies the monthly report,
as

BdayMonth:
Choose(CInt(Left([BirthdayFieldName],2)),"January","February","March",<conti
nuing on to>,"December")

then set the criteria on this calculated field.

hth

Is there an easy way to convert the existing text fields to a date field
without losing data. The data was maintained in Excel previously and so when
it was converted to Access, it was converted as text.

 

[tina]

hmm, i thought you might get that error if any record did not have a value
entered in the birthday field, but i wasn't able to duplicate the error.
nevertheless, try this, just in case

BdayMonth: Choose(CInt(Nz(Left([Member
Information]![Bday],2),0)),"January","February","March","April","May","June"
,"July","August","September","October","November","December")

if above doesn't work, suggest you post the SQL from the query, so we can
look at it. also, i'm wondering a couple things: is your query multi-table?
and if so, do you have a field named Bday in more than one of the query
tables? if not, why include the table name in the calculated field? and how
are your users opening the report? from the database window? or from a form?
if from a form, can you add an unbound combo box listing the months of the
year, to the form, and use that as the query criteria? that way your users
don't have to type in the entire month name, or worry about spelling, and we
could make the calculated field in the query much, much simpler, too.

btw, suggest you don't use "Month" as the name of your calculated field
because that is an Access Reserved Word, and may cause problems. that's why
i used a prefix with "Month" in my previous post and in this one.

hth

Tina - great suggestion! I placed the following in the field name for the
query underlying my birthday report.

Month: Choose(CInt(Left([Member
Information]![Bday],2)),"January","February","March","April","May","June","J
uly","August","September","October","November","December")

It worked like a charm but...... I've got [Enter Month] in the Criteria
field so that I can select which birthdays I want to include. I get a return
message that says "Invalid Use of Null". Any suggestions?

PMFJI...

i agree with Rick about adding an additional field to the table with a
"month" value - redundant data; and the additional problem you ran into -
how to enter a value in that field when a record is added (or the birthday
value is changed in an existing record). if you do want to change your
birthday field to a date/time data type, then Rick, LeAnne and the MVPs will
come up with a solution for you, i'm certain.

if you want to stick with the birthday field as mmdd text, then how about
adding a calculated control to the query that underlies the monthly report,
as

BdayMonth:
Choose(CInt(Left([BirthdayFieldName],2)),"January","February","March",<conti
nuing on to>,"December")

then set the criteria on this calculated field.

hth

Is there an easy way to convert the existing text fields to a date field
without losing data. The data was maintained in Excel previously and

so
when

it was converted to Access, it was converted as text.

:

You are duplicating data here. You have a field with month and

date,
and

another field with month. Why not just have two fields? One with month,
and one with date? Or better, use a real date field?

Using date fields allows yo uto utilize built-in functions to pull

out
the

month or the date and allows "between" logic to be used.

Rick B


I maintain a database for a non-profit and am self-taught. One of the
monthly reports is titled Birthdays. The birthday field is a text field
(mmdd). Previously, the user would enter the beginning and ending mmdd to
include (so 0101 and 0131). They've now asked that they just be

able
to

enter the month name rather than the range of dates.

I've performed an update query to add a field titled bday month and
converts
the dates to month name. The query and report work fine when the user
types
in the month name.

The Question: how do I automatically update the bday month field

each
time

a
new record is entered with only the birthdate?

And, since I'm self-taught, I don't know VB.

Any help would be appreciated. Thanks!

 

[Guest]

To answer a couple of your questions - I ended up including the table name
because I couldn't get it to work until I added it. As far as opening the
report, I've built a switchboard with this as one of the options. Basically,
it'll just run the report. I like the idea of the unbound combo box though
so. My knowledge here is limited so let me play around and I'll get back
with you.

Thanks much!!!

hmm, i thought you might get that error if any record did not have a value
entered in the birthday field, but i wasn't able to duplicate the error.
nevertheless, try this, just in case

BdayMonth: Choose(CInt(Nz(Left([Member
Information]![Bday],2),0)),"January","February","March","April","May","June"
,"July","August","September","October","November","December")

if above doesn't work, suggest you post the SQL from the query, so we can
look at it. also, i'm wondering a couple things: is your query multi-table?
and if so, do you have a field named Bday in more than one of the query
tables? if not, why include the table name in the calculated field? and how
are your users opening the report? from the database window? or from a form?
if from a form, can you add an unbound combo box listing the months of the
year, to the form, and use that as the query criteria? that way your users
don't have to type in the entire month name, or worry about spelling, and we
could make the calculated field in the query much, much simpler, too.

btw, suggest you don't use "Month" as the name of your calculated field
because that is an Access Reserved Word, and may cause problems. that's why
i used a prefix with "Month" in my previous post and in this one.

hth

Tina - great suggestion! I placed the following in the field name for the
query underlying my birthday report.

Month: Choose(CInt(Left([Member
Information]![Bday],2)),"January","February","March","April","May","June","J
uly","August","September","October","November","December")

It worked like a charm but...... I've got [Enter Month] in the Criteria
field so that I can select which birthdays I want to include. I get a return
message that says "Invalid Use of Null". Any suggestions?

PMFJI...

i agree with Rick about adding an additional field to the table with a
"month" value - redundant data; and the additional problem you ran into -
how to enter a value in that field when a record is added (or the birthday
value is changed in an existing record). if you do want to change your
birthday field to a date/time data type, then Rick, LeAnne and the MVPs will
come up with a solution for you, i'm certain.

if you want to stick with the birthday field as mmdd text, then how about
adding a calculated control to the query that underlies the monthly report,
as

BdayMonth:
Choose(CInt(Left([BirthdayFieldName],2)),"January","February","March",<conti
nuing on to>,"December")

then set the criteria on this calculated field.

hth


Is there an easy way to convert the existing text fields to a date field
without losing data. The data was maintained in Excel previously and so
when
it was converted to Access, it was converted as text.

:

You are duplicating data here. You have a field with month and date,
and
another field with month. Why not just have two fields? One with
month,
and one with date? Or better, use a real date field?

Using date fields allows yo uto utilize built-in functions to pull out
the
month or the date and allows "between" logic to be used.

Rick B


I maintain a database for a non-profit and am self-taught. One of the
monthly reports is titled Birthdays. The birthday field is a text
field
(mmdd). Previously, the user would enter the beginning and ending
mmdd to
include (so 0101 and 0131). They've now asked that they just be able
to
enter the month name rather than the range of dates.

I've performed an update query to add a field titled bday month and
converts
the dates to month name. The query and report work fine when the user
types
in the month name.

The Question: how do I automatically update the bday month field each
time
a
new record is entered with only the birthdate?

And, since I'm self-taught, I don't know VB.

Any help would be appreciated. Thanks!

 

[Guest]

For now, I tried adding the Nz (which I'm assuming tells it what to do with a
null value) and it worked fine. I'll leave like this for now and when I have
more time, play with changing it to a form so that I can do the combo box
thing.

Thanks again!

hmm, i thought you might get that error if any record did not have a value
entered in the birthday field, but i wasn't able to duplicate the error.
nevertheless, try this, just in case

BdayMonth: Choose(CInt(Nz(Left([Member
Information]![Bday],2),0)),"January","February","March","April","May","June"
,"July","August","September","October","November","December")

if above doesn't work, suggest you post the SQL from the query, so we can
look at it. also, i'm wondering a couple things: is your query multi-table?
and if so, do you have a field named Bday in more than one of the query
tables? if not, why include the table name in the calculated field? and how
are your users opening the report? from the database window? or from a form?
if from a form, can you add an unbound combo box listing the months of the
year, to the form, and use that as the query criteria? that way your users
don't have to type in the entire month name, or worry about spelling, and we
could make the calculated field in the query much, much simpler, too.

btw, suggest you don't use "Month" as the name of your calculated field
because that is an Access Reserved Word, and may cause problems. that's why
i used a prefix with "Month" in my previous post and in this one.

hth

Tina - great suggestion! I placed the following in the field name for the
query underlying my birthday report.

Month: Choose(CInt(Left([Member
Information]![Bday],2)),"January","February","March","April","May","June","J
uly","August","September","October","November","December")

It worked like a charm but...... I've got [Enter Month] in the Criteria
field so that I can select which birthdays I want to include. I get a return
message that says "Invalid Use of Null". Any suggestions?

PMFJI...

i agree with Rick about adding an additional field to the table with a
"month" value - redundant data; and the additional problem you ran into -
how to enter a value in that field when a record is added (or the birthday
value is changed in an existing record). if you do want to change your
birthday field to a date/time data type, then Rick, LeAnne and the MVPs will
come up with a solution for you, i'm certain.

if you want to stick with the birthday field as mmdd text, then how about
adding a calculated control to the query that underlies the monthly report,
as

BdayMonth:
Choose(CInt(Left([BirthdayFieldName],2)),"January","February","March",<conti
nuing on to>,"December")

then set the criteria on this calculated field.

hth


Is there an easy way to convert the existing text fields to a date field
without losing data. The data was maintained in Excel previously and so
when
it was converted to Access, it was converted as text.

:

You are duplicating data here. You have a field with month and date,
and
another field with month. Why not just have two fields? One with
month,
and one with date? Or better, use a real date field?

Using date fields allows yo uto utilize built-in functions to pull out
the
month or the date and allows "between" logic to be used.

Rick B


I maintain a database for a non-profit and am self-taught. One of the
monthly reports is titled Birthdays. The birthday field is a text
field
(mmdd). Previously, the user would enter the beginning and ending
mmdd to
include (so 0101 and 0131). They've now asked that they just be able
to
enter the month name rather than the range of dates.

I've performed an update query to add a field titled bday month and
converts
the dates to month name. The query and report work fine when the user
types
in the month name.

The Question: how do I automatically update the bday month field each
time
a
new record is entered with only the birthdate?

And, since I'm self-taught, I don't know VB.

Any help would be appreciated. Thanks!

 

[Marshall Barton]

I'll leave like this for now and when I have
more time, play with changing it to a form
so that I can do the combo box thing.

Famous last words! ;-)

Sorry, but I couldn't resist. Can't count the number of
times I've said that and don't even remember what I promised
to get back to.

 

[tina]

to use the unbound combo box solution (on your switchboard, or on another
form if you'd rather), set the combo box properties as

RowSourceType: Value List
Row Source:
01;Jan;02;Feb;03;Mar;04;Apr;05;May;06;Jun;07;Jul;08;Aug;09;Sep;10;Oct;11;Nov
;12;Dec
ColumnCount: 2
ColumnWidths: 0";0.75"
BoundColumn: 1
ListRows: 12
ListWidth: 1"
LimitToList: Yes
AutoExpand: Yes

change the query's calculated field to

BdayMonth: Nz(Left([Member Information]![Bday],2),"00")

and set the criteria for this field to

Forms!NameOfForm!NameOfComboBox

hth

To answer a couple of your questions - I ended up including the table name
because I couldn't get it to work until I added it. As far as opening the
report, I've built a switchboard with this as one of the options. Basically,
it'll just run the report. I like the idea of the unbound combo box though
so. My knowledge here is limited so let me play around and I'll get back
with you.

Thanks much!!!

hmm, i thought you might get that error if any record did not have a value
entered in the birthday field, but i wasn't able to duplicate the error.
nevertheless, try this, just in case

BdayMonth: Choose(CInt(Nz(Left([Member
Information]![Bday],2),0)),"January","February","March","April","May","June"
,"July","August","September","October","November","December")

if above doesn't work, suggest you post the SQL from the query, so we can
look at it. also, i'm wondering a couple things: is your query multi-table?
and if so, do you have a field named Bday in more than one of the query
tables? if not, why include the table name in the calculated field? and how
are your users opening the report? from the database window? or from a form?
if from a form, can you add an unbound combo box listing the months of the
year, to the form, and use that as the query criteria? that way your users
don't have to type in the entire month name, or worry about spelling, and we
could make the calculated field in the query much, much simpler, too.

btw, suggest you don't use "Month" as the name of your calculated field
because that is an Access Reserved Word, and may cause problems. that's why
i used a prefix with "Month" in my previous post and in this one.

hth

Tina - great suggestion! I placed the following in the field name for the
query underlying my birthday report.

Month: Choose(CInt(Left([Member

Information]![Bday],2)),"January","February","March","April","May","June","J
uly","August","September","October","November","December")

It worked like a charm but...... I've got [Enter Month] in the Criteria
field so that I can select which birthdays I want to include. I get a return
message that says "Invalid Use of Null". Any suggestions?


:

PMFJI...

i agree with Rick about adding an additional field to the table with a
"month" value - redundant data; and the additional problem you ran into -
how to enter a value in that field when a record is added (or the birthday
value is changed in an existing record). if you do want to change your
birthday field to a date/time data type, then Rick, LeAnne and the

MVPs
will

come up with a solution for you, i'm certain.

if you want to stick with the birthday field as mmdd text, then how about
adding a calculated control to the query that underlies the monthly report,
as

BdayMonth:

Choose(CInt(Left([BirthdayFieldName],2)),"January","February","March",<conti

nuing on to>,"December")

then set the criteria on this calculated field.

hth


Is there an easy way to convert the existing text fields to a date field
without losing data. The data was maintained in Excel previously

and
so

when
it was converted to Access, it was converted as text.

:

You are duplicating data here. You have a field with month and date,
and
another field with month. Why not just have two fields? One with
month,
and one with date? Or better, use a real date field?

Using date fields allows yo uto utilize built-in functions to

pull
out

the
month or the date and allows "between" logic to be used.

Rick B


I maintain a database for a non-profit and am self-taught.

One of
the

monthly reports is titled Birthdays. The birthday field is a text
field
(mmdd). Previously, the user would enter the beginning and ending
mmdd to
include (so 0101 and 0131). They've now asked that they just

be
able

to
enter the month name rather than the range of dates.

I've performed an update query to add a field titled bday

month
and

converts
the dates to month name. The query and report work fine when

the
user

types
in the month name.

The Question: how do I automatically update the bday month

field
each

time
a
new record is entered with only the birthdate?

And, since I'm self-taught, I don't know VB.

Any help would be appreciated. Thanks!