troubleshooting Question

Access Code to evaluate text field for null or date format

Avatar of dhemple
dhempleFlag for United States of America asked on
Microsoft Access
20 Comments3 Solutions495 ViewsLast Modified:
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

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

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
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 20 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros