Query - Group by Memo field

[Guest]

Hello:
Can someone tell me if there is a way to Group by a Memo field in a Select
Query. I get an error message when I try to do this and have to change my
comment field of type Memo to type Text instead. Is there a work around for
this?
Thanks,
J.

 

[Guest]

Hi Jade,

Try using the Left function:

GROUP BY Left([MemoFieldName],255)

where MemoFieldName is the name of your memo field. Any chance that you are
using Access 97? I think you should be able to group memo fields in Access
2000 and later, without the need for the Left function and without producing
an error.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Yup, I'm using Access 97. I'll try the Left funtion. What does the 255 mean?
I know that's the max char for a Text field but what does it mean when used
with the left function?
Thanks for your help.
J.

Hi Jade,

Try using the Left function:

GROUP BY Left([MemoFieldName],255)

where MemoFieldName is the name of your memo field. Any chance that you are
using Access 97? I think you should be able to group memo fields in Access
2000 and later, without the need for the Left function and without producing
an error.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hello:
Can someone tell me if there is a way to Group by a Memo field in a Select
Query. I get an error message when I try to do this and have to change my
comment field of type Memo to type Text instead. Is there a work around for
this?
Thanks,
J.

 

[Douglas J Steele]

It means take left-most 255 characters (i.e. the first 255 characters) of
the field. If there are fewer than 255 characters in the field, it'll use
whatever's there. As you noted, 255 is the maximum number of characters for
the Text datatype, so that will allow Access to treat the computed field as
Text.

The downside, of course, is that if you've got two fields that are identical
for, say, the first 300 characters, and it's only after that that they
differ, you're out of luck: they will be grouped together.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yup, I'm using Access 97. I'll try the Left funtion. What does the 255 mean?
I know that's the max char for a Text field but what does it mean when used
with the left function?
Thanks for your help.
J.

Hi Jade,

Try using the Left function:

GROUP BY Left([MemoFieldName],255)

where MemoFieldName is the name of your memo field. Any chance that you are
using Access 97? I think you should be able to group memo fields in Access
2000 and later, without the need for the Left function and without producing
an error.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hello:
Can someone tell me if there is a way to Group by a Memo field in a Select
Query. I get an error message when I try to do this and have to change my
comment field of type Memo to type Text instead. Is there a work around for
this?
Thanks,
J.

 

[Guest]

Thanks Douglas. If I understand you, this function will only allow me to
group by max 255 characters. If 2 fields are the same up to 255 characters it
will treat them as the same field. Well if the max is only 255, then I should
just leave it as a text field.
J.

It means take left-most 255 characters (i.e. the first 255 characters) of
the field. If there are fewer than 255 characters in the field, it'll use
whatever's there. As you noted, 255 is the maximum number of characters for
the Text datatype, so that will allow Access to treat the computed field as
Text.

The downside, of course, is that if you've got two fields that are identical
for, say, the first 300 characters, and it's only after that that they
differ, you're out of luck: they will be grouped together.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yup, I'm using Access 97. I'll try the Left funtion. What does the 255 mean?
I know that's the max char for a Text field but what does it mean when used
with the left function?
Thanks for your help.
J.

Hi Jade,

Try using the Left function:

GROUP BY Left([MemoFieldName],255)

where MemoFieldName is the name of your memo field. Any chance that you are
using Access 97? I think you should be able to group memo fields in Access
2000 and later, without the need for the Left function and without producing
an error.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hello:
Can someone tell me if there is a way to Group by a Memo field in a Select
Query. I get an error message when I try to do this and have to change my
comment field of type Memo to type Text instead. Is there a work around for
this?
Thanks,
J.

 

[Douglas J Steele]

I'm having a hard time imagining a situation where I'd need to group on a
memo field: that the first 255 characters aren't going to be sufficient to
distinguish between the fields.

Can you give some background as to what you're trying to do, perhaps with
some sample data?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas. If I understand you, this function will only allow me to
group by max 255 characters. If 2 fields are the same up to 255 characters it
will treat them as the same field. Well if the max is only 255, then I should
just leave it as a text field.
J.

It means take left-most 255 characters (i.e. the first 255 characters) of
the field. If there are fewer than 255 characters in the field, it'll use
whatever's there. As you noted, 255 is the maximum number of characters for
the Text datatype, so that will allow Access to treat the computed field as
Text.

The downside, of course, is that if you've got two fields that are identical
for, say, the first 300 characters, and it's only after that that they
differ, you're out of luck: they will be grouped together.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yup, I'm using Access 97. I'll try the Left funtion. What does the 255 mean?
I know that's the max char for a Text field but what does it mean when used
with the left function?
Thanks for your help.
J.

