• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

Query Criteria, [ContractAmt]

Experts,

I have a query that works but need to add an additional criteria on [ContractAmt].  
I am not sure why I get the error message as it is below.  
I can design a separate query with the same criteria on [ContractAmt] and dont get the error but I dont have all the additional criteria (the Cdate).  
If it matters, the Cdate formula is = Between CDate(Date()-30) And CDate(Date()+30)
[ContractAmt] ppty = Currency, Standard

Let me know what you think could be the issue.  

thank you

Criteria_On_ContractAmt
0
pdvsa
Asked:
pdvsa
  • 10
  • 4
1 Solution
 
jerryb30Commented:
I suspect the criteria type mismatch is with the cdates. Can you post your sql?  I assume your date fields are data data type.
0
 
pdvsaProject financeAuthor Commented:
Jerry, yes the date fields are date property.

Its interesting because I do not get an error when I have the criteria on a row that also has the Cdate criteria...only get the error when the > criteria is on a completely separate row.

the sql if very big.  I will post it though.
0
 
pdvsaProject financeAuthor Commented:
as I said previous answer above....sql is very big.  

SELECT DISTINCT Nz([tblBusinessUnit.BusinessUnit],"")="" AS Expr1, Projects.ATTApprvDate, Projects.ATSApprvDate, Projects.ATCApprvDate, Projects.BidCloseDate, Projects.AnticipatePOSignDate, Projects.CorpATTDate, Projects.ContractAward, Projects.ExpectedContractAward, tblStatus.Status, Projects.Currency, GetBUOwner([tblBusinessUnit].[BusinessUnit]) AS Owner, GetBizUnit([tblBusinessUnit].[BusinessUnit]) AS BU, tblBusinessUnit.BusinessUnit, Projects.SoleSource, tblFunding.CashFlow, IIf([tblStatus.Status] Like "*Awarded*" Or [tblStatus.Status] Like "Won*" Or IsNull([ContractAward])=False,"Awarded - within last 30 days",IIf([tblStatus.Status] Like "*Lost*" Or [tblStatus.Status] Like "*dead*" Or [tblStatus.Status] Like "*Cancel*","Lost/Dead - within last 30 days",GetGroup([Projects.BidCloseDate],[Projects.ATTApprvDate],[Projects.ATCApprvDate],[Projects.ATSApprvDate],[Projects.ContractAward],[Projects.CorpATTDate],[Projects.ExpectedContractAward]))) AS MyGroup, IIf(DCount("*","qryDepartures_2","ProjectID= " & [Projects.ID])>0,"No","") AS Compliant, IIf(DCount("*","qryDepartures_3","ProjectID= " & [Projects.ID])>0,"Yes","TBD") AS ExpctDepart, IIf(DCount("*","Union_HighRisk","ID= " & [Projects.ID])>0,"Yes","No") AS HighRisk, GetFormat([contractamt]) AS AmtOverlay, Projects.SentPPTSlide, Projects.Comments, Projects.EndUserID, Projects.[Project Name], Projects.ID, tblEndUser.[End User], Projects.NotReqdGoATT, Projects.PPTNotReqdYN, Projects.FRASYN, Projects.ContractAmt, Projects.NoRFQdocs, Projects.CreditAcceptableYN, tblFunding.NetExposureYN, Projects.NoRFQdocs, Projects.HedgingStrategy FROM tblEndUser RIGHT JOIN (tblFunding RIGHT JOIN ((Projects LEFT JOIN tblStatus ON Projects.Status2 = tblStatus.ID) LEFT JOIN tblBusinessUnit ON Projects.BusinessUnit = tblBusinessUnit.BUID) ON tblFunding.ProjID = Projects.ID) ON tblEndUser.EndUserID = Projects.EndUserID WHERE (((Projects.ATTApprvDate) Between CDate(Date()-30) And CDate(Date()+30))) OR (((Projects.ATSApprvDate) Between CDate(Date()-30) And CDate(Date()+30))) OR (((Projects.ATCApprvDate) Between CDate(Date()-30) And CDate(Date()+30))) OR (((Projects.BidCloseDate) Between CDate(Date()-30) And CDate(Date()+30))) OR (((Projects.CorpATTDate) Between CDate(Date()-30) And CDate(Date()+30))) OR (((Projects.ContractAward) Between CDate(Date()-30) And CDate(Date()+30))) OR (((Projects.ExpectedContractAward) Between CDate(Date()-30) And CDate(Date()+400))) OR (((Nz([tblBusinessUnit.BusinessUnit],"")="")=""));

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
pdvsaProject financeAuthor Commented:
here it is w/o formatted as code...not sure if it is easier to read.

