Mid$ String function

[RelapseWizard]

Hi all
I'm trying to create an extra field in my table with a "month/year" data so its easy to group the data its a large amount by the way....this is the field I want to convert

"ARRIVAL_DATE_TIME"
19/06/2009 14:31:00

The new field would be
"ARRIVAL_DATE_MONTH"
06/2009 (mm/yyyy)

I've tried formating copying the formatted directly on to the new field but when I paste it reverts back....It's doing my nut in to say the least I know there is a way of updating the new field using Mid$[(ARRIVAL_DATE_TIME)1,3] dont think this works either as always your help is much appreciated.

Regards
ReeWiz

 

[namliam]

NO NO NO NO NO NO

You dont make an extra field to store the same information !

You can easily do this in a query without the need for an extra column in a table.

Just use the Format() function to do this.

Good luck !

 

[RelapseWizard]

The reason for keeping them seperate is the initial field ARRIVAL_DATE_TIME is unique to each individal so is required in the long run also the ovewriting it would create unimaginable problems hence creating another field would just make it easier to group in the database for reporting......

 

[Atomic Shrimp]

namliam is right. Just put a calculated column in your query:

ARRIVAL_DATE_MONTH: Format(ARRIVAL_DATE_TIME,"mm/yyyy")

(insert that expression in the top row of the query grid - then group on that calculated column)

If you store the value, you'll have to maintain the stored value, which is a headache (as you have already discovered), and will at some point go horribly wrong (which hasn't yet happened, but we're trying to spare you from)

 

[namliam]

also the ovewriting it would create unimaginable problems

Who said anything about overwriting??