I have a string attribute [TrnxPO] that I can sort by in a calculated field named Dated in a query. Expression is Dated:cdate(trnxPO). But when I try to filter the calculated filed for < date()-10 I get the error message "Invalid Use of Null".
Why would the select query sort by the calculated field but not allow me to filter by the same calculated field?

My first guess would be that there empty TrnxPO values in that field.

You could try first filtering out TrnxPO where 'IsNull'

hope this helps

If you would like to coerce your string to a Date, and don't want to filter out the Nulls or ZLS (Zero Length String), I suggest the following expression ...

=IIf(IsDate([TrnxPO]), CDate([TrnxPO]), Null)

The IsDate() can handle Nulls and ZLS's and any other anomaly the string of [TrnxPO] might toss out ...

this is for datAdrenaline,
I have a button that copies a series of fields because they need the data for the form to be applied to the next record, on my date fields, if they are blank, I get the error mentioned above, how can I use this in my code? =IIf(IsDate([TrnxPO]), CDate([TrnxPO]), Null)

Dim dat8 As Date

dat8 = Me.Issued.value (this is where I get the NULL error, I can't seem to get past this one)

I have a loop starting here to captue all the fields of interest
Me.Issued.Value = dat8
End if

DoCmd.OpenForm "myfrm"

theres more to this, this is just the area of failure.

thank you

The problem in your case is the Dim statement

Dim dat8 As Date

where the consequence is dat8 cannot be Null. dat8 actually has the default value 31/Dec/1899, i.e. numeric zero, even if your code does not assign any value to it.

Try declaring it to be a Variant instead... (probably name change also if "dat" is your standard prefix for DateTime variables).

thank you, I was wondering where that default date was coming from...I will try making the change to a variant and see what I get. I have 3 fields that are dates so I will see what I can do..
thanks again, I appreciate the input.

You're welcome... Hope the suggestion will resolve your problem...

Ok, I gave it a go, I changed the dat to txt, and made the variant, I still a default date of 1899 when a field is null. how else can I keep the field empty?
this is access 2007.

thanks

It sounds to me that the Control on your Form get the DefaultValue from somewhere else... Certainly it does not come from the Variant variable with Null value.

Have you checked the Table Design, especially the DefaultValue Property of the relevant Field and the FetchDefault Property of the Form? Or the DefaultValue Property of the Control on the Form?

Besides, the code you posted assigns a value which is distinct from DefaultValue in Access context. DefaultValue means that when the Form is on the NewRecord and before you enter anything (or before the codes assigns anything to the new record), a value (supposedly) is already displayed in the Control. Thus I am not too sure what you mean by "default value", especially when you have code assigning values.