By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,420 Members | 1,245 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,420 IT Pros & Developers. It's quick & easy.

Number field defaults to zero

AccessIdiot
100+
P: 493
Is there any way to stop this from happening? I have a table with lots of fields that are number types and I hate having to go in to every one by hand to remove the 0 default. Is there any way to kill it all at once?

thanks :)
Jun 5 '07 #1
Share this Question
Share on Google+
13 Replies

ADezii
Expert 5K+
P: 8,750
Is there any way to stop this from happening? I have a table with lots of fields that are number types and I hate having to go in to every one by hand to remove the 0 default. Is there any way to kill it all at once?

thanks :)
This will work nicely for you - just substitute you Table Name for tblEmployee or better yet, place this code in a Function and pass to it a Table name as an Argument. If you need help on any aspect of this, feel free to ask.

Sorry gentlemen - just forgot 1 minor detail - THE CODE!
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
  2.  
  3. Set MyDB = CurrentDb()
  4. Set tdf = MyDB.TableDefs("tblEmployee")
  5.  
  6. With tdf
  7.   For Each fld In tdf.Fields
  8.     Select Case fld.Type    '(2 = Byte, 3 = Integer, 4 = Long, 6 = Single/Double)
  9.       Case 2, 3, 4, 6
  10.         fld.DefaultValue = ""
  11.     End Select
  12.   Next
  13. End With
Jun 5 '07 #2

AccessIdiot
100+
P: 493
Just one question for the moment - is there code involved?

:-D
Jun 5 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Just one question for the moment - is there code involved?

:-D
Take it easy. This is a message from another world.
Just open your table in design view and set the Default Value property to nothing for the field(s) you've told about.

To ADezii: What's that was, I want the same for me too.
Jun 6 '07 #4

jamjar
P: 50
Take it easy. This is a message from another world.
Just open your table in design view and set the Default Value property to nothing for the field(s) you've told about.

To ADezii: What's that was, I want the same for me too.
Isn't this what the original poster suggested they were doing already, and they wanted to know how to avoid having to change all the defaults one by one? A way to have the defaults themselves default to nothing instead of zero?
Jun 6 '07 #5

AccessIdiot
100+
P: 493
Yes. Right now I've got 50 number fields and to go into the properties for each one and erase the default of 0 is a major pain and time consuming. A one time bit of code or something would be nice and painless.
Jun 6 '07 #6

ADezii
Expert 5K+
P: 8,750
Yes. Right now I've got 50 number fields and to go into the properties for each one and erase the default of 0 is a major pain and time consuming. A one time bit of code or something would be nice and painless.
Did you see Post #2?
Jun 6 '07 #7

AccessIdiot
100+
P: 493
I do now! :-) Thanks!
Jun 6 '07 #8

ADezii
Expert 5K+
P: 8,750
I do now! :-) Thanks!
You're quite welcome.
Jun 7 '07 #9

jamjar
P: 50
Did you see Post #2?
Duh .... got it now!

Still kind of annoying that Access doesn't let you set the default defaults for a project.

James
Jun 7 '07 #10

ADezii
Expert 5K+
P: 8,750
Duh .... got it now!

Still kind of annoying that Access doesn't let you set the default defaults for a project.

James
I guess that because you can specify Default Field Sizes and Type, that that by itself is enough.
Jun 7 '07 #11

P: 1
[quote=ADezii]This will work nicely for you - just substitute you Table Name for tblEmployee or better yet, place this code in a Function and pass to it a Table name as an Argument. If you need help on any aspect of this, feel free to ask.


I placed your code in a function, can you tell me how to pass it to my table as an Argument please?

Thanks,

Chris
Jun 15 '07 #12

ADezii
Expert 5K+
P: 8,750
[quote=Maxptyson]
This will work nicely for you - just substitute you Table Name for tblEmployee or better yet, place this code in a Function and pass to it a Table name as an Argument. If you need help on any aspect of this, feel free to ask.


I placed your code in a function, can you tell me how to pass it to my table as an Argument please?

Thanks,

Chris
Here is demo code indicating same:
Expand|Select|Wrap|Line Numbers
  1. Public Function fSetDefaults(strTableName As String)
  2. Dim MyDB As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
  3.  
  4. Set MyDB = CurrentDb()
  5. Set tdf = MyDB.TableDefs(strTableName)
  6.  
  7. With tdf
  8.   For Each fld In tdf.Fields
  9.     Select Case fld.Type    '(2 = Byte, 3 = Integer, 4 = Long, 6 = Single/Double)
  10.       Case 2, 3, 4, 6
  11.         fld.DefaultValue = ""
  12.     End Select
  13.   Next
  14. End With
  15. End Function
  16.  
To Call this Function:
Dim retVal
Expand|Select|Wrap|Line Numbers
  1. retVal = fSetDefaults("Junction")
  2.                  OR
  3. Call fSetDefaults("Junction")     'ignore return value
  4.  
Jun 15 '07 #13

jamjar
P: 50
This will work nicely for you - just substitute you Table Name for tblEmployee or better yet, place this code in a Function and pass to it a Table name as an Argument. If you need help on any aspect of this, feel free to ask.
I placed your code in a function, can you tell me how to pass it to my table as an Argument please?
Chris,
Place the code in a function
Expand|Select|Wrap|Line Numbers
  1. Function SetTableNumberDefaultsToNull(strTableName As String)
  2.     Dim MyDB As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
  3.     Set MyDB = CurrentDb()
  4.     Set tdf = MyDB.TableDefs(strTableName)
  5.     ....... rest of code as above .....
  6. End Function
and run the function. You can run the function from code or go to View|Immediate Window, and type
Expand|Select|Wrap|Line Numbers
  1. SetTableNumberDefaultsToNull("your table name")
cheers-
James
Jun 19 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.