How to fix this Access query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a SQL database field type varchar, and the column size is 1000.
Now if I query this table in an Access query I can only get the characters
upto 256.
Which means characters after 256 is missing in the Access query table.
Does anyone know how to get all the characters into Access?

cheers

raj
 
I have a SQL database field type varchar, and the column size is 1000.
Now if I query this table in an Access query I can only get the characters
upto 256.
Which means characters after 256 is missing in the Access query table.
Does anyone know how to get all the characters into Access?

cheers

raj
Click to expand...

An Access Text field (limited to 255) is the not-quite-equivalent of a
Varchar. An Access Memo field isn't limited (well, 2GBytes). See if
you can change the SQL datatype to something which Access recognizes
as a Memo field.

John W. Vinson[MVP]
 
Hi John
Thanks for your diea. I had this idea before. When I see the actual database
table, through the Acces database I can see more than 256 characters in that
field. But I do not know how to make my query result that returns a
datatatype of Memo field. Do you know how to do this?
Appreciate any ideas.

cheers
raj
 
I have a SQL database field type varchar, and the column size is 1000.
Now if I query this table in an Access query I can only get the characters
upto 256.
Which means characters after 256 is missing in the Access query table.
Does anyone know how to get all the characters into Access?

cheers

raj
Click to expand...

Raj, I saw that you replied to my reply, but my news server lost the
post. Could you please repost your answer?

John W. Vinson[MVP]
 
Raj, I saw that you replied to my reply, but my news server lost the
post. Could you please repost your answer?
Click to expand...

John,

Here is the last post from Raj:
Hi John
Thanks for your diea. I had this idea before. When I see the actual database
table, through the Acces database I can see more than 256 characters in that
field. But I do not know how to make my query result that returns a
datatatype of Memo field. Do you know how to do this?
Appreciate any ideas.

cheers
raj
 
Thanks Jeff forwarding my reply to John. The query is a join query I assume
when it displays the results, characters more than 255 is truncated. There
should be a way to avoid this. Again back to the last reply. How to make my
query result that returns a datatype of Memo field. So any characters from
the orginal database table, characters more than 255 is not get truncated.
cheers

raj
 
Thanks Jeff forwarding my reply to John. The query is a join query I assume
when it displays the results, characters more than 255 is truncated. There
should be a way to avoid this. Again back to the last reply. How to make my
query result that returns a datatype of Memo field. So any characters from
the orginal database table, characters more than 255 is not get truncated.
cheers
Click to expand...

Please post the SQL of the query. Any query with a Group By or other
aggregate function will truncate memo fields; might this be the
problem?

John W. Vinson[MVP]
 
Thanks John. If I try to remove the GROUP By I get the
message
Ex: You tried to execute a query that does not include the specified
expression 'AccountNumber' as part of an aggregate function

If I remove the Max aggregate function and all the Group By then it is just
fine. Which means I
get more than 256 characters in the Comment field .

Then I tried the query in a stored procedure. It returns more more than 256
characters in the comment field. So I'm thinking to include the query in a
stored proc then call throgh the Access mdb. This will help me to get more
than 256 characters. Just wanna make sure with you , would it work?

Raj
 
Thanks John. If I try to remove the GROUP By I get the
message
Ex: You tried to execute a query that does not include the specified
expression 'AccountNumber' as part of an aggregate function

If I remove the Max aggregate function and all the Group By then it is just
fine. Which means I
get more than 256 characters in the Comment field .

Then I tried the query in a stored procedure. It returns more more than 256
characters in the comment field. So I'm thinking to include the query in a
stored proc then call throgh the Access mdb. This will help me to get more
than 256 characters. Just wanna make sure with you , would it work?

Raj
Click to expand...

Yes, if you get rid of all the grouping it should work fine.

John W. Vinson[MVP]
 
Back
Top