The perfect Month/Year Date

[Bekah]

I want it all. Find, sort, use in joins, reports by month/year category. I want to only have one in custom function (i think). I want it to be reused for many things and mahy different dates.

I keep running into problems in this. I want:

112004

122004

012005

So it sorts right. But I can't get zeros. And I don't know if it should be number or text. I want to use this same date style for joins to real date fields but, of course, I can't. I wish I could have this perfect month/year as a Custom Functions so I don't have to redo it each time I need it, which is constantly in every table it seems. And I want it available to break apart to get year easily whenever I wish -- or the month to use in other calculations by looking at script parameters like I've been doing (a tad bit of) manually with some (limited) succees using Set Field on globals or in insert calculated results for finds. Or using the month in a Date(month( type calc. But using Left(moyearField; 2) only works if I always have two numbers for the month. I need to pin down how to do it. and use the same through all my program. and I don't know how best to do it.

ive read so much on dates I'm half blind. One post will solve one of my date problems but it causes another. I go in circles. I decide to solve it right, stick it in custom function and move on to other things. Is this possible?

[-Queue-]

You need to use Right in order to always have a leading zero, when applicable.

The calculations

Right( "0" & Month(datefield), 2 ) & Year(datefield) or

Right( "0" & Month(datefield) & Year(datefield), 6 )

will always produce a MMYYYY result.

Normally I would make it a text field due to the leading zero and it not being a field for use with numerical calculations. Since 7 will recognize the leading zero in a number field, however, it may be up to your preference. I haven't tested whether it will recognize a leading zero in a relationship key field, so be cautious.

If you have this calculation field in every table where it's applicable, joins to other tables/files based on this 'data style' should be no problem. You would just join the two calc fields instead of the date fields.

[RalphL]

I use the following calculation to get a YearMonth number which sorts well.

YearMonth = 100 * Year(datefield) + Month(datefield)

[cjaeger]

The calc is fast & works fine for sorting in a number field.

In a text field it works as well, unless you enter a date before 1000 bc ... or after 9999 bc.