The expression IIf(([EndDt]>date()),1,0) cannot be used in a calculated column

[R0N]

In Access 2010, I have set a field type to ‘calculate’ but there appears to be a restriction related to date. I wanted to use something link the following: IIf(([EndDt]>date()),1,0)
However, the error message displayed is pretty clear cut. Is there a way around this? I just wanted to populate a field based on two dates, one being the system date.

 

[vbaInet]

Can you explain exactly where and how you applied that expression.

 

[R0N]

I didn't. I wanted to use it but Access issued an error message. I would like to find out if there is a way of incoporating a date in a calculated field.

 

[vbaInet]

You said you got an error when you applied that expression so I asked you where did you put the expression and what did you do when you get the error message?

 

[R0N]

Sorry I wasn’t clear.

I created a table. One of the fields in the table I named Active. I set the Data Type for Active to ‘Calculated’. In the Field Properties for Active is Expression. Opening the Expression Builder (ie. I clicked on the “…” button associated with Expression) I was able to build an expression.

In testing out what I wanted to do I went through various iterations. For example, the following works just fine: IIf(([NFT]=827707401),1,0). The value of Active is assigned to either 1 or 0 based on whether the statement is true or false. I actually want to assign a value to Active by comparing a Date field held in the same table (EndDt) with the system date. For system date I thought Date() might work. However, fields with data type Calculated don’t seem to be able to work with Date().

I was wondering if there was a way around this.

 

[vbaInet]

I created a table. One of the fields in the table I named Active. I set the Data Type for Active to ‘Calculated’.

Ok, this isn't possible. You cannot have a Data Type other than what is defined in the drop down. Unless you mean a different property.

In the Field Properties for Active is Expression. Opening the Expression Builder (ie. I clicked on the “…” button associated with Expression) I was able to build an expression.

Which of the field's properties?

Never mind the above. The bottom line is all that you're trying to do in a table is futile. Where that expression should go is in a query or a form.

 

[R0N]

Ok, this isn't possible. You cannot have a Data Type other than what is defined in the drop down.

The data type 'Calculated' is included in the drop down.

I would like to know how to use a system date to populate a field.

 

[vbaInet]

Ah, that must be an Access 2010 feature. It rings a bell now. Sorry, I don't use Access 2010. I use 2007 and below.

I don't know the functionality of the Calculated field so I can't advise on that front. You can try prefixing your calculation with an equal to sign to see if it helps.

Besides that, it is not normal practice to save calculated fields because it can be calculated on-the-fly, which was why I mentioned putting that calculation in a query or a control on a form.

 

[R0N]

Yes, Access 2010. I wanted to provide some data verification at the table level, but I suppose it could be incorporated into a form.

Possibly nothing can be done, but I thought I’d ask. Thanks for trying. Maybe it will have to wait until I get some VB skills.

 

[vbaInet]

Maybe someone who's aware of this feature could chime in.

But in the meantime, we can work on getting the code working on your form.

 

[spikepl]

Access seems to have some logic in it. Logically, you should not be able to have a field in a table, whose value is variable, depending on some variable parameter outside of the table. Your value would change between today and any other day, depending on date, and how logical is that?

I am sure that if you have a field with default value Date(), then you can use that field for your calculation, and the result would be immutable with time.

 

[spikepl]

Tada: "The calc and store model cannot be used for volatile expressions such as Date() and Now() because the stored result would only be accurate at the moment it is initially calculated. Functions that reference outside the row of the table, such as DSum, DCount and DLookup are also prohibited because it would be costly from a performance perspective to detect when dependencies have changed. These types of calculated fields are better left in base queries." from http://blogs.office.com/b/microsoft...aintainable-apps-with-calculated-columns.aspx

And a cold shower from Allen Browne: http://allenbrowne.com/casu-14.html

 

[vbaInet]

@spikepl: Do you use Access 2010 and do you know about the 'Calculated' data type in Access 2010?

 

[spikepl]

vbaInet. Not yet, but likely cannot escape for long. And no more than I could dredge up in my perception of logic, which until further notice seems to fit the infoon the web, as the first link shows

 

[vbaInet]

It looks like you've done some digging up

 

[HGMonaro]

You could have a extra field in your table that you assign DATE() (using a query). A calculated field working of 2 dates in the table works. Tried it, worked ok but not sure if that's usable in your circumstances or a simple update query is more applicable.