Showing "0" value but not formulated query

[smyeong]

Hi, all my dear friends. Long time didn't email u for helps. Hope u won't mind answering me once again.
This time around i got a request if this is feasible or not...It happens that I used Access query to change the 6 digit string field to date format by using formula, like this

ShipDate: Cdate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([SDaddj]/1000))))+[SDaddj]-Int([SDaddj]/1000)*1000))-1,"mm-dd-yy"))

It works well on the field which has been inserted with date value,For example, 103016 (text field) will be 01-16-03(date type) .

But anyhow, for date field which is not yet inserted will remain "0" like below scenario, So, when i run the MS Access 's above query; it gives me this type of misleading date value as below:

Delivery No Invoice No Status ShipDate
0 0 Outstanding 12/31/1999

The shipdate 's supposed to be 0 in place but came out with wrong date value. This scenario happen because shipped date not yet created

So. how can i show the "0" value as it is but not 12/31/1999 which is formulated?

Any idea, pls help out..
Thanks a lot,

 

[The_Doc_Man]

Add another column to the query grid besides the one that contains your date recomputation. Make it depend on the same field that feeds your computation. In the SHOW row, uncheck the box. In the criteria row, put either > 0 or <> "0" as appropriate to what appears in the table.

If what REALLY appears is a NULL or an empty string, then instead you could put in the field row, ... Len(Nz([MyDateField],"")) .... and then put > 0 in the criteria row.

There is nothing wrong with using the same value twice in the query, once as part of a computation, and once as part of a filter or other criterion for selection. That way, you don't do this to any field that doesn't already have the your date string. In fact, if you always expect 6-digit date strings or nothing, you could make that criterion <> 6 (for the Len(Nz()) case.

 

[Pat Hartman]

If you put 0 in a field defined as a date, the result will be 12/31/1899. Do a search on this forum to find out why. You can't use 0 as the default for a date, you should use null.

 

[smyeong]

Len(Nz([MyDateField],""))

What a rare command given! Guy, could u pls explain in detail how do i use the above statement, i do know what LEN is but dono the Nz, pls elaborate a bit as well as the criterion > 0 or <> "0"

I am just a new learner .............Thanks you