[AccessD] Ambiguous Nmae in Query Expression

Responses in blue and italics. Thanks everyone.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Ken Ismert
Sent: Thursday, April 13, 2006 12:19 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Ambiguous Nmae in Query Expression


Chester,

> ... First point - avoid variants wherever possible. 
> Try defining D and the function as dates ... 

While good programming practice for regular VB coding, keep the Variant
type in this case. Access Query columns always have type Variant, so
functions that are meant to be used with queries should have all
parameters defined as Variant, too. 

Tried it both as variants and as date and integer. Error message still
appears.

> ... Try a dot instead of a bang in [ProdPattern]![Date] ...

Unlikely to help. Its just two ways of saying the same thing. (I use
dot)
Tried both dot and bang with same error message.

> ... Declare the function as Static ... 

While Static can help improve the performance of functions written for
use with queries, it won't solve your error message. But just to be
extra explicit, you can use: 

   Public Static Function DaysInMonth2(ByRef D As Variant)

Made this change and still get the error message.

Since you tried the function in the module and it worked fine, you
probably don't have a duplicate function definition. 

You still have a reserved word in your query: [qry Pattern Days on
Injection by Fluid].Date. The Date function is syntactically valid
without the ending (). When you introduced the DaysInMonth2() function
in your query, Access looked for other function calls, and couldn't
decide whether Date was a function or a field. Thus, the ambiguous name
error. 

Never use field names that are the same as any VB built-in functions or
SQL reserved words. I use compound field names, like 'ProdDate' or
'PatternDate', to ensure that all field names are unique from any
reserved words. 

Here is the latest short version of the query. Still get the error
message. Frustrating. Thanks for the help everyone.

SELECT ProdPattern1.PID, DaysInMonth2([ProdPattern1].[RecordDate]) AS
Expr1
FROM [qry Pattern Days on Injection by Fluid] INNER JOIN ProdPattern1 ON
([qry Pattern Days on Injection by Fluid].ProdDate =
ProdPattern1.RecordDate) AND ([qry Pattern Days on Injection by
Fluid].PID = ProdPattern1.PID);
-Ken
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com