472,958 Members | 1,468 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

Using Multiple Calculated Date Fields in a Query - Looking for the Best Way

SueHopson
43 32bit
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!
Jan 14 '21 #1

✓ answered by isladogs

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
isladogs
443 Expert Mod 256MB
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
Jan 14 '21 #2
SueHopson
43 32bit
Perfect! I knew it was something simple, I just could not for the life of me remember.
Thank you.
Jan 14 '21 #3
isladogs
443 Expert Mod 256MB
You're very welcome.
Jan 14 '21 #4
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?
Jan 15 '21 #5
cactusdata
210 Expert 128KB
Also, your expression can be reduced to:

Expand|Select|Wrap|Line Numbers
  1. IIf([WHMIS Completed] Is Null Or [WHMIS Term]=0,"",DateAdd("yyyy",[WHMIS Term],[WHMIS Completed]))
and consider returning Null for an unknown date:

Expand|Select|Wrap|Line Numbers
  1. IIf([WHMIS Completed] Is Null Or [WHMIS Term]=0,Null,DateAdd("yyyy",[WHMIS Term],[WHMIS Completed]))
Jan 15 '21 #6
SueHopson
43 32bit
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!
Jan 26 '21 #7
SueHopson
43 32bit
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.
Jan 26 '21 #8
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 :-)
Jan 27 '21 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: David Allison | last post by:
Hi, I need a Calculated Date = 4 weeks before Date which is in table ... How ? -- Dave Allison ~ Scotland
2
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...
1
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...
5
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...
8
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...
2
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...
3
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...
2
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...
2
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;...
3
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:...
0
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=()=>{
2
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...
0
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...
2
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...
1
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...
0
isladogs
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...
3
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...
0
NeoPa
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.