Solved

ms access 2000 table default value  truncating leading zeroes

Posted on 2015-01-23
4
112 Views
Last Modified: 2015-01-26
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.
0
Comment
Question by:Shen
  • 2
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40567655
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
 

Author Comment

by:Shen
ID: 40567736
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
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40568130
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
 

Author Closing Comment

by:Shen
ID: 40571365
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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question