datetime field conversion on access report

[sacacompany]

i have a report in access that drive data from sql server query. the date format my report displays is mm/dd/yyyy. i want to display it dd-mmm-yy. i tried format function but its giving error. Any idea what i m doing wrong

 

[arnelgp]

you put this to a Module:

' arnelgp
Public Function MSSQLDateTime2ToDate(ByVal e As Variant) As Variant
    Dim i As Integer
    If IsNull(e) Then
        MSSQLDateTime2ToDate = Null
        Exit Function
    End If
    i = InStr(1, e, ".")
    If i <> 0 Then
        e = Left$(e, i - 1)
    End If
    MSSQLDateTime2ToDate = DateValue(e) + TimeValue(e)
End Function

now create a query to convert your MSSQL DateTime using the function:

SELECT field1, MSSQLDateTime2ToDate([theDateTimeField]) As Dte, fieldX From yourTable;

use the query as recordsource of your reports and Format the Dte field as mm/dd/yyyy

 

[Gasman]

And you cannot say what that error is?

 

[Minty]

How are you trying to format it?
Does Access recognise the field as a date? Look at the linked table design, it will tell you what Access think it is.

 

[sacacompany]

How are you trying to format it?
Does Access recognise the field as a date? Look at the linked table design, it will tell you what Access think it is.

it displays as mm/dd/yyyy i want a display of dd-mmm-yy and i m trying this using format function on datetime filed queried from sql server

 

[sacacompany]

How are you trying to format it?
Does Access recognise the field as a date? Look at the linked table design, it will tell you what Access think it is.

yes it does...i m extracting data from sql server via connection string

 

[sacacompany]

you put this to a Module:

' arnelgp
Public Function MSSQLDateTime2ToDate(ByVal e As Variant) As Variant
    Dim i As Integer
    If IsNull(e) Then
        MSSQLDateTime2ToDate = Null
        Exit Function
    End If
    i = InStr(1, e, ".")
    If i <> 0 Then
        e = Left$(e, i - 1)
    End If
    MSSQLDateTime2ToDate = DateValue(e) + TimeValue(e)
End Function

now create a query to convert your MSSQL DateTime using the function:

SELECT field1, MSSQLDateTime2ToDate([theDateTimeField]) As Dte, fieldX From yourTable;

use the query as recordsource of your reports and Format the Dte field as mm/dd/yyyy

sorry but this function isnt working

 

[Minty]

I'll ask again HOW are you trying to format it. Something like ?

DisplayDate: Format([MySQL_DateField],"dd-mmm-yy")

Show us what your are doing that generates an error and what results/error you get, otherwise we are all guessing.

 

[arnelgp]

added the format:

' arnelgp
Public Function MSSQLDateTime2ToDate(ByVal e As Variant, Optional ByVal fmt As String = "") As Variant
    Dim i As Integer
    If IsNull(e) Then
        MSSQLDateTime2ToDate = Null
        Exit Function
    End If
    i = InStr(1, e, ".")
    If i <> 0 Then
        e = Left$(e, i - 1)
    End If
    If Len(fmt) Then
        MSSQLDateTime2ToDate = Format$(DateValue(e) + TimeValue(e), fmt)
    Else
        MSSQLDateTime2ToDate = DateValue(e) + TimeValue(e)
    End If
End Function

 

[sacacompany]

I'll ask again HOW are you trying to format it. Something like ?

DisplayDate: Format([MySQL_DateField],"dd-mmm-yy")

Show us what your are doing that generates an error and what results/error you get, otherwise we are all guessing.

Sorry for the late response...yes i m using Format([MySQL_DateField],"dd-mmm-yy") function in access forms, but it displaying same format yyyy-mm-dd. I want to get date in this format...dd-mmm-yy like 03-Mar-24

 

[sacacompany]

added the format:

' arnelgp
Public Function MSSQLDateTime2ToDate(ByVal e As Variant, Optional ByVal fmt As String = "") As Variant
    Dim i As Integer
    If IsNull(e) Then
        MSSQLDateTime2ToDate = Null
        Exit Function
    End If
    i = InStr(1, e, ".")
    If i <> 0 Then
        e = Left$(e, i - 1)
    End If
    If Len(fmt) Then
        MSSQLDateTime2ToDate = Format$(DateValue(e) + TimeValue(e), fmt)
    Else
        MSSQLDateTime2ToDate = DateValue(e) + TimeValue(e)
    End If
End Function

View attachment 112937

View attachment 112936

Sorry for the late response...yes well i use your above code in access.....make a public function and try using it on form/report that drives data from sql server....
i m using MSSQLDateTime2ToDate([MySQL_DateField],"dd-mmm-yy") function in access forms, but it displaying same format yyyy-mm-dd. I want to get date in this format...dd-mmm-yy like 03-Mar-24

 

[arnelgp]

create a query and Use the Query as Recordsource of your Form/Report:

 

[sacacompany]

create a query and Use the Query as Recordsource of your Form/Report:

 

[sacacompany]

yes now working date display as 03-Feb-24 thanks!!! same issue is with numbers...i want them to be displayed in standard format but no luck

 

[Gasman]

It is a date type field I assume?

 

[sacacompany]

It is a date type field I assume?

yes u r correct...!!
just want to know if there is some function to convert format of numbers/int from sql into standard/currency

 

[Gasman]

? format(23.45, "Currency")
£23.45

 

[sacacompany]

you put this to a Module:

' arnelgp
Public Function MSSQLDateTime2ToDate(ByVal e As Variant) As Variant
    Dim i As Integer
    If IsNull(e) Then
        MSSQLDateTime2ToDate = Null
        Exit Function
    End If
    i = InStr(1, e, ".")
    If i <> 0 Then
        e = Left$(e, i - 1)
    End If
    MSSQLDateTime2ToDate = DateValue(e) + TimeValue(e)
End Function

now create a query to convert your MSSQL DateTime using the function:

SELECT field1, MSSQLDateTime2ToDate([theDateTimeField]) As Dte, fieldX From yourTable;

use the query as recordsource of your reports and Format the Dte field as mm/dd/yyyy

now i m facing problem is insert statement. when i use the date inserted on access form in my tsql insert via vba its giving error. the issue is of date format. access form has date in dd-mm-yy

 

[arnelgp]

then create another function to covert the dd-mm-yy to Access Date:

Public Function ddMMyyStingToDate(ByVal e As String) As Date
    Dim var As Variant
    var = Split(e, "-")
    If var(0) = e Then
        var = Split(e, "/")
    End If
    ddMMyyStingToDate = DateSerial(var(2), var(1), var(0))
End Function

Insert Into YourTable (DateField) SELECT #" & ddMMyyStingToDate([Forms]![YourForm]![DateTextbox]) & "#;"