Setting General Number Format in vba

[Dixie]

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

[Allen Browne]

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" <di***@dogmail.comwrote in message
news:13*************@corp.supernews.com...

>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

[Dixie]

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

dixie

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46**********************@per-qv1-newsreader-01.iinet.net.au...

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" <di***@dogmail.comwrote in message
news:13*************@corp.supernews.com...

>>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("tblComme ndations").Fields("Year"),
"Format", dbByte, "General Number")

dixie

[storrboy]

On Mar 27, 9:35 pm, "Dixie" <[e-mail]:

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

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

[Allen Browne]

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" <st******@sympatico.cawrote in message
news:11**********************@n76g2000hsh.googlegr oups.com...

On Mar 27, 9:35 pm, "Dixie" <[e-mail]:

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

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

[Allen Browne]

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" <di***@dogmail.comwrote in message
news:13*************@corp.supernews.com...

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

dixie

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46**********************@per-qv1-newsreader-01.iinet.net.au...

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

"Dixie" <di***@dogmail.comwrote in message
news:13*************@corp.supernews.com...

>>>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("tblComm endations").Fields("Year"),
"Format", dbByte, "General Number")

dixie

[storrboy]

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

[Allen Browne]

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" <st******@sympatico.cawrote in message
news:11**********************@y66g2000hsf.googlegr oups.com...

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

[lyle fairfield]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:46**********************@per-qv1-newsreader-01.iinet.net.au:

You're always welcome.

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

I wish to format a cheese sandwich as a general number.

Is this related?

--
lyle fairfield

Ceterum censeo Redmond esse delendam.

[Allen Browne]

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" <ly******@yahoo.cawrote in message
news:wI*****************@read2.cgocable.net...

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:46**********************@per-qv1-newsreader-01.iinet.net.au:

>You're always welcome.

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

I wish to format a cheese sandwich as a general number.

Is this related?

--
lyle fairfield

Ceterum censeo Redmond esse delendam.

[storrboy]

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