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

  • Thread starter Thread starter Robert P.
  • Start date Start date
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.
 
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.
Click to expand...

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;"
 
strSQL = "SELECT * FROM [Policies] WHERE DateDiff("m", [Date
Published], Now()) > 36;"
Click to expand...

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 said:
strSQL = "SELECT * FROM [Policies] WHERE DateDiff("m", [Date
Published], Now()) > 36;"
Click to expand...

Ahem...

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


just a matter of the quotes around the ""m""...
Click to expand...

Good catch Tim.
 
Back
Top