Group By: Date Comparison Vs. Current Date?

[andmunn]

Hello Guys,

I'm fairly "new" to access, however, developing and learning as i go... I have a table which returns the following fieldS:

> Company
> Product
> ID
> Production Date.

I want to create a query which would output hte results as follows. Basically, it compares "today's date" (current datE) to the production date, and then groups the results as below:

Name | Product | 1-5 Days | 6-10 Days | > 11 Days |

I created the query which "groups" the above according to Company / Product, and then provides a count ignoring the dates - but now i need it to further filter according to the dates above?

Any advice?
Thanks,
Andrew.

 

[Brianwarnock]

you need to use the datediff function eg

Prod1_5: sum(iif(datediff,"d",[production date],date())>=1 And (datediff,"d",[production date],date())<= 5,1,0)

Brian

 

[andmunn]

Brian,

Thanks for the help - i tried inserting the code into my query (After modifying it for my table) :

Prod1_5: sum(iif(datediff,"d",[2]![CREAT_TMSTMP],date())>=1 And (datediff,"d",[2]![CREAT_TMSTMP],date())<= 5,1,0)

I tried putting thisn the "field" area of the 4th column..however, i keep getting an error.

I've attached an image of my query to this point... It basically sorts the results by division and type, then counts the number of incidents...

However, i need it to count the number of incidents according to the "time frame" they fall in - i.e./ 1-5 days, 6-10 days, >11 days..

Hopefulyl this makes more sense now that i've attached na image of my query.

Andrew.

 

[Brianwarnock]

Sorry my oversight did not complete the instructions,
in the Totals row select expression.
Another way to do this is to leave the Sum off the expression and select Sum in the totals row, this allows you to run the query as a non totals query which can be useful for checking whats going on if the results are in doubt.

Brian

 

[CraigDolphin]

I think there was some erroneous syntax...

The normal form for Datediff is

=DateDiff("increment here","[FirstDateFieldHere]","LastDateFieldHere")

Prod1_5: sum[COLOR="blue"]([/COLOR]iif[COLOR="DarkGreen"]([/COLOR](Datediff[COLOR="red"]([/COLOR]"d",[2]![CREAT_TMSTMP],date()[COLOR="Red"])[/COLOR]>=1) And (Datediff[COLOR="Red"]([/COLOR]"d",[production date],date()[COLOR="red"])[/COLOR]<= 5),1,0[COLOR="darkgreen"])[/COLOR][COLOR="Blue"])[/COLOR]

Hopefully that works but it's air-code so has not been tested.

And as a note....please, for the love of all that you hold holy, please, please PLEASE take some time to learn about naming conventions for tables, field names etc and remember that if you die (or get murdered by an access developer who sees that jpg ) some other poor schmuck will have to try to figure out what the hell your tables and fields represent.

 

[andmunn]

Hi Guys,

Thanks so much for your help... i can conclude that the code for getting results for 1-5 days would be:

Prod1_5: sum(iif((Datediff("d",[2]![CREAT_TMSTMP],date())>=1) And (Datediff("d",[CREAT_TMSTMP],date())<= 5),1,0))

However, for the life of me, wherever in my "query" i put this code, i get an error of some kind....

Going from the image i attached earlier, where would i place this code? Would i leave the query as as "Totals" query?

And FYI - lol - i didnt' create these table names / etc - i'm simply inheriting them - and impossile to change at the moment.

Thanks for your patience - i really am VERY ignorant when it comes to MS Access, and i pretty much learn by trying (then failing about 100x times) until somehow it finally works

 

[Brianwarnock]

OOps! my typos, sorry

I have explained, I hope but not having the best of dats, how to use this in post it is just more columns in the query.

brian

Ps what are the errors?

 

[andmunn]

I've attached this expression (cut and paste directly from my query) into a new column:

Prod1_5: sum(iif((Datediff("d",[2]![CREAT_TMSTMP],date())>=1) And (Datediff("d",[CREAT_TMSTMP],date())<= 5),1,0))

As shown in the attachemt - i've also included the error i get. I know i'm missing something Thanks again for the help..

Andrew.

 

[Brianwarnock]

It is saying that there is an error in a VBA module.
I don't understand that.

I note that the Show is not checked, but more worryingly the sum and iif have not capitalised as if they have not been found.

I honestly don't know what's going in , can you attach your db, I'm on 2002 but I can handle all but 2007 I think.

Brian

 

[andmunn]

I've filtered out my database to take out confidential information ..... and limited it to about 2000 records in the table...

This is MS Access 2003 database..

Any help appreciated..

Basically, you can see from looking at "Query1" - i got it to group by division , then by type, but now i need it to divide by days..in different columns..

Any advise appreciated

Andrew.

 

[Brianwarnock]

I copied and pasted the code from post 10 into a 4th columnand it all ran fine.

I didn't think that you can have a missing library for the basic functions such as those being used but I've no answer as to why it is not working for you.

I will ask for help from other VIPs.

Brian

 

[pbaldy]

I'm out the door for lunch, but like Brian I copied the formula and it worked fine:

SELECT [2].DIV_NM_EN, [2].INCDNT_REC_TYP, Count([2].INCDNT_ID) AS CountOfINCDNT_ID, Sum(IIf((DateDiff("d",[2]![CREAT_TMSTMP],Date())>=1) And (DateDiff("d",[CREAT_TMSTMP],Date())<=5),1,0)) AS Prod1_5
FROM 2
GROUP BY [2].DIV_NM_EN, [2].INCDNT_REC_TYP;

Is that not the result you want?

 

[andmunn]

You guys are correct - ti does work when i copy it into that database....In fact, it works perfectly....

However! When i copy it into my "real" database, i still get that same error as above although i do nothing to modify / edit the code....this makes no sense?

FYI...

Im' trying to getg a code working for days > 90...

i did this:

Prod90: sum(Datediff("d",[2]![CREAT_TMSTMP],date())>=91)

However, it returns negative numbers, so i know that can't be right?

 

[pbaldy]

It's probably the correct number but negative, and perfectly understandable based on how you did it. You want the same technique you used on the other, using the IIf() function.

 

[andmunn]

I see what you are saying - but how do i get it to give me a positive number? I assume i'd haev to use a different forumala?

Andrew.

 

[Brianwarnock]

You are getting a TRUe result from the comparison which is -1

Brian

 

[pbaldy]

I see what you are saying - but how do i get it to give me a positive number? I assume i'd haev to use a different forumala?

Andrew.

Use the same one you were using before:

Sum(IIf(..., 1, 0))

 

[andmunn]

Thanks everyone,

That worked perfectly in the "example" database i pasted on here...any one have any clue why i'm still getting that error (pasting the EXACT same code) into a different database?

Andrew.

 

[pbaldy]

I've never seen a compile error in a query. Does code compile before you try this? Are the field names the same? Any bad or missing references in the VBA editor?

 

[CraigDolphin]

Note: this post is a response to Brian's earlier question....not to the code you've used most recently. I have no idea why the syntax would work in your test db and not your production UNLESS there's something screwy in the real data like some alphanumeric values or nulls where there should only be numbers.

Ps what are the errors?

Prod1_5: sum(iif(datediff,"d",[production date],date()) >=1And (datediff,"d",[production date],date())<= 5,1,0)

Without looking too closely at anything else....the part highlighted in red reads:
datediff,"d",[production date],date())
And I think should read:
datediff("d",[production date],date())