Passing date cell to SQL string

  • Thread starter Thread starter Gerd
  • Start date Start date
G

Gerd

I have an Excel (2003) sheet where I want to import data from and SQL Server
2000 view. The view has a date field which is defined as CONVERT(NVARCHAR,
[TRANSACTION].TIME, 101) AS DATE so it should be stored in MM/DD/YYYY
format.

In my excel I specified the following SQL string:

select date, tendertype, sum(amount) from
"tenterentry"."dbo"."VIEWDAILYTENDER" where date = '12/22/2007' group by
date, tendertype

When running the query I get all the data for that date.

I now wanted to replace the hard coded date field with a field reference to
an Excel cell (P2) where I enter a date field. The query string looks like
this:

select date, tendertype, sum(amount) from
"tenderentry"."dbo"."VIEWDAILYTENDER" where date = '" & Range("P2").Value &
"' group by date, tendertype

When running the query I do not get any records. I tried to format the P2
cell as a text field, a date field with mm/dd/yyyy format but I never get
any records.

Any help is greatly appreciated.

Gerd
 
what do you get if you just look at Range("P2").value?
i.e. with debug.print Range("P2").value?

otherwise you could use the format function:

"tenderentry"."dbo"."VIEWDAILYTENDER" where date = '" &
Format(Range("P2").Value,"MM/DD/YYYY") & "' group by date, tendertype

hth

Carlo
 
The P2 value shows the date from the cell (like 12/23/2007 or 9/9/2007). I
had formatted the cell with custom format MM/DD/YYYY so I would have
expected the debug to show 09/09/2007. However, with the format in the sql
string it should not matter what date format I have in the cell.

In any case using your sql string I do not get any records either.
 
Back
Top