Autofill table columns

[melody]

Hi,

Is it possible to use a macro to fill the blank fields in
a column with the data in another column ?

Thanks in advance,
Melody

 

[Steve Schapel]

Melody,

Most likely this is not a job for a macro. You will probably need an
action query for this. But if you need more specific help, please
post back with more details, perhaps with an example, of what you are
really trying to do?

- Steve Schapel, Microsoft Access MVP

 

[Melody]

Hi Steve,

Thanks for pointing that out I have a table with
two fields (lets call them field X and field Y) which are
date/time datatypes. For the majority of records, field Y
is null while field X has a non-null value.

What I intended to do was to fill all the null values in Y
with the values in X.

Appreciate any help with this.

Thanks in advance,
Melody

 

[Steve Schapel]

Melody,

You can do this with an Update Query. Just put [field X] in the
Update To row of the field Y column in the query.

I must say, though, that doing this is almost certainly unnecessary,
in fact it is almost certainly a bad idea, and is likely based on a
misapprehension of some sort.

- Steve Schapel, Microsoft Access MVP

 

[Melody]

Steve,

May I have an example of such a query ? And why 'this is
almost certainly a bad idea' ?

Thanks in advance,
Melody

-----Original Message-----
Melody,

You can do this with an Update Query. Just put [field X] in the
Update To row of the field Y column in the query.

I must say, though, that doing this is almost certainly unnecessary,
in fact it is almost certainly a bad idea, and is likely based on a
misapprehension of some sort.

- Steve Schapel, Microsoft Access MVP

Hi Steve,

Thanks for pointing that out I have a table with
two fields (lets call them field X and field Y) which are
date/time datatypes. For the majority of records, field Y
is null while field X has a non-null value.

What I intended to do was to fill all the null values in Y
with the values in X.

Appreciate any help with this.

Thanks in advance,
Melody

.

 

[Steve Schapel]

Melody,

Example:
Make a query based on your table. Make it an Update Query (select
Update from the Query menu in the query design window). Enter FieldY
in the query design grid. In the Update To row of the grid, in the
FieldY column, put [fieldX] and then run the query by clicking the
toolbar button with the red ! icon.

Bad idea:
I can't really say anything very specific about this, because you have
not given any details that we can go on. But by putting the fieldX
data into fieldY, this data is being duplicated, which is never a good
idea in a database, which should always have each piece of information
stored in only one place at a time. I am happy to discuss further if
you would care to explain what the data is all about and what you are
trying to achieve.

- Steve Schapel, Microsoft Access MVP

Steve,

May I have an example of such a query ? And why 'this is
almost certainly a bad idea' ?

Thanks in advance,
Melody

-----Original Message-----
Melody,

You can do this with an Update Query. Just put [field X] in the
Update To row of the field Y column in the query.

I must say, though, that doing this is almost certainly unnecessary,
in fact it is almost certainly a bad idea, and is likely based on a
misapprehension of some sort.

- Steve Schapel, Microsoft Access MVP

Hi Steve,

Thanks for pointing that out I have a table with
two fields (lets call them field X and field Y) which are
date/time datatypes. For the majority of records, field Y
is null while field X has a non-null value.

What I intended to do was to fill all the null values in Y
with the values in X.

Appreciate any help with this.

Thanks in advance,
Melody

.

 

[Melody]

-----Original Message-----
Melody,

Example:
Make a query based on your table. Make it an Update Query (select
Update from the Query menu in the query design window). Enter FieldY
in the query design grid. In the Update To row of the grid, in the
FieldY column, put [fieldX] and then run the query by clicking the
toolbar button with the red ! icon.

It works. I also added 'is null' as criteria to
prevent the non-null values of Field X from being replaced.

Bad idea:
I can't really say anything very specific about this, because you have
not given any details that we can go on. But by putting the fieldX
data into fieldY, this data is being duplicated, which is never a good
idea in a database, which should always have each piece of information
stored in only one place at a time. I am happy to discuss further if
you would care to explain what the data is all about and what you are
trying to achieve.

- Steve Schapel, Microsoft Access MVP

Thanks, I appreciate any advice on this situation :

I have a table of invoices with two date/time fields named
VerifiedDT and EffectiveDate. VerifiedDate is the date an
invoice is verified, while EffectiveDate is the data entry
date of an invoice. Whether the value of VerifiedDate and
EffectiveDate is the same or not is pretty much subject to
when the user has the time to key in invoices. This means
that an invoice can be verified and keyed in on the same
date or verified and keyed in on different dates.

The problem was, the use of EffectiveDate was only
introduced a few months ago, meaning that the Effective
Date for all invoice records that were keyed in _prior_ to
that time are null. What I was trying to do was to fill up
all the null values in the EffectiveDate field with
VerifiedDate (since the actual EffectiveDate of 700
records or so are quite impossible to trace).
The reason I needed to do this is so I can query based on
EffectiveDate.

Thanks in advance,
melody

Steve,

May I have an example of such a query ? And why 'this is
almost certainly a bad idea' ?

Thanks in advance,
Melody

-----Original Message-----
Melody,

You can do this with an Update Query. Just put [field

X]
in the

Update To row of the field Y column in the query.

I must say, though, that doing this is almost certainly unnecessary,
in fact it is almost certainly a bad idea, and is

likely
based on a

misapprehension of some sort.

- Steve Schapel, Microsoft Access MVP

wrote:

Hi Steve,

Thanks for pointing that out I have a table with
two fields (lets call them field X and field Y) which are
date/time datatypes. For the majority of records,

field
Y

is null while field X has a non-null value.

What I intended to do was to fill all the null values

in
Y

with the values in X.

Appreciate any help with this.

Thanks in advance,
Melody

.

.

 

[Steve Schapel]

Melody,

Thanks for the further explanation.

All is forgiven Now that I see what you have done and why, I
agree that it is acceptable. I had assumed you were making a mistake
that many people commonly make, but I was wrong. Best wishes for your
future endeavors with Access.

- Steve Schapel, Microsoft Access MVP

 

[melody]

Thanks for looking into the situation

Melody