Macro Creating Variable and using variable in a SQL statement

[Guest]

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

 

[Bob Phillips]

Use

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

 

[Patrick Molloy]

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)

 

[Guest]

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

 

[mudraker]

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