-
Jan 7th, 2021, 09:19 PM
#1
Thread Starter
Addicted Member
[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.
-
Jan 7th, 2021, 10:50 PM
#2
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).
-
Jan 8th, 2021, 12:05 AM
#3
Thread Starter
Addicted Member
Re: How to get access field dataformat using ado
Originally Posted by SamOscarBrown
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.
-
Jan 8th, 2021, 12:10 AM
#4
Thread Starter
Addicted Member
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 ?
-
Jan 8th, 2021, 03:12 AM
#5
Re: How to get access field dataformat using ado
Originally Posted by Hosam AL Dein
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.
-
Jan 8th, 2021, 04:53 AM
#6
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
-
Jan 8th, 2021, 04:59 AM
#7
Thread Starter
Addicted Member
Re: How to get access field dataformat using ado
Originally Posted by ChrisE
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
-
Jan 8th, 2021, 05:00 AM
#8
Thread Starter
Addicted Member
Re: How to get access field dataformat using ado
Originally Posted by westconn1
is the field actually a text field or a date type?
No , not a Text filed. It is Date/Time
-
Jan 8th, 2021, 08:42 AM
#9
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).
-
Jan 8th, 2021, 08:47 AM
#10
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE 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.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jan 8th, 2021, 09:02 AM
#11
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.
-
Jan 8th, 2021, 09:25 AM
#12
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).
-
Jan 8th, 2021, 12:09 PM
#13
Re: How to get access field dataformat using ado
Originally Posted by Zvoni
*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
-
Jan 8th, 2021, 12:16 PM
#14
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
-
Jan 8th, 2021, 01:26 PM
#15
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).
-
Jan 8th, 2021, 02:15 PM
#16
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.
-
Jan 8th, 2021, 02:16 PM
#17
Re: How to get access field dataformat using ado
Originally Posted by Hosam AL Dein
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.
-
Jan 8th, 2021, 02:22 PM
#18
Re: How to get access field dataformat using ado
Doesn't work. "f" will be Nothing.
-
Jan 8th, 2021, 06:23 PM
#19
Thread Starter
Addicted Member
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.
-
Jan 9th, 2021, 02:13 AM
#20
Re: How to get access field dataformat using ado
Originally Posted by techgnome
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|