Detecting an Invalid Date

[dowlingm815]

When a date field contains an invalid date, it needs to be flagged. However, my code is not acknowledging it. For instance, it doesn't recognize 8/15/0007 as a bad date. Any assistance would be appreciated.

            If IsDate(rst_AllText_PRData_Editable_Dates![Contract Effective Date]) = False Then
                rst_tbl_PRData_Error_Dates.Add
                    rst_tbl_PRData_Error_Dates.[Contract Effective Date] = rst_AllText_PRData_Editable_Dates![Contract Effective Date]
                rst_tbl_PRData_Error_Dates.Update
            End If

[Rabbit]

Why isn't it a valid date? There is such a thing as year 0007.

[NeoPa]

If you want to use the term 'invalid date' differently from how most people would understand it then you should define what you mean clearly in your question. That way we can respond intelligently to it.

[hvsummer]

I think you should trap your condition Year(field) between startYear and EndYear for your problem
ex:

If IsDate(rst_AllText_PRData_Editable_Dates![Contract Effective Date]) = False and year(rst_AllText_PRData_Editable_Dates![Contract Effective Date]) not between 1900 and 3000 Then
                rst_tbl_PRData_Error_Dates.Add
                    rst_tbl_PRData_Error_Dates.[Contract Effective Date] = rst_AllText_PRData_Editable_Dates![Contract Effective Date]
                rst_tbl_PRData_Error_Dates.Update
End If

[dowlingm815]

There is an issue with the syntax for year. Can you pls guide? The pound sign is flagged.

If IsDate(DateValue(rst_AllText_PRData_Editable_Dates![Contract Effective Date])) = False _
                    and year(rst_AllText_PRData_Editable_Dates![Contract Effective Date]) Between #2009# and #2025# Then

When the

#" &2009& "#

syntax is used, it flags it as well.

[hvsummer]

you don't have to put # # around the year value inside between .. and .., since year() function return numberic datatype.

Edit: using NOT + Between ... AND ...
you miss the NOT, then condition combine will be wrong in ur current code.
this Code need NOT + Between:

If IsDate(DateValue(rst_AllText_PRData_Editable_Dates![Contract Effective Date])) = False _
                    and year(rst_AllText_PRData_Editable_Dates![Contract Effective Date]) Between #2009# and #2025# Then

[dowlingm815]

Thank you, however, the vba is still flagging an syntax error on the code.

 If IsDate(DateValue(rst_AllText_PRData_Editable_Dates![Contract Effective Date])) = False _
                        and year(rst_AllText_PRData_Editable_Dates![Contract Effective Date]) NOT Between 2009 And 2025 Then
    End If

[hvsummer]

ok, I know where is the problem.

just replace "NOT between ... and.." syntax with

year(rst_AllText_PRData_Editable_Dates![Contract Effective Date]) < 2009 or year(rst_AllText_PRData_Editable_Dates![Contract Effective Date]) > 2025 Then

[NeoPa]

This is getting silly. You haven't explained what you consider to be invalid in a date value for the question, and now you haven't given the VBA error message but ask for help with VBA syntax error.

I would have thought the necessity of providing the basic information in a question would be obvious to everyone. Please do so before proceeding in order to avoid this thread going further into territory that no-one can understand.

Frankly, I have no idea why anyone is suggesting answers before the question is clear. Clearly there is no 'Best Answer' at this stage as we haven't even got to a properly expressed question.

[hvsummer]

@NeoPa: I think I know what he's facing, sorry that I did not explain.

the fact that data type or date/time setting in windows is not correct leading to that year of date field can be like this

1/1/40001 or 8/15/0007
(office still understand this as valid year (decode into 1900 or 2000 whatever)

but we can't work with it (import this can lead to wrong year number, or manually typing may lead thing by far going worse)

he need to set condition for his date field that force to contain year only between valid range

to do this he used his code to flag/note/remind in an addition field (1 is yes, 0 is no for example) to show that rows is wrong date (wrong year format).

so I suggest him to set additional condition for year of his field by year() function trapped in valid range ("Not between and" -OR- "< 1900 or > 2025")

[dowlingm815]

@hvsummer, thanks for explaining. Mary

[NeoPa]

Right. That starts to make some sense.

@HVSummer.
No need to apologise. It's not your responsibility to ask the question clearly, or even to explain it when you understand what it is. Well done anyway for doing so.

In light of the explanation, and the fact that an explanation of what the question should have been is never appropriate for a 'Best Answer' post, I will reset it again, but set your earlier post back up as 'Best Answer' instead. That is an answer to the question and also explains the process behind the code.

@Mary.
I do understand that expressing questions clearly and fully enough is difficult for most people. The number of poorly expressed questions in here makes it impossible for me not to realise that it's a very common problem. Nevertheless, please do your best when posting questions. Often experts will prompt you for extra information. It's very important that you reply to these with the information in order to get the question understood by as many as possible.