SELECT DISTINCT Nz([tblBusinessUnit.BusinessUnit],"")="" AS Expr1, Projects.ATTApprvDate, Projects.ATSApprvDate, Projects.ATCApprvDate, Projects.BidCloseDate, Projects.AnticipatePOSignDate, Projects.CorpATTDate, Projects.ContractAward, Projects.ExpectedContractAward, tblStatus.Status, Projects.Currency, GetBUOwner([tblBusinessUnit].[BusinessUnit]) AS Owner, GetBizUnit([tblBusinessUnit].[BusinessUnit]) AS BU, tblBusinessUnit.BusinessUnit, Projects.SoleSource, tblFunding.CashFlow, IIf([tblStatus.Status] Like "*Awarded*" Or [tblStatus.Status] Like "Won*" Or IsNull([ContractAward])=False,"Awarded - within last 30 days",IIf([tblStatus.Status] Like "*Lost*" Or [tblStatus.Status] Like "*dead*" Or [tblStatus.Status] Like "*Cancel*","Lost/Dead - within last 30 days",GetGroup([Projects.BidCloseDate],[Projects.ATTApprvDate],[Projects.ATCApprvDate],[Projects.ATSApprvDate],[Projects.ContractAward],[Projects.CorpATTDate],[Projects.ExpectedContractAward]))) AS MyGroup, IIf(DCount("*","qryDepartures_2","ProjectID= " & [Projects.ID])>0,"No","") AS Compliant, IIf(DCount("*","qryDepartures_3","ProjectID= " & [Projects.ID])>0,"Yes","TBD") AS ExpctDepart, IIf(DCount("*","Union_HighRisk","ID= " & [Projects.ID])>0,"Yes","No") AS HighRisk, GetFormat([contractamt]) AS AmtOverlay, Projects.SentPPTSlide, Projects.Comments, Projects.EndUserID, Projects.[Project Name], Projects.ID, tblEndUser.[End User], Projects.NotReqdGoATT, Projects.PPTNotReqdYN, Projects.FRASYN, Projects.ContractAmt, Projects.NoRFQdocs, Projects.CreditAcceptableYN, tblFunding.NetExposureYN, Projects.NoRFQdocs, Projects.HedgingStrategy FROM tblEndUser RIGHT JOIN (tblFunding RIGHT JOIN ((Projects LEFT JOIN tblStatus ON Projects.Status2 = tblStatus.ID) LEFT JOIN tblBusinessUnit ON Projects.BusinessUnit = tblBusinessUnit.BUID) ON tblFunding.ProjID = Projects.ID) ON tblEndUser.EndUserID = Projects.EndUserID WHERE (((Projects.ATTApprvDate) Between CDate(Date()-30) And CDate(Date()+30))) OR (((Projects.ATSApprvDate) Between CDate(Date()-30) And CDate(Date()+30))) OR (((Projects.ATCApprvDate) Between CDate(Date()-30) And CDate(Date()+30))) OR (((Projects.BidCloseDate) Between CDate(Date()-30) And CDate(Date()+30))) OR (((Projects.CorpATTDate) Between CDate(Date()-30) And CDate(Date()+30))) OR (((Projects.ContractAward) Between CDate(Date()-30) And CDate(Date()+30))) OR (((Projects.ExpectedContractAward) Between CDate(Date()-30) And CDate(Date()+400))) OR (((Nz([tblBusinessUnit.BusinessUnit],"")="")=""));
0
 
jerryb30Commented:
I don't see Project.ContractAmt in the SQL.
Will Access not allow you to view as sql with ContractAmt criteria included?

Do you have extra expression in
(((Nz([tblBusinessUnit.BusinessUnit],"")="")=""));
Should it be:
(((Nz([tblBusinessUnit.BusinessUnit],"")=""));
0
 
pdvsaProject financeAuthor Commented:
HI Jerry, it is ProjectS. (notice S).   I think you can find that way.  

Yes, I can view it in SQL with the criteria on Projects.ContractAmt:
here it is
OR (((Projects.ContractAmt)>50000000));

let me know what you think now.
0
 
pdvsaProject financeAuthor Commented:
<Do you have extra expression in
(((Nz([tblBusinessUnit.BusinessUnit],"")="")=""));
Should it be:
(((Nz([tblBusinessUnit.BusinessUnit
],"")=""));

this is how I have in the QD:
Expr1: Nz([tblBusinessUnit.BusinessUnit],"")=""
0
 
pdvsaProject financeAuthor Commented:
Hi Jerry just checking in....thank you
0
 
jerryb30Commented:
pdvsa,
Sorry.
On the face of it, I cannot see why you have the data type mismatch.
Can you post an empty database (mdb format please), so I do not have to recreate the data structure? Or a sanitized sample?
Jerry
0
 
pdvsaProject financeAuthor Commented:
OK...let me see if I can do that.  Might have to wait till tomorrow or weekend though.  

thank you for checking in....
0
 
pdvsaProject financeAuthor Commented:
Jerry, the report was corrupted.  I reimported all into a new db and the criteria issue went away.  

How often do you reimport into a new db?  Last time I did was about a month ago.

I guess I will have to delete the question?  

thanks
0
 
jerryb30Commented:
I don't do it often.
Ask that it be closed, with no points, as you solved it.
Someone else may run into same problem.
0
 
pdvsaProject financeAuthor Commented:
ok thank you sir...sorry about that.
0
 
pdvsaProject financeAuthor Commented:
I found that the report was corrupted.  I reimported all into a new db and the issue went away.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now