Replace IFF with something else??

[wrightyrx7]

Hi all,

I have a query which determines if a case is open or closed by the closed date (some close dates could be in the future)

I am guessing having this as a field is slowing the data when loaded

IIf([Date Closed]<=Date(),"Closed","Open")) AS [Case Open/Closed]

I would prefer this to be some sort of calculated field in the table.

Is there any better way of doing this?

Regards
Chris

 

[pbaldy]

That might slow it down, but I wouldn't expect it to be much (try without). Later versions of Access do have a calculated field data type which could be used for this.

Edit: forgot to say there must be a cut/paste issue, as that would throw an error

 

[wrightyrx7]

That might slow it down, but I wouldn't expect it to be much (try without). Later versions of Access do have a calculated field data type which could be used for this.

Edit: forgot to say there must be a cut/paste issue, as that would throw an error

Hi pbaldy,

Thank you for your reply. I have Access 2016 and i have just tried to add the above IFF as a calculated field but i get the following error

"The expression IIf([Date Closed]<=Date(),"Closed","Open") cannot be used in a calculated column.

 

[arnelgp]

if you can create a calculated field in your table, but Date() is not acceptable in calculated field.

 

[moke123]

i'm not sure if the date comparison is integral to what you want to do, but you could also try..

iif(isnull([DateClosed]),"Open","Closed")

edit: oops just re-read that closed could be in the future.

 

[pbaldy]

Hmm, some testing and research indicates that you can't use Date() in a calculated field.

 

[moke123]

why store a calculated field in the table? why not just include it in a query and use it wherever needed.

 

[The_Doc_Man]

I'm with Moke on this one. Just remember that you need a stream of records, which Access calls a recordset, for most queries, forms, reports, and VBA recordset operations - but you can get that from a table OR a SELECT query opened in dynaset or table mode.

As to computed fields and the speed of opening something, if you had a query that included a field like this...

SELECT a, b, c, ..., NZ([DateClosed], 0) < Now() AS CaseOpen, ... FROM ....

then you could test CaseOpen as a True/False field. The only oddity here would be whether you would allow/require a null [DateClosed] to count as open or closed. If you required the null case to be OPEN, then that field becomes more complex. One approach would be

SELECT a, ...., IIF( IsNull([DateClosed], True, [DateClosed]< Now()), ...

As to speed, I doubt you would see a slowdown on this case UNLESS one of a few simple situations apply: (1) the data tables are in a backend database file that is accessible only over a very slow network link, or (2) you have 100K records returned by the SELECT query that would drive this process, or (3) the query includes an ORDER BY but you don't have an index on the fields being ordered.

If this approach is still slow and none of the above cases apply, then your slowness problem isn't with the recordset, it is with the thing that is trying to open the recordset. Seriously, Access queries as simple as the one suggested above are not that slow because the query engine is compiled, not interpreted.

 

[wrightyrx7]

As to speed, I doubt you would see a slowdown on this case UNLESS one of a few simple situations apply: (1) the data tables are in a backend database file that is accessible only over a very slow network link, or (2) you have 100K records returned by the SELECT query that would drive this process, or (3) the query includes an ORDER BY but you don't have an index on the fields being ordered.

If this approach is still slow and none of the above cases apply, then your slowness problem isn't with the recordset, it is with the thing that is trying to open the recordset. Seriously, Access queries as simple as the one suggested above are not that slow because the query engine is compiled, not interpreted.

You have just given me the anser to why it is slow. The data tables are in a backend database that is on a network drive which is pretty slow.

I am currently trying to learn how to get the data to go onto a sql database that our IT department have set up for me. So i think this is what i need to get on with by the sounds of things.

I dont think it helps that i also have: -
-created a function to work out the number of days a case has been open (unless closed)
-conditional formatting on the number of days the case has been open (RAG Rating)


... anyway i will stop going on with myself.

Appreciate you all taking the time to post and make suggestions. Hopefully getting the data into SQL will help.

 

[CJ_London]

I am currently trying to learn how to get the data to go onto a sql database that our IT department have set up for me

if you are doing this, you definitely don't want a calculated field - sql server doesn't use them

 

[pbaldy]

if you are doing this, you definitely don't want a calculated field - sql server doesn't use them

https://msdn.microsoft.com/en-us/library/ms188300(v=sql.105).aspx

 

[moke123]

i still say the calculated field is not needed assuming it is only needed to display on a form. No one should be looking at your raw data anyway. Calculate as needed on your forms or reports.

 

[CJ_London]

I stand corrected

 

[wrightyrx7]

i still say the calculated field is not needed assuming it is only needed to display on a form. No one should be looking at your raw data anyway. Calculate as needed on your forms or reports.

THis is what i am going to do not that i have established that my data is loading slow due to the back-end database being on a slow network drive

 

[pbaldy]

I stand corrected

I should have added that they added that in the 2008 version if memory serves, so it's a fairly recent addition.