:

Hi Jade,

Try using the Left function:

GROUP BY Left([MemoFieldName],255)

where MemoFieldName is the name of your memo field. Any chance that

you
are

using Access 97? I think you should be able to group memo fields in Access
2000 and later, without the need for the Left function and without producing
an error.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hello:
Can someone tell me if there is a way to Group by a Memo field in

a
Select

Query. I get an error message when I try to do this and have to

change
my

comment field of type Memo to type Text instead. Is there a work around for
this?
Thanks,
J.

 

[Guest]

I have a history table that logs company activities. One of the fields was a
memo field and is now a text field because of the problem I ran into. I
create a crosstab query using this table and use the query to create a
report. I use the History table and the company table in the query to get the
activities for each company. Since I am using a crosstab query I have to
group each field and that's when I have a problem grouping the memo field.

Report looks something like this:

company name comp id activity activity date comment

Comp1 1234 closed 01/01/04 not making enough
money


Thanks,
J

I'm having a hard time imagining a situation where I'd need to group on a
memo field: that the first 255 characters aren't going to be sufficient to
distinguish between the fields.

Can you give some background as to what you're trying to do, perhaps with
some sample data?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas. If I understand you, this function will only allow me to
group by max 255 characters. If 2 fields are the same up to 255 characters it
will treat them as the same field. Well if the max is only 255, then I should
just leave it as a text field.
J.

It means take left-most 255 characters (i.e. the first 255 characters) of
the field. If there are fewer than 255 characters in the field, it'll use
whatever's there. As you noted, 255 is the maximum number of characters for
the Text datatype, so that will allow Access to treat the computed field as
Text.

The downside, of course, is that if you've got two fields that are identical
for, say, the first 300 characters, and it's only after that that they
differ, you're out of luck: they will be grouped together.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yup, I'm using Access 97. I'll try the Left funtion. What does the 255
mean?
I know that's the max char for a Text field but what does it mean when
used
with the left function?
Thanks for your help.
J.

:

Hi Jade,

Try using the Left function:

GROUP BY Left([MemoFieldName],255)

where MemoFieldName is the name of your memo field. Any chance that you
are
using Access 97? I think you should be able to group memo fields in
Access
2000 and later, without the need for the Left function and without
producing
an error.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hello:
Can someone tell me if there is a way to Group by a Memo field in a
Select
Query. I get an error message when I try to do this and have to change
my
comment field of type Memo to type Text instead. Is there a work
around for
this?
Thanks,
J.

 

[Guest]

I have a history table that logs company activities. One of the fields was a
memo field and is now a text field because of the problem I ran into. I
create a crosstab query using this table and use the query to create a
report. I use the History table and the company table in the query to get the
activities for each company. Since I am using a crosstab query I have to
group each field and that's when I have a problem grouping the memo field.

Report looks something like this: I will have to double check tomorrow

company name comp id Opened Closed comment

Comp1 234 01/01/04 01/01/05 not making enough money


Thanks,
J

I'm having a hard time imagining a situation where I'd need to group on a
memo field: that the first 255 characters aren't going to be sufficient to
distinguish between the fields.

Can you give some background as to what you're trying to do, perhaps with
some sample data?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas. If I understand you, this function will only allow me to
group by max 255 characters. If 2 fields are the same up to 255 characters it
will treat them as the same field. Well if the max is only 255, then I should
just leave it as a text field.
J.
:

It means take left-most 255 characters (i.e. the first 255 characters) of
the field. If there are fewer than 255 characters in the field, it'll use
whatever's there. As you noted, 255 is the maximum number of characters for
the Text datatype, so that will allow Access to treat the computed field as
Text.

The downside, of course, is that if you've got two fields that are identical
for, say, the first 300 characters, and it's only after that that they
differ, you're out of luck: they will be grouped together.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yup, I'm using Access 97. I'll try the Left funtion. What does the 255
mean?
I know that's the max char for a Text field but what does it mean when
used
with the left function?
Thanks for your help.
J.

:

Hi Jade,

Try using the Left function:

GROUP BY Left([MemoFieldName],255)

where MemoFieldName is the name of your memo field. Any chance that you
are
using Access 97? I think you should be able to group memo fields in
Access
2000 and later, without the need for the Left function and without
producing
an error.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hello:
Can someone tell me if there is a way to Group by a Memo field in a
Select
Query. I get an error message when I try to do this and have to change
my
comment field of type Memo to type Text instead. Is there a work
around for
this?
Thanks,
J.