Date Field

We are having an issue with a date field in one of our tables. We create many records, which have a date field that gets set in a number of methods. The majority of the time, all is fine. Since we have upgraded to V17.3, we are noticing that the field will be empty. It doesn’t have a date in the field, which includes 00/00/00.

So we are finding it impossible to find these records. We typically find them thru happenstance. Any query run with a comparison to that date field will NOT find those records.

Suggestions? Anyone else have this issue?

Thank you for the help.

Kevin

have you added ORDA code or are you using pure classic language?

We are using classic language.

Kevin

Hi,

Could it be a problem with NULL (wild guess) ?

  1. Can you check if your field has the property “translate NULL into empty values” ?

  2. Where does the date value come from ? a “Current date” method ? or do you create the date from a string using the Date function ? Or do you do some date arithmetics ?

HTH

I did check the field, it is NOT checked MAP null values to blank values.

Our guess was that it had something to do with Null as well, but the problem is, I have no idea how to find date fields that are marked as Null. A query, [table]datefield=Null, will throw an error, correct?

The value gets set to 00/00/00 upon creation of the record, or someone sets it thru a request, which they could set to both a date on the calendar, or 00/00/00.

Kevin

: Kevin HERTZOG

I did check the field, it is NOT checked MAP null values to blank
values.
Our guess was that it had something to do with Null as well, but the
problem is, I have no idea how to find date fields that are marked as
Null. A query, [table]datefield=Null, will throw an error, correct?
Hi Kevin,
yes, Null is not a valid search value with QUERY, it’s SQL specific. Unusable with legacy langage - at least with dates as you noticed. I had the same problem a few years ago, in an existing database a lot of fields where like that because tables and fields had been created using SQL.

To find such fields more easily than clicking fields one by one in structure editor, you can:

  • display the property palette, then, for each table, clic one field, edit/select all; if the property palette displays the property checkbox as “-”, at least one field is of that kind
  • create your own code using commands from the “design object access” chapter to find these fields
  • use the component https://forums.4d.com/Post/FR/24944477/2/31249766#29666349here> (not null column)

Hi,

You need to check this option. Otherwise if you create a record without setting a value for this field it will default to NULL which is different than 00/00/0000.

You can SEARCH by SQL with … myFieldDate IS NULL and you should get your records in the selection, then apply myFieldDate=!00/00/0000!.

Lets us know if that helps.