convert a field's type from text to date

mo9rissat · Jul 26, 2012

Hello,
I'm establishing a connection from access to a SQL Server DB. It is impossible for me to change the tables' structure (fields, types ...) yet I have a field that is text and I want to display it in a form in date format (dd / mm / yyyy ), I read in some publications that it must be converted using format but I do not know how to do it.

John Big Booty · Jul 26, 2012

I think the DateSerial() function will do what you are after.

pr2-eugin · Jul 26, 2012

I think the mwthod for SQL server is CONVERT. But if you want to display the date vale as text in your form, you have heard right to use Format, the general format is Format("value","how_you_wish_to_see") so your code should be like..

Code:

Format(Date,"dd/mm/yyyy")    'would return 26/07/2012
Format(Date,"mm-dd-yy")      'would return 07-26-12

mo9rissat · Jul 26, 2012

John Big Booty said:
I think the DateSerial() function will do what you are after.

I think that DateSerial can resolve my problem, but what I've noticed is that I must give a specific date to the function and it changes values one by one, thing that i can't do beacause I've to change the format to all data by using only one query.

mdlueck · Jul 26, 2012

mo9rissat said:
yet I have a field that is text and I want to display it in a form in date format (dd / mm / yyyy )

So is the column a "text" datatype in the SQL BE DB?

Or is it a date datatype in the BE DB and you have chosen to map that field to a text field in the Access FE DB? This latter way is how I handle the date formatting differences between SQL Server and Access.

Then, since it is a text field in the Access FE DB, I have a function which converts from YYYY-MM-DD to MM/DD/YYYY format.

Code:

Public Function datetimeutils_ConvStandardToUSA(ByVal strInputDate As String) As String
  On Error GoTo Err_datetimeutils_ConvStandardToUSA

  datetimeutils_ConvStandardToUSA = Format(strInputDate, "mm/dd/yyyy")

Exit_datetimeutils_ConvStandardToUSA:
  Exit Function

Err_datetimeutils_ConvStandardToUSA:
  Call errorhandler_MsgBox("Module: modshared_fsutils, Function: datetimeutils_ConvStandardToUSA()")
  datetimeutils_ConvStandardToUSA = vbNullString
  Resume Exit_datetimeutils_ConvStandardToUSA

End Function

SQL Server converts Access MM/DD/YYYY formatted dates back into YYYY-MM-DD on its own, so I did not need to develop the conversion in that direction.

So, when downloading records into a FE Temp Table, then I perform a fix-up of teh date format once the download completes. I simply use an adoRS object, scan through the FE temp table, and swap out the date strings, passing them through the function above.

For using unbound forms, I simply call the function above before sending the date value to the unbound control.

mo9rissat · Jul 26, 2012

mdlueck said:
So is the column a "text" datatype in the SQL BE DB?

Yes it's a text datatype in SQL Server

Then, since it is a text field in the Access FE DB, I have a function which converts from YYYY-MM-DD to MM/DD/YYYY format.

Code:

Public Function datetimeutils_ConvStandardToUSA(ByVal strInputDate As String) As String
  On Error GoTo Err_datetimeutils_ConvStandardToUSA

  datetimeutils_ConvStandardToUSA = Format(strInputDate, "mm/dd/yyyy")

Exit_datetimeutils_ConvStandardToUSA:
  Exit Function

Err_datetimeutils_ConvStandardToUSA:
  Call errorhandler_MsgBox("Module: modshared_fsutils, Function: datetimeutils_ConvStandardToUSA()")
  datetimeutils_ConvStandardToUSA = vbNullString
  Resume Exit_datetimeutils_ConvStandardToUSA

End Function

This code supposes that i've already a date datatype field no?? Also I didn't see in it where i can put my field's name??!! So can u tell me how to apply it?? (Sorry i'm not very good neither in Access nor in VBA)

mdlueck · Jul 26, 2012

mo9rissat said:
Yes it's a text datatype in SQL Server

Then I do not see how you would be getting bit by SQL Server converting the date to YYYY-MM-DD format if the data is stored in a SQL Server text field.

And I have never seen Access decide to convert dates to YYYY-MM-DD format on its own.

mo9rissat said:
This code supposes that i've already a date datatype field no?? Also I didn't see in it where i can put my field's name??!! So can u tell me how to apply it?? (Sorry i'm not very good neither in Access nor in VBA)

My date fix-up code is generic and not tied to a database field. It may be used to act upon the value in a field, or the value of a text box, or or or... It merely accepts a date in YYYY-MM-DD format, and returns the same date in MM/DD/YYYY format.

No, it does not assume the variable is already of date datatype. Like I said, I store dates in SQL Server date datatype columns and in the Access FE I store them in text fields. My generic function is content to change the date format of dates stored in text fields. But in my case it seems reverse of what you have. I do not have the FE column datatype set to date.

convert a field's type from text to date

mo9rissat

Registered User.

John Big Booty

AWF VIP

pr2-eugin

Super Moderator

mo9rissat

Registered User.

mdlueck

Sr. Application Developer

mo9rissat

Registered User.

mdlueck

Sr. Application Developer

Similar threads

Users who are viewing this thread