Constructed field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

I'm new at Access so please forgive me if this is a really basic question...
I have a mdb file with more than 1million datapoints. The database file has a
field which is constructed has the combination of month and year, ie, if a
given observation is from April 1990, it will have the value "41990". I need
to extract data from this database but in such a way that the month and year
are in diferente fields: I need a "month" with just the "4" and another field
"ye" with "1990". i can do this in excel quite easily, but given the number
of observations, Excel can't treat everything. Does anyone knows how to do
this in Access?

Regards,

Nuno
 
First, I think I would be putting "041990" so that you could easily pull out
the parts of the fiel. Even better, since these are two separate pieces of
data, I would go back and make two fields ObservationMonth and
ObservationYear. You could then build an update query to pull the
appropriate data from the CombinedObservation field an put it in the proper
fields.

If you had used a six-digit number, I know you could use
Left([ObservationDate],2) and
Right([ObservationDate],4) since your fields size varies, I am nor sure
how you will pull out the month.

Hopefully someone else will have a magical formula to grab the left one or
two digits.

Rick B
 
Hi
I've managed to do this with:
Right([ObsDate],4) for the year and
IIf(Len(Trim([ObservationDate]))=5,Left([ObservationDate],1),Left([ObservationDate],2))
for the month

Hope this helps.
--
Andy.


Rick B said:
First, I think I would be putting "041990" so that you could easily pull
out
the parts of the fiel. Even better, since these are two separate pieces
of
data, I would go back and make two fields ObservationMonth and
ObservationYear. You could then build an update query to pull the
appropriate data from the CombinedObservation field an put it in the
proper
fields.

If you had used a six-digit number, I know you could use
Left([ObservationDate],2) and
Right([ObservationDate],4) since your fields size varies, I am nor sure
how you will pull out the month.

Hopefully someone else will have a magical formula to grab the left one or
two digits.

Rick B



Nuno said:
Hi everyone,

I'm new at Access so please forgive me if this is a really basic question...
I have a mdb file with more than 1million datapoints. The database file has a
field which is constructed has the combination of month and year, ie, if
a
given observation is from April 1990, it will have the value "41990". I need
to extract data from this database but in such a way that the month and year
are in diferente fields: I need a "month" with just the "4" and another field
"ye" with "1990". i can do this in excel quite easily, but given the number
of observations, Excel can't treat everything. Does anyone knows how to
do
this in Access?

Regards,

Nuno
Click to expand...
Click to expand...
 
Back
Top