string longer than 250 characters

[QueenKirsty]

I have a string that represents an SQL statement that I want to run using DoCmd.RunSQL in VBA.

I have put the string into a variable SQL3 and then called
DoCmd.RunSQL SQL3

The problem is SQL3 is only picking up the first 250 characters of the string. I tried splitting up the string into SQL3a and SQL3b (each with less than 250 characters) and they look fine but when I concatenate the two strings I still only get the first 250 characters.

I do not get any error messages on the string concatenation or the original string but obviously the string only contains half an SQL command and so the DoCmd.RunSQL will not work.

Any ideas?

 

[gemma-the-husky]

how have you defined SQL3

 

[QueenKirsty]

dim SQL3 as string
dim SQL3a as string
dim SQL3b as string

SQL3a = "...first bit here..."
SQL3b = "...next bit here..."
SQL3 = SQL3a & SQL3b

 

[gemma-the-husky]

why do you thnik you are only seeing 250 chars in sql3 ?

are you checking it in a msgbox - if so a msgbox only shows a limited number of chars

try msgbox(len(sql3)) to see the length of sql3

 

[QueenKirsty]

I am looking at it in the Watch window of the VBA code. It has nothing to do with a msgbox. I am still at the "doing it in VBA" stage before I even try to look at the "do something visual" stage ! )

 

[DCrake]

The Watch window also has limitations on it. View it in the immediate window. Do a Debug.Print on your Sql3 to clarify.

David

 

[QueenKirsty]

Thanks DCrake! That is it. I din't realise that there was a limit on the Watch window. Will use debug.print from now on.

 

[gemma-the-husky]

so if the runsql is not working, then you have an error in it?


try this mod - this will tell you what is wrong with the sql statement


on error goto fail
currentdb.execute sqlstrg, dbfailonerror

fail:
msgbox(err.description)

 

[QueenKirsty]

Thanks. That looks really useful and I will keep it in mind for the future but rather embarrasingly the reason the docmd.runsql didn't work was because I had misspelled something!

All assistance has been gratefully appreciated!