And the code hasn't come through again! I must be a jinx. here it is again without the bold:

IF (SELECT COUNT(*) FROM xllrequisitions WHERE [Date Processed] IS NOT NULL AND...

For some reason, the IF statement line in my code didn't come through properly. It should be:

IF (SELECT COUNT(*) FROM xllrequisitions WHERE [Date Processed] IS NOT NULL AND CONVERT(CHAR(10),[Date Processed],...

Sorry! Scrap my last post!

Replace(varText,"'", "''") works!!!!!

I cocked up the use of quote marks and apostrophes!

The full syntax needs to be (spaces added for clarification):

Replace(vartext, " ' ", " '...

Hi Ken,

Thank you for your reply......I need all the help I can get at the moment because ActiveX, ADO and vbscript are all new to me.........can you tell????? I'm learning...

For anyone interested, I worked around this by using an ActiveX Script Task in DTS that referred to the global variable. E.g:

rsXMC.Open "SELECT soh_ordref, soext_code2, soh_orddate, soh_account, ndm_name, ndm_addr1,...

As a follow-up to this, I'm starting to realise that it's the Informix ODBC driver that can't handle the "?" in the SQL statement. Are global variables only allowed with...

Well, I've kind of found my own solution to this problem........

If I remove the "ORDER BY [DATE], [TIME]" section of the SQL statement that is embedded in the xp_sendmail script,...

Many thanks John and Prasad for your help. It is very much appreciated.

I have gone away and read about dynamic queries and re-written the code to use that and it...

Many thanks Karl and rsharma for your blindingly quick replies!

All those solutions are superb and I thank you for coming up with them and teaching me something about T-SQL!

Regards,

Mark

A very simple solution that works perfectly! In my opinion, that makes it brilliant!

Many thanks for your help Steve.

Many thanks Paul!

I've incorporated your ideas into a T-SQL script using a cursor etc and it works really well. I may have to revise it later to cope with extraneous...