Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 30th, 2006, 09:45 AM
Registered User
 
Join Date: Jan 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date Validation for Access Table

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()?



 
Old January 30th, 2006, 09:56 AM
Registered User
 
Join Date: Jan 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old January 30th, 2006, 10:55 AM
Registered User
 
Join Date: Jan 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
 
Old January 30th, 2006, 04:01 PM
Authorized User
 
Join Date: Dec 2005
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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")
 
Old February 3rd, 2006, 02:26 PM
Registered User
 
Join Date: Jan 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Date validation vacak Struts 0 November 21st, 2006 12:11 PM
Date Validation ramesh055 ASP.NET 1.0 and 1.1 Professional 1 November 15th, 2006 11:09 AM
Date Validation surendran Javascript How-To 3 February 25th, 2005 07:40 AM
Date validation lily611 General .NET 4 September 30th, 2004 02:56 AM
Date validation Raul Javascript 4 February 25th, 2004 04:04 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.