Currently, my query has 2 calculated query fields, where one is based upon the other (and I remember vaguely that this doesn't work, but can't remember the solution)
WHMIS Next Renew: IIf([WHMIS Completed] Is Null,"",IIf([WHMIS Term]=0,"",DateAdd("yyyy",[WHMIS Term],[WHMIS Completed])))
WHMIS Status: IIf([WHMIS Next Renew]="","Required", IIf([WHMIS Next Renew]<Date(),"Expired","Current"))
WHMIS Next renew does exactly what I need it to do, but the WHMIS Status does not... it returns the Required Values Fine but the Expired and Current values incorrectly.
Can someone remind me why this doesn't work and how to fix - or provide a better way of doing this?
Thanks!
If you want to use the same query, you need to replace both instances of WHMIS Next Renew in the second expression with the full expression: IIf([WHMIS Completed] Is Null,"",IIf([WHMIS Term]=0,"",DateAdd("yyyy",[WHMIS Term],[WHMIS Completed])))
However, that's going to get very messy with two sets of IIf statements.
So I would suggest using two queries with WHMIS Next Renew done in query 1 then use that as a field to derive WHMIS Status in query 2
8 3270
If you want to use the same query, you need to replace both instances of WHMIS Next Renew in the second expression with the full expression: IIf([WHMIS Completed] Is Null,"",IIf([WHMIS Term]=0,"",DateAdd("yyyy",[WHMIS Term],[WHMIS Completed])))
However, that's going to get very messy with two sets of IIf statements.
So I would suggest using two queries with WHMIS Next Renew done in query 1 then use that as a field to derive WHMIS Status in query 2
Perfect! I knew it was something simple, I just could not for the life of me remember.
Thank you.
NeoPa 32,547
Expert Mod 16PB
Hi Sue.
Generally speaking calculated fields within a query work fine. However, this is not true when the resultant calculated field is used either in the filtering or sorting (WHERE, ORDER BY or GROUP BY clauses). This is because these clauses are processed first and the rest of the fields are then only calculated as and when they're needed. That is necessarily after the first sets.
Typically, as Jet/ACE has no knowledge of the dependent calculated field at the time it processes the depending field, it will prompt the user for it.
Can we assume that [WHMIN Status] is included in one of the other clauses in your original query?
Also, your expression can be reduced to: - IIf([WHMIS Completed] Is Null Or [WHMIS Term]=0,"",DateAdd("yyyy",[WHMIS Term],[WHMIS Completed]))
and consider returning Null for an unknown date: - IIf([WHMIS Completed] Is Null Or [WHMIS Term]=0,Null,DateAdd("yyyy",[WHMIS Term],[WHMIS Completed]))
To NeoPa and Cactusdata
My thanks for the technical explanation and for the code simplification. This is why I love this forum, clear concise, and effective replies!
I took Isladogs advice, once I remembered why, and reorganized my fields more efficiently within 2 queries. My data flows much more smoothly, and yes, [WHMIS Status] was and continues to be a defined Yes/No field.
NeoPa 32,547
Expert Mod 16PB
Very pleased to hear Sue. In view of that I've set that post as Best Answer for you :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: David Allison |
last post by:
Hi, I need a Calculated Date = 4 weeks before Date which is in table ...
How ?
--
Dave Allison ~ Scotland
|
by: msaccess |
last post by:
Dear knowledgeable folks,
System: Windows XP and Access 2002.
In my participants form, I have the following date fields:
EnrollmentDate: the actual date that the participant officially began...
|
by: Riley DeWiley |
last post by:
I have an UPDATE query that is always setting 0 records. When I cut and
paste the SQL into Access and use it, it fails in the same way unless I
coerce the date fields to be '=now()', in which case...
|
by: QBCM |
last post by:
I am trying to create a report by selecting three date fields from one
table with records between a start date and end date. I have tried to
adapt one of Allen Browne's scripts as follows but it...
|
by: Ragbrai |
last post by:
Howdy All,
I have a query that is used for filtering results to be used in a combo box. The query needs to test fields from both a table and then unbound text boxes on the form that also contains...
|
by: jennwilson |
last post by:
I am trying to generate a report based on a query that will list any records where an individual has a date listed that matches the specified time for one or both of the date fields. The two fields...
|
by: Ciara9 |
last post by:
I am having problems trying to update a field in a database using a field in a form. I currently have two fields, Today and Tomorrow in a table named Date. The Today field automatically defaults to...
|
by: Tony K |
last post by:
I have a form that involves a datagrid view to be filled after a start and
end date are selected from 2 DateTimePicker fields using the Short Format.
The query works without error but...
I have...
|
by: celticmystyrose |
last post by:
I am a beginner in access
I was making a database for work. I need to make a query to bring out information from two separate data fields.
My table is as such
Surname; First name; Date1; time 1;...
|
by: Martin Lang |
last post by:
Hi guys,
Long time ago since last I posted here. I have always appreciated the help offered by this community!!
I have a question similar to this thread:...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
|
by: Aliciasmith |
last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: nia12 |
last post by:
Hi there,
I am very new to Access so apologies if any of this is obvious/not clear.
I am creating a data collection tool for health care employees to complete. It consists of a number of...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
| |