In MS Access 2007, I am having difficulty with the syntax for a field validation using a date range for current year.
For example, >= #1/1/2013# And <#1/1/2014# will prevent users from entering a date before 1/1/2013 and after 1/1/2014.
The problem is I need this to transfer from year to year without updating the year, so I would need something like >="1/1/" & format(date(),"yyyy") and <="12/31/" & format(date(),"yyyy"). But I cannot seem to get this to work.
I have tried using the validation on the form in a before BeforeUpdate(Cancel As Integer) sub, but I get a validation error. Help!
13 4956
While I have never done this before, this is what I would try. In the form's OnLoad event put the following (assuming the textbox's name is txtMyDate). - Me.txtMyDate.ValidationRule = ">=#1/1/" & Year(Date()) & _
-
"# AND <#1/1/" & Year(DateAdd("yyyy", 1, Date())) & "#"
This should put in all the parts that you need.
Okay, I see where I was messing up. I knew I needed the pound sign, but I was not sure where to put it inside the quotes. Thank you I will try this!
I tested the string and it does come out to the correct values, but I have never tried to set a field's validation rule this way so I can't guaranty that this will work.
ADezii 8,834
Recognized Expert Expert
Personally, I prefer to handle this as hard coded in a Table Validation Rule. Assuming the DATE Field in your Table is named [MyDate], then the Table Level Validation Rule would be: - [MyDate]>=DateSerial(Year(Date()),1,1) And [MyDate]<=DateSerial(Year(Date()),12,31)
P.S. - Do not forget to also enter a meaningful Validation Text.
The following works perfectly for the Validation Rule on the form field. Thanks so much for all the help! - >=DateSerial(Year(Date()),1,1) And <=DateSerial(Year(Date()),12,31)
Thats nice to know ADezii. I haven't used DateSerial yet and wasn't in my "bag of tricks", but it is now. Nice to know how/when to use it.
ADezii 8,834
Recognized Expert Expert @Seth Schrock:
We all learn something new every day, don't we Seth?
One of the things I love about this site and group of people!
nico5038 3,080
Recognized Expert Specialist
When it's always a year you could also use ADezii's solution with: -
YEAR([MyDate])=YEAR(Date())
-
It's however my experience that users often need some extra days in the new year to correct previous year data, so I often switch to storing the Year field in a tblSystem and add a [Start New Year] button to a form to allow the user to decide when to start the new recording. This button than updates the Year field with the current year.
Nic;o)
@nico5038
How do you subtract 1 or two years from the year?
nico5038 3,080
Recognized Expert Specialist
Just like you wrote:
Year(datefield)-1
Nic;o)
Im trying to integrate into the current logic but it is turning to yes for each year. If its 2017, it should be for previous year.
If (Year(Nz([WCCClmDt1])) = 2016) Then
WCCNF1 = "Yes"
ElseIf (Year(Nz([WCCMRDt1])) = 2016) And _
(Year(Nz([wccmrdtwgt])) = 2016) And _
(Year(Nz([wccmrdthgt])) = 2016) Then
WCCNF1 = "Yes"
Else
WCCNF1 = "No"
End If
nico5038 3,080
Recognized Expert Specialist
Just check the individual datefield to match 2016. A NULL value will return 0 (zero), that won't match 2016. Better to use NZ(year(datefield),2016) to force a TRUE value.
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Mark |
last post by:
Hi
I have been trying to convert the week number to a range of dates that
I can use. It should be fairly simple for you guru's out there but for
us mere mortals it is beyond our grasp.
I know...
|
by: Cann |
last post by:
http://cheapdevtools.com/product/product.asp?ProdID=246
|
by: bluelaser |
last post by:
Hi there, i'm developing an asp.net page which contains 3 drop down
boxes each for day, month and year. The problem i have is i need to
ensure that the dates the user selects must be within a...
|
by: DhavalPatel1983 |
last post by:
Hi,
I need help in VB Datareport, my problem describe as below....
I have one Bank application project with all the information like Deposite,Clearness,Check,Cash,Creadit,ATM...
|
by: norma.j.hildebrand |
last post by:
I have a database that has a field (performance standard), every
year the standard changed which was not a problem since we start out
each year with a blank database and just change the standards...
| |
by: nsymiakakis |
last post by:
Hello Everyone, I am almost completed with this database, the only thing left is to graph the results of a query. I built a parameter query that gives me totals for a date range that I enter. This...
|
by: Greg (codepug |
last post by:
I’m working on setting a date range for filtering that is created
OnClick of a button.
I want to use the current date as the EndDate and the StartDate I want
to be the 01/01/SameYear.
Example:...
|
by: shapper |
last post by:
Hello,
I am checking a DateTime variable to check if in range: low <
MyDateTime < high.
However, I know need do the same but not care about the year. Can I do
this?
Thanks,
Miguel
|
by: zandiT |
last post by:
hello again
i have almost finished with my database. i have decided to generate the reports by using a date or date range and i can't get it to work. first i used parameters in a query but its...
|
by: Ramli |
last post by:
Hello,
I like to use a function like DateDiff to know Number of Years, Months, and Days between Days. As an Example, 5 Years, 10Months, 28Days for date between...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |