Question on MS Query in Excel 2000

[Bill]

I have a spreadsheet that has an MS Query embedded in it.
The query pulls from a database and then returns the data
to the excel spreadsheet. I have a field that I'm
prompted for called Received Date. Right now, when I open
the spreadsheet I get a dialog prompting me for the date.
What I want to do is change this to automatically use the
current date.
I right click on the data in the spreadsheet and select
edit query. Here is where I need help. I can't seem to
pinpoint the syntax to use today's date. I've tried
=Today()
=Date()
=Now()

and so on. I've tried with and without the parenthesis. I
also get the error:

'Now' is not a recognized function name. Statement(s) can
not be prepared.

Same thing with Today and Date.

Here is the SQL statement that was created:

SELECT CallLog.CallID, Subset.LASTNAME, Subset.FIRSTNAME,
CallLog.CallType, CallLog.ShortDesc, CallLog.RecvdBy,
CallLog.RecvdDate, CallLog.RecvdTime, CallLog.ModBy,
CallLog.ModDate, CallLog.ModTime, CallLog.Tracker
FROM heatdb.dbo.CallLog CallLog, heatdb.dbo.Subset Subset
WHERE Subset.CallID = CallLog.CallID AND
((CallLog.Tracker='JonesB') AND (CallLog.RecvdDate=?))
ORDER BY CallLog.Tracker, CallLog.RecvdDate,
CallLog.ModDate


Can any one tell me how to do this?
Thanks
Bill

 

[Jake Marx]

Hi Bill,

Is the data source SQL Server, Access, or something else? If SQL Server,
you should be able to use GetDate(). If Access, you can use Date(). You
should put one of these directly in the SQL statement in place of the "?".

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Bill]

Sorry for the delay in responded, Jake. I was out of the
office. I think it is SQL and I put the GetDate() in the
query and and excepts it, but returns no information.
Here is the modified SQL statement:

((CallLog.Tracker='JonesB') AND (CallLog.RecvdDate=GetDate
())

-----Original Message-----
Hi Bill,

Is the data source SQL Server, Access, or something else? If SQL Server,
you should be able to use GetDate(). If Access, you can use Date(). You
should put one of these directly in the SQL statement in place of the "?".

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have a spreadsheet that has an MS Query embedded in it.
The query pulls from a database and then returns the data
to the excel spreadsheet. I have a field that I'm
prompted for called Received Date. Right now, when I open
the spreadsheet I get a dialog prompting me for the date.
What I want to do is change this to automatically use the
current date.
I right click on the data in the spreadsheet and select
edit query. Here is where I need help. I can't seem to
pinpoint the syntax to use today's date. I've tried
=Today()
=Date()
=Now()

and so on. I've tried with and without the parenthesis. I
also get the error:

'Now' is not a recognized function name. Statement(s) can
not be prepared.

Same thing with Today and Date.

Here is the SQL statement that was created:

SELECT CallLog.CallID, Subset.LASTNAME, Subset.FIRSTNAME,
CallLog.CallType, CallLog.ShortDesc, CallLog.RecvdBy,
CallLog.RecvdDate, CallLog.RecvdTime, CallLog.ModBy,
CallLog.ModDate, CallLog.ModTime, CallLog.Tracker
FROM heatdb.dbo.CallLog CallLog, heatdb.dbo.Subset Subset
WHERE Subset.CallID = CallLog.CallID AND
((CallLog.Tracker='JonesB') AND (CallLog.RecvdDate=?))
ORDER BY CallLog.Tracker, CallLog.RecvdDate,
CallLog.ModDate


Can any one tell me how to do this?
Thanks
Bill

.