Solved

Query Criteria, [ContractAmt]

Posted on 2013-11-17
14
366 Views
Last Modified: 2013-12-04
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
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
14 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 39655010
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
 

Author Comment

by:pdvsa
ID: 39657481
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
 

Author Comment

by:pdvsa
ID: 39657482
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:pdvsa
ID: 39657488
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 39657515
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
 

Author Comment

by:pdvsa
ID: 39657693
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
 

Author Comment

by:pdvsa
ID: 39657701
<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
 

Author Comment

by:pdvsa
ID: 39664248
Hi Jerry just checking in....thank you
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39664461
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
 

Author Comment

by:pdvsa
ID: 39665710
OK...let me see if I can do that.  Might have to wait till tomorrow or weekend though.  

thank you for checking in....
0
 

Accepted Solution

by:
pdvsa earned 0 total points
ID: 39670086
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 39670099
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
 

Author Comment

by:pdvsa
ID: 39675980
ok thank you sir...sorry about that.
0
 

Author Closing Comment

by:pdvsa
ID: 39695007
I found that the report was corrupted.  I reimported all into a new db and the issue went away.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question