hi,

im trying to run a daily report that will use a function to set the date criteria in a pass through query.
Basically i need all records from todays date last year up to todays date this year.
i've written a function that will return the correct date, but when i add this function to the pass through query i get this error

ODBC-failed

No function by the name "MTRDATE" having compatible arguments was found in the functin path. SQLSTATE=42884(#-440)

so i tried some other things: the date as it currently is on the query is in this (american) format
('05/18/2003')

if i remove the parenthesis and the single quotes so as to just have the date i.e. 05/18/2004 then i get the following error (i tried this just to make sure the field format was Date\Time and later found it is)

ODBC-failed

The data types of the operands for the operation ">=" are not compatible. SQLSTATE=42818(#-401)

The query nor the database belong to me, what im trying to do is automate the process so that the whole process can be put onto a scheduler so we don't have to run it manually every day,

any help would be much appreciated,

gavin

You cannot use custom function in Pass-Through queries. You can only use functions supported by the Database server you have an OBBC to. What you can do is use VBA to create your SQL and updated it to the Pass-Trough Query Object.

cheers, that did the trick!

Happy to help.

I am trying to do something very similar to this but I want to allow the user to provide a max date. I am not very familiar with functions and could use some further help.

First, can I replace the line SQL = "Select * From Table1 Where EntryDate = " & CustomFunction() & ";"
with SQL = "Select * From Table1 Where EntryDate < " & [Enter Date] & ";"

Second, is QryName the name of my pass-through query?

Third, the results of the query will populate a report. How do I make the report execute this?

Create an unbound textbox on a form. This will be used to get the date from the user. Yes, queryname is the name of your Pass-Trough query. On the after update event of the unbound textbox you would code:

Dim SQL As String

SQL = "Select * From Table1 Where EntryDate < " & Me![Unbounb Textbox Name] & ";"
SQLEdit_MEI "MyPassThrough", SQL

Set the reports record source to the Pass-Trough Query

Matt,

Thanks. I've got it to run the pass through query but I'm having trouble with the date format. I received a data type mismatch error, so I changed the data type in my sql server view to convert(varchar(11), mydatefield,101)

The query runs but it does not limit it to the date I have specified. Any ideas?

I think I've just figured it out. When I enter the date in the text box, I have to use all 8 characters, such as 05/01/2005 instead of 5/1/2005.