ACCESS / EXCEL formula comparison

[Leadparsons]

Hi All,

New DB is working great thanks to all your help. Have a new problem.
I have 3 columns in excel and a 4th with a formula

Main Reject Return 60 days
27/08/2014 21/10/2014 22/10/2014 21/12/2014
24/01/2015 25/01/2015 DO REJECTIONS
17/02/2015 18/04/2015

"Main" is the first input date
"Reject" is the date it gets rejected
"Return" is the date returned after Reject

The 4th column needs to show 60 days from the "Main" date, "Do Rejections" if a date is in "Reject" and 60 days from "Return" date

Excel I use"=IF(AND(B2>0,C2>0),C2+60,IF(AND(B2>0,C2<1),"DO REJECTIONS",IF(AND(B2<1,A2>0),A2+60,0)))"

I Have tried in access IF(AND([Reject]>0,[Return]>0),[Return]+60,IF(AND([Reject]>0,[Return]<1),"DO REJECTIONS",IF(AND([Reject]<1,Main]>0),[Main]+60,0)))

Can anyone help ?

 

[Leadparsons]

Excellent, that works a treat, thank you.

If you set up 2 fields in your query:

Expr 1: IIf(IsDate([tblYourTable]![Reject]) And IsDate([tblYourTable]![Return]),[tblYourTable]![Return]+60,IIf(IsNull([tblYourTable]![Reject]) And IsDate([tblYourTable]![Main]),[tblYourTable]![Main]+60,Null))


Expr2: IIf(IsDate([tblYourTable]![Reject]) And IsNull([tblYourTable]![Return]),"Do Rejections",Null)

The query will return a calculated date or null in the 1st column
or
It will return Do Rejections or Null in the 2nd column

Using is IsDate and IsNull avoids the ambiguity of < or > 0 or 1

 

[Leadparsons]

OK, new question but related. From my query I have created a form, this now shows the Expr2 above. Is there a way I can show on the form a count of how many Expr2 fields are blank, i.e Expr2 = 8 ?

 

[Ben Nevis]

You need a new query to do this:
1 Create a new query in design view
2 Select the Queries tab and add the query with Expr2
3 Drag Expr2 from the query to Field
4 Click on the capital sigma in the toolbar, in Total: it will say "Group By"
5 Change it to Count

If you run the query it will give you the number of "Do rejections" since it wont count the Nulls.
You can then add this field to your report

 

[Leadparsons]

I don't understand "Drag Expr2 from query to Field.
I have used the sigma to count, and it shows 1 if there is a rejection, however, I want a field to show the total number of Rejections.

 

[Ben Nevis]

If you create a new query in design view and add the query with Expr2 to it you should be looking at a screen with 2 panes. The upper pane should have a box in it representing your query and you should see Expr2 inside that box.
Click on Expr2, hold the mouse button down and drag it to the lower pane and drop it on the first column where it says field.
This structure is called the Query by Example (QBE) grid and is fundamental to writing queries in Access.
When you click on Sigma in the toolbar it should add a row called Total to the lower pane and this is where you change "Group By" to "Count". When you run the query it should only return 1 row with a total figure in it.
Let me know if I am making these steps clear since I am not able to paste a screenshot.

 

[Norie]

For the count you could tryDCount.

CountBlanks:DCount("[Reject]","[tblYourTable]","IsDate([Reject]) And IsNull([Return])")

 

[Leadparsons]

I see what you mean now, is there a way I can include that in a form I have already created ?

If you create a new query in design view and add the query with Expr2 to it you should be looking at a screen with 2 panes. The upper pane should have a box in it representing your query and you should see Expr2 inside that box.
Click on Expr2, hold the mouse button down and drag it to the lower pane and drop it on the first column where it says field.
This structure is called the Query by Example (QBE) grid and is fundamental to writing queries in Access.
When you click on Sigma in the toolbar it should add a row called Total to the lower pane and this is where you change "Group By" to "Count". When you run the query it should only return 1 row with a total figure in it.
Let me know if I am making these steps clear since I am not able to paste a screenshot.

 

[Leadparsons]

Cant seem to get this to work as im already using expressions below:

Expr 1: IIf(IsDate([tblYourTable]![Reject]) And IsDate([tblYourTable]![Return]),[tblYourTable]![Return]+60,IIf(IsNull([tblYourTable]![Reject]) And IsDate([tblYourTable]![Main]),[tblYourTable]![Main]+60,Null))


Expr2: IIf(IsDate([tblYourTable]![Reject]) And IsNull([tblYourTable]![Return]),"Do Rejections",Null)

For the count you could tryDCount.

CountBlanks:DCount("[Reject]","[tblYourTable]","IsDate([Reject]) And IsNull([Return])")

 

[Norie]

What I posted is just for the count you mentioned in post #22.

Where do you want that count to appear?

What are you actually showing on the form?

 

[Ben Nevis]

If you've written the query that gives you the total from Expr2 to work (i.e. returning one cell with a total in it) you can add a sub form based on that query to your form:
Open your form in design view and click on the Subform/Subreport button in the toolbox - if you can't see the toolbox, click View and click on Toolbox
Select Use existing Tables and Queries and select the query with the total in it and move that field into the Selected fields box. There are a few more steps but you should be able to work it out
That will add the total to your form.