I can not get the SQL Statement to run in VB. I am trying
to create a query that will use DateDiff function to
select policies that are older than 36 months. Any ideas?

strSQL = "SELECT * FROM [Policies] WHERE" & DateDiff
("m", "[Date Published]", Now()) & ">" & "36;"

Do you know of any good reference material on the subject
of Building SQL Statements w/Functions, Variables or
Controls in VB? I have taken a SQL class but writing SQL
statements in code seems to be a different type of beast.
Thank You.

Robert P. wrote:

>I can not get the SQL Statement to run in VB. I am trying
>to create a query that will use DateDiff function to
>select policies that are older than 36 months. Any ideas?
>
>strSQL = "SELECT * FROM [Policies] WHERE" & DateDiff
>("m", "[Date Published]", Now()) & ">" & "36;"
>
>Do you know of any good reference material on the subject
>of Building SQL Statements w/Functions, Variables or
>Controls in VB? I have taken a SQL class but writing SQL
>statements in code seems to be a different type of beast.

If you know how to write SQL statements, then you can debug
the code that constructs SQL by using a Debug,Print strSQL
right after the above line of code. This will let you see
what was constructed and it's usually pretty obvious what
happened in the code.

In the above case you have the DateDiff outside the quotes
so [Date Published] is refering to something in your
procedure, noy to a field in the table. From what I can
tell, you don't need to be calculating a value in that where
clause:

strSQL = "SELECT * FROM [Policies] WHERE DateDiff("m", [Date
Published], Now()) > 36;"

--
Marsh
MVP [MS Access]

Marshall Barton <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

>
> strSQL = "SELECT * FROM [Policies] WHERE DateDiff("m", [Date
> Published], Now()) > 36;"
>
>

Ahem...

"SELECT * FROM [Policies] " & _
"WHERE DateDiff(""m"", [Date Published], Now()) > 36;"


just a matter of the quotes around the ""m""...

All the best


Tim F

Tim Ferguson wrote:

>Marshall Barton <(E-Mail Removed)> wrote in
>news:(E-Mail Removed):
>
>>
>> strSQL = "SELECT * FROM [Policies] WHERE DateDiff("m", [Date
>> Published], Now()) > 36;"
>>
>>
>
>Ahem...
>
>"SELECT * FROM [Policies] " & _
>"WHERE DateDiff(""m"", [Date Published], Now()) > 36;"
>
>
>just a matter of the quotes around the ""m""...

Good catch Tim.

--
Marsh
MVP [MS Access]