Macro Creating Variable and using variable in a SQL statement
 

Created a Variable where someone enters a date into a inputbox

eg. StartDate = InputBox("Enter Start Date?", "Report")

Then i used this variable in the SQL statement by saying

date=Startdate in the where clause in the VB script.

Instead of using the variable it tried to put "startdate" in the field and
filter by "startdate"

How do i make the the macro use the variable in the SQL statement.

Also should i be changing the date in a date format for the variable in the
inputbox statement. Because it is currently under no format. How can i change
this format.

Thanks If anyone can help

Macro Creating Variable and using variable in a SQL statement
 

Use

...date=""" & Format(Startdate,"yyyy/mm/dd") & """ ...

--

HTH

RP

"Jimmy" wrote in message
...
Created a Variable where someone enters a date into a inputbox

eg. StartDate = InputBox("Enter Start Date?", "Report")

Then i used this variable in the SQL statement by saying

date=Startdate in the where clause in the VB script.

Instead of using the variable it tried to put "startdate" in the field and
filter by "startdate"

How do i make the the macro use the variable in the SQL statement.

Also should i be changing the date in a date format for the variable in
the
inputbox statement. Because it is currently under no format. How can i
change
this format.

Thanks If anyone can help

Macro Creating Variable and using variable in a SQL statement
 

dim sql as string
dim sDate as string
sdate = format$( InputBox("Enter Start Date?", "Report") , "dd-mmm-yy")
sql = "usp_FetchData '%d%'"

sql = REPLACE(sql, "%d%", sDate)



--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Jimmy" wrote in message
...
Created a Variable where someone enters a date into a inputbox

eg. StartDate = InputBox("Enter Start Date?", "Report")

Then i used this variable in the SQL statement by saying

date=Startdate in the where clause in the VB script.

Instead of using the variable it tried to put "startdate" in the field and
filter by "startdate"

How do i make the the macro use the variable in the SQL statement.

Also should i be changing the date in a date format for the variable in
the
inputbox statement. Because it is currently under no format. How can i
change
this format.

Thanks If anyone can help

Macro Creating Variable and using variable in a SQL statement
 

Question

dim sql as string
dim sDate as string

sql = "usp_FetchData '%d%'"

sql = REPLACE(sql, "%d%", sDate)

Do i have to put this only once in the Macro or before every SQL script

What does sql = REPLACE(sql, "%d%", sDate) actually do?

And what does %d% represent?

In the sql statement to i write date=sdate or as date=%d% in the 'where'
clause.

Thanks

"Patrick Molloy" wrote:

dim sql as string
dim sDate as string
sdate = format$( InputBox("Enter Start Date?", "Report") , "dd-mmm-yy")
sql = "usp_FetchData '%d%'"

sql = REPLACE(sql, "%d%", sDate)



--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Jimmy" wrote in message
...
Created a Variable where someone enters a date into a inputbox

eg. StartDate = InputBox("Enter Start Date?", "Report")

Then i used this variable in the SQL statement by saying

date=Startdate in the where clause in the VB script.

Instead of using the variable it tried to put "startdate" in the field and
filter by "startdate"

How do i make the the macro use the variable in the SQL statement.

Also should i be changing the date in a date format for the variable in
the
inputbox statement. Because it is currently under no format. How can i
change
this format.

Thanks If anyone can help

Macro Creating Variable and using variable in a SQL statement
 

Jimmy

It sounds like in your SQL command you have "StartDate" (inside quot
marks).
You need to remove the quote marks to enable VB to see the variable

I have not played around with sql but you may need to do something lik
this to enable your StartDate variable

"SqlTextCode" & StartDate & "MoreSqlTxtCode

--
mudrake
-----------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...nfo&userid=247
View this thread: http://www.excelforum.com/showthread.php?threadid=27188