Hi

I have set up a User Defined Function (UDF) in a MS Access Module that is called in a MS Access Query. I tested the query in MS Access and it works fine.

In MS Excel, I tried to import data from the query but the Import Data query doesn’t locate the particular query. Why? Is there a fix that will allow me to import data from an MS Access query than contains a UDF?

I can see other normal queries through the import data wizard but none of the ones that contain UDFs.

Blue Bottle Boy

Hi

I have set up a User Defined Function (UDF) in a MS Access Module that is called in a MS Access Query. I tested the query in MS Access and it works fine.

In MS Excel, I tried to import data from the query but the Import Data query doesn’t locate the particular query. Why? Is there a fix that will allow me to import data from an MS Access query than contains a UDF?

I can see other normal queries through the import data wizard but none of the ones that contain UDFs.

Blue Bottle Boy

BBB..from what i have read up..you can't use UDF from Excel..only within Access. c/o eggheadcafe

Just posting a follow up for anyone who is interested.

Using George's recommendation above I managed to get a solution.

DateAdd("m",DateDiff("m",0,[OrderDate]),1)));



I replicate it below in an example query.

SELECT Orders.OrderDate
FROM Orders
WHERE (((Orders.OrderDate)=DateAdd("m",DateDiff("m",0,[OrderDate]),1)));


The trick is that DateAdd and DateDiff function calculate slightly differenty. One must start its index from 0 whilst the other must start from 1. In any case, if you use the formula as shown you should calculate the end of month date from a given date.

Regards

BBB