Solved

Field Type property

Posted on 2000-04-11
46
347 Views
Last Modified: 2013-12-03
I have used VBA code to evaluate the Field Types of recordset using Field.Type and was expecting a text value eg int, or text, etc, but instead I am getting numeric values.

How can I obtain the datatype in text format?
0
Comment
Question by:ncw
  • 12
  • 11
  • 8
  • +3
46 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2704625
Hello ncw,

Use constants which have the numeric value to parse field type values. You can read more information about datatypes from Access help using keyword "Comparison of Data Types":

Yes/No  dbBoolean
Byte      dbByte
Long Integer dbLong
Currency dbCurrency
Date/Time dbDate
Double      dbDouble
Replication dbGUID
OLE Object dbLongBinary
Memo dbMemo
Single      dbSingle
Integer dbInteger
Text dbText

Hope this helps,
Paasky
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2704626
Table fields      
Query
parameters      

Visual Basic      DAO Field object
Type property
constants      Microsoft Jet database
engine SQL and
synonyms
Not supported      Binary      Not supported      Not supported      BINARY (See Notes)
(Synonym: VARBINARY)
Yes/No      Yes/No      Boolean      dbBoolean      BOOLEAN
(Synonyms: BIT, LOGICAL, LOGICAL1, YESNO)
Number
(FieldSize =
Byte)      Byte      Byte      dbByte      BYTE
(Synonym: INTEGER1)
AutoNumber
(FieldSize =
Long Integer)      Long Integer      Long      dbLong      COUNTER
(Synonym: AUTOINCREMENT)
Currency      Currency      Currency      dbCurrency      CURRENCY
(Synonym: MONEY)
Date/Time      Date/Time      Date      dbDate      DATETIME
(Synonyms: DATE, TIME, TIMESTAMP)
Number
(FieldSize =
Double)      Double      Double      dbDouble      DOUBLE
(Synonyms: FLOAT, FLOAT8, IEEEDOUBLE, NUMBER, NUMERIC)
Number or AutoNumber
(FieldSize =
Replication ID)      Replication ID      Not supported      dbGUID      GUID
Number
(FieldSize =
Long Integer)      Long Integer      Long      dbLong      LONG (See Notes)
(Synonyms: INT, INTEGER, INTEGER4)
OLE Object      OLE Object      String      dbLongBinary      LONGBINARY
(Synonyms: GENERAL, OLEOBJECT)
Memo      Memo      String      dbMemo      LONGTEXT
(Synonyms: LONGCHAR, MEMO, NOTE)
Number
(FieldSize =
Single)      Single      Single      dbSingle      SINGLE
(Synonyms: FLOAT4, IEEESINGLE, REAL)
Number
(FieldSize =
Integer)      Integer      Integer      dbInteger      SHORT (See Notes)
(Synonyms: INTEGER2, SMALLINT)
Text      Text      String      dbText      TEXT
(Synonyms: ALPHANUMERIC, CHAR, CHARACTER, STRING, VARCHAR)
Hyperlink      Memo      String      dbMemo      LONGTEXT
(Synonyms: LONGCHAR, MEMO, NOTE)




sorry about the formatting.....rather than worrying about numbers...just use the constants: like dbBoolean, dbByte, dbLong etc.etc...
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2704627
paasky,
   jinx!
0
 
LVL 10

Expert Comment

by:paasky
ID: 2704630
You can find out the numeric value of constant easily with debug window:

? dbText
 10

? dbCurrency
 5

etc...
0
 
LVL 3

Expert Comment

by:davereynolds
ID: 2704694
ncw,
I think you should accept my answer because it is prettier than either paasky's or Mike's (:-))

Pasted below is an example I found in Help:
Function FieldType(intType As Integer) As String

