equivalent of varchar2?

[hilz]

Hi all,

What is the equivalent of VARCHAR2 in access?

thanks
hilz

[PC Datasheet]

VARCHAR-2 ????

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"hilz" <no**@y.com> wrote in message
news:0L********************@comcast.com...

Hi all,

What is the equivalent of VARCHAR2 in access?

thanks
hilz

[hilz]

> VARCHAR-2 ????

Is there such a thing as VARCHAR-2 ????
Incase my question was not clear, here are some more details:

In Oracle, there is a datatype called VARCHAR2. It can hold more than the
length of the field.
for example ;
create table test (name VARCHAR(10));
Now if you insert into this table a row with name='a string that is longer
than 10 characters'
it will accept it and store it. I beleive the length (10) is just for
optimizing, and should be close to what you expect most of the values in
that field to be.

And if you insert into the table a row with name='abc' it will not pad the
string with spaces, and thus saving storage space.

Now in Access, declaring a filed as CHAR will have a fixed length that
cannot be exceeded, and if the value was shorter than the length, it is
padded with spaces to fill the whole length of the field, and that is not
really very desirable.

If i create my access table using an SQL statement, rather than the table
designer, i can set the field as VARCHAR, but not VARCHAR2.
and VARCHAR in this case does not pad the short string with spaces, which is
good, but it does not allow a string that is longer than the column width.
so my question is: is there such a datatype in Access that is similar to
VARCHAR2?

I remember there was a memo filed, but I also remember that it is not
recommended to use that for just any string field.

thanks
hilz

[Randy Harris]

"hilz" <no**@y.com> wrote in message
news:0L********************@comcast.com...

Hi all,

What is the equivalent of VARCHAR2 in access?

thanks
hilz

Text.

[Neil Ginsberg]

Such a beast doesn't exist in Access.

"hilz" <no**@y.com> wrote in message
news:K9********************@comcast.com...

VARCHAR-2 ????

Is there such a thing as VARCHAR-2 ????
Incase my question was not clear, here are some more details:

In Oracle, there is a datatype called VARCHAR2. It can hold more than the
length of the field.
for example ;
create table test (name VARCHAR(10));
Now if you insert into this table a row with name='a string that is longer
than 10 characters'
it will accept it and store it. I beleive the length (10) is just for
optimizing, and should be close to what you expect most of the values in
that field to be.

And if you insert into the table a row with name='abc' it will not pad the
string with spaces, and thus saving storage space.

Now in Access, declaring a filed as CHAR will have a fixed length that
cannot be exceeded, and if the value was shorter than the length, it is
padded with spaces to fill the whole length of the field, and that is not
really very desirable.

If i create my access table using an SQL statement, rather than the table
designer, i can set the field as VARCHAR, but not VARCHAR2.
and VARCHAR in this case does not pad the short string with spaces, which
is
good, but it does not allow a string that is longer than the column width.
so my question is: is there such a datatype in Access that is similar to
VARCHAR2?

I remember there was a memo filed, but I also remember that it is not
recommended to use that for just any string field.

thanks
hilz

[Mike Preston]

On Tue, 18 Jan 2005 20:29:44 -0500, "hilz" <no**@y.com> wrote:

Hi all,

What is the equivalent of VARCHAR2 in access?

I don't think there is one. You'll have to roll your own.

mike

[Larry Linson]

"hilz" <no**@y.com> wrote

Now in Access, declaring a filed as
CHAR will have a fixed length that
cannot be exceeded, and if the value
was shorter than the length, it is
padded with spaces to fill the whole
length of the field, and that is not
really very desirable.

If you are talking about a Text field in a Jet database, this is absolutely
incorrect. Text and Memo fields are all variable-length, requiring no more
storage than the contents plus necessary controlling information. If you
specify a length on a Text field, that will be used as a maximum length, but
the field will NOT be padded with blanks or null characters to that length.

If you are defining fields other than by using the Table design UI, then you
are (1) wasting effort and (2) perhaps limiting yourself by using things
that work better in some other database than in Access.

Larry Linson
Microsoft Access MVP

[Randy Harris]

"hilz" <no**@y.com> wrote in message
news:K9********************@comcast.com...

VARCHAR-2 ????
Is there such a thing as VARCHAR-2 ????
Incase my question was not clear, here are some more details:

In Oracle, there is a datatype called VARCHAR2. It can hold more than the
length of the field.
for example ;
create table test (name VARCHAR(10));
Now if you insert into this table a row with name='a string that is longer
than 10 characters'
it will accept it and store it. I beleive the length (10) is just for
optimizing, and should be close to what you expect most of the values in
that field to be.

