SQL statement keeps throwing errors

[magster06]

Hello All,

I am glad I am not trying to make Access vba a career, because I really suck at it, lol.

Anyways, I am hoping someone can see where I am going wrong (yet again) with my wrapping of my sql statement.

I have tried for about an hour trying different configurations, but to no avail.

    strWhereCriteria = LightDuty.[Email Address] = LightDuty.[Drs Excuse Exp] = DateDiff('d', Date(), [Drs Excuse Exp]) <= 14 And LightDuty.[Email Sent] = 0

I keep getting a syntax error.

This is my WHERE criteria for my sql statement

I am placing it here:

strSQL = "SELECT LightDuty.[Email Address] FROM LightDuty WHERE '" & strWhereCriteria & "'

 

[Isskint]

perhaps you could explain what the criteria is trying to determine? You appear to have string and numeric variables within your criteria, so it will not work.

 

[magster06]

I am trying to pull the email addresses from the table Lightduty with the criteria of where the email address is = to the email sent field is = false and the Drs Excuse Exp date is = to 14 days or less.

I pulled this statement directly from my access query and it works just fine in the query, but not in vba

This is the expression directly from my access query:

((DateDiff("d", Date(), [Drs Excuse Exp]) <= 14) And ((LightDuty.[Email Sent] = False))

If I remove the ' from around the d and replace them with ", then I get an error that LightDuty is not defined.

 

[Isskint]

It is all in the quotes around the d argument in DateDiff(). try double double quotes thus


"((DateDiff(""d"", Date(), [Drs Excuse Exp]) <= 14) And (([Email Sent] = False))"

 

[magster06]

Isskint,

Thanks for the replies. I ended up taking the strWhereCriteria out of the sql statement and replace it with the criteria and it now gets by that hiccup.

 

[MSAccessRookie]

In your Criteria, SQL will use a Double Quote (") to delimit a string, while VBA will want a Single Quote (') to delimit a string. This should make your VBA Criteria as follows:

strWhereCriteria = "((DateDiff('d', Date(), [Drs Excuse Exp]) <= 14) And ((LightDuty.[Email Sent] = False))"

Also, you may need to remove the Quotes marked in RED below.

strSQL = "SELECT LightDuty.[Email Address] FROM LightDuty WHERE '" & strWhereCriteria & "'

 

[magster06]

Thanks for the response MSAccessRookie!