Query returning a NULL date - how to check for this null

[Richard Horne]

I have a form whose recordsource is updated on the AfterUpdate event of a control on the form. A query is built according to the value chosen in the form and the form's recordsource requeried accordingly.

One of the fields returned by the query is a date field (promised_date). A bound control called promised_date exists on the form to hold the value of this field.

When this date is NULL I get an error message stating:

The value you entered isn't valid for this field.

I have tried the following to stop this and but still get the same error:

If IsNull(Me.promised_date) then
    'Do something
else
    'Do something else
End If

I have checked the database table and the field allows NULL and NULL is definitely stored when a date hasn't yet been set. What else can I do to recognise that the date field is NULL?

Thanks in advance.

 

[CJ_London]

since you are updating the recordsource in the after update of a control, it could be any of the fields, not just this one

not enough information to suggest anything else. You need to provide the whole event code as a minimum since the little you did provide doesn't do anything

But would suggest do your validation in the form beforeupdate event, not the control afterupdate

 

[theDBguy]

A query is built according to the value chosen in the form...

Can you please show us this query? Thanks.

 

[Richard Horne]

I ommited most of the code because it's very specific to my form/query but see below:

Private Sub main_form_requery()

str_sales_order_item_qry = " AND sales_order_status.Sales_Order_Detail_ID = " & Me.cbo_sales_order_item_id_filter

Dim str_main_form_qry As String

str_main_form_qry = "SELECT sales_order_status.sales_order_id, sales_order_status.company_name, sales_order_status.order_date, sales_order_status.customers_ref, sales_order_status.product_code, " _
& " sales_order_status.item_number, sales_order_status.sales_order_quantity, sales_order_status.nsn, sales_order_status.tl_number, sales_order_status.item_type_name, " _
& " sales_order_status.secondary_item_type_name, sales_order_status.design, sales_order_status.sort_colour, sales_order_status.SumOfquantity_delivered AS SO_Quantity_Delivered, " _
& " sales_order_status.LastOfapproval_submission_date AS CWS_Last_Submission, sales_order_status.LastOfsample_comments AS CWS_Last_Comment, sales_order_status.LastOfapproval_date AS CWS_Last_Approval_date, " _
& " sales_order_status.LastOfsample_rejected_date AS CWS_Last_Rejected_Date, sales_order_status.width, sales_order_status.width_denomination, sales_order_status.despatch_date, " _
& " sales_order_status.promised_date, sales_order_status.LastOfcompany_name, sales_order_status.LastOforder_date, sales_order_status.purchase_order_id, sales_order_status.revised_delivery_date, " _
& " sales_order_status.purchase_quantity, sales_order_status.PO_Outstanding, sales_order_status.CWS_Submitted, sales_order_status.Sales_order_Detail_ID, " _
& " sales_order_status.CWS_Comments, sales_order_status.CWS_Status, [sales_order_quantity]-[SO_Quantity_Delivered] AS SO_Qty_Outstanding, sales_order_status.demand_comment, sales_order_status.Purchase_Order_Detail_ID " _
& " From sales_order_status " _
& " WHERE (((sales_order_status.sales_order_id) > 0)) " & str_sales_order_item_qry & ";"

Forms!sales_order_status.Form.RecordSource = str_main_form_qry

str_main_form_qry = "SELECT * From sales_order_status " _
& " WHERE (((sales_order_status.sales_order_id) > 0)) " & str_sales_order_item_qry & ";"

Forms!sales_order_status.Form.RecordSource = str_main_form_qry

If Not IsNull(Me.promised_date) Then
    Me.txt_so_promised_date = Me.promised_date
Else
    Me.txt_so_promised_date = Null
End If

If Not IsNull(Me.revised_delivery_date) Then
    Me.txt_revised_PO_delivery_date = Me.revised_delivery_date
Else
    Me.txt_revised_PO_delivery_date = Null
End If

End Sub

When I step through the code I always get the error at the "If IsNull(Me.promised_date) Then" line. If the date is not NULL then it works fine.

It's as though the query result is giving me an empty string and not NULL, but I've also tried checking for Len(me.promised_date > 0) and get the same error. It's as though promised_date is completely empty. But again, as I said earlier, if there is a valid date this all works fine.

 

[MajP]

Agree with CJ that there is mire to the issue. However you should check for a date not Null.
ISDATE

 

[gemma-the-husky]

does this work ...
If Me.promised_date is null then

or if nz(me.promised_date,0)=0 then

 

[CJ_London]

So why bother doing this?

If IsNull(Me.promised_date) Then
    Me.txt_so_promised_date = Me.promised_date
