Building An Expression Help

[Lisa Perry]

Hello,

I have a query that I need to build an expression and am not getting the results I need.

I have a frequency field containing numbers of 0-2000 and a field for a Last Exam Start Date (date) and if the last exam start date is null then I need it to return "00-00-0000", and if the last exam start date does have date then I need it to return that date -- any help would be appreciated.

This is what I have but not working and I was not sure how to put the last piece in about if it does have a date then put that?



Last Exam Start Date Up: IIf([TblHotListCCMGActiveClients].[Frequency]=0 And IIf[Last Exam Start Date]=Null,"00-00-0000"), [Last Exam Start Date]))

Thank you.

Lisa

[RuralGuy]

Have you looked at the Nz() function?

[Lisa Perry]

No, I have never used it - could you show me how to use in the above example?

[RuralGuy]

From Help: expression.Nz(Value, ValueIfNull)
...so...
Nz([Last Exam Start Date],"00-00-0000")

[Lisa Perry]

Thank you so much, that worked but there was just one more step and I truly apologize for not being more knowledgeable here and I am learning by example by you really nice, smart people out here who help me and I so appreciate it.


I only want this if I have a zero "0" in my Frequency field and no last start date and some do have a frequency but no last exam date yet. Is there a way to do that? If so, would you be so kind as to show me?

If not, thank you for the help you gave to me, it was so great!

Lisa

[RuralGuy]

What do you want the [Last Exam Start Date Up] field to contain if the [Frequency] field is *not* 0?

[Lisa Perry]

Hello,

If the frequency is not 0 and there is no last exam start date yet then I need the field to remain blank and then when that date eventually gets filled in that date will pass over...does that make sense? Thank you for all your help.

Lisa

[RuralGuy]

Try:
Last Exam Start Date Up: IIf([TblHotListCCMGActiveClients].[Frequency]=0, Nz([Last Exam Start Date],"00-00-0000"), ""))
Warning: Air code, not tested!!

FYI: It is not a good idea to have embedded spaces in any of your names. They generally cause grief down the road.

[RuralGuy]

Additional note, the code offered does not precisely match what you said.

[Lisa Perry]

Hello, thank you - that worked for the fields with 0 and no date and 0 and with a date but did not bring over the Last Exam Start Date for those with a frequency of >0 and maybe I did not word that correctly so you understood - I tried to add to the formula you sent - see below and I got errors - could you help me with the last step? I so appreciate your help and I am trying to learn... Lisa

Last Exam Start Date Upd: IIf([TblHotListCCMGActiveClients].[Frequency]=0,Nz([Last Exam Start Date],"00-00-0000"),""), this is what I added after your code... Iif([TblHotListCCMGActiveClients].[Frequency]>0,[Last Exam Start Date])) Example of what is passing over - which is great just missing the last step... Client Frequency Last Exam Start Date Last Exam Start Date Upd Need the other Last exams dates to come over if the freq is greater than zero Customer 360 29-Jan-13 Customer 360 30-Apr-12 Customer 0 18-Jun-10 6/18/2010 Customer 360 14-Aug-12 Customer 360 15-Apr-13 Customer 0 00-00-0000 Customer 360 25-Jun-12 Customer 720 11-Jul-11

[RuralGuy]

Okay, try this:
Last Exam Start Date Upd: IIf([TblHotListCCMGActiveClients].[Frequency]=0,Nz([Last Exam Start Date],"00-00-0000"),Nz([Last Exam Start Date],""))
...with the same warning as last time.

[Lisa Perry]

That did it, thank you so much and yes, I heard your warning about the spaces between field names...This database was created by someone else and hard to change the field names that have spaces as they are linked to so many other objects and would cause queries not to run, forms, reports, unless I can figure out where the field is located throughout etc...

Any way, thank you so much for you time, I so appreciate your help, you have no idea. Have a great day!

Lisa

[RuralGuy]

Excellent! Glad we could help.