Select Case intType
  Case dbBoolean
    FieldType = "dbBoolean"
  Case dbByte
    FieldType = "dbByte"
  Case dbInteger
    FieldType = "dbInteger"
  Case dbLong
    FieldType = "dbLong"
  Case dbCurrency
    FieldType = "dbCurrency"
  Case dbSingle
    FieldType = "dbSingle"
  Case dbDouble
    FieldType = "dbDouble"
  Case dbDate
    FieldType = "dbDate"
  Case dbText
    FieldType = "dbText"
  Case dbLongBinary
    FieldType = "dbLongBinary"
  Case dbMemo
    FieldType = "dbMemo"
  Case dbGUID
    FieldType = "dbGUID"
End Select

End Function

0
 
LVL 10

Accepted Solution

by:
paasky earned 15 total points
ID: 2704751
davereynolds :-)))

More enchantment:

Function FieldType(Fld As Field) As String

    With Fld
        Select Case .Type
          Case dbBoolean
            FieldType = "Boolean"
          Case dbByte
            FieldType = "Byte"
          Case dbInteger
            FieldType = "Integer"
          Case dbLong
            FieldType = "Long"
          Case dbCurrency
            FieldType = "Currency"
          Case dbSingle
            FieldType = "Single"
          Case dbDouble
            FieldType = "Double"
          Case dbDate
            FieldType = "Date/Time"
          Case dbText
            FieldType = "Text (" & CStr(.Size) & ")"
          Case dbLongBinary
            FieldType = "Ole Object"
          Case dbMemo
            FieldType = "Memo"
          Case dbGUID
            FieldType = "GUID"
        End Select
    End With

End Function

usage examples:
? FieldType(Currentdb.TableDefs("table1").Fields(0))
Text (50)

? FieldType(Currentdb.TableDefs("table1").Fields("field2"))
Integer

Paasky
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2704752
It's even prettier this way:


Function FieldType(intType As Integer) As String

       Select Case intType
              Case dbBoolean: FieldType = "dbBoolean"
              Case dbByte: FieldType = "dbByte"
              Case dbInteger: FieldType = "dbInteger"
              Case dbLong: FieldType = "dbLong"
              Case dbCurrency: = "dbCurrency"
              Case dbSingle: FieldType = "dbSingle"
              Case dbDouble: FieldType = "dbDouble"
              Case dbDate: FieldType = "dbDate"
              Case dbText: FieldType = "dbText"
              Case dbLongBinary: FieldType = "dbLongBinary"
              Case dbMemo: FieldType = "dbMemo"
              Case dbGUID: FieldType = "dbGUID"
       End Select

End Function

Brian
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2704777
or how about this: (sorry paasky...I wanted this answer to show the same time as our last two responses...but I had to actually do some work)

Switch(Field.Type=dbBoolean,"dbBoolean",FieldType=dbByte,"dbByte",FieldType=dbInteger,"dbInteger",FieldType=dbLong,"dbLong",FieldType=dbCurrency,"dbCurrency",FieldType=dbSingle,"dbSingle",FieldType=dbDouble,"dbDouble",FieldType=dbDate,"dbDate",FieldType=dbText,"dbText",FieldType=dbLongBinary,"dbLongBinary",FieldType=dbMemo,"dbMemo",FieldType=dbGUID,"dbGUID")

(this is my BrianWren styled answer)
0
 
LVL 3

Expert Comment

by:davereynolds
ID: 2704778
ncw is probably thinking "These guys have too much time on their hands."
0
 
LVL 10

Expert Comment

by:paasky
ID: 2704807
You guys are probably participating EE at work - hoping your boss doesn't see - I'm already at home :-P
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2704833
aahhh....all those 'FieldType's should be Field.Type

it'd probably be quicker to store that in an integer variable and then do the switch on that too....


paasky,
I'm at the office today, but usually I work from home where it doesn't matter!!  Besides...EE is where I find the answers to those issues I just can't seem to figure out, and take me 'days' to fix.......   8^)
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2704856
Except that I would use:

