help : filed data to be months of year

[eddii]

i have a field name [monthofcal] and it contains data type as text and i store value like jan,feb,mar.... till dec.
but the problem is iam not able to retrive this for calculation purpose

like this
<= Month(Date())

what should be the data type so that its value should be jan,feb,mar.... till dec. and i should be able to use this in criteria in aquery
<= Month(Date())

thanks for helping

 

[Brianwarnock]

This is basically a duplicate post and you were told in the other thread that this is not the way to go, if it is too late now to adopt the sensible approach of storing a full date then you will have a complicated solution probably involving a conversion table and double column combo, although Switch might offer a solution.

Brian

 

[CBrighton]

You haven't said what you actually need help with, but I would strongly advise dropping the string olding the 3 digit month and adding a record creation date field instead (or whatever name suits the field)

As mentioned in your previous thread you can isolate any part of a date/time field, you can also perform calculations (add 10 days, minus 2 months, etc) without manually thinking things like "If I go later than Dec I need to increase the year".

You can even automate the change, just add a new date/time field and run a series of 12 update queries to check the existing field for "Jan" and set the new field to "01/01/2011" (or whatever date you want, if you only care about month then this should be fine for historic records).

 

[Brianwarnock]

You can even automate the change, just add a new date/time field and run a series of 12 update queries to check the existing field for "Jan" and set the new field to "01/01/2011" (or whatever date you want, if you only care about month then this should be fine for historic records).

That's a good point CB

Brian

 

[Brianwarnock]

Having now been back to the other thread I see that eddii doesn't want a full date stored in the table, why not? Users will never see it as they only see forms or reports when the Month can be shown using Format.

Brian

 

[eddii]

thanks cbrighton and Brian for the reply iam still weak in access i am learning now
can you pls tell me how to do it, because i dont really no how to do it in a query or using vba code or in a form

 

[CBrighton]

What's "it" that you want to do in a query? Again, the OP doesn't specify what you want help with, just that you are currently trying to evaluate strings as parts of dates.

Are we talking displaying a 3 character month from a date/time field or creating update queries?

 

[eddii]

brainwarnock i got your reply and it worked thanks for the solution but i have i problem now i have 1210 records which have only jan,feb,mar.... till dec data how will i be able transfer this data to a new field and use your trick so that i can use this * <= Month(Date()) * in the criteria of the query

 

[CBrighton]

Once you've added the new date/time field to the table create a new query.

Add the field which contains the month name and the field which will contain the date.

In the toolbar at the top there is a button with a drop-down arrow which will let you choose what type of query it is (update, make table, delete, append, etc), select update.

This should add an extra row to the query, something like "Update To".

Set the criteria on the old field (e.g. jan) and set the Update To row on the new field to 01/01/2011.

Run it (not view it in datasheet mode, there is a button with an exclamation mark in the toolbar to run it), then change the criteria to feb and the date field to 01/02/2011, and so on (if your regional settings do not use dd/mm/yyyy adjust accordingly).

 

[eddii]

cbrighton thanks i got the solution for the table but now the problem is i have stored the data in field whose data type is text and contains 1210 records if iam able to transfer this data to a new field name as [monthofcal] and data type as date/time and in form format i will give only month or " mmm" so that i need not type the data type again

pls bear with me sorry all of you

 

[CBrighton]

I got the solution for the table but now the problem is i have stored the data in field whose data type is text

I hate to disagree but the "solution for the table" was to add & populate a date/time field.

That makes the fact that you have the month stored as text moot as you can now use the date/time field.

Therefore I can only assume one of the following is the case:

1. You have not added & populated the new date/time field.
2. You have, but are continuing to use the text field instead of formatting the results of the date/time field.


It's still not really clear what you are asking for help with I'm afraid.

 

[eddii]

CBRIGHTON SORRY my reply was before i could see your path way to the solution i got it when i followed your latest reply and got 1210 records updated in two minutes to a new field

thanks for spending TIME, MONEY & BRAIN TO HELP ME

 

[CBrighton]

Glad you got it sorted.