SQL - where am I going wrong?

[NoelMG]

A few of us have looked at this SQL that is in a module and just can't figure out where it's going wrong - we're getting loads of problems getting it to work - I've transposed it from the SQL window to the VBA window and inserted line breaks etc but think somewhere it's not worked properly!

MTIA!

 SQLStmt = "SELECT [qryMailing - All Information].[qrymailing addresses].[People ID] AS PeopleID, qryCurrentMailing.MailingSuccessful," _
   & " nz([Surname]) AS DSurname, Nz([Forename]) AS DForeName, [qryMailing - All Information].Email AS EmailAddress, Nz([Company name]) AS CompanyName, Nz([Fullname]) AS NewFullName, [qryMailing - All Information].ConventionType" _
   & " FROM [qryMailing - All Information] LEFT JOIN qryCurrentMailing ON [qryMailing - All Information].[qrymailing addresses].[People ID] = qryCurrentMailing.QPeopleID" _
   & " WHERE ((([qryMailing - All Information].[qrymailing addresses].[People ID])=54 Or ([qryMailing - All Information].[qrymailing addresses].[People ID])=133 Or ([qryMailing - All Information].[qrymailing addresses].[People ID])=134) " _
   & " AND ((qryCurrentMailing.MailingSuccessful)=No Or (qryCurrentMailing.MailingSuccessful) Is Null) AND (([qryMailing - All Information].Email) Is Not Null)"

 

[Crowley16]

your NZ needs a value to default to i.e. NZ(variable, "value")

what is with [qryMailing - ...].[qrymailin add...].[peop...]?
the syntax is [tblName/qryName].[fieldName]

the '= No' condition doesn't mean anything since No is not a keyword or string...
either '= false' if the value ur testing is boolean or '= "No"' if the value ur testing is a string

correct syntax for is null is: isnull("somevalue") or NOT isnull("somevalue")

your best bet is to use the query builder to build this, and then edit the sql accordingly...

Procrastinate Now!

 

[NoelMG]

Hi there

We've just copied the SQL from the query builder and added line breaks in - we haven't touched the SQL!

 

[Crowley16]

there's probably more errors on there that I've missed...

if you used a querybuilder to build this, then stop using it...

Procrastinate Now!

 

[NoelMG]

Right the problem was a missing bracket - the rest of the code worked.

I'm now getting a message "No Value Given for One Or More Given Parameters"

Not quite sure where this is from - it's not being helpful and showing me where it is!

Cheers,
Noel.

 

[Crowley16]

step through the code, and check each value...

Procrastinate Now!