And if you insert into the table a row with name='abc' it will not pad the
string with spaces, and thus saving storage space.

Now in Access, declaring a filed as CHAR will have a fixed length that
cannot be exceeded, and if the value was shorter than the length, it is
padded with spaces to fill the whole length of the field, and that is not
really very desirable.

If i create my access table using an SQL statement, rather than the table
designer, i can set the field as VARCHAR, but not VARCHAR2.
and VARCHAR in this case does not pad the short string with spaces, which

is good, but it does not allow a string that is longer than the column width.
so my question is: is there such a datatype in Access that is similar to
VARCHAR2?

I remember there was a memo filed, but I also remember that it is not
recommended to use that for just any string field.

thanks
hilz

hilz,

I think you are a bit confused. Both about how VARCHAR2 behaves in Oracle
and how Text behaves in Jet. Neither permits insertion of data greater in
length than the field width, nor does either pad fields as you have
described. Jet does not have a data type equivalent to CHAR.

Regards

[hilz]

"Larry Linson" <bo*****@localhost.not> wrote in message
news:ZHkHd.6235$ef6.1630@trnddc07...

"hilz" <no**@y.com> wrote

> Now in Access, declaring a filed as
> CHAR will have a fixed length that
> cannot be exceeded, and if the value
> was shorter than the length, it is
> padded with spaces to fill the whole
> length of the field, and that is not
> really very desirable.
If you are talking about a Text field in a Jet database, this is

absolutely incorrect. Text and Memo fields are all variable-length, requiring no more
storage than the contents plus necessary controlling information. If you
specify a length on a Text field, that will be used as a maximum length, but the field will NOT be padded with blanks or null characters to that length.
If you are defining fields other than by using the Table design UI, then you are (1) wasting effort and (2) perhaps limiting yourself by using things
that work better in some other database than in Access.

Larry Linson
Microsoft Access MVP

You were right. I was confused, and now i have a little better understanding
about this matter, but what is wrong with creating the schema in Access
using an SQL script that is executed throught an ado connection?

thanks.

[Tim Marshall]

hilz wrote:

Hi all,

What is the equivalent of VARCHAR2 in access?

Text. Unfortunately, it has a character limit of 255 versus 2000 for
Oracle's varchar2.

However, for fields greater than 255 characters you can use a memo data
type. Unsure of max characters, but it's effectively unlimited for my
usages, at least. The nice things about memo type fields as compared to
the equivalentlong data type in Oracle are:

1) You can enter where criteria

2) It can be sorted

3) You can have MORE THAN ONE per table!

8)

If you get around to using Access as a FE to an oracle DB, results
displayed from Oracle long data types cn be searched and filtered in a
data sheet/form.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Neil Ginsberg]

>

1) You can enter where criteria

2) It can be sorted

You can't sort or index on a memo field.

Neil

3) You can have MORE THAN ONE per table!

8)

If you get around to using Access as a FE to an oracle DB, results
displayed from Oracle long data types cn be searched and filtered in a
data sheet/form.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[PC Datasheet]

Memo = up to 64,000 characters

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cs**********@coranto.ucs.mun.ca...

hilz wrote:

Hi all,

What is the equivalent of VARCHAR2 in access?

Text. Unfortunately, it has a character limit of 255 versus 2000 for
Oracle's varchar2.

However, for fields greater than 255 characters you can use a memo data
type. Unsure of max characters, but it's effectively unlimited for my
usages, at least. The nice things about memo type fields as compared to
the equivalentlong data type in Oracle are:

1) You can enter where criteria

2) It can be sorted

3) You can have MORE THAN ONE per table!

8)

If you get around to using Access as a FE to an oracle DB, results
displayed from Oracle long data types cn be searched and filtered in a
data sheet/form.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Neil Ginsberg wrote:

You can't sort or index on a memo field.

Quite right, I was thinking of standard menu filters. It's still miles
better than the Oracle Long data type, though. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Randy Harris]

"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cs**********@coranto.ucs.mun.ca...

hilz wrote:

Hi all,

What is the equivalent of VARCHAR2 in access?

Text. Unfortunately, it has a character limit of 255 versus 2000 for
Oracle's varchar2.

VARCHAR2 can be up to 4000 characters.

[Trevor Best]

PC Datasheet wrote:

Memo = up to 64,000 characters

1,535 short :-)

--
This sig left intentionally blank