Access 2002 macro problem

[Helen]

A query is pulling info from an external d/base where one field arrives as a
MEMO type field. I then need to use this query’s data in a crosstab query.
The MEMO field info cannot be used. If I save the query data to a table and
change the field property to TEXT, it works fine. I’m trying to set up a
macro to run the initial query, change the field property and then run the
crosstab query. I cannot make the macro change the field property. Can
anyone let me know what I need to enter in the macro to do this?
Thanks

 

[Jerry Whittle]

You can use the query as the source for the crosstab without saving it to a
table first. The trick is to convert the memo data to text in the query.
Something like below, with the proper field name, will take the first 255
characters of the memo data and that data will act like a text field. The
trim is to get rid of any leading or trailing spaces.

The crosstab will see BRCH_NAME_TEXT as the new field name so you might need
to change it.

BRCH_NAME_TEXT: Trim(Left([BRCH_NAME],255))

 

[Arvin Meyer [MVP]]

You cannot do this with a macro. Instead you need to use VBA code:

It is probably easier to simply build the table the way you want and append
the records to that table. You might be able to build a macro to do that. If
that's what you decide, please ask that specifically in the macro
newsgroup/forum. I exclusively use VBA code, and haven't written a macro in
almost 14 years.

 

[Helen]

Thank you, that worked brilliantly.
--
Helen

You can use the query as the source for the crosstab without saving it to a
table first. The trick is to convert the memo data to text in the query.
Something like below, with the proper field name, will take the first 255
characters of the memo data and that data will act like a text field. The
trim is to get rid of any leading or trailing spaces.

The crosstab will see BRCH_NAME_TEXT as the new field name so you might need
to change it.

BRCH_NAME_TEXT: Trim(Left([BRCH_NAME],255))
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

A query is pulling info from an external d/base where one field arrives as a
MEMO type field. I then need to use this query’s data in a crosstab query.
The MEMO field info cannot be used. If I save the query data to a table and
change the field property to TEXT, it works fine. I’m trying to set up a
macro to run the initial query, change the field property and then run the
crosstab query. I cannot make the macro change the field property. Can
anyone let me know what I need to enter in the macro to do this?
Thanks