Jump to content
Sign in to follow this  
Ziggy54354

SQL within VBA macro

Recommended Posts

Hi i have the following code written in VB

[code]Sub Macro2()
'
' Macro2 Macro

Dim Start As String
Start = "{ts '2007-07-17 00:00:00'}"

With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=HPSD-MSSQL-PROD;Description=HPSD-MSSQL-PROD;UID=mzheng;APP=Microsoft Office 2003;WSID=E3XBPWLV9RO6UMF;DATABASE=SD_PROD_DB;N" _
), Array("etwork=DBMSSOCN;Address=ttsddpq1,40000;Trusted_Connection=Yes"))
.CommandText = Array( _
"SELECT Count(v_incident.created) AS 'Host'" & Chr(13) & "" & Chr(10) & "FROM SD_PROD_DB.dbo.v_incident v_incident" & Chr(13) & "" & Chr(10) & "WHERE (v_incident.created>=[b]{ts '2007-07-17 00:00:00'}[/b] And v_incident.created<[b]{ts '2007-07-24 00:00:00'}[/b]) AND (v_i" _
, "ncident.to_workgroup_name='OPS Operations Command Center')")
.Refresh BackgroundQuery:=False
End With
End Sub[/code]

my problem is for the query, i want the user to be able to input the dates you see in bold. I tried testing with a string as you can see but that doesnt work.

meh u can't see bold type but they are shown by [b][/b]

i guess i can use integer representations for dates so 7/17/07 = 39278 but I cant put something like the word start after the operators. Edited by Ziggy54354

Share this post


Link to post
Share on other sites
A little more background info would be helpful. I'm going to assume you are trying to run against a MS SQL Server db. In that case, using a date in the WHERE clause is simply a matter of enclosing it in single quotes.

"SELECT COUNT(v_incidentcreated) AS 'Host' FROM SD_PROD_DB.dbo.v_incident v_incident WHERE v_incident.created>= '2007-07-17 00:00:00' And v_incident.created< '2007-07-24 00:00:00' AND
v_incident.to_workgroup_name='OPS Operations Command Center'"

Also, you can omit the "00:00:00" timestamp if you want since that is implied.

This should also work for an MS Access db. It will not work for an Oracle db. I don't know how MYSQL represents dates.

If you want to set the date into a string variable ou could do it like this:

Dim Startdate as String
Dim Stopdate as String
Startdate = "2007-07-17 00:00:00"
Stopdate = "2007-07-24 00:00:00"

and then simply use the variable in the query like this:
"SELECT COUNT(v_incidentcreated) AS 'Host' FROM SD_PROD_DB.dbo.v_incident v_incident WHERE v_incident.created>= '" & Startdate &"' And v_incident.created < '" & Stopdate & "' AND
v_incident.to_workgroup_name='OPS Operations Command Center'"


Also, this line of code:
), Array("[color="#FFFF00"]etwork[/color]=DBMSSOCN;Address=ttsddpq1,40000;Trusted_Connection=Yes"))

Shouldn't that be "Network"and not "etwork"?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×