Trying to re-format a date

[rdg0351]

I have this query with various dates per record, i.e. status tracking. Each record has values for at least the first date, but not necessarily any more dates. I've tried to create a Public Function which will return the string value of month & date:

1/15/2015 "0115"
2/1/2014 "0201"
10/14/2016 "1014"

The concept is to compare dates by month and day WITHOUT regard to the year. If a date field does not have a value, I would like to return "0000", or something other than "Error". I have tried

IsNull()
IsDate()
month and day extraction and comparing these results to 0

Obviously I'm missing something.... Any help would be greatly appreciated

 

[Minty]

Can you show us the code you have tried?
Normally you would use something like Nz(Format(YourDateField,"ddmm"),"0000")

 

[rdg0351]

Getting closer....I like your string of functions; however, you would think that it would return "0000" for a null date, BUT, it's returning, what looks like a blank value. If I perform IsDate(MyDateFld), it returns "0" for a null value and "-1" for a valid value.

Sooooo, when I compare the date to today (which is "0222"), as in

iif( Nz(Format(YourDateField,"mmdd"),"0000") > mmddToday, 0, 1 )

I get a value of 1 for a blank date. As a follow-up, after the conversion, I'm doing a comparison on the strings and returning a 0 or 1 which will then get accumulated for a summary report.

Thanks in advance

 

[Minty]

You can't do mathematical comparisons on string values. They don't work out like you would expect them to.

If you take a step back I think you are trying to fix a data design problem here. If all your status updates had a date/time stamp you wouldn't be jumping through hoops to try and accommodate the blank ones. I would fix that issue then you avoid this one.

 

[CJ_London]

does this need to be actually formatted as a "0000" value? why not leave it blank (i.e. null)? You can use the format property to display as you required if the value is null

in a query on the field row put

txtdate:format(mydate,"mmdd")

if the property sheet is not visible, click on property sheet in the ribbon or right click on the column and select properties

in the format property put

@;"0000"

if the query is to be displayed in a form or report, you may also need to set the format property for the relevant control as well.

To find all records where the date is 0000, you would use

where txtdate is null

instead of txtdate="0000"

Saves a lot of faffing around

 

[rdg0351]

Bingo....

Thank you to everyone that assisted in this data-messy situation. I appreciate all that assisted. Perhaps when there is more $$ in the budget, I could clean up this app

Once again, thanks to all