Expected array

[Emmanuel Ayivi]

I have a database in which there is a table named as tblfeesdetails. In this table the following fields exist; groupid, Academic_year, Term, Tdate, Debit and credit. Now I need to select from the above table base of two (2) criteria into a diferent table called tblfeestrans where Tdate is maximum but anytime I run the query below an error occurs thus "compile error: expected array" and the system highlights the max with blue colour.

strsqldetails = "Select * from tblfeesdetails where groupid = """ & mgroupid & """ And Tdate = """ & max(mdate) & """ ; "
 
Set rstd = CurrentDb.OpenRecordset(strsqldetails)

[Mariostg]

What is the function max? Is it a user defined function? If not then it should be inside your quotes.
Also, what is mdate? You do not refer to it in your problem statement.
Is there a specific reason to use triple quotes in your scenario?

You should write your query in the sql query editor and try to run it.

[NeoPa]

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

You will find that, while Max() makes sense within the SQL part of the string, it probably doesn't within the VBA (which is where you refer to it).

Mariostg, The triple quotes should work, as they return a single character within a string when doubled up. Here they are either at the start or end of a string where the string itself starts with, or ends with, a single double-quote. Better usage would be to use the single-quote character as it is the correct (ANSI standard) character to use for SQL string variables (See ANSI Standards in String Comparisons).