Switch( _
          Field.Type=dbBoolean, "dbBoolean", _
          FieldType=dbByte, "dbByte", _
          FieldType=dbInteger, "dbInteger", _
          FieldType=dbLong, "dbLong", _
            FieldType=dbCurrency, "dbCurrency", _
          FieldType=dbSingle, "dbSingle", _
     
FieldType=dbDouble, "dbDouble", _
          FieldType=dbDate, "dbDate", _
          FieldType=dbText, "dbText", _
          FieldType=dbLongBinary," dbLongBinary", _
          FieldType=dbMemo, "dbMemo", _
          FieldType=dbGUID, "dbGUID" _
        )
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2704882
what can I say....I have bad handwriting
AND bad formatting!
0
 
LVL 3

Expert Comment

by:davereynolds
ID: 2704936
Hey, I noticed that Brian propogated Mike's "FieldType" instead of "Field.Type" :-).
Incidently, does anyone know what "CEST" means in EE's timestamp- Central European Standard Time? I noticed it is about 6 hours later than Eastern. Does that mean EE is somewhere in central Europe, maybe even Finland? (I've always been a little suspicious that paasky managed to get so many points so quickly.)
Sorry ncw, these threads sometimes develop a life of their own. You certainly got your 10 points worth!
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2704957
davereynolds,
   EE is a conspiracy!!!  You've just uncovered the conspiracy theory...lets make a movie!  Wow....I REALLY need to cut back on the coffee in the afternoon
0
 
LVL 10

Expert Comment

by:paasky
ID: 2704978
davereynolds: Where do you see CEST, you're from Britain? I see only "Date: Tuesday, April 11 2000 - 09:19PM EEST" in your previous comment... Our time here is CET (Central European Time)+2hours

Paasky

PS. it's so fanny to scroll five miles to right to hit submit button.. :-)
0
 
LVL 10

Expert Comment

by:paasky
ID: 2704998
oops.. gave you wrong information. Time in Finland is CET + 1h, and +2 hours from London Greenwitch time...
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2704999
I see "Date: Tuesday, April 11 2000 - 02:25PM EDT"    not the EEST.....something fishy is going on.  The server must change the dates to match your default settings in your browser?
0
 
LVL 3

Expert Comment

by:davereynolds
ID: 2705006
paasky, I'm in Atlanta, where it is 2:30 PM Eastern Daylight Time, but I see your comment as 8:28 PM CEST.
Everything was fine until Mike tried to imitate Brian with that really long Switch command.
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2705008
paasky:
   you can thank me for that scrolling feature.....brianwren needs to give me formatting tips
0
 
LVL 10

Expert Comment

by:paasky
ID: 2705038
davereynolds, it's 9:44PM here (8:44CET+1hour). I see all your comments in my time, you mean you see your own comments 2:44PM EDT and mine 8:44 CEST? You should probably check your EE profile settings that you have entered your location correctly?!
0
 
LVL 3

Expert Comment

by:davereynolds
ID: 2705049
Just checked my EE profile- it said I was in Andorra, which I must assume is somewhere in Europe (if you are from Andorra, please refrain from sending me nasty e-mail!)
0
 
LVL 10

Expert Comment

by:paasky
ID: 2705138
:-))))

I wonder how many EE experts are registered beeing from Andorra.. even they actually live in some other country. Andorra was probably the first choice in the country list, eh?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Expert Comment

by:MikeRenz
ID: 2705180
Andorra is a very suittable programming country...sounds like me when I'm stuttering:

  "so then you reference this object and...or.....uhhhh....."

0
 
LVL 3

Expert Comment

by:davereynolds
ID: 2705198
Phew- Now I can relax because all the hate mail regarding Andorra will go to Mike!
I bet ncw will never submit another question after this....
0
 
LVL 1

Author Comment

by:ncw
ID: 2705521
I've only been out 2 hours and I think this little question has made a serious contribution to my 40 emails just downloaded, - all for 10 points!

