Solved

Access Prompts when I use Criteria

Posted on 2011-09-15
14
235 Views
Last Modified: 2012-05-12
When I use the query criteria selection  >0      Access prompts for NewWe and then prompts for PASTWE.  When I don't use criteria I get the data fine but I don't want the data that is zero.

Field: WE13: IIf(CDate([NewWe])<=DateAdd("ww",13,CDate([PASTWE])),([NewWe]),0)


Criteria: >0
0
Comment
Question by:CompTech810
  • 5
  • 3
  • 2
  • +4
14 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 36545773
are those expressions - the newwe and pastwe - if so, i think having the >0 criteria causes the clause to be evaluated before they have been created....

add a new column for the criteria and see if that helps
0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 125 total points
ID: 36545779
actually, i think when i encountered this problem, i ended up savign query1... and adding a 2d query, with select * from query 1 in it, and then putting the criteria and there for that column, so that it forces the expressions to be created before the criteria is apllied...
0
 
LVL 2

Author Comment

by:CompTech810
ID: 36545782
I did that.  I added Expr1: [WE13]  with the criteria of >0 and then it prompted for WE13.

0
 
LVL 2

Author Comment

by:CompTech810
ID: 36545794
Hmmm....  I'll have to try that.  
0
 
LVL 75
ID: 36545801
Can you post the full SQL for the query?

mx
0
 
LVL 2

Author Comment

by:CompTech810
ID: 36545826
Sudonim:  That has appeared to work, awesome.  I'm going to leave this open until I finish testing.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36545868
You don't actually need two queries.  Sudonim's logic is sound, but the problem can also be handled by using the experssion instead of it's alias in the WHERE clause.

In other words, the SQL view would look like this:

SELECT field1, field2, DateAdd("ww",13,CDate([PASTWE])),([NewWe]),0) as [WE13]
FROM somewhere
WHERE DateAdd("ww",13,CDate([PASTWE])),([NewWe]),0) > 0


You would likewise use the expression, not the alias in an Order  By clause, I believe.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36545870
You can almost certainly be achieved in a single query.  Please post the SQL statement, as MX requested.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36545889
I missed part of the expression in the above... it should be IIf(CDate([NewWe])<=DateAdd("ww",13,CDate([PASTWE])),([NewWe]),0)  in both places, but you get the idea.


0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 36548101
aah yes, good call mbizup

you will need to replace both the expressions source code in the where clause.

If its not a big query, i'd stick with having two...
0
 
LVL 2

Author Comment

by:CompTech810
ID: 36548591
Here is the SQL statement:


SELECT Administrators_SOD_1_BaseData.Item_Wo_Gl, Administrators_SOD_1_BaseData.Cust_Name, Administrators_SOD_1_BaseData.Description, Administrators_SOD_1_Del_Date_Data.Req_Del_Dt, Administrators_SOD_1_BaseData.Qty_On_Hand, Administrators_SOD_1_Del_Date_Data.Bal_Qty, Administrators_SOD_1_Del_Date_Data.Bal_Ext_Price, Administrators_SOD_1_Del_Date_Data.ID, Administrators_SOD_1_Del_Date_Data.Prod_Del_Date, Administrators_SOD_1_Del_Date_Data.Prod_Date_Note, Date()-Weekday(Date())+1 AS PASTWE, CDate(IIf(Weekday(Nz([Prod_Del_Date],[Req_Del_Dt]))=1,(Nz([Prod_Del_Date],[Req_Del_Dt])),(8-Weekday(Nz([Prod_Del_Date],[Req_Del_Dt])))+(Nz([Prod_Del_Date],[Req_Del_Dt])))) AS WEDATE, IIf([WEDATE]<[PASTWE] And [Prod_Del_Date] Is Null,"1/1/2000",[WEDATE]) AS NewWE, IIf(CDate([NewWe])<=DateAdd("ww",13,CDate([PASTWE])),([NewWe]),0) AS WE13
FROM Administrators_SOD_1_BaseData INNER JOIN Administrators_SOD_1_Del_Date_Data ON Administrators_SOD_1_BaseData.ID = Administrators_SOD_1_Del_Date_Data.ID
WHERE (((Administrators_SOD_1_Del_Date_Data.Bal_Qty)>0))
ORDER BY Administrators_SOD_1_BaseData.Item_Wo_Gl;
0
 
LVL 2

Expert Comment

by:mansooralia_yahoo
ID: 36553878
enclose the fields in square braces [ ]
0
 
LVL 1

Expert Comment

by:smartcheater
ID: 36554683
Use WHERE instead of IF.

and

Date type data comparision are made in text format by default not as number format. So you will not get a required output. You will have to write a self procedure for comprision.
0
 
LVL 2

Author Closing Comment

by:CompTech810
ID: 36573910
I elected to pick this solution because of timing and did not have a chance to try the rest of the solutions provided.  Thanks everyone for their solutions.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

863 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

26 Experts available now in Live!

Get 1:1 Help Now