Datatype on Queries

[Chris]

does the datatype ( IncID-autonumver, Scope-mem0) that is set matter or
interfere when setting up a query.

when i run a query which is about 15 fields, the query doesn t not work,
when i take out certain fields and run one or two fields at a time the query
works. my feeling is three fields which are set to memos are interferring
with the results.

 

[Dale Fye]

Chris,

It depends on the query. Normally, with a simple SELECT query, memo fields
will not cause a problem. But if try to include them in queries with
DISTINCT or GROUP BY, they will cause the query to run slower, and will only
return the first 255 characters from the memo field.

If you post the structure of your tables and the SQL of your query, someone
here might be able to help you identify the problem with your query.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Allen Browne]

Access sets aside a memory buffer to hold records. In Access 2000 and later,
this is 4KB in size. Some bytes are used for overhead, so record can contain
a maximum of 4000 characters (or up to 2000 for some languages the depend on
Unicode.) Anything more will generate errors.

Memos are not stored in-line, so a memo field uses only an address space (10
bytes) regardless of how many characters are in the field. Similarly, the
Hyperlink field (which is treated as a memo) and OLE Object, and Attachment
fields (Access 2007) contribute only the address space.

With basic English text, 15 Text fields with 255 characters in each should
not exceed the limit, but the 16th one could.

This limit is one we probe with this database utility that checks the
structure of your database:
Database Issue Checker
at:
http://allenbrowne.com/AppIssueChecker.html

 

[Dale Fye]

Neat little tool, Allen.

Think I'll put this one in my toolbox.

--
Dale

email address is invalid
Please reply to newsgroup only.

 

[Allen Browne]

Neat little tool, Allen.

You're welcome, Dale.