Solved

Query Criteria, [ContractAmt]

Posted on 2013-11-17
14
338 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 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

13 Experts available now in Live!

Get 1:1 Help Now