IIF Statement to not Fill in Table

[gkrenton]

I have data where I only need to add the result if the date range falls from today to the current period. However if the date if after today I don't want anything to fill into the field. If I 'use' a blank I get an error about nulls since it's a data type mismatch when I use the following
iif([pr_beg]>date(), [pr_beg], else " " logic.
I know there's some way to do this without a data mismatch but my hacking skills haven't figured it out.

Any help would be greatly appreciated.

-Gina

 

[freewilly]

Gina, Is the field's Required property set to Yes?

What is the data type? If it is Text then you will have to also check the Allow Zero Length property. This need to set to Yes.

Good Luke

FW

 

[gkrenton]

Thanks for the tips, but unfortunately not the issue.
The date field is a date/time field & it's not set to required.

The actual error is MS Access set 78 field(s) to Null due to a
type conversion failure. So even though not a required field it doesn't like the else statement of " ".

Any other hints?

Gina

 

[GoDawgs]

Your iif statement should look something like this:

iif([pr_beg]>date(), [pr_beg], "&quot

Is that what you used? If not, try it. It sounds like you're getting the result you want though...Access is setting some fields to null (or is it setting all of the fields to null?)...

If that doesn't work, give us some data and some expected results.

Kevin

 

[GoDawgs]

Let's change that, sorry...misread the first time:

if([pr_beg]>date(), [pr_beg], null)

You say the field is not required so it should work with nulls, but you're not passing it a null, you're passing it a string of one space (" &quot...so it's trying to put that in a date field...not going to work...try the above.