i have a temporary table which is created by importing an excel file

some extra fields are then added to the table using sql vb code for example

strSQL = "ALTER TABLE [TBL_TmpSubmission] ADD COLUMN [EnergyUnit] TEXT;"
CurrentProject.Connection.Execute strSQL

these fields are later populated conditionally using an update exectuted again from vb code

If Me.cboProgram.Column(0) < 3 Then
strSQL = "UPDATE TBL_TmpSubmission SET EnergyUnit = 'GWh'"
Else
strSQL = "UPDATE TBL_TmpSubmission SET EnergyUnit = 'CarbonTonne'"
End If
CurrentProject.Connection.Execute strSQL

the problem is that this field now has a memo data type, this is a problem because i need to write a query which uses this field in a table join and access won't let you join on the memo type

anyone know why this might happen? for the time being i'm going to try and fudge this by using a further ddl statement to change the data type back to text but i'd rather know whats going on here

ok i just read this thread

http://www.access-programmers.co.uk/forums/showthread.php?t=25100

saying you actually can't change field types though i was sure i'd done this myself before, perhaps not

this makes it much more important that i sort this out because at some point in the future i will need to filter data using that field

A Text field is limited to 255 chars while a memo field can be much larger. So if you have a lot of data in a particular cell in your excel file it will force a Memo field.

its not that because the data isn't imported from the excel file

this is a field which is added after the excel file has been imported the field is populated with one of the two text strings in the if statement posted above and both of these are less than 255 characters

I have checked in Access help under SQL Data types and it looks like SQL type TEXT is the same as Access MemO. Try using CHARACTER instead. This looks like what you want.

right that sounds like it makes sense

i've had problems before with my data types appearing as memo and that'll probably be why i'll try it and let you know

yes it looks like your right TEXT used in pure sql results in memo type, the sql for access's text type is CHARACTER

......thanks :)

Happy to help