I shall need a few days to digest the above.

Thanks,

Nick
0
 
LVL 1

Author Comment

by:ncw
ID: 2705524
I've only been out 2 hours and I think this little question has made a serious contribution to my 40 emails just downloaded, - all for 10 points!

I shall need a few days to digest the above.

Thanks,

Nick
0
 
LVL 1

Author Comment

by:ncw
ID: 2705590
I think the above is really about setting field types.  

Actually I have a number of tables and I want to interigate them to find out what the data types are using VBA code.  I have extracted the field names, size, and type, but the type returns a numeric value which I can't relate to anything.

I was hoping it would return eg int etc.

Nick
0
 
LVL 3

Expert Comment

by:davereynolds
ID: 2705616
Nick,
Thats what the various versions of the functions submitted by your wordy expects do for you- you pass the function the value of Type (a number) and it returns a string (i.e. "Boolean").
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2705617
ncw,

Is that 2 hours CEST, PST, EST, CET, or What?

Mike,

Re: "brianwren needs to give me formatting tips..."  I  _DID_!  That's what my post was!

paasky,

It's late...  you are getting sleeeeeepeeeee...  go to bed!

Brian


0
 
LVL 24

Expert Comment

by:SunBow
ID: 2705977
;o((  alrigh! Who pasted in that thingy that blows up my browser (IE5) - I now have to scroll right again and again because of that one long line wherever.

I thought
I had thought that this was one of the examples presented in one of the help files.

IMHO, as phrased it calls for typical case stmt. As you have three or more worthy examples, I can only be different in verbage

Add a few 'other' kinds of cases to any of the worthy ones above. Checking up front for errors, negatives, invalids, and error on format of input can be a good proactive implementation.  At a minimum, add a 'case else' for "Other" or for "else" to get a step ahead.

I see good examples above where one is 1st, one more expansive, one 'prettier'. Although IMHO, prettiest is columns lining up, I'd opt for agreeing w/BrianWren on 'prettier' if the data text lined up in a column as good as priors. So here's 2nd suggestion:

If you have trouble deciding between them, how about asking for prettiness suggestion to complement above. Namely, Many threads contain paste jobs that are succeeded by comments on the jaggies, apologizing for columns not lining up the same in EE. So, how's about hint on getting EE to align as the tiebreaker (if needed).

I will do 3rd recommend - in VB columns alignment is a bit easier on some alignments.  So, use them to make straight columns at least as good as above, for it greatly enhances search , change, validation, completeness, handling typos and misc. program maintenance needs. I want to 'assume' that is a given, but.     Line up.

[just noticed, trouble locating 'submit' -- it is 3-4 screens over horizontally to the right to find that button on THIS thread....]
0
 
LVL 3

Expert Comment

by:davereynolds
ID: 2707847
Hey ncw,
I just reread this ridiculous thread, and I think you should give the 10 points to paasky for his response on 4-11 at 1:19PM EDT (I got my time zones straightened out!) for the following reasons:
1. It is pretty.
2. It returns real words like "Integer" instead of "dbInteger".
3. On text types, it even returns the length.
4. paasky even supplied examples of how to call the function (with results).
5. paasky paid me $5 to say this and I'm tired of having to scroll 4 screens to the right to find the Submit button (thanks Mike!).
0
 
LVL 1

Author Comment

by:ncw
ID: 2709080
davereynolds : I agree.

to all:

I would like to accept paasky's answer because its the one I cut and pasted today and it works well.  If anyone wants to extend this email a bit further, it does pose a further questions: what if you do not want the field length in brackets but just the type.  Also I incorporated sunbow's suggestion of having a 'case else', because I came across a binary field in SysObject table, but I could no see this type mentioned in the Access help.

I was interested in MikeRenz's switch function, but did not have time to experiment with it.

As to timezones, I'm in Hampshire UK so is that 'CEST'?

Thank you all

Nick
0
 
LVL 1

Author Comment

by:ncw
ID: 2709085
Adjusted points from 10 to 15
0
 
LVL 1

Author Comment

by:ncw
ID: 2709087
Ta
0
 
LVL 10

Expert Comment

by:paasky
ID: 2709165
Happy to help you ncw and thank you for extra points. :o)

Here's the "final" and "pretty" solution which is combination of above suggestions:

Function FieldType(Fld As Field, Optional ShowSize As Boolean) As String

    With Fld
        Select Case .Type
          Case dbBoolean:    FieldType = "Boolean"
          Case dbByte:       FieldType = "Byte"
          Case dbInteger:    FieldType = "Integer"
          Case dbLong:       FieldType = "Long"
          Case dbCurrency:   FieldType = "Currency"
          Case dbSingle:     FieldType = "Single"
          Case dbDouble:     FieldType = "Double"
          Case dbDate:       FieldType = "Date/Time"
          Case dbText:       FieldType = "Text" & IIf(ShowSize, "(" & CStr(.Size) & ")", "")
          Case dbLongBinary: FieldType = "Ole Object"
          Case dbMemo:       FieldType = "Memo"
          Case dbGUID:       FieldType = "GUID"
          Case Else:         FieldType = "<Unknown>"
        End Select
    End With

End Function

Thank you all people participated this question which has been same time entertaining and informative. :o)

