Results 1 to 20 of 20

Thread: [RESOLVED] How to get access field dataformat using ado

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jul 2017
    Posts
    216

    Resolved [RESOLVED] How to get access field dataformat using ado

    I am trying to get the format of an access table Date field to check if it is in date or time format .

    when I write

    Code:
    Msgbox dd.fields("Datex").Dataformat.format

    it throws an error >> method or data member not found although it is in the popup list . This does not happen if I set a StdDataFromat object to the field and then try to read it using the previous code .

    So , How can I get the data format of an access 2010 field using ADO ?
    Last edited by Hosam AL Dein; Jan 7th, 2021 at 09:47 PM.

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,705

    Re: How to get access field dataformat using ado

    Not sure if this is what you are after...but, this code will return a the field name and a 7, meaning it is a date/time field:

    Code:
    msgbox dd.Fields("column_name").Value, dd.Fields("Data_Type").Value
    (I'm assuming dd is your recordset)
    Sam I am (as well as Confused at times).

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jul 2017
    Posts
    216

    Re: How to get access field dataformat using ado

    Quote Originally Posted by SamOscarBrown View Post
    Not sure if this is what you are after...but, this code will return a the field name and a 7, meaning it is a date/time field:

    Code:
    msgbox dd.Fields("column_name").Value, dd.Fields("Data_Type").Value
    (I'm assuming dd is your recordset)

    Thanks for help Sam .

    Yes , dd is my recordset and my field name is datex .

    I think you meant the following but it`s ok I understand what you are trying to help me with .

    Code:
    msgbox dd.fields("Column_Name").Name & vbtab & dd.fields("Column_Name").Type
    Now , the previous code will display 7 for all date/time fields . What I am after is to distinguish between date/time fields that are formatted for dates and those who are formatted for time . So , I was trying to read the DataFormat property but it gives an error method or data member not found

    My code is

    Code:
    Msgbox Rs.Fields("SomeDateField").DataFormat.Format
    I was expecting it to output "hh:mm:ss" for date/time fields formatted as long time and "dd:mm:yyyy" for short dates .
    Last edited by Hosam AL Dein; Jan 8th, 2021 at 12:11 AM.

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Jul 2017
    Posts
    216

    Re: How to get access field dataformat using ado

    So , You can say this is my question and ignore anything else .

    How can I get the data format not the data type of an access 2010 field using ADO ?

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,680

    Re: How to get access field dataformat using ado

    Quote Originally Posted by Hosam AL Dein View Post
    So , You can say this is my question and ignore anything else .

    How can I get the data format not the data type of an access 2010 field using ADO ?
    not with ADO(well I don't know a way), you have to use DAO

    Code:
    
    Public Function GetFieldProperty(TableName As String, FieldName As String, _
                                     Optional PropertyName As String = "Format")
     
       'Quelle: http://www.dbwiki.net/
     
       Dim db  As DAO.DataBase
       Dim tdf As DAO.TableDef
       Dim fld As DAO.Field
       Dim prp As DAO.Property
       Dim dbPath As String
       
       dbPath = "E:\Adressen.mdb"
       Set db = DBEngine.Workspaces(0).OpenDatabase(dbPath, False)
       GetFieldProperty = Null
       Set tdf = db.TableDefs(TableName)
       Set fld = tdf.Fields(FieldName)
       On Error Resume Next
       GetFieldProperty = fld.Properties(PropertyName)
    End Function
    call like this
    Code:
    Private Sub Command1_Click()
    Debug.Print GetFieldProperty("tbl_ReadDateFormat", "TestDate1", "Format")
    Debug.Print GetFieldProperty("tbl_ReadDateFormat", "TestDate2", "Format")
    Debug.Print GetFieldProperty("tbl_ReadDateFormat", "TestDate3", "Format")
    
    'the Debug output:
    'General Date
    'Long Time
    'Medium Time
    
    End Sub
    HTH
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,249

    Re: How to get access field dataformat using ado

    is the field actually a text field or a date type?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jul 2017
    Posts
    216

    Re: How to get access field dataformat using ado

    Quote Originally Posted by ChrisE View Post
    not with ADO(well I don't know a way), you have to use DAO

    Code:
    
    Public Function GetFieldProperty(TableName As String, FieldName As String, _
                                     Optional PropertyName As String = "Format")
     
       'Quelle: http://www.dbwiki.net/
     
       Dim db  As DAO.DataBase
       Dim tdf As DAO.TableDef
       Dim fld As DAO.Field
       Dim prp As DAO.Property
       Dim dbPath As String
       
       dbPath = "E:\Adressen.mdb"
       Set db = DBEngine.Workspaces(0).OpenDatabase(dbPath, False)
       GetFieldProperty = Null
       Set tdf = db.TableDefs(TableName)
       Set fld = tdf.Fields(FieldName)
       On Error Resume Next
       GetFieldProperty = fld.Properties(PropertyName)
    End Function
    call like this
    Code:
    Private Sub Command1_Click()
    Debug.Print GetFieldProperty("tbl_ReadDateFormat", "TestDate1", "Format")
    Debug.Print GetFieldProperty("tbl_ReadDateFormat", "TestDate2", "Format")
    Debug.Print GetFieldProperty("tbl_ReadDateFormat", "TestDate3", "Format")
    
    'the Debug output:
    'General Date
    'Long Time
    'Medium Time
    
    End Sub
    HTH
    Thanks Chris . It works great .
    But I am still wondering if it can be done in ADO and mainly why this error appears

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Jul 2017
    Posts
    216

    Re: How to get access field dataformat using ado

    Quote Originally Posted by westconn1 View Post
    is the field actually a text field or a date type?
    No , not a Text filed. It is Date/Time

  9. #9
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,705

    Re: How to get access field dataformat using ado

    That is what I THOUGHT you were going to want (date FORMAT), but do not know of a way to do that (with ADO). So, my next question is why? What is the reason you need to know this?...there may be other ways to get to your final desires.

    If you already have DATA in the fields, you can do a select statement, return and 'look' at the results. Using Instr() you can easily determine if the returned data is a date or time.

    So, again, WHY do you need to know?
    Sam I am (as well as Confused at times).

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,342

    Re: How to get access field dataformat using ado

    *sigh*
    And a Date-Format is basically a Long Integer (actually: a Double without the Fraction part)
    and a Time-Format is a Double in the Range of 0 to 1 (actually: the fraction-part of a Double)

    Very rudimentary process:
    Just read the Value into a Double
    1) If Fraction is yes and 0<=Value<1 --> Time-Format (Only Time!)
    2) If Fraction is no (Double=Clng(double)) --> Date-Format


    .... and people crucify me, when i say i've done away with all that Date/Time-Crap in Databases, and just using Doubles/Long Integers
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  11. #11
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,879

    Re: How to get access field dataformat using ado

    Field.DataFormat is effectively an expando property. Few if any OLEDB Providers persist it, and probably no ODBC Drivers do.

    MS Access stores that sort of thing in private structures it writes to MDB, ACCDB, etc. Just like UserForm layouts, VBA macros, etc.

  12. #12
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,705

    Re: How to get access field dataformat using ado

    @ zvoni....yeah, double is easier I guess than looking at a returned STRING. But, I guess OP MAY be wanting to know for some 'other' reason..where there is NO data in the field...that's why I wanted to know 'why' he needs this information. But yeah, if data exists, very easily can determine if it is a date or time only.
    Sam I am (as well as Confused at times).

  13. #13
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,213

    Re: How to get access field dataformat using ado

    Quote Originally Posted by Zvoni View Post
    *sigh*
    And a Date-Value is basically a Long Integer (actually: a Double without the Fraction part)
    and a Time-Value is a Double in the Range of 0 to 1 (actually: the fraction-part of a Double)

    Very rudimentary process:
    Just read the Value into a Double
    1) If Fraction is yes and 0<=Value<1 --> Time-Format (Only Time!)
    2) If Fraction is no (Double=Clng(double)) --> Date-Format


    .... and people crucify me, when i say i've done away with all that Date/Time-Crap in Databases, and just using Doubles/Long Integers
    There, I fixed that for you... don't start confusing values with formats again... values is the value... the format is the visual representation of the underlying value, regardless of what that value is. :P

    That's what allows you to get rid of using Date-Time fields in the database, you're just bypassing the visual representation of it, there's nothing really wrong with it, but that's between you and your application.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,213

    Re: How to get access field dataformat using ado

    Meanwhile, it should be noted that the OP is NOT having a problem with the VALUE of the date ... that is NOT the issue... but rather wants to know the FORMAT the user has selected for the field.
    EXAMPLE: When you create a Date field in Access, you can set the format to mm/dd/yyyy... or yyyy-mm-dd or dd MMMM, yyyy or DDDD dd MMMM yyyy or .... THAT is what they are after. Much like you can set a boolean field to Yes/No, Y/N, True/False, T/F on/Off... etc... It's about configuring the display of the underlying value.
    My guess is that they want this format so that they can then display the date in the same format that the user has configured in the table during design. How to get it, I don't know. It's probably in the metadata somewhere... if ADO can access it, I don't know... something tells me no... DAO, probably can.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,705

    Re: How to get access field dataformat using ado

    My guess is that they want this format so that they can then display the date in the same format that the user has configured in the table during design.
    So, who is the one who configured the format in the table during design? OP? Or is this a program that allows other users to create and populate their own tables/fields? If the latter, I understand the 'why'. If the former (OP), he/she already knows (should know) the format. Confused (as usual).
    Sam I am (as well as Confused at times).

  16. #16
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,879

    Re: How to get access field dataformat using ado

    My guess is that somebody used MS Access to create the database.

    Now we have some program that wants to intuit intent by examining the DataFormat. But the Provider does not return the information because Jet/ACE does not support it.

    As I suggested above, MS Access stuffs this sort of client metadata into private structures you cannot get at via ADO, ADOX, or DAO.

  17. #17
    PowerPoster
    Join Date
    Feb 2017
    Posts
    2,962

    Re: How to get access field dataformat using ado

    Quote Originally Posted by Hosam AL Dein View Post
    This does not happen if I set a StdDataFromat object to the field and then try to read it using the previous code .
    Then why not do that?

    Code:
    Dim f As StdDataFormat
    
    Set f = dd.Fields("Datex").DataFormat
    If Not f Is Nothing Then
        MsgBox f.Format
    End If
    PS: I don't know if you will get the format but at least that should avoid the error.

  18. #18
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,879

    Re: How to get access field dataformat using ado

    Doesn't work. "f" will be Nothing.

  19. #19

    Thread Starter
    Addicted Member
    Join Date
    Jul 2017
    Posts
    216

    Thumbs up Re: How to get access field dataformat using ado

    Thanks all for your input .
    After reading all posts I need to clarify some points

    1- I don`t have data in the field ( or I need to get the format without depending on data )
    2- I already did what Eduardo suggested and yes Dielttante , it will return Nothing .
    3- My main goal is that : I am developing some user control that should return the field type to populate a corresponding control at run time . So , depending on the type only , I can not determine whether to load a Datepicker formatted for time or for date . That`s why I went to the DataFormat property .
    4- I will consider using DAO as a solution to what I am trying to achieve .
    5 - This thread is partially resolved as DAO can do the job and the other half of the the thread is the ADO DataFormat issue .
    Last edited by Hosam AL Dein; Jan 9th, 2021 at 12:48 AM.

  20. #20
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,680

    Re: How to get access field dataformat using ado

    Quote Originally Posted by techgnome View Post
    Meanwhile, it should be noted that the OP is NOT having a problem with the VALUE of the date ... that is NOT the issue... but rather wants to know the FORMAT the user has selected for the field.
    EXAMPLE: When you create a Date field in Access, you can set the format to mm/dd/yyyy... or yyyy-mm-dd or dd MMMM, yyyy or DDDD dd MMMM yyyy or .... THAT is what they are after. Much like you can set a boolean field to Yes/No, Y/N, True/False, T/F on/Off... etc... It's about configuring the display of the underlying value.
    My guess is that they want this format so that they can then display the date in the same format that the user has configured in the table during design. How to get it, I don't know. It's probably in the metadata somewhere... if ADO can access it, I don't know... something tells me no... DAO, probably can.

    -tg
    DAO can get to the 'InputMask'

    like this
    Code:
    Private Sub Command1_Click()
    Debug.Print GetFieldProperty("tbl_ReadDateFormat", "TestDate1", "Format")
    Debug.Print GetFieldProperty("tbl_ReadDateFormat", "TestDate1", "InputMask")
    Debug.Print "----------------"
    Debug.Print GetFieldProperty("tbl_ReadDateFormat", "TestDate2", "Format")
    Debug.Print GetFieldProperty("tbl_ReadDateFormat", "TestDate2", "InputMask")
    Debug.Print "----------------"
    Debug.Print GetFieldProperty("tbl_ReadDateFormat", "TestDate3", "Format")
    Debug.Print GetFieldProperty("tbl_ReadDateFormat", "TestDate3", "InputMask")
    End Sub
    the debug output
    Code:
    Medium Date
    00,00,0000;0;_
    ----------------
    Long Time
    00:00:00;0;_
    ----------------
    General Date
    99,99,0000;0;_
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width