将长的SQL VBA语句分成多行 [英] break a long sql vba statement into multiple lines

问题描述

我对VBA气氛完全陌生.我试图将这条线分成多行，但失败了.有人可以帮我将这段代码分成多行吗?

I am totally new to a VBA atmosphere. I tried to break this line into mulitple lines but I failed. Can someone help me to break this code into multiple lines?

DoCmd.RunSQL "UPDATE INDIVIDUAL SET INDIVIDUAL.INDI_FIRSTNAME = '" & prospect_contact!FirstName & "', INDIVIDUAL.INDI_LASTNAME = '" & prospect_contact!LastName & "', INDIVIDUAL.INDI_TEL = '" & prospect_contact!BusinessTelephone & "', INDIVIDUAL.INDI_ADDRESS1 = '" & Replace(prospect_contact!Street, "'", "") & "', INDIVIDUAL.INDI_ADDRESS2 = '" & Replace(prospect_contact!Street1, "'", "") & "', INDI_STATUS = '" & pro & "',INDIVIDUAL.INDI_FUNEL1 = '" & prospect_contact!QualificationStatus & "', INDIVIDUAL.INDI_COUNTRY = '" & prospect_contact!Country_Employer & "', INDIVIDUAL.ACCT_NAME = '" & Replace(prospect_contact!Employer, "'", "") & "' WHERE INDIVIDUAL.INDI_FULLNAME = '" & key & "';"

更新:我用& _尝试了此操作，但是出现语法错误，并且代码在VBA中变成红色. 我在逗号或引号上弄错了吗?我不知道.

UPDATE: I tried this with the &_ but I get a syntax error and the code becomes red in VBA. Am I making a mistake with the commas or the quotes. I have no idea.

                        DoCmd.RunSQL "UPDATE INDIVIDUAL SET INDIVIDUAL.INDI_FIRSTNAME = '" & prospect_contact!FirstName & "', & _
                    INDIVIDUAL.INDI_LASTNAME = '" & prospect_contact!LastName & "',  & _
                    INDIVIDUAL.INDI_TEL = '" & prospect_contact!BusinessTelephone & "', & _
                    INDIVIDUAL.INDI_ADDRESS1 = '" & Replace(prospect_contact!Street, "'", "") & "', & _
                    INDIVIDUAL.INDI_FUNEL1 = '" & prospect_contact!QualificationStatus & "', & _
                    INDI_STATUS = '" & pro & "', & _
                    INDIVIDUAL.INDI_COUNTRY = '" & prospect_contact!Country_Employer & "', & _
                    INDIVIDUAL.ACCT_NAME = '" & Replace(prospect_contact!Employer, "'", "") & "' & _
                    WHERE INDIVIDUAL.INDI_FULLNAME = '" & key & "';"

更新2:

成功！有用！有用！感谢@Bathsheba，@TheLaurens:)

IT WORKS! IT WORKS! IT WORKS! thanks to @Bathsheba, @TheLaurens :)

推荐答案

在VBA中，空格后加下划线和没什么，否则将给您换行.

In VBA a space followed by underscore and nothing else after that gives you a line break.

例如

DoCmd.RunSQL "UPDATE INDIVIDUAL SET INDIVIDUAL.INDI_FIRSTNAME = '" & _
prospect_contact!FirstName & "', INDIVIDUAL.INDI_LASTNAME = '" & _ 
etc

但是请不要在字符串文字中换行:这是一种语法错误.

But don't break lines within a string literal: that is a syntax error.

您可以拥有的换行符数量的限制非常小.

There is a surprisingly small limit to the number of line breaks you can have.

这篇关于将长的SQL VBA语句分成多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！