Jul 26 2009, 06:06 AM
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?
Jul 26 2009, 11:22 AM
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
Jul 26 2009, 08:35 PM
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 ...
Aug 10 2012, 09:57 AM
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
theres more to this, this is just the area of failure.
Aug 10 2012, 12:38 PM
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).
Aug 10 2012, 12:41 PM
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.
Aug 10 2012, 12:44 PM
You're welcome... Hope the suggestion will resolve your problem...
Aug 10 2012, 04:21 PM
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.
Aug 10 2012, 04:44 PM
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here