Solved

Query Criteria, [ContractAmt]

Posted on 2013-11-17
14
327 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
  • 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now