If then Else or something similar

[lutzkac]

Hi Everyone,
I am in a bit of a pickle and need some expert advice. I have a db in access. I am trying to create a query with fields from the tables. In my query I need to take the value of one field and depending on what the value is pull a date from other fields. I have tried a IIF and it errors. Tried to write an if-then-else function ( which I doubt I did right since I have never done one) and I get an undefined expression error.

So for my example it would be from the function I wrote....

Public Function StatusDate(StatusDate)
If [WO Info Main]![WO Status] = 1 Then
StatusDate = [WO Info Main]![Final Date Received for Estimate]
Else
If [WO Info Main]![WO Status] = 2 Then
StatusDate = [WO Info Main]![Date Estimate Completed]
Else
If [WO Info Main]![WO Status] = 3 Then
StatusDate = [WO Info Main]![FD Assign Date]
Else
If [WO Info Main]![WO Status] = 4 Then
StatusDate = [WO Info Main]![FD Approval Sent Date]
Else
If [WO Info Main]![WO Status] = 5 Then
StatusDate = [WO Info Main]![Development Assign Date]
Else
If [WO Info Main]![WO Status] = 6 Then
StatusDate = [WO Info Main]![Test Deploy Date]
Else
If [WO Info Main]![WO Status] = 7 Then
StatusDate = [WO Info Main]![Production Deploy Date]
End If
End If
End If
End If
End If
End If
End If
End Function

Can someone tell me if this is something I can do or if there is another way. Any help would be much appreciated and any ribbing as well. Thanks.

[FishVal]

Hi, there.

I think SELECT CASE construction or, even better, table lookup or, even more better, database normalization would look more elegant.

Regards,
Fish

[lutzkac]

And how would I create a select case. Like I said I am pretty novice to this and not even sure if I wrote this function right. Thanks for the help.

[FishVal]

Have you already tried to find it in Access help?

Kind regards,
Fish.

[lutzkac]

Yes I have used the very limited Help feature on Access, I have search on countless forum pages for examples to go by. The problem I have is none of the example I have found are doing exactly what I am looking for which is evaluating one field and based on that field populate with another field. So I have searched hi and low as well as experimented and no luck.