Keith Williamson
Kwilliamson at RTKL.com
Wed Dec 20 12:35:17 CST 2006
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