Hello,
I have a fairly complicated query that pulls information from various tables to populate a report.
Basically, i want a letter sent to all people who meet a certain set of criteria. I have a calculated field in the query:
Let: DLookUp("[letter]","diagnosis","[type] ='" & [d1] & "'")*IIf(IsNull(DLookUp("[letter]","diagnosis","[type] ='" & [d2] & "'")),1,DLookUp("[letter]","diagnosis","[type] ='" & [d2] & "'"))
Where d1 and d2 are fields already in the query.
This Let field returns a 0, if no letter should be printed and either a 1 or a -1 if it should.
This works perfectly. What i initially wanted to do was to set the criteria for this field <>0, but then it prompts me for d1 and d2. I discovered a way round this by setting [let] <> 0, as the wherecondition, when i call the report.
This worked perfectly until last week. Now a message "data type mismatch in criteria" comes up and the report won't generate.
I've tried all sorts of things, and the only way to overcome it that i have found so far is to remove the wherecondition as i call the report. This, of course, causes all the records to print, regardless of the value of [let].
What is odd is that this was working - and still is on my old version of the backend, but not with the current version, using exactly the same front end!
Any one have any ideas?
I can produce another report that lists all the people who should get letters, so once the whole lot has printed they can manually pull the ones that shouldn't be sent - not the sweetest solution!
Anna
I have a fairly complicated query that pulls information from various tables to populate a report.
Basically, i want a letter sent to all people who meet a certain set of criteria. I have a calculated field in the query:
Let: DLookUp("[letter]","diagnosis","[type] ='" & [d1] & "'")*IIf(IsNull(DLookUp("[letter]","diagnosis","[type] ='" & [d2] & "'")),1,DLookUp("[letter]","diagnosis","[type] ='" & [d2] & "'"))
Where d1 and d2 are fields already in the query.
This Let field returns a 0, if no letter should be printed and either a 1 or a -1 if it should.
This works perfectly. What i initially wanted to do was to set the criteria for this field <>0, but then it prompts me for d1 and d2. I discovered a way round this by setting [let] <> 0, as the wherecondition, when i call the report.
This worked perfectly until last week. Now a message "data type mismatch in criteria" comes up and the report won't generate.
I've tried all sorts of things, and the only way to overcome it that i have found so far is to remove the wherecondition as i call the report. This, of course, causes all the records to print, regardless of the value of [let].
What is odd is that this was working - and still is on my old version of the backend, but not with the current version, using exactly the same front end!
Any one have any ideas?
I can produce another report that lists all the people who should get letters, so once the whole lot has printed they can manually pull the ones that shouldn't be sent - not the sweetest solution!
Anna
