[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

Access Prompts when I use Criteria

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
CompTech810
Asked:
CompTech810
  • 5
  • 3
  • 2
  • +4
1 Solution
 
Simon BallCommented:
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
 
Simon BallCommented:
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
 
CompTech810Author Commented:
I did that.  I added Expr1: [WE13]  with the criteria of >0 and then it prompted for WE13.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CompTech810Author Commented:
Hmmm....  I'll have to try that.  
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Can you post the full SQL for the query?

mx
0
 
CompTech810Author Commented:
Sudonim:  That has appeared to work, awesome.  I'm going to leave this open until I finish testing.
0
 
mbizupCommented:
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
 
Patrick MatthewsCommented:
You can almost certainly be achieved in a single query.  Please post the SQL statement, as MX requested.
0
 
mbizupCommented:
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
 
Simon BallCommented:
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
 
CompTech810Author Commented:
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
 
mansooralia_yahooCommented:
enclose the fields in square braces [ ]
0
 
smartcheaterCommented:
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
 
CompTech810Author Commented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 5
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now