Adding Data to a Field based on another Field in a Table

[lreecher]

Data is pulled by random month. In the resulting table there are 2 fields of
information I want to work with. (see below) The Month field is created by
formatting the DateSold date. What I need to do is the populate the Month
field where it is null with the same mmm yyyy as wheen the DateSold field has
a date. This one really has me stumped. Thanks for any help provided.

DateSold Month
5/1/2007 May 2007
5/23/2007 May 2007
Is null Is null
5/16/2007 May 2007
Is null Is null

Results
DateSold Month
5/1/2007 May 2007
5/23/2007 May 2007
Is null May 2007
5/16/2007 May 2007
Is null May 2007

 

[KARL DEWEY]

What I need to do is the populate the Month field where it is null with the
same mmm yyyy as when the DateSold field has a date.

If DateSold is null where will it pull a date from?

 

[lreecher]

That is the dilemma.

Of course, I could do this simply by using an update query. However, the
audience using the db will not have access to run queries of any sort except
when a form is based on a query. This would be a part of the data prep macro.
They will only be able to use forms and switchboards.

I was thinking an IIf statement might work something like if DateSold is
null then format(Month) where DateSold is not null. Needless to say, it
didn't work.

Do you thinnk this can be done?

 

[KARL DEWEY]

You can not format a date from a null. Or I missing something?

You said that if the DateSold field had no date (Null) then format DateSold
as "mmm yyyy" but a null has no information to format.

 

[lreecher]

Format the 'Month' as 'mmm yyyy' based on DateSold where the value is not
null. Sorry for the confusion.

 

[Dale Fye]

The issue is what value do you want in the MONTH colum if DateSold is blank?
If it is the current month, then you should be able to do something like:

SoldMonth: NZ([DateSold], Format(Date(), "mmm yyyy")

BTW, I would not actually name a field Month, this is a reserved word in
Access and it is advisable not to use reserved words as field names.

Still not sure why you would want this field filled in at all if the
DateSold is empty?

HTH
Dale

 

[lreecher]

It is a customer requirement. What I want to do is identify each record with
a Month value based on the month the data is pulled by DateSold. (I just used
DateSold and Month as placeholder names for the example.) In a macro, a qry
runs that asks for a month's date range (ex: between 1/1/2007 and 1/31/2007)
and then any values that are null. So what I want to do based on the dates in
the DateSold field is add the mmm yyyy to each record in the Month field.
This needs to be done without admin intervention preferably a step in a
macro.

The issue is what value do you want in the MONTH colum if DateSold is blank?
If it is the current month, then you should be able to do something like:

SoldMonth: NZ([DateSold], Format(Date(), "mmm yyyy")

BTW, I would not actually name a field Month, this is a reserved word in
Access and it is advisable not to use reserved words as field names.

Still not sure why you would want this field filled in at all if the
DateSold is empty?

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Format the 'Month' as 'mmm yyyy' based on DateSold where the value is not
null. Sorry for the confusion.