Solved

ms access 2000 table default value  truncating leading zeroes

Posted on 2015-01-23
4
108 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

I wanted to share this with fellow Experts, who might not know. How often have you wanted to learn something, only to be set back by either restrictions imposed on "trial" or "evaluation" software?  How often have you had to rebuild a home networ…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

939 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now