We help IT Professionals succeed at work.

Access Code to evaluate text field for null or date format

dhemple
dhemple asked
on
492 Views
Last Modified: 2014-08-19
Hello Experts.

With the help of experts here at Expert Exchange the below code has been created to assist me in checking a text field within a table to determine if the information provided is blank or if the information is in a date format.


Here's the function code:
Function chk1(A As Variant) As Boolean
'Check date
Dim i As Integer, l As Integer, c As String
A = Trim(A) 'remove possible spaces
chk1 = False

If IsNull(A) Or IsDate(A) Then chk1 = True

End Function

Open in new window


and here's the code that runs from a button on a form:
Private Sub cmd_UpdateCheckFields_Click()

Dim MyDB As DAO.Database
Dim MyRst As DAO.Recordset

Set MyRst = CurrentDb.OpenRecordset("tbl_TEMP_RFI")

MyRst.MoveFirst

Do Until MyRst.EOF

    MyRst.Edit
    MyRst![chk_ProductInfo_NDC] = chk0(MyRst![ProductInfo_NDC])
    MyRst![chk_ProductInfo_ExpectedLaunchDate] = chk1(MyRst![ProductInfo_ExpectedLaunchDate])
    MyRst![chk_ProductInfo_PkgSize] = chk2(MyRst![ProductInfo_PkgSize])
    MyRst.Update
    
    MyRst.MoveNext
    
Loop
       
MyRst.Close
Set MyRst = Nothing

End Sub

Open in new window


The field [chk_ProductInfo_ExpectedLaunchDate] should update to TRUE if the field is null or in a date format

If IsNull(A) Or IsDate(A) Then chk1 = True

Open in new window

but this code only seems to work when the field is  evaluated on a form and not within a table as the above code is doing.

Any thoughts on getting this to work correctly?

Thank you.
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

Commented:
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

Open 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

Open in new window

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Open in new window

/gustav

Author

Commented:
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))

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

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

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

/gustav

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

Commented:
Perfect!!  thanks you so much
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav

Author

Commented:
Thank you for all your time on this request.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.