Query not returning whole field?

[hedidit]

Hi I've got a query designed using the access query wixard that is returning rows a treat one field is often not being fully returned. For example quite often fields over 8 lines in length are being cut short, this can happen half way through a fields data etc. The field I'm having trouble with is a memo field...

Can anyone suggest what might be causing this? The field doesn't hold any symbols or carriage returns etc.

Cheers

 

[Golom]

There are special rules about memo fields that can cause this. Unlike most other data types, memos are not stored "in" the table that contains them. The table has only a pointer to the memo that is stored elsewhere. If you attempt to do thinks like

WHERE MemoField LIKE 'ABC*'

SQL will retrieve only the first 255 characters of the memo for use in the comparison and that's all that will be reported in the field.

 

[hedidit]

Hi Golom,

Thanks for the reply, never knew that about memo fields but the field which is being cut short isn't part of the where clause. Other fields are in the where clause but not the one being cut short.

I don't suppose it could be my dbase going a bit corrupt could it?

 

[Scoob55k]

How can you query an entire memo field from a table? I'm trying to do so for a report. Would it be better to do so using a DLOOKUP in the report?

Looks as if it could be tricky or at least very specific on the can's and cannot's. Thanks!

 

[dhookom]

Displaying an entire memo field in a combo box on a report normally requires nothing special other than to set the Can Grow property to Yes.

What is your experience? What have you tried?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Scoob55k]

I have done that. Through that I figure out that the problem wasn't the report, but the query. It isn't allowing all the data to come trough. It is truncating it at 255 characters as a text field does.

I have one last thing to try that I thought of in the middle of the night last night. Yes, I was supposed to be sleeping.

Thanks!

 

[Scoob55k]

Not sure I exactly know what I figured out, but I just recreated the query using just the table the memo field is in and omitting the other tables that gave me all the data I wanted.

Now my next issue is getting this DLOOKUP function to work for me so I can get that data the previous query I used for this report got me.

It's returning a Null value event though my criteria is set correctly. I've more than quadruple checked it. My criteria is the primary key and it is linked to another table in the db relationships dealio.

Errrrr! Thanks!

 

[dhookom]

If you have truncation in a query, it is usually caused by phrases like "DISTINCT" or "GROUP BY" etc. Since you never provided the SQL view of any record source, we can't help you further.

You also haven't provided any information regarding your DLookup() expressions, table structures, values,...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[missinglinq]

I've read in the past that doing any kind of data manipulation with a memo field in a query causes Access to truncate the field to 255 characters before performing the job, and that some formatting of a memo field in a report will do the same thing!

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[Scoob55k]

Sorry. I'll get better.

I almost always use the wizard or copy another query. Using the wizard to make a new query with only the one table must have gotten rid of the distinct. I did have a group by set to Count. That may have been it although it was on another field and not the one truncated.

My DLookup was =DLookUp("[QText]","tbl Questions","[QCode] Like 'Q35'") where the [Qcode] in the table was the primary key as well as had a relationship conection with another table and had an Input Mask on it.

My new one that works is =DLookUp("[QText]","qry Text","[QCode] Like '*35'") I think I could have used the "tbl Questions" after I use the '*35', but figured I already had it and this being what I call a "throw away" DB, I figured it wasn't worth the time.

Thanks for all your assistance. This site has really helped me move forward.

 

[dhookom]

You might be able to get the full text of a memo field in a totals query if you can use "First" as the aggregate.

From your most recent reply, I assume your report is working.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Scoob55k]

Yes it is. Thanks for all your help!