Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ms access 2000 table default value  truncating leading zeroes

Posted on 2015-01-23
4
Medium Priority
?
119 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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

876 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