Aumatically fill in current month validation rule.

[jagstangman]

Aumatically fill in current month default value

Hello,

I have been trying to get the current month automatically filled into a field (like with the Now() function but to show the current month only)

iv searched the forums and had no luck as well as trying a number of different ways to do it but havnt had much success.

If you can help thank you very much.
------
Steve

 

[boblarson]

Set the default to

=DatePart("m",Date)

 

[jagstangman]

Thanks or your reply bob but i couldnt get it to work. In the field it says #Error.

I have tried Month(Now()) as the defualt value but it shows the month in its numerical form (E.G. october is represented as 10) and i would like it as text,

is there any way to do this or do you know the problem with the code you provided.

 

[boblarson]

what is the field type for the field you wish to have as the default?

 

[jagstangman]

It is text becuase the months are in a look up table but iv tried it as date aswell

 

[boblarson]

Stick this in the Default property of your table's field:

=Format(DatePart("m",Date()),"mmmm")

I just tested it and it worked for me.

 

[raskew]

Try this from the debug (immediate) window (today is 20-Oct-06):

? Format(DatePart("m",Date()),"mmmm")
It returns:
January


Reason being: Your month number is being interpreted as a date, as stored internally by Access. You can see this from the debug (immediate) window with this:

? format(1, "short date") 12/31/99 'that's 1899. 
So when you attempt to return the month, you get "Dec" 
? format(2, "short date) returns 1/1/00 'that's 1900. Thus, all numbers 2 - 12 will return "Jan" when formatted as a month 
A workaround could be (example, you submit 11 expecting to return "Nov": 
? format(dateserial(year(date), 11,1), "mmm") Nov

Added:

An additional option might be the Choose() function. Example: x = 12 
? choose(x, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December") December

Added: This MSKB link describes how dates are stored in MS Access: http://support.microsoft.com/kb/q130514/

HTH - Bob

 

[boblarson]

This one will work too:

MonthName(DatePart("m",Date()))

 

[jagstangman]

This one will work too:

MonthName(DatePart("m",Date()))

Thanks for the help but I tried the MonthName but it comes up with an error when i try to save the changes to the table.


Unknown Function 'MonthName' in validation or default value on tablename.fieldname


Sorry for the trouble but i cannot get it to work.

 

[neileg]

Too complex!

Just use Date() to populate your field. In the form, change the format to show the month only. It is easier, and may well be more efficient to store the complete date.

 

[boblarson]

Could the reason

MonthName(DatePart("m",Date()))

didn't work, is that you forgot to put the equal sign before it in the default property? If you use it in the default property you have to preface it with an "=" sign. It worked for me in my table.

=MonthName(DatePart("m",Date()))

 

[boblarson]

If you still can't get it to work, can you post a stripped down version of your Db and I can take a look at it to try to figure out why it won't?