[AccessD] Ambiguous Nmae in Query Expression

One thing that has grabbed my attention from the Beginning was Expr1...have 
you tried naming this?...and second...if you remove the [qry Pattern Days on 
Injection by Fluid] from the join do you still get the error?

Mark


>From: "Kaup, Chester" <Chester_Kaup at kindermorgan.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem 
>solving"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Ambiguous Nmae in Query Expression
>Date: Thu, 13 Apr 2006 13:04:54 -0500
>
>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
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com