ms access 2000 table default value truncating leading zeroes

I need to setup a table in ms  access  2000 that will be used to send data to a text file.  Some of the fields(columns) need to have default values like  '01' .  but when I put 01 in the default value for a field, it display just 1. it needs to display  01( is truncating the leading zeroes.)  I need to export this table to a text file with the value not truncated( i.e. 01). I tried with single quote and I get error.  I also tried formats like 999999 or 000000, but did work either.
ShenAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SimonConnect With a Mentor Commented:
Again, I'd do it in the export query by defining the column as
Exprx: "  "
I'm not sure that you can do it in the table designer because of the way that Access auto-trims trailing spaces. I don't have Access 2000 any more, but you could try disallowing nulls and zero length in the field (if those options are available).

You could also try using non-breaking spaces as the default, but that may not be suitable for the output?
This wikipedia shows some methods of entering non-breaking spaces http://en.wikipedia.org/wiki/Non-breaking_space
or you could create them in Word (CTRL+SHIFT+SPACEBAR) and then copy-n-paste into the default value in the table design.

Really, I think the best answer is to store data in tables, and format output in queries, but I can understand that you may have a requirement to get the model right in the table because some export methods require a table rather than a query. Is this the case for you?
0
 
SimonCommented:
in the query you use to generate the export, replace that field value with
right("00" & yourfieldname,2)
or
right("0000000" & yourfieldname,6) if you need to cater for larger values without truncating them.
or
convert the field to a text field so you can add the zeros... but check first if that will break anything in your database design.

Hopefully clear, but come back with any questions.
0
 
ShenAuthor Commented:
I have been trying to do this as default value in the table .  I put  "01" as a default value, and  it is working now, however, I have another case, I can't create a field with say two blanks . So a field text length 2 if I put in default value " ", it creates a null. is there a way to create blanks  or  spaces through the table designer
0
 
ShenAuthor Commented:
thanks,  i am now doing a lot of the work through queries now. I have been using the format function in the queries. i have not completed the project yet.  I will propbably have addtional question as i move into the next steps in the project.

thanks again, very helpfull.
0
All Courses

From novice to tech pro — start learning today.