Field calculated output based on date range (1 Viewer)

[CK_One]

Hi,

I have a field called 'Audit Date' and 'Period'. I would like the 'Period' field to automatically the period number if the audit date entered in the 'Audit Date' field is between a certain date range.

Below is the date ranges and period numbers

30-04-2017 - 27-05-2017 = Period 1
28-05-2017 - 24-06-2017 = Period 2
25-06-2017 - 29-07-2017 = Period 3
30-07-2017 - 26-08-2017 = Period 4
27-08-2017 - 23-09-2017 = Period 5
24-09-2017 - 28-10-2017 = Period 6
29-10-2017 - 25-11-2017 = Period 7
28-11-2017 - 30-12-2017 = Period 8
31-12-2017 - 27-01-2018 = Period 9
28-01-2018 - 24-02-2018 = Period 10
25-02-2018 - 24-03-2018 = Period 11
25-03-2018 - 28-04-2018 = Period 12

So if the users inputs the date 10-05-2017 in the 'Audit Date' field the 'Period' field should change to 'Period 1'.

Any ideas on how this can be achieved would greatly appreciated.

Regards,
Craig

 

[plog]

In a relational database, you don't store data you can calculate. You wouldn't store someone's age and birthdate--you would store their birthdate, then calculate their age when you need it. Same thing applies in this situation.

