Solved

Query Criteria, [ContractAmt]

Posted on 2013-11-17
14
346 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
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: 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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