Hello,
I have Four fields:
[Starting Date]
[Ending Date]
[Time Extension]
[Maintenance Period]
All are on a date format.
I want to input a text on a text box field doing the following:
1) If today’s date < [Starting Date], then return “STARTING”
2) If [Starting Date] < today’s date < [Ending Date] then return “ONGOING”
3) If [Starting Date] < today’s date < [Time Extension] then return “ONGOING”
4) If [Time Extension] is Null then if [Ending Date] < today’s date < [Maintenance Period] then return “MAINTENANCE”
5) If [Time Extension] is not Null then if [Ending Date] < today’s date < [Maintenance Period] then return “MAINTENANCE”
6) If today’s date > [Maintenance Period], then return “COMPLETION”
Later, I will want the message “COMPLETED” to appear, when the user will have ticked a checkbox confirming the completion.
Going step by step with the Expression builder, I have built so far this: - =IIf(Date()<[Start Date],"Starting",IIf(Date()>[Start Date] And Date()<[Ending Date],"Ongoing",IIf(Date()>[Start Date] And Date()<[Time Extention],"Ongoing",IIf([Time Extention]=Null,IIf(Date()<[Maintenance Period] And Date()>[Ending Date],"Maintenance"),IIf(Date()<[Maintenance Period] And Date()>[Time Extention],"Maintenance")))))
It works fine with 1), 2), 3), 5), but to my surprise it did not work with 4).
Regarding 6), I did not go across it yet as I did not resolve the problem for 4).
If anyone can help…
Thanking you in advance,
G.
39 4316 NeoPa 32,578
Recognized Expert Moderator MVP
You don't don't say what you'd like to see if any of the dates are equal to today.
You don't say whether any of the stored date fields contain time elements with the date.
Otherwise, I think this is a reasonable straightforward question and I would expect an answer shortly.
If you haven't got anything by tomorrow, bump the thread and we'll see what we can do for you.
You don't don't say what you'd like to see if any of the dates are equal to today.
That is a good remark and actually it did not come to my mind. My database is for following up projects that typically will run over months. So if any of the dates are equal to today, I think I can just go along with replacing '<' by '<='.
You don't say whether any of the stored date fields contain time elements with the date.
There are no time elements in the date fields. Only dates on a dd/mm/yyyy format.
Otherwise, I think this is a reasonable straightforward question and I would expect an answer shortly.
If you haven't got anything by tomorrow, bump the thread and we'll see what we can do for you.
Thank you for your help.
Best regards,
G.
MMcCarthy 14,534
Recognized Expert Moderator MVP - =IIf(Date()<=[Start Date],"Starting",
-
IIf(Date()>[Start Date] And Date()<[Ending Date],"Ongoing",
-
IIf(Date()>[Start Date] And Date()<[Time Extention],"Ongoing",
-
IIf(IsNull([Time Extention]),
-
IIf(Date()>[Ending Date] And Date()<[Maintenance Period],"Maintenance",
- What do you return if false?),
-
IIf(Date()>[Time Extention] And Date()<[Maintenance Period],"Maintenance")
-
' In No 5 you use [Ending Date] not [Time Extension]
-
))))
NeoPa 32,578
Recognized Expert Moderator MVP
Gari,
You need to look at your question a little more.
I'm assuming that your requirements are better expressed as (Please notice where I have included & omitted the '='s in my version.) :
I have four Date fields (no time elements) :- [Starting Date]
- [Ending Date]
- [Time Extension]
- [Maintenance Period]
All must contain values except [Time Extension] which may be empty. Otherwise the dates are in progressive order.
I have a control which needs to show a string determined by the following logic :- Treat [Time Extension] as [Ending Date] if it is not entered.
- If today < [Starting Date], then return "STARTING"
- Otherwise if today <= [Time Extension] then return "ONGOING"
- Otherwise if today <= [Maintenance Period] then return "MAINTENANC E"
- Otherwise (today must be > [Maintenance Period]) return "COMPLETION "
Please can you confirm that this is correct and not a wrong guess.
Dear both,
Thank you very much for your remarks. Thanks to them, I have built the following line of code: - =IIf(Date()<=[Start Date],"Starting",
-
IIf(Date()>[Start Date] And Date()<= [Ending Date],"Ongoing",
-
IIf(Date()>[Start Date] And Date()<= [Time Extention],"Ongoing",
-
IIf(IsNull([Time Extention]),
-
IIf(Date()>[Ending Date] And Date()<= [Maintenance Period],"Maintenance","Completion"),
-
IIf(Date()>[Time Extention] And Date()<=[Maintenance Period],"Maintenance","Completion")))))
From what I have tested, it seems to work correctly. I think it is a good assumption that:
If Date() is >=[Start Date]
And If Date()>=[Ending Date] Or If Date()>=[Time Extention]
And If Date()>=[Maintenance Period]
Then the project is obviously completed and the formula should return "Completion ".
Regarding the Checkbox thing (I did not yet set up this option), I think that in the code, instead of "Completion ", I will put a line as follows: - IIf([Checkbox]=True,"Completed","Completion")
Thank you for your feedback.
Best regards,
G.
NeoPa 32,578
Recognized Expert Moderator MVP
Gari,
I await a response to my last post.
Your code may work but could do with a lot of tidying up (redundant code will only confuse you later on). Unfortunately, to do this properly I need confirmation of what you're actually asking.
-Adrian.
NeoPa 32,578
Recognized Expert Moderator MVP
Alternatively, if you're not interested, please let me know anyway - just a courtesy note.
Dear NeoPa (or Adrian ??),
Sorry to not have answered your post directly.
Your assumption is right : that is my requirement.
I wanted actually to test your sequence but I was confused by the way of writing line 1 (Treat [Time Extension] as [Ending Date] if it is not entered) (I am quite new in programming), so I came back to a code nearer to the one proposed by mmccarthy.
But as I am always eager to learn, I am greatly interested by your feedback.
Thank you and best regards.
G.
NeoPa 32,578
Recognized Expert Moderator MVP
Gari,
You can refer to me as either. My real name is public knowledge on this site now so ...
My reasoning for that line was to try to clarify the underlying logic of the request before moving on to a solution. Rather than complicated Ifs & Elses when refering to the [Time Extension] & [Ending Date] fields, I wanted to see them as a single entity where [Ending Date] is the original planned end date of the project but allowances can be made where necessary to extend this, giving [Time Extension]. For the purposes of your code (logic) however, Whenever we compare [Time Extension], we should always use [Ending Date] if [Time Extension] is not entered.
That's a more longwinded way of saying it but hopefully clearer.
The code proposed by Mary, a top contributor in this forum, is absolutely correct in that it matches your question directly. However, I think a tidying up of the question can produce simpler (easier to understand) code.
I will go off now and try to produce something a little more direct. It will work on the reliable assumption that previous lines already test for less than the value.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Piotr |
last post by:
I have popup window (window.open) where I put any value in input field.
After submit I wan to to return to the main window and get value from popup
window. How to close popup window and return to the main after submit?
|
by: jen_designs |
last post by:
Is there a way to return the character position on a page? Not the x
and y coordinates, but the number of characters on a page. For
instance i have a html page with the following text: This is my string.
Then character postion for m would be 9. Any thoughts?
|
by: jen_designs |
last post by:
I need to find a way to determine the character position of a users
selection in a div. I can do this with the following using an input
text box. Any way for a div or any text within the body tags?
<html>
<body>
<form>
<div onkeyup="s(this)" onclick="s(this)" id="myDiv">This is some
text</div>
<input type="text" onkeyup="s(this)" onclick="s(this)" value="This is
|
by: Neil Schemenauer |
last post by:
python-dev@python.org.]
The PEP has been rewritten based on a suggestion by Guido to change
str() rather than adding a new built-in function. Based on my
testing, I believe the idea is feasible. It would be helpful if
people could test the patched Python with their own applications and
report any incompatibilities.
PEP: 349
|
by: WebBuilder451 |
last post by:
I have a function that returns a dataset or a boolean depending upon whether
a record was found. I can check for the string value of the return type, but
i don'r think this is the right way to do it what is the correct method?
any help would be appreciated.
(hey, i'm actually learning how to do this "-------"!
FUNCTION:
' this function will return the a dataset with the current user if
the loogin was successful else if will return a...
| |
by: AssanKhan Ismail |
last post by:
Im using an C#'s user defined private function on which i want to return more
than one value (like int and string ) and from that function.
please let me know in advance..
assankhan Ismail
|
by: Mike Hoff |
last post by:
Hello,
I am trying to write a function that will return the value to be stored from
any control based on the control's type. So for text boxes and labels it
would return the text value, for list boxes the ListIndex, for combos maybe
the Items data etc. with several possible return types (string, int etc).
My function (that does not work) is:
Public Function SaveValue(ByVal ctrlIn As Control) As VariantType
|
by: Ed Jay |
last post by:
<disclaimer>js newbie</disclaimer>
My page has a form comprised of several radio buttons. I want to poll the
buttons to determine which button was selected and convert its value to a
string. I then want to use the string on the same page.
My script is:
function checkRadio(field) {
for(var i=0; i < field.length; i++) {
|
by: David |
last post by:
Hi,
I have a problem with returning a value from an external function.....
My asp page is basically a list taken from a database.
A date record is written from the DB, then all the recordslinked to
that date are listed, then the next date, then the next series of
records, i.e.
|
by: colmkav |
last post by:
Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to using this
method with store procs that dont return a value back to Access. Hope
this makes sense.
Set Cmd = New Command
With Cmd
Set .ActiveConnection = get_XE_Conn 'makes a connection
Oracle XE
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |