Access Code to evaluate text field for null or date format

What do you want to do if the value is a zero length string ("" without the quotes)?  As it is now, a blank (ZLS) will return False, which may be the problem you are having in your dataset.  Is there a chance that your fields contain ZLS instead of NULLs?

Function IsNullOrDate(A As Variant) As Boolean

    'Check date
    If IsNull(A) Then
       IsNullOrDate = True
    Elseif TRIM(A & " ") = "" Then
        'include this test if you want to treat blanks (ZLS) like NULLs
        IsNullOrDate = true
    Elseif IsDate(A) Then
       IsNullOrDate = True
    End If

End Function

Further, you are on a wrong track here as you are writing redundant data to the table.

You could run a query to report the same:

Select
    IsDate([ProductInfo_ExpectedLaunchDate]) Or ([ProductInfo_ExpectedLaunchDate] Is Null) As
        [chk_ProductInfo_ExpectedLaunchDate
From
    tbl_TEMP_RFI

And why do you store dates as strings? That's a no-no. Use data type Date which can hold both Null and a valid date and nothing else.

/gustav

Hey Pat, Thanks for your response and I'm sorry, but I can't explain why using A works, but it does.  I believe the key reason for using the function is that it allows me to check the multiple text fields that should either be null or contain a date with only one entry of the code.  The original expert that created this code thought it would work best this way, and I agree.

Do you understand how dangerous it is to modify data owned by the passing program?  This is really poor practice.
Use the function if you want but at least move A to a variable before you modify it.

It sounds like you never read my response or simply didn't understand what I said.

Is the data field defined as a date data type?  If it is, it CANNOT contain padding characters or be a ZLS.  It is either Null or a Date as gus said.

Is ExpectedLaunchDate a date?  If it isn't, it is poorly named.  If you can change the name, make it reflect the true nature of the data it contains.  Don't make it sound like something it isn't.  If it is a date, then don't be surprised to find the field containing one of the two values I mentioned earlier.

Just because code compiles doesn't make it right.  There are numerous flaws with the code and the logic.

The most likely reason the code isn't working is because you are referencing the recordset and attempting to modify it in the FUNCTION because you are CLOBBERING the value you are passing.

I’m not the expert here.  I rely on folks like you to point me in the right direction.  I’m always looking for the best way to do something.  I never set out to do something the wrong way.  Please don’t judge me for the code provided by an expert.
 
Let me back up and provide some background on the purpose of this database.

The purpose of this database is to upload a file manipulated by a customer and verify that the data within it is correctly formatted.

This is what should happen:
1) Navigate to Excel file provided by customer and import data into TEMP table containing all text fields.
2) Evaluate each field to ensure that numbers, text, dates, and other field types are what they are.  Customer's sometimes change the field and/or do not provide the right formatted data and this is our way of confirming the data provided before pushing forward.  Within the TEMP table is a chk field for each imported field (Date has chk_Date). If the data provided is formatted correctly, the chk_field is updated to True, else False.
3) Once the checks on each field and record is completed, the user opens a form that utilizes the chk_fields and conditional formatting to quickly identify data issue and allows the user to modify the data into the correct format.  If Date is anything but null or an actual date, chk_Date = False and conditional formatting will highlight Date of the record/field containing the issue. Changes made on the form calls the same functions used when checking the whole table and chk_field is updated if correct.
4) Once all data format issues are resolved, the result is pushed into a CSV file for a corporate system process.

Your insight is greatly appreciated and much needed.  As I mentioned, I’m not the expert, but I do want to do it the right way.

Thank you.

OK. Then my chk1 function will do as it will not modify the (text) date field read.

However, most likely chk0 and chk2 will need a makeover as well.

/gustav

Thank you. I've implemented the change to chk1.  Would you also assist with chk0 and chk2? I'll also need code added to each that will allow null values.

Function chk0(A As String) As Boolean
'Check 11 alphanumeric
Dim i As Integer, l As Integer, c As String
A = Trim(A) 'remove possible spaces
chk0 = False
l = Len(A)
If l = 11 Then                    'exact 11 symbols
    chk0 = True
    For i = 1 To l
        c = Mid(A, i, 1)
        If Not (c >= "0" And c <= "9") Then    'only symbols from 0 to 9 are allowed
            chk0 = False
            Exit Function
        End If
    Next i
End If
End Function

Select allOpen in new window

Function chk2(A As String) As Boolean
'Check number
A = Trim(A)
chk2 = False
If IsNumeric(A) Then chk2 = True

End Function

Select allOpen in new window

dhemple, I know that you are not the expert but it is quite frustrating to have you ignore specific questions and respond as though you hadn't even read the post.  It is EXTREMELY important that you understand what I said about not modifying arguments passed into functions.  If you don't get this concept, it will come back to haunt you again and again.  Gus rewrote the function so that it doesn't modify it's input arguments and I told you in words what to do.  

Please acknowledge that you understand that modifying an argument passed to a function or sub actually changes the value in the calling procedure.  In fact, you should take the time to write a test procedure to prove it to yourself so it sinks in.

Although most of the folks who answer questions here are true experts, not all are.  Everyone is allowed to answer questions and even encouraged to do so.  You would be amazed how much you learn by trying to answer other peoples questions.

Also, keep in mind that posted code is not necessarily tested so even if it is posted by an expert, he may have written it in his sleep and expected you to understand what it was doing and verify that it was correct.

Pat,  As you are aware, I do not understand. I have accepted the fact that I'm not going to understand everything, as I do not work with code daily.  .  If you have a way to help explain, I would gladly read and try to understand.

Original code: Function chk1(A As Variant) As Boolean

Revised code: Function chk1(ByVal varValue As Variant) As Boolean

Between the first and second code, I see "ByVal" has been added and "A" has been replaced with "varValue".  I have to assume that "ByVal" is an important part of the change.  What exactly does "ByVal" do?

Gustav, thank you for your response.  I'm sure the code is simple to you, but it is not for me.  I'll need help rewriting to work for my scenario and allow for null values in each statement (chk0 and chk2)

This tread is very concerning to me.  I've been a member of Experts Exchange for many years.  From time to time a project pops up that I need help with and the Experts has always been here to help.  You both have given me the sense that I should have already known what I'm asking and shouldn't be asking these questions here.  The truth is, I don't know the answers and this is why I'm here.

I truly appreciate all your assistance.

You can say, that by using ByVal, the function works on a copy of the passed variable.

> .. allow for null values in each statement (chk0 and chk2)

That could be:

Public Function chk0(ByVal varValue As Variant) As Boolean
    ' Check if varValue is Null or contains exactly 11 digits.
    chk0 = IsNull(varValue) Or (Format(Val(strValue), String(11, "0")) = Trim(strValue))
End Function

Public Function chk2(ByVal varValue As Variant) As Boolean
    ' Check if varValue is Null or numeric.
    chk2 = IsNull(varValue) Or IsNumeric(strValue)
End Function

Select allOpen in new window

/gustav

I'm getting an error (invalid use of Null) on this line of code

    chk0 = IsNull(varValue) Or (Format(Val(varValue), String(11, "0")) = Trim(varValue))

Select allOpen in new window

Yes, sorry, Val dislikes Null. It should read:

    chk0 = IsNull(varValue) Or (Format(Val(Nz(varValue)), String(11, "0")) = Trim(varValue))

/gustav

Perfect Gustav!  One last request.  Could you break down/explain to me what your below code is doing

(Format(Val(Nz(varValue)), String(11, "0"))

I believe I understand that it is formatting varValue into a string that is 11 digits, but what is "Val" and "Nz" doing?  Also by adding "0" in String(11, "0") is this what is allowing leading zeros?

Thank you.

Yes. The idea is to convert the string to a number and back. Then compare the converted string with the original. A match is success.

Nz(varValue) return a zero length string for Null. Val accepts strings only.

Val(Nz(varValue)) returns the numeric value for a string of digits ignoring leading and trailing spaces.
If not convertible, 0 is returned.
However, leading zeroes are killed. Thus, "00567802510" => 567802510

Format converts a value to a string. The format to use is 11 zeroes. String(11, "0") => "00000000000"
Format(Val(Nz(varValue)), String(11, "0")) converts, say, 567802510 to "00567802510".
Trim(varValue) removes leading and traling Space: " 00567802510  " => "00567802510"

Now you can compare to check: "00567802510" = "00567802510"

/gustav

Perfect!!  thanks you so much

You are welcome!

/gustav

Thank you for all your time on this request.