You are viewing limited content. For full access, please sign in.

Discussion

Discussion

Form displays null values from SQL database as 1900/01/01 - How to show as null

posted on March 20, 2023

I am using a  sql database  to populate fields in my form. SQL displays null values as 1900/01/01 which is what populates on my form. How can I have this date fields show as "blank" or empty on my form?

I'm using forms v. 11. 

 

0 0
replied on March 20, 2023 Show version history

Please consider editing your post and changing it to a question instead of a discussion.  That way you can mark it as answered once you have a solution.

Although we could get into discussions of having SQL actually store NULL in the field instead of 1900-01-01, that's kind of outside the scope of this forum, so for now let's just stick to options to handle the values populating into Forms as 1900-01-01.

I recommend actually having two date fields on your form.  One is hidden (using Field rules or CSS) and that is what is actually populated from the database.  The second one is shown, and it populates via a formula from the hidden field.

If you are not working within a table or collection, then formulas like these should work (using variable name hidden_date_field for the date field that is populated from the database): 

=IF(hidden_date_field=""," ",IF(hidden_date_field=DATE(1900,1,1)," ",hidden_date_field))

 

This formula checks if the hidden_date_field is equal to 1900-01-01 or if it is blank.  If it is either, it will populate a single space character into the visible field.  Note that I had to do a single space (" ") and not an empty string ("") because empty string was evaluating as zero and actually becoming a date at the end of 1899.  If it is not blank, it will just copy that date.

This worked for me, testing in the Classic Designer on Version 11.0.2212.30907

 

UPDATE: 

On the New Designer, I had to remove the spaces and just do an empty string ("").  So the formula in that case worked like this: 

=IF(hidden_date_field="","",IF(hidden_date_field=DATE(1900,1,1),"",hidden_date_field))
3 0
replied on March 20, 2023

Note that on the New Designer on Version 11.0.2212.30907, I had to remove the spaces on just do an empty string ("").  So the formula in that case worked like this: 

=IF(hidden_date_field="","",IF(hidden_date_field=DATE(1900,1,1),"",hidden_date_field))

 

You are not allowed to follow up in this post.

Sign in to reply to this post.