IIf([HELP!],"","")

[hardesttrance]

Hi all:

Please forgive me if this question has been posted already. For some reason I get a server error whenever I run a search for what I need help with.

I have an simple Access DB in which I need the date to auto-populate and store into the table when a specific field is marked "Yes" (checkbox)

I have the code as follows:
IIf([Feedback]=-1,Date(),"")
(if feedback field is marked yes, display the system date, otherwise, display null)

For some reason, it will dispay the system date, but it does not store the result. I tested this by creating a record, then going to another workstation set up with yesterdays system date... It displayed yesterday's system date.

Any ideas on where I'm going wrong?

Thanks for your help!
-Chris

Chris

http://www.bshcinfo.org/v2_beta

- in development

 

[KornGeek]

Where are you running this code?

You state that the code should "if feedback field is marked yes, display the system date, otherwise, display null", which it looks like it should do (actually, an empty string, which is subtly different from a NULL).

However, you later say that it is displaying the result, but not storing it. Based on the fact that the code is used to display (and not store), it seems like that is working correctly. If you're trying to use it to store the result, try replacing "" with NULL. You can't store a string in a date field.

 

[hardesttrance]

Wow... For some reason... I'm not surprised that I missed that.

I did as you suggested, but it still didn't store what I needed. What should I be using since "IIf" obviously isn't what I need to be using?

Thanks!
-Chris

Chris
cgferguson@gmail.com
AIM: HardestTrance
ICQ: 81443730

http://revivednumbness.blogspot.com

"Long Live Toast!

 

[KornGeek]

Actually, based on what you've described, it seems that IIF is returning the correct result. I suspect the issue has more to do with where you are using it.

 

[hardesttrance]

I am using it as the control source in the form. It is returning the correct result... it just doesn't store it... If my user needs to view when the feedback was received, they get today's date, instead of the date that they actually received it.

Thanks!


Chris
cgferguson@gmail.com
AIM: HardestTrance
ICQ: 81443730

http://revivednumbness.blogspot.com

"Long Live Toast!

 

[KornGeek]

Chris,

OK, first of all, if you're using the IIF function as the control source, Access doesn't attempt to store the value in a field. (After all, the field is a function that returns a result, not a field in the underlying table.)

Some options would include using an event (Such as the form OnOpen event or the AfterUpdate event from the feedback control) to either programmatically update the record in the table, or tie the control's data source to the field in the table, and on the event, use the IIF function to update that control.

Also, the reason it is showing today's date is that you're using the Date() function which returns the current date. It sounds like you want to extract a date field from the underlying table rather than using this function.

I'm sorry I can't give a clearer response right now. I'm wrestling with some issues of my own. (Thanks to Tek-Tips for the assist on my issues also. This site is amazing!)

 

[hardesttrance]

Actually your last message helps me out a lot! Thanks! I'll look further. (I'm an Access newbie, hence the questions)

Thanks again!
-Chris

Chris
cgferguson@gmail.com
AIM: HardestTrance
ICQ: 81443730

http://revivednumbness.blogspot.com

"Long Live Toast!

 

[KornGeek]

Chris,

Don't worry about asking questions. We all started as newbies, and we all have questions from time to time.

Every question that you ask, somebody else will have later, so think of it as doing them a favor.