By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

field based on an expression (calculated field) and can not be changed error

P: 12
I have a calculated field in a query, it calculates the # of days an employee has been employed using the following statement =datediff("d", [HIREDATE], NOW()) by default. When an employee is terminated, I need that same field to be updated with the number of days employed. Simple huh, so simple I can't even think of it. In the TERMDATE lostfocus event I put the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub EMP_TERM_DATE_LostFocus()
  2.     If EMP_TERM_DATE <> "" Then [Days_Employed] = DateDiff("D",[EMP_HIREDATE], [EMP_TERM_DATE])
  3. End Sub
The error I receive is: days_employed is based on an expression and can not be changed

thanks for any help ...
Feb 12 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
This should work. Where are you setting the default for [Days_Employed] ?

Expand|Select|Wrap|Line Numbers
  1. Private Sub EMP_TERM_DATE_AfterUpdate()
  2.  
  3.    If Me!EMP_TERM_DATE <> "" Then 
  4.     Me![Days_Employed] = DateDiff("D",[EMP_HIREDATE], [EMP_TERM_DATE])
  5.    End If
  6.  
  7. End Sub
  8.  
Mary
Feb 12 '07 #2

P: 12
This should work. Where are you setting the default for [Days_Employed] ?

Expand|Select|Wrap|Line Numbers
  1. Private Sub EMP_TERM_DATE_AfterUpdate()
  2.  
  3.    If Me!EMP_TERM_DATE <> "" Then 
  4.     Me![Days_Employed] = DateDiff("D",[EMP_HIREDATE], [EMP_TERM_DATE])
  5.    End If
  6.  
  7. End Sub
  8.  
Mary
I just calculated in query design view like this:
Days Employed: DateDiff("d",tbl_NEW_DED_HIRE!EMP_HIREDATE,Now())
so it's an expression in the query.
Feb 12 '07 #3

P: 12
This should work. Where are you setting the default for [Days_Employed] ?

Expand|Select|Wrap|Line Numbers
  1. Private Sub EMP_TERM_DATE_AfterUpdate()
  2.  
  3.    If Me!EMP_TERM_DATE <> "" Then 
  4.     Me![Days_Employed] = DateDiff("D",[EMP_HIREDATE], [EMP_TERM_DATE])
  5.    End If
  6.  
  7. End Sub
  8.  
Mary
I tried that too and it won't update, should I remove the expression from the query and just use the field name from the table and write the extra code that says:
Private Sub EMP_TERM_DATE_AfterUpdate()

If Me!EMP_TERM_DATE <> "" Then
Me![Days_Employed] = DateDiff("D",[EMP_HIREDATE], [EMP_TERM_DATE])
else
Me![Days_Employed]=DateDiff("D",[EMP_HIREDATE], Now())

End If

End Sub
Feb 12 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
The answer is yes. If you've created a calculated field in the query it won't be updateable.
Feb 12 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
Alternatively, as it's a calculated field, don't store it at all. See Normalisation and Table structures for why this is not a good idea.
Feb 12 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.