Re: Automatic Update Query?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: tina (nospam_at_address.com)
Date: 12/08/04 

Date: Wed, 08 Dec 2004 20:16:43 GMT

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

"Dumb Blonde" <DumbBlonde@discussions.microsoft.com> wrote in message
news:439A39DD-16E1-4515-BF7F-B562E6FD55BE@microsoft.com...
> 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?
>
>
> "tina" wrote:
>
> > 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
> >
> >
> > "Dumb Blonde" <DumbBlonde@discussions.microsoft.com> wrote in message
> > news:0FA22F0F-CEE1-4F7E-9B92-6F1AC98D26D1@microsoft.com...
> > > 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.
> > >
> > > "Rick B" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "Dumb Blonde" <DumbBlonde@discussions.microsoft.com> wrote in
message
> > > > news:24EF5149-FFC9-4AEF-8D8C-419DF3BE66EB@microsoft.com...
> > > > > 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!
> > > >
> > > >
> > > >
> >
> >
> >

Relevant Pages

  • Re: Automatic Update Query?
    ... > entered in the birthday field, but i wasn't able to duplicate the error. ... > if above doesn't work, suggest you post the SQL from the query, so we can ... > could make the calculated field in the query much, much simpler, too. ... >> query underlying my birthday report. ...
    (microsoft.public.access.queries)
  • Re: Selecting only one month for a report
    ... Selecting only one month for a reportLets say your birthdate field is called ... I've just done one of these Birthday database myself and have worked out ... I have a birthday report from a query "Birthdates" and my report include all ...
    (microsoft.public.access.gettingstarted)
  • Re: list of birthdays
    ... Create a query to use as the source of the report. ... Type this into the Field row of your query: ... > my database that has a birthday that month. ...
    (microsoft.public.access.forms)
  • Re: displaying multiple dates in a table
    ... If you cannot do that then you would need to build a fairly complex query ... that as the source of your birthday query. ... Sample Union query - ... display per month... ...
    (microsoft.public.access.queries)
  • Re: Number of days to birthday
    ... I have a query that is supposed to have a calculated field that show the ... Assuming you have a field named Birthday in your table, ... Your calculation ...
    (microsoft.public.access.formscoding)