ACCESS FEED: Re: [MS_AccessPros] Problem with dates in queries

Selasa, 20 Maret 2012

Re: [MS_AccessPros] Problem with dates in queries

Thanks for the pointers guys, after an awful lot of testing I have found out what the problem is.

I've made use of library that comes with some 3rd party software that certain members of the company have installed. I had assumed that the runtime would just ignore the missing library and it would not be an issue as the parts of the system they are allowed to access do not make use of this 3rd party software library.

I was wrong, although this library has nothing to do with dates, it's absence caused the error I reported.

To prove this, I copied the xxxx.tlb from my machine to one of those with the error and it cured it. Obviously there is a licensing issue so this is not a solution I can live with and it would be too costly to purchase the software for all users of the database that don't need the facility.

So, I wonder if there is a way around this. Is it possible in the autoexec macro to check if the 3rd party application is installed and then link in the reference on the fly?

Bob

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> topfuelbob- (name?)
>
> It's not specifically the version that's getting you. When queries stop
> recognizing simple builtin functions like IIf and Date, it usually means the
> database has a VB reference to a library not on the machine. On your machine,
> open the database in a retail copy, open the VB Editor, and choose References
> from the Tools menu. Remove any libraries that you don't actually need. The
> basic libraries required are:
>
> Visual Basic for Applications
> Microsoft Access 12.0 Object Library
> Microsoft Office 12.0 Access database engine Object Library
>
> Try removing all but these three libraries, then do a compile. If that works,
> save the compile, compact and repair, and give your users the new database.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
>
> ---------------------------------------
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of topfuelbob
> Sent: Monday, March 19, 2012 12:25 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Problem with dates in queries
>
>
> I have a calculated field in a query which works fine for all my users that have
> runtime 12.0.6612.1000.
> But for all my users that have runtime version 12.0.6425.1000 it fails with the
> error:
> "3075 (Function is not available in expressions in query expression)"
>
> The field value in the query is:
> "ActiveContract: IIf([ContractStartDate]<Date() And Not
> [ContractEndDate]<Date(),True,False)"
>
> So, question, am I using the correct syntax in that field?
>
> We are all in the UK but as far as I can tell, all machines are set up with the
> same regional settings regarding dates.
>