Passing date cell to SQL string

[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

 

[carlo]

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

 

[Gerd]

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.