The data you posted above where you define your Periods would become a table (Let's call it Periods), then you would use a query to tie that data to the other table using a JOIN and then when you wanted to use the Period for an Audit date you would use that query.

 

[sneuberg]

I think it would be difficult to join one date field to two date fields although I wouldn't mind seeing that. Another way to do this would be to create a table as plog suggested but use DLookup to get the period. The DLookUp would be something like:

Period: Nz(DLookUp("[PeriodName]","[tblPeriods]","[StartDate] <= #" & [AuditDate] & "# And [EndDate] >= #" & [AuditDate] & "#"),"Period Not Defined")

which you can find demonstrated in qryPeriods in the attached database.

If these periods are defined by the same rules each year it might be better to do this with a function rather than maintain a table. Are they?

 

[CK_One]

Thanks Sneuberg and Plog

The periods are set by our financial team and I am not sure what rules they apply to set the periods each year.

The query works fine but I am new to Access and would like the period to be automatically calculated when a user enters the audit date in the job audit logger form.

Is there a way to run the query when the job audit is saved (via save button) taking the audit date from the audit date text box?


Regards,
Craig

 

[plog]

I think it would be difficult to join one date field to two date fields although

I wouldn't say difficult, but it's not something you can do through the query builder gui:

SELECT Audits.AuditDate, Periods.PeriodName
FROM Audits INNER JOIN Periods ON (Audits.AuditDate >= Periods.PeriodBegin AND  Audits.AuditDate <= Periods.PeriodEnd);

would like the period to be automatically calculated when a user enters the audit date in the job audit logger form

You can do that via a Dlookup ((https://www.techonthenet.com/access/functions/domain/dlookup.php)). You would have an unbound input on your form, then when the user enters the Audit date, you do a Dlookup with the correct criteria to retrieve that period.

 

[sneuberg]

I wouldn't say difficult, but it's not something you can do through the query builder gui:.

Thanks for the example. I redid it below to work in the database I uploaded. I didn't realize you could use boolean operators in a join but obviously since this works you can. But I'd call anything you can't do with the GUI difficult.

SELECT tblAudits.AuditDate, tblPeriods.PeriodName
FROM tblAudits INNER JOIN tblPeriods ON tblAudits.AuditDate >= tblPeriods.StartDate And tblAudits.AuditDate <= tblPeriods.EndDate;

 

[Minty]

I didn't realize you could use boolean operators in a join but obviously since this works you can. But I'd call anything you can't do with the GUI difficult.

I'm still amazed that people can write complex nested / subquery SQL statements off pat. I probably get anything slightly complicated wrong at least 6/10 times.

If you use SQL Server then SSMS allows you to do this sort of thing in the View designer. It can be a godsend for thickies like me.

 

[CK_One]

Thanks for the example. I redid it below to work in the database I uploaded. I didn't realize you could use boolean operators in a join but obviously since this works you can. But I'd call anything you can't do with the GUI difficult.

SELECT tblAudits.AuditDate, tblPeriods.PeriodName
FROM tblAudits INNER JOIN tblPeriods ON tblAudits.AuditDate >= tblPeriods.StartDate And tblAudits.AuditDate <= tblPeriods.EndDate;

Sneuberg,

Could you upload the updated database with the new query so I can see how it is done?

Thanks

 

[sneuberg]

The query is named qryPeriodPlog in the attached database. You can see that it works but please note that if you try to view this query in Design View (grid view) it will open in the SQL view and if you try to switch to Design View it will complain about the not being able to represent the join.

 

[CK_One]

Thanks Sneuberg and Plog, the solution works perfectly. Much appreciated

 

[CK_One]

Hi Sneuberg,

I thought I had it working but it seems I was wrong and I am now pulling out what little hair I had.

I have attached a copy of my database for better understanding.

When I run the 'qryPeriodPlog' nothing seems to happen even though the coding is exactly the same I cant see where the fault is.

I would also like the 'Period' field to be automatically updated when a user enters the audit date in the 'AuditDate' field in the 'Job Audit Logger'. Then the job is saved when the user selects the 'Save Job' button.

Regards,

 

[plog]

1 problem at a time. The reason your query isn't returning values is because there are no values to return. Your earliest period date is April 30th, 2017, your latest Audit date is 3/31/2017. No audit date matches any period.

 

[sneuberg]

I would also like the 'Period' field to be automatically updated when a user enters the audit date in the 'AuditDate' field in the 'Job Audit Logger'. Then the job is saved when the user selects the 'Save Job' button.

Regards,

I'm working on this but I want to make sure it works with your date format (dd/mm/yyy). Right now I have

Me.Period = DLookup("[PeriodName]", "[Periods]", "[StartDate] <= #" & Me.Audit_Date & "# AND [EndDate] >= #" & Me.Audit_Date & "#")

in the afterupdate of the Audit Date textbox and it work's here but probably won't work on your system. I should be able to get you something by the end of the day.

 

[sneuberg]

To update the Period field add the line in blue to the Audit_Date_AfterUpdate shown below.

Private Sub Audit_Date_AfterUpdate()
Audit_Year = Year(Audit_Date)
[COLOR="Blue"]Me.Period = DLookup("[PeriodName]", "[Periods]", "Forms![Job Audit Logger]![Audit Date] Between [StartDate] And [EndDate]")[/COLOR]

End Sub

You will see the Period fill in after entering a date in the AuditDate and then moving out of that field. If there is no range defined in the Periods table the textbox will remain blank or will be erased in something was already there.

I noticed that you are storing the Period in the JobAudit table. Since this can now be determined by looking it up in the Periods table you should consider if you want to continue doing this. The problem that might arise would be if for example the ranges for the periods were entered erroneously and this was discovered after adding records to the JobAudit table. If the period is stored you would have to run an update query on the existing records to correct them. If you just display a calculated (looked up) period then this update wouldn't be necessary.

 

[CK_One]

Thanks Steve,

The calculated (looked up) period would be a better option but I cant work out how the periods are calculated by the financial team. I have requested this info and waiting for them to get back to me.

The query is now work perfectly. Thanks for your help

Regards,

 

[Galaxiom]

I'm still amazed that people can write complex nested / subquery SQL statements off pat.

It is really all matter of formatting. Don't be afraid to use plenty of lines and white space. Separate the subqueries into blocks and ensure the pairs of parentheses are easily recognised. You also have to understand the way nested joins must be constructed in Access.

Of course Access will then completely screw the layout for you. I sometimes union a query on a dummy table to the end of complex SQL so the designer won't screw with the layout when it is saved.

If you use SQL Server then SSMS allows you to do this sort of thing in the View designer. It can be a godsend for thickies like me.

Oh yes, the SQL editor in SSMS is wonderful with excellent Intellisense. But the results won't work in Access if there are nested joins because the required bracketing in Access isn't required in TSQL.

 

[Galaxiom]

The data you posted above where you define your Periods would become a table (Let's call it Periods),

Technically, that table would breach Normalization because the periods are contiguous, hence the end date could be calculated. Only the StartDate of the period needs to be recorded. This structure also ensures there are no gaps in the periods.

The required period record is the one with the latest StartDate less than or equal to the date in question. Or if the Period names can be ordered, the Maximum period number less than or equal to the date.

To see how the full sql is written, search for "correlated subquery".