asked on

Format Date Syntax for Access 2002 runtime Query

I can run an Append query on my computer with a full version of Access 2002 that doesn't want to run on Access Runtime. The runtime version doesn't like the Format syntax i'm using to change a date field before exporting to MySQL. There must be a .dll or some supporting file needed for the Windows 2003 server computer to recognize the date format, right?

Here's an example of the formatting I'm using in the queries:
 
CreatedOn1: IIf(IsNull([Tbl_DCOReviewerList]![CreatedOn]),Null,Format([Tbl_DCOReviewerList]![CreatedOn],"YYYY-MM-DD HH:MM:SS"))
 
My preference is to figure out how to make this run as is, since I have to go through 262 queries if I need to change the query syntax.

Open in new window

Error2.jpg

Helen Feddema

You can't reference a field in a table using the syntax in your note -- you would need to create a recordset, and then reference the recordset field. In the screen shot, the value is taken from a form field, and there you might want to use Nz() instead of IsNull to check the value in the form field, because that checks for zero-length strings as well. Use this syntax (lots of variables, for purposes of debugging and checking -- simplify as you prefer):

Public Function FormattedDate() As String
 
   Dim frm As Access.Form
   Dim strFormName As String
   Dim dteSold As Date
   Dim strFormattedDate As String
   
   strFormName = "fpriLoadSoldPackingSlip"
   DoCmd.OpenForm strFormName
   Set frm = Forms(strFormName)
   If IsDate(frm![DateSold].Value) = True Then
      dteSold = CDate(frm![DateSold].Value)
      Debug.Print "Date sold: " & dteSold
      FormattedDate = Format(dteSold, "YYYY-MM-DD HH:MM:SS")
   Else
      MsgBox "Invalid date"
   End If
   
End Function

Open in new window

Helen Feddema

Actually, I use IsDate for checking dates -- Nz is used for numeric and string values.

ASKER CERTIFIED SOLUTION

Graham Mandeno

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Smithstein

ASKER

Actually it was the references in my VBA project that needed to be changed. I added DAO 3.6 and ActiveX data objects 2.8. Not sure which one was needed, but I use these in my other projects and that did the trick.
References.jpg