MS Access database support - Is it possible to set a text field to spaces?

Author

Is it possible to set a text field to spaces?

usenet@isbd.co.uk

2006-04-04, 8:30 pm

I would like, if it's possible, to set the value of a field in a table
to a number of spaces. One space would be fine, I just want to be
able to set the field to a default value that's not NULL but also
doesn't show anything when the field is displayed.

Is this possible or would it be a 'bad thing'?

--
Chris Green

Rick Brandt

2006-04-04, 8:30 pm

<usenet@isbd.co.uk> wrote in message news:4432d61e.0@entanet...
>I would like, if it's possible, to set the value of a field in a table
> to a number of spaces. One space would be fine, I just want to be
> able to set the field to a default value that's not NULL but also
> doesn't show anything when the field is displayed.
>
> Is this possible or would it be a 'bad thing'?

In design view of the table you can turn on the property "Allow Zero Length
String" for that field and then set the default value to "". And yes I would
consider that a bad thing to do.

I don't like the idea of a field that can have two possible values that both
"look" the same.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Bernard Peek

2006-04-04, 8:30 pm

In message <gUCYf.44722$_S7.21741@newssvr14.news.prodigy.com>, Rick
Brandt <rickbrandt2@hotmail.com> writes

>In design view of the table you can turn on the property "Allow Zero Length
>String" for that field and then set the default value to "". And yes I would
>consider that a bad thing to do.
>
>I don't like the idea of a field that can have two possible values that both
>"look" the same.

It's better than having a system that lets users create different files
called and . That did cause a fair amount of confusion when I used
those in a database.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

Ted

2006-04-04, 8:30 pm

I deal with accounting software that requires a value in all fields.
When I write data from my Access application into their data tables, I
have to insert spaces into fields where I don't have a value ot insert,
so I create spaces in fields by assigning the value of Chr(32) which is
the value of the "space".
If I need more than one field with those spaces, I use a variables such
as:

Dim strSpace As String
Dim strSpaces5 As String
strSpace = Chr(32)
strSpaces5 = Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32)

Ted

usenet@isbd.co.uk wrote:
> I would like, if it's possible, to set the value of a field in a table
> to a number of spaces. One space would be fine, I just want to be
> able to set the field to a default value that's not NULL but also
> doesn't show anything when the field is displayed.
>
> Is this possible or would it be a 'bad thing'?
>
> --
> Chris Green

Br@dley

2006-04-05, 9:35 am

Ted wrote:
> I deal with accounting software that requires a value in all fields.
> When I write data from my Access application into their data tables, I
> have to insert spaces into fields where I don't have a value ot
> insert, so I create spaces in fields by assigning the value of
> Chr(32) which is the value of the "space".
> If I need more than one field with those spaces, I use a variables
> such as:
>
> Dim strSpace As String
> Dim strSpaces5 As String
> strSpace = Chr(32)
> strSpaces5 = Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32)

Icky.

Space(5)

Simple eh? :)

<>
--
regards,

Br@dley

usenet@isbd.co.uk

2006-04-05, 9:35 am

Rick Brandt <rickbrandt2@hotmail.com> wrote:
> <usenet@isbd.co.uk> wrote in message news:4432d61e.0@entanet...
>
> In design view of the table you can turn on the property "Allow Zero Length
> String" for that field and then set the default value to "". And yes I would
> consider that a bad thing to do.
>
> I don't like the idea of a field that can have two possible values that both
> "look" the same.
>
No, I can't set it to "" as that is NULL and I don't want the value to
be NULL.

In Oracle SQL (with which I am fairly familiar) it's dead easy:-

SQL> desc SYS_PARAM
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME NOT NULL VARCHAR2(34)
VALUE NOT NULL VARCHAR2(30)
DESCRIPTION VARCHAR2(120)

SQL> insert into SYS_PARAM values(' ', ' ', 'empty space');

1 row created.

A space is just as valid data as any other ASCII character so why
shouldn't I be able to put one in a column value?

--
Chris Green

usenet@isbd.co.uk

2006-04-05, 9:35 am

Ted <bear999@gmail.com> wrote:
>
> Ted
>
> usenet@isbd.co.uk wrote:
>
> I deal with accounting software that requires a value in all fields.
> When I write data from my Access application into their data tables, I
> have to insert spaces into fields where I don't have a value ot insert,
> so I create spaces in fields by assigning the value of Chr(32) which is
> the value of the "space".
> If I need more than one field with those spaces, I use a variables such
> as:
>
> Dim strSpace As String
> Dim strSpaces5 As String
> strSpace = Chr(32)
> strSpaces5 = Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32)

But can I get a space into a table without writing VB to do it?

For that matter how do I do it in VB, all the above does is tell me
how to create a variable containing a space which I knew already.

--
Chris Green

Br@dley

2006-04-05, 8:28 pm

usenet@isbd.co.uk wrote:
> Ted <bear999@gmail.com> wrote:
>
> But can I get a space into a table without writing VB to do it?
>
> For that matter how do I do it in VB, all the above does is tell me
> how to create a variable containing a space which I knew already.

Create an update query, add you table, and add a column:

Field: MyField
UpdateTo: Space(5)
--
regards,

Br@dley

Sponsored Links