Build SQL Statements w/Functions, Variables or Controls in VB

R

Robert P.

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.
 
M

Marshall Barton

Robert said:
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;"
 
T

Tim Ferguson

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
 
M

Marshall Barton

Tim said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top