fit in code

G

Guest

I am trying to fit the following in code behind a form button:

SELECT tbl_MasterPop_new.[Loan Acct #], tbl_MasterPop_new.PopEnterDt,
tbl_MasterPop_new.Status, "x" AS prevsta, tbl_MasterPop_new.[Buying Center
#], tbl_MasterPop_new.[Division Name], tbl_MasterPop_new.[Region Name],
tbl_MasterPop_new.[Group Name], tbl_MasterPop_new.[Collection Center Name] AS
Expr1, tbl_MasterPop_new.[Distribution Channel], tbl_MasterPop_new.[New
Institution Name], tbl_MasterPop_new.[Origination Channel],
tbl_MasterPop_new.[Sub Channel], tbl_MasterPop_new.[Short Name],
tbl_MasterPop_new.[Loan Status], tbl_MasterPop_new.[Contract Date],
tbl_MasterPop_new.[Mat date], tbl_MasterPop_new.[Next Pmt Due Date],
tbl_MasterPop_new.[Payoff Date], tbl_MasterPop_new.[Paid Off?],
tbl_MasterPop_new.[Nbr of Reg Pmts Made], tbl_MasterPop_new.[# Reg Pmts
Remaining], tbl_MasterPop_new.[Pmt Scd Monthly Pmt Amt],
tbl_MasterPop_new.[Last Pmt Date], tbl_MasterPop_new.[Last Pmt Amt],
tbl_MasterPop_new.[Amended?], tbl_MasterPop_new.[Amended This Mo?],
tbl_MasterPop_new.[DEL Group], tbl_MasterPop_new.[Days Delinquent],
tbl_MasterPop_new.[Tot Past Due Pmts $], tbl_MasterPop_new.[Principal
Balance], tbl_MasterPop_new.[Val Reserve Dollar Amt],
tbl_MasterPop_new.NETBAL, tbl_MasterPop_new.[Ext?],
tbl_MasterPop_new.[Extended This Mo?], tbl_MasterPop_new.[Payoff Dollar Amt],
tbl_MasterPop_new.[Flat Void Mth of Orig], tbl_MasterPop_new.[Active BK Flag
Date], tbl_MasterPop_new.[Active BK Flag], tbl_MasterPop_new.[Inventory
Status], tbl_MasterPop_new.[Charge Off Principal], tbl_MasterPop_new.[Chg Off
Type], tbl_MasterPop_new.[CHG OFF Date], tbl_MasterPop_new.[Cure Letter
Date], tbl_MasterPop_new.[Skip Active Date], tbl_MasterPop_new.[Skip
Completion Date], tbl_MasterPop_new.[Skip Completion Sts],
tbl_MasterPop_new.[Redeemed Date], tbl_MasterPop_new.[Entered Date],
tbl_MasterPop_new.[Repo Ordered Date], tbl_MasterPop_new.[Active Inactive
Flag] INTO Tbl_MasterPop
FROM tbl_MasterPop_new;

how do I break this into several lines, as it does not all fit into one line?

Thanks in advance,
geebee
 
O

OfficeDev18 via AccessMonster.com

Never mind. Cut and paste the entire statement into a new query, name the
query, and set a recordset object to the open query. Put this code into the
button's OnClick event.

Dim RSO as Recordset

Set RSO=CurrentDb.OpenRecordset("your query name here",dbOpenDynaset)

With RSO

e t c .

.Close
End with

Hope this helps,

Sam
I am trying to fit the following in code behind a form button:

SELECT tbl_MasterPop_new.[Loan Acct #], tbl_MasterPop_new.PopEnterDt,
tbl_MasterPop_new.Status, "x" AS prevsta, tbl_MasterPop_new.[Buying Center
#], tbl_MasterPop_new.[Division Name], tbl_MasterPop_new.[Region Name],
tbl_MasterPop_new.[Group Name], tbl_MasterPop_new.[Collection Center Name] AS
Expr1, tbl_MasterPop_new.[Distribution Channel], tbl_MasterPop_new.[New
Institution Name], tbl_MasterPop_new.[Origination Channel],
tbl_MasterPop_new.[Sub Channel], tbl_MasterPop_new.[Short Name],
tbl_MasterPop_new.[Loan Status], tbl_MasterPop_new.[Contract Date],
tbl_MasterPop_new.[Mat date], tbl_MasterPop_new.[Next Pmt Due Date],
tbl_MasterPop_new.[Payoff Date], tbl_MasterPop_new.[Paid Off?],
tbl_MasterPop_new.[Nbr of Reg Pmts Made], tbl_MasterPop_new.[# Reg Pmts
Remaining], tbl_MasterPop_new.[Pmt Scd Monthly Pmt Amt],
tbl_MasterPop_new.[Last Pmt Date], tbl_MasterPop_new.[Last Pmt Amt],
tbl_MasterPop_new.[Amended?], tbl_MasterPop_new.[Amended This Mo?],
tbl_MasterPop_new.[DEL Group], tbl_MasterPop_new.[Days Delinquent],
tbl_MasterPop_new.[Tot Past Due Pmts $], tbl_MasterPop_new.[Principal
Balance], tbl_MasterPop_new.[Val Reserve Dollar Amt],
tbl_MasterPop_new.NETBAL, tbl_MasterPop_new.[Ext?],
tbl_MasterPop_new.[Extended This Mo?], tbl_MasterPop_new.[Payoff Dollar Amt],
tbl_MasterPop_new.[Flat Void Mth of Orig], tbl_MasterPop_new.[Active BK Flag
Date], tbl_MasterPop_new.[Active BK Flag], tbl_MasterPop_new.[Inventory
Status], tbl_MasterPop_new.[Charge Off Principal], tbl_MasterPop_new.[Chg Off
Type], tbl_MasterPop_new.[CHG OFF Date], tbl_MasterPop_new.[Cure Letter
Date], tbl_MasterPop_new.[Skip Active Date], tbl_MasterPop_new.[Skip
Completion Date], tbl_MasterPop_new.[Skip Completion Sts],
tbl_MasterPop_new.[Redeemed Date], tbl_MasterPop_new.[Entered Date],
tbl_MasterPop_new.[Repo Ordered Date], tbl_MasterPop_new.[Active Inactive
Flag] INTO Tbl_MasterPop
FROM tbl_MasterPop_new;

how do I break this into several lines, as it does not all fit into one line?

Thanks in advance,
geebee
Click to expand...
 
G

Guest

OfficeDev18's suggestion is fine; however, to answer your direct question, in
VBA the underscore _ is the line continuation character. It tells the
compile the statement is continued on the next line. I don't know how many
are allowed, but there is a limit to continuations. I have hit it in just
such a large SQL statement. The work around for that is to start a new
statement that uses the variable with & to continue adding to the variable.
Here is an example using your code:

strSQL = "SELECT tbl_MasterPop_new.[Loan Acct #], " _
& "tbl_MasterPop_new.PopEnterDt, tbl_MasterPop_new.Status, "x" " _
& "AS prevsta, tbl_MasterPop_new.[Buying Center#], " _
& "tbl_MasterPop_new.[Division Name], tbl_MasterPop_new.[Region Name], "
strSQL = strSQL & "tbl_MasterPop_new.[Group Name], " _
& "tbl_MasterPop_new.[Collection Center Name] AS Expr1, " _
& "tbl_MasterPop_new.[Distribution Channel], " _
& "tbl_MasterPop_new.[NewInstitution Name], " _
& "tbl_MasterPop_new.[Origination Channel], " _

etc, etc, etc

geebee said:
I am trying to fit the following in code behind a form button:

SELECT tbl_MasterPop_new.[Loan Acct #], tbl_MasterPop_new.PopEnterDt,
tbl_MasterPop_new.Status, "x" AS prevsta, tbl_MasterPop_new.[Buying Center
#], tbl_MasterPop_new.[Division Name], tbl_MasterPop_new.[Region Name],
tbl_MasterPop_new.[Group Name], tbl_MasterPop_new.[Collection Center Name] AS
Expr1, tbl_MasterPop_new.[Distribution Channel], tbl_MasterPop_new.[New
Institution Name], tbl_MasterPop_new.[Origination Channel],
tbl_MasterPop_new.[Sub Channel], tbl_MasterPop_new.[Short Name],
tbl_MasterPop_new.[Loan Status], tbl_MasterPop_new.[Contract Date],
tbl_MasterPop_new.[Mat date], tbl_MasterPop_new.[Next Pmt Due Date],
tbl_MasterPop_new.[Payoff Date], tbl_MasterPop_new.[Paid Off?],
tbl_MasterPop_new.[Nbr of Reg Pmts Made], tbl_MasterPop_new.[# Reg Pmts
Remaining], tbl_MasterPop_new.[Pmt Scd Monthly Pmt Amt],
tbl_MasterPop_new.[Last Pmt Date], tbl_MasterPop_new.[Last Pmt Amt],
tbl_MasterPop_new.[Amended?], tbl_MasterPop_new.[Amended This Mo?],
tbl_MasterPop_new.[DEL Group], tbl_MasterPop_new.[Days Delinquent],
tbl_MasterPop_new.[Tot Past Due Pmts $], tbl_MasterPop_new.[Principal
Balance], tbl_MasterPop_new.[Val Reserve Dollar Amt],
tbl_MasterPop_new.NETBAL, tbl_MasterPop_new.[Ext?],
tbl_MasterPop_new.[Extended This Mo?], tbl_MasterPop_new.[Payoff Dollar Amt],
tbl_MasterPop_new.[Flat Void Mth of Orig], tbl_MasterPop_new.[Active BK Flag
Date], tbl_MasterPop_new.[Active BK Flag], tbl_MasterPop_new.[Inventory
Status], tbl_MasterPop_new.[Charge Off Principal], tbl_MasterPop_new.[Chg Off
Type], tbl_MasterPop_new.[CHG OFF Date], tbl_MasterPop_new.[Cure Letter
Date], tbl_MasterPop_new.[Skip Active Date], tbl_MasterPop_new.[Skip
Completion Date], tbl_MasterPop_new.[Skip Completion Sts],
tbl_MasterPop_new.[Redeemed Date], tbl_MasterPop_new.[Entered Date],
tbl_MasterPop_new.[Repo Ordered Date], tbl_MasterPop_new.[Active Inactive
Flag] INTO Tbl_MasterPop
FROM tbl_MasterPop_new;

how do I break this into several lines, as it does not all fit into one line?

Thanks in advance,
geebee
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

combine 2 access queries 3
unwanted duplicate loan account numbers 1
duplicate records 3
DUPLICATE QUERY results 1
runtime error 3001 invalid argument error message 2
Query Error 2
delete query 1
query speed 5

Top