I have seen this issue in the past. If I remember correctly the way I
got around it was to - write the current date/time value to a table
with a single indexed record that was used in the query using the
function "dlookup" to write the criteria:
---------------------------------------------------------------------
DLookup(expr, domain, [criteria])
---------------------------------------------------------------------
You can use the DLookup function to get the value of a particular
field from a specified set of records (a domain). Use the DLookup
function in Visual Basic, a macro, a query expression, or a calculated
control on a form or report.
You can use the DLookup function to display the value of a field that
isn't in the record source for your form or report. For example,
suppose you have a form based on an Order Details table. The form
displays the OrderID, ProductID, UnitPrice, Quantity, and Discount
fields. However, the ProductName field is in another table, the
Products table. You could use the DLookup function in a calculated
control to display the ProductName on the same form.
The DLookup function returns a single field value based on the
information specified in criteria. Although criteria is an optional
argument, if you don't supply a value for criteria, the DLookup
function returns a random value in the domain.
If no record satisfies criteria or if domain contains no records, the
DLookup function returns a Null.
If more than one field meets criteria, the DLookup function returns
the first occurrence. You should specify criteria that will ensure
that the field value returned by the DLookup function is unique. You
may want to use a primary key value for your criteria, such as
[EmployeeID] in the following example, to ensure that the DLookup
function returns a unique value:
Dim varX As Variant
varX = DLookup("[LastName]", "Employees", "[EmployeeID] = 1")
For More Information:
http://msdn2.microsoft.com/en-us/library/aa172176(office.11).aspx
Hope this helps.
On Jun 19, 8:33 am, "Bhagawat, Prashanth"
<[EMAIL PROTECTED]> wrote:
> Hi Listers,
>
> I'm trying to fetch Report from Remedy in MS-Access. Report will be
> run`ed daily, so need to pass the Current-Date as parameter which needs
> to be included in the SQL Script.
>
> Link Tables is being used with AR-System-ODBC-Driver to connect to
> Remedy Server and the sample query is below:
>
> Select count(*) from HPD_Helpdesk
> Where Create_time >= #06/19/2007 00:00:01 AM# AND Create_time <=
> #06/19/2007 23:59:59 PM#
>
> The date hard coded query runs fine but when I pass date as parameter
> with a function returning the current date( Macro code Written for this
> ), on executing query I get message that no data found.
>
> When I use SQL ODBC driver Macro written code (which returns current
> date) works fine with same query it returns the data.
>
> I did even try with record set ( Macro Code open connection ,open record
> set and directly executing SQL script from code ) I got the same message
> that "no record was found".
>
> Is there something which iam missing out here, (on Remedy ODBC Driver ),
> I need to pass the Date daily ( current Date ) if that's the case then
> what date time conversion I should take care of.
>
> Guide me how can this be done.
>
> Regards,
>
> Prashanth Bhagawat
> IT Consultant
> Remedy Application Management Services
> _____________________________________
> LogicaCMG - Releasing your potential
>
> Divyasree Technopolis
> 124-125 Yemlur PO, off Airport Road
> Bangalore - 560 037, INDIA
> T: +44 20718 25414 Extn No: 25414
> E: [EMAIL PROTECTED]<http://www.logicacmg.com/>
>
> This e-mail and any attachment is for authorised use by the intended
> recipient(s) only. It may contain proprietary material, confidential
> information and/or be subject to legal privilege. It should not be copied,
> disclosed to, retained or used by, any other party. If you are not an
> intended recipient then please promptly delete this e-mail and any attachment
> and all copies and inform the sender. Thank you.
>
> _______________________________________________________________________________
> UNSUBSCRIBE or access ARSlist Archives atwww.arslist.orgARSlist:"Where the
> Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
