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

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

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

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

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