Re: running a query in a loop

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

"Steve Schapel" <schapel@xxxxxxxxxxx> wrote in message
news:O7bcc79EGHA.376@xxxxxxxxxxxxxxxxxxxxxxx
> Bob,
>
> As regards your code...
>
>> strSQL = "INSERT INTO tblMissingCommissionReport(LoanNumber, Date) " & _
>> " SELECT tblDate.MonthYear" & _
>> " FROM tblDate" & _
>> " WHERE MonthYear < Now()" & _
>> " AND Format ([MonthYear],"mmmm,yyyy")" & _
>> " Not In ( SELECT Format ([PaymentDate],"mmmm,yyyy")" &_
>> " FROM tblCommission" & _
>> " WHERE LoanNo =" & rst!LoanNo & ")"
>
> ... I can only see one place where it will definitely choke, which is no
> space in one of the &_ bits.
>
> The word 'date' is a Reserved Word (i.e. has a special meaning) in Access,
> and should not be used as the name of a field or control. If you do, it
> should always be enclosed in []s in code. However, if this causes an
> error, I doubt that Access would call it a syntax error.
>
> Also I think there would normally be spaces in the INSERT INTO clause.
>
> The keyword VALUES is not applicable in this case.
>
> You can put a ; at the end if you like, but it is not necessary.
>
> So, try it with these changes...
> strSQL = "INSERT INTO tblMissingCommissionReport ( LoanNumber, [Date] ) "
> & _
> " SELECT tblDate.MonthYear" & _
> " FROM tblDate" & _
> " WHERE MonthYear < Now()" & _
> " AND Format ([MonthYear],"mmmm,yyyy")" & _
> " Not In ( SELECT Format ([PaymentDate],"mmmm,yyyy")" & _
> " FROM tblCommission" & _
> " WHERE LoanNo =" & rst!LoanNo & ")"

The INSERT INTO statement is trying to insert into 2 fields, but only one
value is being provided.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)



.

Relevant Pages

  • Re: Syntax error with Access database
    ... Since you are using generated commands - it's probalby not a problem w/ not ... Reserved word Check ... >to the dataset text boxes with no problems but I get a syntax error ... >attempting to update or insert into a Access database. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Reserved Word "Position"
    ... Freeware Tools, Games and Humour ... > this using this column I am greeted with a 'Syntax error in Update ... I am assuming Position is a reserved word. ...
    (microsoft.public.dotnet.general)
  • SPL question
    ... I get a syntax error. ... can I do this in SPL? ... Regards ... Colin ...
    (comp.databases.informix)
  • Re: Percentage analysis
    ... But it says Syntax error. ... > percentage against the "All Customers" member. ... >> I want to show Percentage analysis on basis of # of passengers per City. ... >> Regards, ...
    (microsoft.public.sqlserver.olap)
  • Re: Syntax Error
    ... I think Language is a reserved word. ... Syntax error in INSERT INTO statement. ... > Here is the OLEDBAdapter UpdateCommand command string ...
    (microsoft.public.dotnet.framework.adonet)