Hi all,
I'm not quite sure how to skin this cat; there are several possible approached mulling over in my head which mean it's hard to narrow it down to a particular area of the forum or a search (I've done some searches on critical keywords but having browsed a few of the umpteen hits, I'm not getting anywhere!).
Here is the issue.
I have a record with a "last reviewed date" on it, plus some numeric fields that basically relate how important the record is (they represent inspection records for health and safety). The higher the priority (a field calculated on the basis of risk and likelyhood), the sooner the next inspection date is due.
The due date for inspection is wholly calculable based on the last inspection date, and the priority factor, therefore I didn't want to represent it as an actual field on the table. This way there is never any honus on the user to enter it (and potential to get it wrong).
Calculating it in a form is pretty simple - I have a function in a module that I can call to calculate the review date. The form view works fine. The function takes a Date and Integer in as parameters (last inspection date, and the priority value) and then returns the result as a Date object too.
The challenge I have is with reports - I want the users to be able to print off a report which represents all the records that are due for review within a certain period (say, the next week or the next month).
So initially I approached this with a simple query, and I planned on basing the report on that query. Initially I had thought something like this would be the approach :-
Running the query just prompts for calcReviewDate to be entered. Without the where clause, the query runs fine, so the function can be called and works okay, populating the field with no problem. I'm sure if we can solve this basic issue, the rest is plain sailing. Anyway, bit confused, I figured for now I'd just repeat the function call, so tried :-
...but that results in a Data Type Mismatch in criteria expression. I tried wrapping with hashes but there was no joy there, so further investigation on Google suggested that probably function calls to my getReviewDate() function were not welcome in SQL WHERE clauses.
I next tried to programatically set the recordset on the report itself, but then further googling (I'm boiling this down for you, hopefully to keep it short and sweet!) implied that Reports can't be tied to recordsets, only tables or queries. Since I can't get queries working, the only other approach I can think of is to create a temporary table with the data in it, filled via some module code on a button click to launch the report, and then base the report on that table. I don't particularly like this approach, so just wondered if there was any way I could get my query working how I wanted it to.
Essentially, if I can get my query working with the column "calcReviewDate" then I'm home dry. If this can't be done, it's either an approach done within the report itself by code, or a temporary table.
Anyone done anything similar and can advise?
It's Access 2007, if it helps. I'm not formally trained in the product, but I've mucked around with it over the years - but never faced a challenge like this before
I'm not quite sure how to skin this cat; there are several possible approached mulling over in my head which mean it's hard to narrow it down to a particular area of the forum or a search (I've done some searches on critical keywords but having browsed a few of the umpteen hits, I'm not getting anywhere!).
Here is the issue.
I have a record with a "last reviewed date" on it, plus some numeric fields that basically relate how important the record is (they represent inspection records for health and safety). The higher the priority (a field calculated on the basis of risk and likelyhood), the sooner the next inspection date is due.
The due date for inspection is wholly calculable based on the last inspection date, and the priority factor, therefore I didn't want to represent it as an actual field on the table. This way there is never any honus on the user to enter it (and potential to get it wrong).
Calculating it in a form is pretty simple - I have a function in a module that I can call to calculate the review date. The form view works fine. The function takes a Date and Integer in as parameters (last inspection date, and the priority value) and then returns the result as a Date object too.
The challenge I have is with reports - I want the users to be able to print off a report which represents all the records that are due for review within a certain period (say, the next week or the next month).
So initially I approached this with a simple query, and I planned on basing the report on that query. Initially I had thought something like this would be the approach :-
Code:
SELECT tabHSE.id, tabHSE.title, tabHSE.location, tabHSE.inspectionDate, getReviewDate(tabHSE.inspectionDate,riskFactor*likelyhoodFactor) AS calcReviewDate, tabHSE.type, tabHSE.status, tabHSE.riskFactor, tabHSE.LikelyhoodFactor
FROM tabHSE
WHERE calcReviewDate<dateadd("d",7,date());
Running the query just prompts for calcReviewDate to be entered. Without the where clause, the query runs fine, so the function can be called and works okay, populating the field with no problem. I'm sure if we can solve this basic issue, the rest is plain sailing. Anyway, bit confused, I figured for now I'd just repeat the function call, so tried :-
Code:
SELECT tabHSE.id, tabHSE.title, tabHSE.location, tabHSE.inspectionDate, getReviewDate(tabHSE.inspectionDate,riskFactor*likelyhoodFactor) AS calcReviewDate, tabHSE.type, tabHSE.status, tabHSE.riskFactor, tabHSE.LikelyhoodFactor
FROM tabHSE
WHERE getReviewDate(tabHSE.inspectionDate,riskFactor*likelyhoodFactor)<dateadd("d",7,date());
...but that results in a Data Type Mismatch in criteria expression. I tried wrapping with hashes but there was no joy there, so further investigation on Google suggested that probably function calls to my getReviewDate() function were not welcome in SQL WHERE clauses.
I next tried to programatically set the recordset on the report itself, but then further googling (I'm boiling this down for you, hopefully to keep it short and sweet!) implied that Reports can't be tied to recordsets, only tables or queries. Since I can't get queries working, the only other approach I can think of is to create a temporary table with the data in it, filled via some module code on a button click to launch the report, and then base the report on that table. I don't particularly like this approach, so just wondered if there was any way I could get my query working how I wanted it to.
Essentially, if I can get my query working with the column "calcReviewDate" then I'm home dry. If this can't be done, it's either an approach done within the report itself by code, or a temporary table.
Anyone done anything similar and can advise?
It's Access 2007, if it helps. I'm not formally trained in the product, but I've mucked around with it over the years - but never faced a challenge like this before