Best regards,
Paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2709175
(darn, the pretty alignment of case sentence was messed with this EE font... it looks better when pasted to fixed size editor window)
0
 
LVL 10

Expert Comment

by:paasky
ID: 2709180
..mean editor window with fixed size font :o)
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2709720
ncw,

As to time zones . . .  CEST la vie!
0
 
LVL 24

Expert Comment

by:SunBow
ID: 2709955
Since all them eMails got my late return here <cr>
I am pleased, very, that someone actually likes that approach<cr>
of case else, even if not expected<cr>
even if not as 'wierd' as I go<cr>
Still<cr>

Since this q got a raise to 15 pt<cr>
Since I now have to scroll to submit<cr>
Since persons other than me are actually noting the scroll issue this run<cr>

I think the 5 more points should now include answer<cr>
What was it that did this scroll feature<cr>
Lest we get stuck with it again<cr>
On something even more important<cr>
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2713334
Mike's comment from April 11:

Switch(Field.Type=dbBoolean,"dbBoolean",...,FieldType=dbGUID,"dbGUID")

When there is no break in the line, EE just runs it on forever...

0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2713359
both
my
space
bar
and
enter
key
were
broken
that
day.


Sorry,
Mike

p.s.
this
better?

8^)
0
 
LVL 10

Expert Comment

by:paasky
ID: 2713382
H
i
!

S
u
r
e

i
t

l
o
o
k
s

b
e
t
t
e
r

M
i
k
e
..

H
a
v
e

a

n
i
c
e

d
a
y
,

h
o
p
e

y
o
u
r

s
p
a
c
e
b
a
r

w
i
l
l

b
e

f
i
x
e
d

s
o
o
n
!

:
o
)


P
a
a
s
k
y
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2713438
I think this is the bestest thread ever!
0
 
LVL 9

Expert Comment

by:BrianWren
ID: 2713965
hmmm...  yes..  if a bit spacey at times, and at others, a bit strung out...
0
 
LVL 24

Expert Comment

by:SunBow
ID: 2714184
OK, now I see it.
Only prior time was a URL clipping,
posted as solution a URL as answer
It was over eight lines long
WITH the horizontal scroll.

It was something of a search engine using a search engine with criteria galore.

Once I got it to work, I made it more bookmarkable for user (me too)
and asked the commenter to plz consider doing a
briefer URL the next time to help clipping it.
He got real tiffy (antagonistic) - I moved on, but it di leave me wonder on what made the scroll go.
EE can't hyphen or break like eMail does. I think the link was clickable, so maybe that is an ok thing.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now