I want my "requalify date" to auto populate to the beginning of the month in the following year when my "liability date" gets entered/changed.
Example: Liablity date = 11/15/2010; 7/31/2011
Requalify date should be 11/1/2011; 7/1/2012
I am not sure how to write the formula to get this result.
The requalify date is a field in my main table as well as the liability date. When entering the formula, would entering it in the requalify date field's default value be the best.
Any help would be much appreciated. Thank you!
Yappy
9 8903
Try using DateSerial(year , month, day) combined with DatePart(interv al, date) to build your formula. The formula for requalify date can be:
DateSerial(Date Part("y",[Liability Date])+1,DatePart("m ",[Liability Date]),1))
Sorry, that should be:
DateSerial(Date Part("yyyy",[Liability Date])+1,DatePart("m ",[Liability Date]),1)
Hi, Steven!
Thanks for your reply. I do have another question since I am not sure if I am placing the expression in the correct area.
When entering the formula, would entering it in the requalify date field's default value be the best?
I have tried that and am getting errors.
It could go in a query as a calculated field, or as the control source on the form.
Using it as a default value wouldn't work, as you've found out.
Did you want the value to be editable, or always based on a calculation? If you want it to be initially calculated after the liability date is entered, and then to be able to edit it after, you'd need to use VBA.
The code would be something like this: - Private Sub Liability_Date_AfterUpdate()
-
If IsDate([Liability Date]) Then
-
[Requalify Date] = DateSerial(DatePart("yyyy", [Liability Date]) + 1, DatePart("m", [Liability Date]), 1)
-
End If
-
End Sub
-
NeoPa 32,566
Recognized Expert Moderator MVP
You will find it complicated to keep updating the value. Not impossible, but complicated.
This is not something you should really even be considering (See Normalisation and Table structures). A normalised database would calculate the related value as and when needed, but never store a value that can always be calculated reliably.
I agree: if the value can always be calculated then it should not be a field in your table. If that's the case, add Requalify Date as a calculated field in a query. The exception would be if the database is for Access 2010, which supports calculated fields in tables.
The field in the query would be something like: - Requalify Date: DateSerial(DatePart("yyyy", [Liability Date]) + 1, DatePart("m", [Liability Date]), 1)
Thank you so much! That is exactly what I needed. I tested it and it works perfectly.
NeoPa 32,566
Recognized Expert Moderator MVP
Steven,
you may want to check your PMs :)
Hi NeoPa,
I'll be sure to include code in the [code] tags.
I'm unable to reply (regarding changing my status to Expert) with a PM. I haven't sent any, but I get the message:
Registered members under 50 posts can only send 3 private messages a day.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: pb648174 |
last post by:
I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based on a
column called RefId and one called Type, i.e. type 1 is a relationship
to BidItem and type 2 is a relationship to BidAddendum.
Is there any...
|
by: jochen scheire |
last post by:
Is there a way I can calculate a field in a form based on another field in
the same form. When clicking submit, both values should be posted to the
next page. I want to be able to type in a value in one field, and
automatically in a second field the value*1,36 should appear.
|
by: pkinville |
last post by:
ok, this is gonna seem stupid, but it has been stumping me all
afternoon.
I have two fields on a HTML page. HTML looks like this...
<html>
<head>
<title>New Page 3</title>
</head>
<body>
|
by: Mike |
last post by:
I'm sure this has been covered before in the newsgroup but have had no
luck locating it.
I have two tables: InstMonitors & MonModDesc
Structure of MonModDesc:
Model, Description
Structure of InstMonitors:
CompName, Make, Model, Description, Serial, PurchDate, WarrExpDate
|
by: rmputnam |
last post by:
I have a new patient table (PatTbl) that lists Acct, Last, First, among other fields. I need another table listing referring information (RefTbl).
My goal is to enter the Acct number in the RefTbl and have the name fields populate automatically by pulling from the PatTbl based on the Acct number.
I have searched the manuals, looked at...
| |
by: Neha Jain |
last post by:
i have a table 'Tab1' with some fields. i want to create a screen consisting one of the fields in Table 'Tab1'.user must be able to select any of the data stored in the table for that field and any calculated data can be displayed based on the value selected by the user.
Is it possible or any way to implent this?
Thanks in advance.
|
by: kpfunf |
last post by:
I have one table of transactions, another table of price quotes. Transactions are nearly daily; quotes are periodic, roughly once per week. In a query, I want to pull the oldest (or least date) price quote whose date is greater or equal to the transaction date (trying to find the nearest price quote to compare to actual price paid). I have...
|
by: Ronald |
last post by:
I hope somebody can help. I can't get into the specifics of my
project, but I'll try to create a simple example:
tblVehicle
* VIN (text box)
* Make (text box)
* Model (text box)
frmRepair
|
by: dkyadav80 |
last post by:
<html>
///
here what shoud be java script for:
->when script run then not display all input text field only display selection field.
->when user select other value for institute only this field display not display degree text field
->when user select other value in the selection field for degree then text field display and wise versa ...
|
by: Cron |
last post by:
Hi I'm trying to Dcount *unique* records by comparing a date/time
field. I say *unique* because the field contains a date/time but I
need to ignore the timestamp and work off the date only.
I think the code below should work to count unique values in a normal
text field but the date/time is causing a lot of problems because it's
reading the...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
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 then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |