Field Type property

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

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...

paasky,
   jinx!

You can find out the numeric value of constant easily with debug window:

? dbText
 10

? dbCurrency
 5

etc...

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

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

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)

ncw is probably thinking "These guys have too much time on their hands."

You guys are probably participating EE at work - hoping your boss doesn't see - I'm already at home :-P

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^)

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" _
        )

what can I say....I have bad handwriting
AND bad formatting!

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!

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

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.. :-)

oops.. gave you wrong information. Time in Finland is CET + 1h, and +2 hours from London Greenwitch time...

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?

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.

paasky:
   you can thank me for that scrolling feature.....brianwren needs to give me formatting tips

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?!

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!)

:-))))

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?

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

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

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....

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

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

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

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").

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

;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....]

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!).

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

Adjusted points from 10 to 15

Ta

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

(darn, the pretty alignment of case sentence was messed with this EE font... it looks better when pasted to fixed size editor window)

..mean editor window with fixed size font :o)

ncw,

As to time zones . . .  CEST la vie!

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>

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...

both
my
space
bar
and
enter
key
were
broken
that
day.


Sorry,
Mike

p.s.
this
better?

8^)

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

I think this is the bestest thread ever!

hmmm...  yes..  if a bit spacey at times, and at others, a bit strung out...

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.