Gary Kjos
garykjos at gmail.com
Wed Dec 20 12:51:08 CST 2006
There is a line length limit in the VB editor. you have to use a quote
and a continuation terminator at the end of each line for long SQL
when you build it. I would usually DIM a string field like strSQL (DIM
strSQL as String) and then populate that wih something like this
strSQL = "INSERT INTO tblExtItemCost ( INVENTORY_ITEM_ID,
ORGANIZATION_ID, ITEM_COST ) " + _
"SELECT BOM_CST_ITEM_COSTS.INVENTORY_ITEM_ID,
BOM_CST_ITEM_COSTS.ORGANIZATION_ID, BOM_CST_ITEM_COSTS.ITEM_COST " + _
"FROM BOM_CST_ITEM_COSTS INNER JOIN INV_MTL_SYSTEM_ITEMS_B ON
(BOM_CST_ITEM_COSTS.ORGANIZATION_ID =
INV_MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID) AND
(BOM_CST_ITEM_COSTS.INVENTORY_ITEM_ID =
INV_MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID);"
Of course email is gonna wrap thos lines eve SHORTER but you can see
that the " + _ is the end of each line
Then the strSQL gets loaded into your record source with the
me.recordsource = strSQL or whatever.
GK
On 12/20/06, Keith Williamson <Kwilliamson at rtkl.com> wrote:
> Okay...this is getting more bizarre. I keep pasting the sql statement
> into the VB window....and it keeps automatically putting the statement
> on (4) lines....with and end quote at the back of the first line (that
> line only). I can't get rid of the quote....nor will it let me try to
> put the entire statement on one line.
>
>
> grrrrrrrrrrr
>
> Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com
>
> RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
> 21231-3305
>
> 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos
> Sent: Wednesday, December 20, 2006 12:01 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Unbound Subform Revisited
>
> Perhaps you need quotes around the sql string?
>
> GK
>
> On 12/20/06, Keith Williamson <Kwilliamson at rtkl.com> wrote:
> > Yeah. That is what is funny. That is how I got the Sql statement in
> > the first place. I had a bound form that worked, and simply copied
> the
> > recordsource. I used the same recordsource statement in my event for
> > the click button.
> >
> > And when I copy that same sql statement into a new query....it works
> > fine.
> >
> > ??
> >
> > Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com
> >
> > RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
> > 21231-3305
> >
> > 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Beach
> Access
> > Software
> > Sent: Wednesday, December 20, 2006 11:33 AM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Unbound Subform Revisited
> >
> > Keith:
> >
> > Sometimes when I can't spot a problem in a lengthy SQL statement like
> > this I
> > paste it into the SQL view of a new query and then switch to the
> design
> > view. Sometimes you can spot the problem that way.
> >
> > I would also paste the original record source SQL and the modified SQL
> > into
> > WordPad and eyeball them for differences. The line breaks should
> occur
> > in
> > roughly the same places so you can start by checking the length of
> each
> > and
> > the end of each line to find the differences.
> >
> > HTH
> >
> > Rocky Smolin
> > Beach Access Software
> > 858-259-4334
> > www.e-z-mrp.com
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith
> > Williamson
> > Sent: Wednesday, December 20, 2006 8:14 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Unbound Subform Revisited
> >
> > Here is the sql statement:
> >
> > me.form.RecordSource = (SELECT
> > [qryWeeklyTimesheetVarianceSummary].[Year],
> > [qryWeeklyTimesheetVarianceSummary].[Month],
> > [qryWeeklyTimesheetVarianceSummary].[Entity],
> > Sum([qryWeeklyTimesheetVarianceSummary].[SumOfRegPay]) AS [Reg Stand
> > Cost], Sum([qryWeeklyTimesheetVarianceSummary].[SumOfOvtPay]) AS [OT
> > Standard Cost],
> Sum([qryWeeklyTimesheetVarianceSummary].[SumOfOvt2Pay])
> > AS [OT2 Standard Cost],
> Sum([sumofregpay]+[sumofovtpay]+[sumofovt2pay])
> > AS [Total Standard Cost],
> > Sum([qryWeeklyTimesheetVarianceSummary].[ActReg]) AS [Calc Reg Cost],
> > Sum([qryWeeklyTimesheetVarianceSummary].[ActOvt]) AS [Calc OT Cost],
> > Sum([qryWeeklyTimesheetVarianceSummary].[ActOvt2]) AS [Calc OT2 Cost],
> > Sum([actreg]+[actovt]+[actovt2]) AS [Total Calc Cost],
> > First([qryPayrollPaid].[SumOfAmountPaid]) AS FirstOfSumOfAmountPaid,
> > First([qryPayrollDiffSummary].[SumOfHrs]) AS FirstOfSumOfHrs,
> > First([qryPayrollDiffSummary].[SumOfWageDiff]) AS FirstOfSumOfWageDiff
> > FROM qryPayrollDiffSummary RIGHT JOIN (qryPayrollPaid RIGHT JOIN
> > qryWeeklyTimesheetVarianceSummary ON
> > ([qryPayrollPaid].[Year]=[qryWeeklyTimesheetVarianceSummary].[Year])
> AND
> > ([qryPayrollPaid].[Month]=[qryWeeklyTimesheetVarianceSummary].[Month])
> > AND
> >
> ([qryPayrollPaid].[Entity]=[qryWeeklyTimesheetVarianceSummary].[Entity])
> > ) ON
> >
> ([qryPayrollDiffSummary].[Entity]=[qryWeeklyTimesheetVarianceSummary].[E
> > ntity]) AND
> >
> ([qryPayrollDiffSummary].[Month]=[qryWeeklyTimesheetVarianceSummary].[Mo
> > nth]) AND
> >
> ([qryPayrollDiffSummary].[Year]=[qryWeeklyTimesheetVarianceSummary].[Yea
> > r])
> > WHERE [qryWeeklyTimesheetVarianceSummary].[Entity] = "00" And
> > [qryWeeklyTimesheetVarianceSummary].[Month] = 10 And
> > [qryWeeklyTimesheetVarianceSummary].[Year] = 2006
> > GROUP BY [qryWeeklyTimesheetVarianceSummary].[Year],
> > [qryWeeklyTimesheetVarianceSummary].[Month],
> > [qryWeeklyTimesheetVarianceSummary].[Entity];)
> >
> > Me.Form.Requery
> >
> >
> > Anyone? Anyone?
> >
> > :)
> >
> > Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com
> >
> > RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
> > 21231-3305
> >
> > 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Beach
> Access
> > Software
> > Sent: Wednesday, December 20, 2006 10:37 AM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Unbound Subform Revisited
> >
> > Keith:
> >
> > Put a debug statement to print the SQL string right before the
> > me.recordsource = and post it. Someone will see that there's probably
> a
> > syntax/punctuation error.
> >
> > Rocky Smolin
> > Beach Access Software
> > 858-259-4334
> > www.e-z-mrp.com
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith
> > Williamson
> > Sent: Wednesday, December 20, 2006 7:05 AM
> > To: Access Developers discussion and problem solving
> > Subject: [AccessD] Unbound Subform Revisited
> >
> > Hey Guys,
> >
> >
> >
> > If I want to change a bound form to an unbound form, am I going about
> it
> > correctly if I do the following:
> >
> >
> >
> > 1) Add (3) unbound text fields for variables in form header;
> >
> > 2) Delete Record Source for form;
> >
> > 3) Add button to form header;
> >
> > 4) Add Event to button, to set form.recordsource = (original sql
> > statement for record source, deleted in step 2, but also add where
> > statements for the (3) variables in the unbound text fields)
> >
> >
> >
> > Shouldn't this run? I keep getting a syntax error on my sql
> statement,
> > when I click the button. Even if I change the sql statement to
> exactly
> > equal the original recordsource statement....I get the syntax error.
> >
> >
> >
> > ??
> >
> >
> >
> > Thanks,
> >
> >
> >
> > Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com
> >
> > RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
> > 21231-3305
> >
> > 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com
> > <http://www.rtkl.com/>
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > No virus found in this incoming message.
> > Checked by AVG Free Edition.
> > Version: 7.5.432 / Virus Database: 268.15.25/593 - Release Date:
> > 12/19/2006
> > 1:17 PM
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > No virus found in this incoming message.
> > Checked by AVG Free Edition.
> > Version: 7.5.432 / Virus Database: 268.15.25/593 - Release Date:
> > 12/19/2006
> > 1:17 PM
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> Gary Kjos
> garykjos at gmail.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
Gary Kjos
garykjos at gmail.com