Jump to content
Sign in to follow this  
Ziggy54354

SQL within VBA macro

Recommended Posts

Hi i have the following code written in VB

 

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

 

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

 

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("etwork=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  

×