[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

Access Code to evaluate text field for null or date format

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.
0
dhemple
Asked:
dhemple
  • 8
  • 8
  • 3
  • +1
3 Solutions
 
Dale FyeCommented:
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
0
 
PatHartmanCommented:
As I pointed out in different thread, you are actually changing the value of A since in VBA arguments are by default passed as By Reference.  

Declare a local variable if you want to actually change the value of an argument.

A simple way to check for Null and for being a date Is:
IIf(IsNull(A) OR IsDate(A), True, False)

If A is not defined as a date data type, then you have the problem of ZLS creeping in.
IIf(A & "" = "" OR IsDate(A), True, False)
I wouldn't even bother with a function.

And finally, the destination field is ExpectedLaunchDate which sure sounds like it should be a date field.  Setting it to true will make it Dec 29, 1899 whereas false would be Dec 30, 1899.
0
 
Gustav BrockCIOCommented:
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
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
dhempleAuthor 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.
0
 
Gustav BrockCIOCommented:
Pat is too kind. That chk1 function is sloppy code given the conditions and should be abandoned.

As a bare minimum it should be adjusted like this:

Function chk1(ByVal varValue As Variant) As Boolean
    ' Check if varValue is Null or a valid date expression.
    chk1 = IsNull(varValue) Or IsDate(varValue)
End Function

/gustav
0
 
PatHartmanCommented:
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.
0
 
dhempleAuthor 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.
0
 
Gustav BrockCIOCommented:
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
0
 
dhempleAuthor 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

0
 
PatHartmanCommented:
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.
0
 
Gustav BrockCIOCommented:
ByVal is the magic Pat mentions and whick is very important here.
These functions are so simple that you should be able to rewrite them yourself.
Anyway:
Public Function chk0(ByVal strValue As String) As Boolean
    ' Check if strValue contains exactly 11 digits.
    chk0 = (Format(Val(strValue), String(11, "0")) = Trim(strValue)
End Function

Public Function chk2(ByVal strValue As String) As Boolean
    ' Check if strValue is numeric.
    chk2 = IsNumeric(strValue)
End Function

Open in new window

/gustav
0
 
dhempleAuthor 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.
0
 
Gustav BrockCIOCommented:
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
0
 
dhempleAuthor 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

0
 
Gustav BrockCIOCommented:
Yes, sorry, Val dislikes Null. It should read:

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

/gustav
0
 
dhempleAuthor 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.
0
 
Gustav BrockCIOCommented:
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
0
 
dhempleAuthor Commented:
Perfect!!  thanks you so much
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
 
dhempleAuthor Commented:
Thank you for all your time on this request.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now