Sounds like the problem you are having is due to the results not being
returned according to the criteria you specify. Is this what you are having
problems with? If you have Query Analyzer or MSQuery, you might to run your
SQL select statement from those clients to see what results it is returning
you. This will mean that you have to hard code the parameter values in your
criteria. So, test your resultset from Query Analyzer or MSQuery and make
sure it is correct before coding it in VBA. Otherwise highlight the problem
you have with a few sample records and what results you are seeing.
-- james
***Disclaimer: This posting is provided "as is" with no warranties and
confers no rights.***
--------------------
[quoted text, click to view] >From: "Thank You" <thanksmyfriend@hotmail.com>
>Newsgroups: microsoft.public.sqlserver.connect
>Subject: Use SQL statements in Excel VBA to query SQL Server Data Problem
>Date: Sat, 20 Nov 2004 20:48:06 +0800
>Organization: HGC Boardband
>Lines: 26
>Message-ID: <cnnfvk$9s8$1@news.hgc.com.hk>
>NNTP-Posting-Host: 221.124.32.214
>X-Trace: news.hgc.com.hk 1100956468 10120 221.124.32.214 (20 Nov 2004
13:14:28 GMT)
>X-Complaints-To: abuse@net-yan.com
>NNTP-Posting-Date: Sat, 20 Nov 2004 13:14:28 +0000 (UTC)
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!tornado.fastwebnet.it!tiscali!new
sfeed1.ip.tiscali.net!fi.sn.net!newsfeed2.fi.sn.net!newsfeed.bahnhof.se!195.
197.54.117.MISMATCH!feeder1.news.jippii.net!news.net.hanse.com!nntp.gblx.net
!nntp3.phx1!news.hgc.com.hk!not-for-mail
[quoted text, click to view] >Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.connect:43804
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>
>I try to use SQL satatment to query data from SQL Server but I don't know
>why I need to increase 2 into the "intDay". If there no 2 increse the day
>will display is 3 not 5. Who can tell me what problems have. Thanks!
>=================================================
>Public Function SetSQLString(strSQL as String)
> intYear = 2004
> intMonth = 11
> intDay = 5 + 2
> strReaderName = "'" & Sheet2.Cells(12, 3) & "'"
>
> strSQL = " SELECT Name, " _
> + " Description, " _
> + " Min(Date) as FirstIn, " _
> + " Max(Date) as LastOut " _
> + " FROM HistoryTable " _
> + " WHERE Name = " + strName + _
> + " AND DatePart(yyyy,Date) = " + intYear + _
> + " AND DatePart(mm,Date) = " + intMonth + _
> + " AND DatePart(dd,Date) = " + intDay + _
> + " GROUP BY Name, Description" + _
> + " ORDER BY Name "
>End Sub
>--
>Ronald
>
>
>
