I'm still learning a lot of Access 2002 features so this is probably a question with an easy solution.

I am trying to set my default date on a date field in my Access table to be today. I used Date() and that works fine. As a validation rule, I am trying to allow the date entered to be any day between today and 20 days before today (since this date refers to an order date and not all orders were processed the day they are entered in the database).

I used >=Now()-20 and it seems to work okay except that testing that rule today (1/30/2006) with 1/10/2006 as the date it won't allow it to be entered. I thought the = part of the rule would include 20 days ago. 1/11/2006 - 1/30/2006 are allowed but 1/10/2006 is not.

Aside from wondering why my day is off by 1, I was trying to figure out how to setup this rule using Date() instead of Now(). Since Now() returns the time (which I'm not too interested in...but I'm assuming could have something to do with my date selection being off by one) I'd prefer to use Date() but I don't know the format.

In other words, >=Date()-20 doesn't work and I know its because I somehow have to specify that I am interested in the dd portion - 20, not the month or year (which makes me wonder how Now()-20 knows the -20 refers to the day and not the month, year or time).

Can anyone tell me how to have this rule set using Date() instead of Now()?

I went in and made a new rule for my date field mentioned in the above post:

Between Date() And Date()-20

It seems to work so I suppose I may have answered my own question. Can anyone shed light on why Now()-20 was not allowing 1/10/2006. Also, can anyone explain how Access knows Date()-20 refers to the day and not the month or year? I'm assuming that's the default but say I was interested in any date between today and 20 years ago or 20 months ago. That's not what I need for this table but I am trying to gain a better understanding of how the date() now() functions work.

As it turns out, I have come across another field in my table that relates to my previous question(s). In this case I have a field "Report Month" and I would like to set the default value to the current month but in the form (YY)_Month

In other words if I input a new record today, I want the default value to be: (06)_January

I know that when I learn VB scripts this will probably be much easier but is there a way to do this with the built in functions in the Access library?

1. The reason DATE worked where NOW didn't is that NOW includes the time. So if the date is represented by an integer, let's use 12345 as an example, then if you call NOW at mid-day the value would be 12345.5 where the point five represents the half a day that's elapsed since midnight. 12345.75 would be 6pm on that day.

2. You can get the default value you want with this code:

"(" & Format(Date(),"yy") & ")_" & Format(Date(),"mmmm")

Thanks for the info it makes sense to me now. I appreciate your help.