Else
    Me.txt_so_promised_date = Null
End If

this is saying 'if Me.promised_date is null then Me.txt_so_promised_date=null otherwise Me.txt_so_promised_date=null

you might just as well say

Me.txt_so_promised_date = Null

and be done with it

and if Me.txt_so_promised_date is bound perhaps that field does not allow nulls?

 

[Richard Horne]

If me.promised_date Is Null Then

Gives me an Object Required error.

If nz(me.promised_date,0)=0 then

Gives me the same original error.

What's weird is if you look at the image below, I have simplifed the query by just using SELECT *, and stepping through my code, the yellow highlight is where my mouse cursor is hovering over the field so would show its value if there was one but I just get nothing. Not Null.

Image

 

[Richard Horne]

So why bother doing this?

If IsNull(Me.promised_date) Then
    Me.txt_so_promised_date = Me.promised_date
Else
    Me.txt_so_promised_date = Null
End If

this is saying 'if Me.promised_date is null then Me.txt_so_promised_date=null otherwise Me.txt_so_promised_date=null

you might just as well say

Me.txt_so_promised_date = Null

and be done with it

and if Me.txt_so_promised_date is bound perhaps that field does not allow nulls?

Yeah sorry I realised my logic was flawed and have since corrected the original code but it makes no difference. Should have been If Not IsNull.

Me.txt_so_promised_date isn't bound.

 

[CJ_London]

in which case why not just use

Me.txt_so_promised_date = Me.promised_date

or should that be?

Me.txt_so_promised_date = Me.txt_promised_date

or if promised_date does not have a control

Me.txt_so_promised_date = Me!promised_date

 

[Richard Horne]

If I try

me.txt_so_promised_date = Me.promised_date

The problem is that when promised_date is Null I get a generic error. I was trying to trap for Nulls to prevent this error which led me here in the first place. When me.promised_date has a date everything works fine. I'm not even doing anything particularly complex, I don't understand what's going wrong.

It seems like the date is NULL in the DB but once the query runs and the record updates, it somehow becomes empty instead of null but yet I can't trap for that either.

 

[Richard Horne]

See the screen capture below which hopefully demonstrates what I'm trying to describe a bit better.

Capture

As you can see, when I select record 28605 where promised_date has a date saved, the form queries just fine.

When I try 29299 which has NULL in promised_date I get the error. Then when I view the code me.promised_date isn't NULL it's just 'empty' which may or may not be part of the problem.

As you can also briefly see, this is with me simplifying the query to just a select * from sales_order_status (which itself is a saved query).

 

[CJ_London]

really confused - I don't see a control that might be called txt_so_promised_date, unless that is the control populated with promised_date? In which case why not just bind it?

I see you have a control displaying #Name - perhaps that has something to do with it, you have a problem there which might be having an impact.

Otherwise I think you need to upload your app - remove all forms/reports not relevant to this problem and use example data. Then compact and repair and finally zip it.

 

[Richard Horne]

really confused - I don't see a control that might be called txt_so_promised_date, unless that is the control populated with promised_date? In which case why not just bind it?

I see you have a control displaying #Name - perhaps that has something to do with it, you have a problem there which might be having an impact.

Otherwise I think you need to upload your app - remove all forms/reports not relevant to this problem and use example data. Then compact and repair and finally zip it.

As the recordsource is a complex query (sales_order_status) made up of 4 other queries, it's not directly editable.

To combat this I made txt_so_promised_date an unbound text field that AfterUpdate manually updates the appropriate record and the form is then requeried to pull in the newly-saved data.

The field promised_date is what is updated.
txt_so_promise_date shows the initial value of promised_date but the user can modify it.

The #name error is simply because I streamlined the query to select *. The original contains a calculation which is what would normally appear in this field. Unrelated to this issue.

Uploading my app is a massive undertaking unfortunately. There are hundreds of forms, queries, reports, there's also a login system and the backend database is on an Azure Cloud server so it's not so easy to bundle it altogether in a way that can easily be opened by others.

I do a thousand way more complicated things than this in my database and it's frustrating that it's causing so much head-scratching. I can only think there's something about the underlying query or database configuration that's causing this problem because nothing I'm doing form-wise is all that complicated.

 

[theDBguy]

...and it's frustrating that it's causing so much head-scratching.

Hi. Are you able to post a sample copy of your db with test data?

 

[gemma-the-husky]

txt_so_promised_date

is this a text field on a form, rather than a table field.
If it's on a form, unbound and not formatted correctly, it could be treated as a string (by default)