Error On Dmax
2002 (XP) MDB database which has 99% linked tables to a SQL Server 2000 database. I recently changed a query and tested it and it worked fine, gave me back many records as expected. However now a line of code that uses the query causes Odbc failed message - "Incorrect syntax near the keyword NOT". The line of code is: w = DMax("Len([" & rs(c - 1).Name & "])", q) .... rs is a recordset, c is a column loop index, q is the name of the query to use. Stepping through the code the: rs(c - 1).Name bit is return a valid field name that the query q returns. You might say the error tells me exactly what is wrong. But the query works just fine if I https://www.experts-exchange.com/questions/24035136/MS-Access-DMax-Error.html run it directly by double clicking on the query in the database window. It just doesn't want to work when called with the above code. It worked before I altered the query, but why it has now stopped, given that query still works is beyond my limited Access knowledge. Any help would be much appreciated. Regards, Peter Peter Row, Mar 8, 2004 #1 Advertisements Show Ignored Content Want to reply to this thread or ask your own question? It takes just 2 minutes to http://www.pcreview.co.uk/threads/dmax-causes-odbc-failed-error.1653166/ sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum. Sign Up Now! Similar Threads DMAX Criteria David, Jan 23, 2004, in forum: Microsoft Access Reports Replies: 4 Views: 631 Guest Jan 23, 2004 Subreport causes ODBC error Andrew Proctor, Jun 22, 2005, in forum: Microsoft Access Reports Replies: 0 Views: 244 Andrew Proctor Jun 22, 2005 Using DMax function to compute highest visit number for patients Guest, Sep 19, 2005, in forum: Microsoft Access Reports Replies: 0 Views: 195 Guest Sep 19, 2005 DMax in grouping level in report Access 2000 Guest, Sep 28, 2005, in forum: Microsoft Access Reports Replies: 3 Views: 721 David Lloyd Sep 29, 2005 Dmax syntax Gil Lopes via AccessMonster.com, Oct 6, 2005, in forum: Microsoft Access Reports Replies: 2 Views: 523 Gil Lopes via AccessMonster.com Oct 6, 2005 Can't get DMax to work Crystee24, Mar 17, 2006, in forum: Microsoft Access Reports Replies: 2 Views: 305 Crystee24 via AccessMonster.com Mar 17, 2006 Help using Top Values or DMAX for a report Guest, May 3, 2006, in forum: Microsoft Access Reports Replies: 3 Views: 263 Duane Hookom May 3, 2006 Error - ODBC-Failed Guest, Feb 22, 2007, in forum: Microsoft Access Reports Replies: 0 Views: 173 Guest Feb 22, 2007 Loading... Your name or email address: Do you already have an account? No, create an account now. Yes, my password is: Forgot your password? Stay logged in Welcome to PC Re
Dev Articles Community Forums >Databases >Microsoft Access Development DMax gives invalid use of null error sometimes Discuss DMax gives invalid use of null error sometimes in the Microsoft Access Development forum on Dev Articles. DMax gives invalid use of null error sometimes Microsoft Access Development forum to discuss problems and solutions with this popular DBMS. Use Access to build and modify database tables, or full-featured applications. Add This Thread To: Del.icio.us Digg Google Spurl Blink Furl Simpy Y! MyWeb « Previous Thread | Next Thread » Thread Tools Search this Thread Display Modes Dev Articles Community Forums Sponsor: #1 November 1st, 2007, 10:17 AM evilearthwormji Registered User Join Date: Nov 2007 Posts: 2 Time spent in forums: 57 m 17 sec Reputation Power: 0 DMax gives invalid use of null error sometimes Hi, I'm using Dmax to retrieve the most recent (highest) time from a table after finding the most recent date It was working fine with the following code: ImpDate = DMax("DateofInterval", "tblAgentCalls") ImpTime = DMax("StartofInterval", "tblAgentCalls", "[DateofInterval] =#" & ImpDate & "#") this enabled me to show the last date and time a record was entered (they are in separate fields). However once data went in for the 01/11/2007 it started generating a runtime error 94: invalid use of null. I cleared out the data from the table and tried a number of different dates. Everything before 1/11/2007 works find. Everything from the 7/11/2007 works too. So only when the dates are from the first through to the fifth does the error appear. Also when I debug the error, I can see that the first use of Dmax gets the highest date fine but when this is then used in the second instance of the Dmax, to find the time, the error occurs. Are there some limitations to Dmax with dates and times? evilearthwormji View Public Profile View Exte