Constructing Strings in Visual Basic

[clarencelai]

Hi all,

I need to run a SQL statement in Microsoft VB.

The following statement returns an error "Compile Error: Expected: end of statement". yyyy was highlighted when the error was encountered.

I believe that there are something wrong with my syntax. It could be the quotes (") that I have used to enclose the date format.

I would appreciate if anyone could help to correct.

************************************************

strSQL = "SELECT DISTINCT Min(Format([SHOW_DATE],"yyyy/mm/dd")) AS [Start Date], Max(Format([SHOW_DATE],"yyyy/mm/dd")) AS [End Date] INTO NewSurveys" _
& "From STIXMV_EVENT_SHOW HAVING (((Max(Format([SHOW_DATE],"yyyy/mm/dd")))=Format(Date()-15,"yyyy/mm/dd")));"
************************************************

Regards,
Clarence

[MikeTheBike]

Hi all,

I need to run a SQL statement in Microsoft VB.

The following statement returns an error "Compile Error: Expected: end of statement". yyyy was highlighted when the error was encountered.

I believe that there are something wrong with my syntax. It could be the quotes (") that I have used to enclose the date format.

I would appreciate if anyone could help to correct.

************************************************

strSQL = "SELECT DISTINCT Min(Format([SHOW_DATE],"yyyy/mm/dd")) AS [Start Date], Max(Format([SHOW_DATE],"yyyy/mm/dd")) AS [End Date] INTO NewSurveys" _
& "From STIXMV_EVENT_SHOW HAVING (((Max(Format([SHOW_DATE],"yyyy/mm/dd")))=Format(Date()-15,"yyyy/mm/dd")));"
************************************************

Regards,
Clarence

Hi

You need to duplicate all the quotation marks that are part of the string (those that do not start or end the string) like this

strSQL = "SELECT DISTINCT Min(Format([SHOW_DATE],""yyyy/mm/dd"")) AS [Start Date], Max(Format([SHOW_DATE],""yyyy/mm/dd"")) AS [End Date] INTO NewSurveys " _
& "From STIXMV_EVENT_SHOW HAVING (((Max(Format([SHOW_DATE],""yyyy/mm/dd"")))=Format(Date()-15,""yyyy/mm/dd"")));"

By doing this the complier/interpreter/parser know when you want a quotation mark in the string and where the string starts and stops!


HTH


MTB

[clarencelai]

Hi..

Thanks for your help!

Regards,
Clarence

Hi

You need to duplicate all the quotation marks that are part of the string (those that do not start or end the string) like this

strSQL = "SELECT DISTINCT Min(Format([SHOW_DATE],""yyyy/mm/dd"")) AS [Start Date], Max(Format([SHOW_DATE],""yyyy/mm/dd"")) AS [End Date] INTO NewSurveys " _
& "From STIXMV_EVENT_SHOW HAVING (((Max(Format([SHOW_DATE],""yyyy/mm/dd"")))=Format(Date()-15,""yyyy/mm/dd"")));"

By doing this the complier/interpreter/parser know when you want a quotation mark in the string and where the string starts and stops!


HTH


MTB