Setting General Number Format in vba

I am appending some new fields to a table in vba and when I append a number
field with is a byte, it does not inherit any format. I want it to be the
General Number format, but it is blank. I have tried to change the format
with the following code, but it does not work. What is wrong with this code
and how can I make that byte number field have the General Number format?

Call
SetPropertyDAO(DBEngine(0)(0).TableDefs("tblCommen dations").Fields("Year"),
"Format", dbByte, "General Number")

dixie

re: Setting General Number Format in vba

From memory, the Format property is dbText, not dbByte?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <dixie@dogmail.comwrote in message
news:130jg04rhr7qr64@corp.supernews.com...

re: Setting General Number Format in vba

So how do I set a byte size number field to General Number?

dixie

"Allen Browne" <AllenBrowne@SeeSig.Invalidwrote in message
news:4609ca3a$0$7427$5a62ac22@per-qv1-newsreader-01.iinet.net.au...

re: Setting General Number Format in vba

On Mar 27, 9:35 pm, "Dixie" <d...@dogmail.comwrote:
Hi.

I have no idea what SetPropertyDAO is supposed to be or do, but to
change a local field property this seems to work. This is an example,
not intended to work as is for you. If the Format of the field was not
previously set, it may need to be created with Field.CreateProperty
first. This changed a byte field from a currency format to general
number.

Function TestFldChange()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field

Set db = CurrentDb
Set tbl = db.TableDefs("tblTestTable")
Set fld = tbl.Fields("TestFld")
fld.Properties("Format") = "General Number"
tbl.Fields.Refresh

Set fld = Nothing
Set tbl = Nothing
Set db = Nothing

End Function

re: Setting General Number Format in vba

storrboy, you are right that SetPropertyDAO() is not a built-in function.
FYI, it's here:
http://allenbrowne.com/func-DAO.html#SetPropertyDAO

What it does is set the value of the property, creating it if it does not
exist.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"storrboy" <storrboy@sympatico.cawrote in message
news:1175050405.386530.293960@n76g2000hsh.googlegr oups.com...

re: Setting General Number Format in vba

Dixie, if you select a field in table design view, in the lower pane, you
see properties such as:
Field Size
Format
...

Are you attempting to set the Field Size to byte?
Or are you attempting to set the Format property to "General Number"?

If you actually want to change the field size to the Byte data type, you
can't do that with DAO. Instead execute a DDL query statemetn like this:
Dim strSql As String
strSql = "ALTER TABLE tblCommendationsALTER COLUMN [Year] BYTE;"
DBEngine(0)(0).Execute strSql, dbFailOnError

If DDL is new, here is an introduction and some examples:
http://allenbrowne.com/func-DDL.html

For a list of the names ot use with the different libraries, see:
Field type reference - names and values for DDL, DAO, and ADOX
at:
http://allenbrowne.com/ser-49.html

And if you really have a field named "Year", this can cause you grief. You
might want to consider renaming the field (and changing the reference in all
queries, forms, reports, macros and code where the name is used.) For a list
of the field names that cause problems, see:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <dixie@dogmail.comwrote in message
news:130jl43big3m72c@corp.supernews.com...

re: Setting General Number Format in vba

I thought so. Sorry if I jumped into the middle of a conversation.

re: Setting General Number Format in vba

You're always welcome.

These are open conversations, so anything you can contribute to help answer
the poster's question is appreciated.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"storrboy" <storrboy@sympatico.cawrote in message
news:1175100266.277079.226440@y66g2000hsf.googlegr oups.com...

re: Setting General Number Format in vba

"Allen Browne" <AllenBrowne@SeeSig.Invalidwrote in
news:460aa170$0$7402$5a62ac22@per-qv1-newsreader-01.iinet.net.au:
I wish to format a cheese sandwich as a general number.

Is this related?

--
lyle fairfield

Ceterum censeo Redmond esse delendam.

re: Setting General Number Format in vba

Sub Eat(CheeseSandwich As Byte)
If (CheeseSandwich - Bread) Or (CheeseSandwich - Cheese) Then
MsgBox "Invalid format"
ElseIf Bread Cheese Then
MsgBox "Cheese must be grated"
Else
Nibble CheeseSandwich, Hi
Nibble CheeseSandwich, Lo
MsgBox "Four-matted"
End If
End Sub

There's no foreign key, lyle, so it's not related. :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"lyle fairfield" <lylefair@yahoo.cawrote in message
news:wIyOh.12190$_f.7033@read2.cgocable.net...

re: Setting General Number Format in vba

Man, that will never work.
You forgot to check for HasMustard().