Error 3075

G

Guest

I have a team of people unable to work today!!!!

When they run this command an error indicating Error 3075 - Function is not
available in expressions in query expression 'IIf([completiondate_45 (see the
code below).....

I don't understand this code has work for many years then this morning boom!!
For the last 4 hours I have dug into this code removing this IIf and and
counting commas & quotes etc...

Please let me know if you see why it gets hung starting at this point???
////


SELECT DISTINCTROW tblQualityData.ICNNo, tblQualityData.GBULocation,
IIf([tblqualitydata].[typeofcomplaint]="1","Complaint",IIf([tblqualitydata].[typeofcomplaint]="2","Grievance","Not
Indicated")) AS Type, tblQualityData.MemberNo, tblQualityData.ReceiptDate,
tblQualityData.Source, tblQualityData.AcknowledgementDate,
tblReviewers.Reviewer, tblQualityData.IssueCloseDate, IIf([csreceiptdate] Is
Null And [receiptdate] Is Null,0,IIf([csreceiptdate] Is
Null,[receiptdate],IIf([csreceiptdate]>[receiptdate],[receiptdate],[csreceiptdate])))
AS StartDate, [startdate]+60 AS CompletionDate_60, [startdate]+45 AS
CompletionDate_45, IIf([issueclosedate] Is Not Null,"CLOSED",

IIf([completiondate_45]<Date(),"OVER DUE",IIf([csreceiptdate] Is Null And
[receiptdate] Is Null,"NO DATE",IIf([csreceiptdate] Is
Null,DateDiff("d",Date(),[receiptdate]+45),DateDiff("d",Date(),[csreceiptdate]+45)))))

AS DaysRemaining_45, IIf([issueclosedate] Is Not
Null,"CLOSED",IIf([completiondate_60]<Date(),"OVER DUE",IIf([csreceiptdate]
Is Null And [receiptdate] Is Null,"NO DATE",IIf([csreceiptdate] Is
Null,DateDiff("d",Date(),[receiptdate]+60),DateDiff("d",Date(),[csreceiptdate]+60)))))
AS DaysRemaining_60, tblQualityData.InsuranceType, tblMemberInfo.LastName,
tblMemberInfo.FirstName, tblReviewers.RACF,
tblQualityData.ClinicalPeerReview, tblQualityData.SeverityLevel,
tblQualityData.CSReceiptDate, IIf([daysremaining_60]="1" Or
[daysremaining_60]="2" Or [daysremaining_60]="3" Or [daysremaining_60]="4" Or
[daysremaining_60]="5" Or [daysremaining_60]="6" Or [daysremaining_60]="7" Or
[daysremaining_60]="8" Or [daysremaining_60]="9","0" &
[daysremaining_60],[daysremaining_60]) AS DaysRemaining2,
IIf([daysremaining_45]="1" Or [daysremaining_45]="2" Or
[daysremaining_45]="3" Or [daysremaining_45]="4" Or [daysremaining_45]="5" Or
[daysremaining_45]="6" Or [daysremaining_45]="7" Or [daysremaining_45]="8" Or
[daysremaining_45]="9","0" & [daysremaining_45],[daysremaining_45]) AS
DaysRemaining_45_2
FROM tblMemberInfo RIGHT JOIN (tblQualityData LEFT JOIN tblReviewers ON
tblQualityData.Reviewer = tblReviewers.RACF) ON tblMemberInfo.MemberNo =
tblQualityData.MemberNo;
 
G

Guest

Date() . Anytime I see a problem with the Date() function, I have a knee-jerk
reaction about references. Go to the following web page for information on
checking references:

http://www.mvps.org/access/bugs/bugs0001.htm
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Dan @BCBS said:
I have a team of people unable to work today!!!!

When they run this command an error indicating Error 3075 - Function is not
available in expressions in query expression 'IIf([completiondate_45 (see the
code below).....

I don't understand this code has work for many years then this morning boom!!
For the last 4 hours I have dug into this code removing this IIf and and
counting commas & quotes etc...

Please let me know if you see why it gets hung starting at this point???
////


SELECT DISTINCTROW tblQualityData.ICNNo, tblQualityData.GBULocation,
IIf([tblqualitydata].[typeofcomplaint]="1","Complaint",IIf([tblqualitydata].[typeofcomplaint]="2","Grievance","Not
Indicated")) AS Type, tblQualityData.MemberNo, tblQualityData.ReceiptDate,
tblQualityData.Source, tblQualityData.AcknowledgementDate,
tblReviewers.Reviewer, tblQualityData.IssueCloseDate, IIf([csreceiptdate] Is
Null And [receiptdate] Is Null,0,IIf([csreceiptdate] Is
Null,[receiptdate],IIf([csreceiptdate]>[receiptdate],[receiptdate],[csreceiptdate])))
AS StartDate, [startdate]+60 AS CompletionDate_60, [startdate]+45 AS
CompletionDate_45, IIf([issueclosedate] Is Not Null,"CLOSED",

IIf([completiondate_45]<Date(),"OVER DUE",IIf([csreceiptdate] Is Null And
[receiptdate] Is Null,"NO DATE",IIf([csreceiptdate] Is
Null,DateDiff("d",Date(),[receiptdate]+45),DateDiff("d",Date(),[csreceiptdate]+45)))))

AS DaysRemaining_45, IIf([issueclosedate] Is Not
Null,"CLOSED",IIf([completiondate_60]<Date(),"OVER DUE",IIf([csreceiptdate]
Is Null And [receiptdate] Is Null,"NO DATE",IIf([csreceiptdate] Is
Null,DateDiff("d",Date(),[receiptdate]+60),DateDiff("d",Date(),[csreceiptdate]+60)))))
AS DaysRemaining_60, tblQualityData.InsuranceType, tblMemberInfo.LastName,
tblMemberInfo.FirstName, tblReviewers.RACF,
tblQualityData.ClinicalPeerReview, tblQualityData.SeverityLevel,
tblQualityData.CSReceiptDate, IIf([daysremaining_60]="1" Or
[daysremaining_60]="2" Or [daysremaining_60]="3" Or [daysremaining_60]="4" Or
[daysremaining_60]="5" Or [daysremaining_60]="6" Or [daysremaining_60]="7" Or
[daysremaining_60]="8" Or [daysremaining_60]="9","0" &
[daysremaining_60],[daysremaining_60]) AS DaysRemaining2,
IIf([daysremaining_45]="1" Or [daysremaining_45]="2" Or
[daysremaining_45]="3" Or [daysremaining_45]="4" Or [daysremaining_45]="5" Or
[daysremaining_45]="6" Or [daysremaining_45]="7" Or [daysremaining_45]="8" Or
[daysremaining_45]="9","0" & [daysremaining_45],[daysremaining_45]) AS
DaysRemaining_45_2
FROM tblMemberInfo RIGHT JOIN (tblQualityData LEFT JOIN tblReviewers ON
tblQualityData.Reviewer = tblReviewers.RACF) ON tblMemberInfo.MemberNo =
tblQualityData.MemberNo;
Click to expand...
 
G

Guest

This put me on the right track..
The actual solution was very simple.

I checked an unchecked reference.
Closed the references - ReOpened the references.
Closed the app - ReOpened the app.

Then it worked... For whatever reason the references needed to be woken up...
I don't notice any new references but it works...


Thanks for the direction...


Jerry Whittle said:
Date() . Anytime I see a problem with the Date() function, I have a knee-jerk
reaction about references. Go to the following web page for information on
checking references:

http://www.mvps.org/access/bugs/bugs0001.htm
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Dan @BCBS said:
I have a team of people unable to work today!!!!

When they run this command an error indicating Error 3075 - Function is not
available in expressions in query expression 'IIf([completiondate_45 (see the
code below).....

I don't understand this code has work for many years then this morning boom!!
For the last 4 hours I have dug into this code removing this IIf and and
counting commas & quotes etc...

Please let me know if you see why it gets hung starting at this point???
////


SELECT DISTINCTROW tblQualityData.ICNNo, tblQualityData.GBULocation,
IIf([tblqualitydata].[typeofcomplaint]="1","Complaint",IIf([tblqualitydata].[typeofcomplaint]="2","Grievance","Not
Indicated")) AS Type, tblQualityData.MemberNo, tblQualityData.ReceiptDate,
tblQualityData.Source, tblQualityData.AcknowledgementDate,
tblReviewers.Reviewer, tblQualityData.IssueCloseDate, IIf([csreceiptdate] Is
Null And [receiptdate] Is Null,0,IIf([csreceiptdate] Is
Null,[receiptdate],IIf([csreceiptdate]>[receiptdate],[receiptdate],[csreceiptdate])))
AS StartDate, [startdate]+60 AS CompletionDate_60, [startdate]+45 AS
CompletionDate_45, IIf([issueclosedate] Is Not Null,"CLOSED",

IIf([completiondate_45]<Date(),"OVER DUE",IIf([csreceiptdate] Is Null And
[receiptdate] Is Null,"NO DATE",IIf([csreceiptdate] Is
Null,DateDiff("d",Date(),[receiptdate]+45),DateDiff("d",Date(),[csreceiptdate]+45)))))

AS DaysRemaining_45, IIf([issueclosedate] Is Not
Null,"CLOSED",IIf([completiondate_60]<Date(),"OVER DUE",IIf([csreceiptdate]
Is Null And [receiptdate] Is Null,"NO DATE",IIf([csreceiptdate] Is
Null,DateDiff("d",Date(),[receiptdate]+60),DateDiff("d",Date(),[csreceiptdate]+60)))))
AS DaysRemaining_60, tblQualityData.InsuranceType, tblMemberInfo.LastName,
tblMemberInfo.FirstName, tblReviewers.RACF,
tblQualityData.ClinicalPeerReview, tblQualityData.SeverityLevel,
tblQualityData.CSReceiptDate, IIf([daysremaining_60]="1" Or
[daysremaining_60]="2" Or [daysremaining_60]="3" Or [daysremaining_60]="4" Or
[daysremaining_60]="5" Or [daysremaining_60]="6" Or [daysremaining_60]="7" Or
[daysremaining_60]="8" Or [daysremaining_60]="9","0" &
[daysremaining_60],[daysremaining_60]) AS DaysRemaining2,
IIf([daysremaining_45]="1" Or [daysremaining_45]="2" Or
[daysremaining_45]="3" Or [daysremaining_45]="4" Or [daysremaining_45]="5" Or
[daysremaining_45]="6" Or [daysremaining_45]="7" Or [daysremaining_45]="8" Or
[daysremaining_45]="9","0" & [daysremaining_45],[daysremaining_45]) AS
DaysRemaining_45_2
FROM tblMemberInfo RIGHT JOIN (tblQualityData LEFT JOIN tblReviewers ON
tblQualityData.Reviewer = tblReviewers.RACF) ON tblMemberInfo.MemberNo =
tblQualityData.MemberNo;
Click to expand...
Click to expand...
 
D

Don Garry

Hello, not sure whether we have exactly the same circumstances but I am getting the exact same error message on an Access 2000 database that has been running just fine for years.

The only difference I can see is that I'm getting this error on a brand new 'Vista' PC. All of my other clients PCs are using XP without a problem.

In my case I have a query that drives a form with an IIf statement in it.

I will carry on trying to resolve so if you find how to resolve before I do, I would sure appreciate knowing what the cause was.

Thx...Don Garry


EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 
D

Don Garry

Oops, I didn't scroll down far enough on the page to see that you had already solved the problem.

DanBCB, thank you very much for posting your solution....it solved my problem as well.

Have a good day !

Don Garry

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date() Not working?? 6
Expression Exceeds 1024 character limit of design view, HELP!? 1
DateDiff returning incorrect years 11
Time calc. 3
IIF Expression with Between Dates criteria 7
Calc Diff Date with ID - Karl Dewey 2
Error when trying to save a query 4
Help with small result type 2

Top