Dmax from vba query runtime 3163

[crossy5575]

Hi there,
I have created a query in VBA to find a max id number from which i can then do 'things'

I am getting a runtime error stating the field is too small to accept the amount of data

Public Function holquery(findx As Integer)

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset


strSQL = "SELECT [12b Holsub].[staff no], [12c holdetail].hcID, [12c holdetail].dayscf " & _
"FROM ([01 Name] INNER JOIN [12b Holsub] ON [01 Name].ID = [12b Holsub].[staff no]) INNER JOIN ([12 Holiday] INNER JOIN [12c holdetail] ON [12 Holiday].ID = [12c holdetail].hcID) ON [12b Holsub].ID = [12 Holiday].holidayno " & _
"WHERE ((([12b Holsub].[staff no])=" & findx & "));"


'Set db = CurrentDb
'Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'rs.MoveLast
'rs.MoveFirst

holquery = DMax("hcID", strSQL)
holquery2 = DLookup("dayscf", "12c holdetail", "hcID =" & holquery)

'Set db = Nothing
'Set rs = Nothing
End Function

I have removed the set db lines etc as they have no effect, in fact they make things worse with more issues!

how do i get the dmax to work?

thanks

Simon

 

[Minty]

As far as I know you can't set the Domain in DMax as a query string, you have to refer to a query or table that already exists.

So this won't work holquery = DMax("hcID", strSQL)

You would have to create a query def then refer to that query, to get your result.

 

[crossy5575]

You would have to create a query def then refer to that query, to get your result.

sorry i dont quite know what you mean by that? i thought the query def was the strsql?

 

[plog]

The second argument of a DMax has to be an Access Table or Query Object, not a line of SQL.

So, if you could take your strSQL and make an actual query object out of it, let's call it 'MyQuery'. You could then use this Dmax:

DMax("[hcID]", "MyQuery")

 

[plog]

Looking over your function, it seems the DMax is unnecessary (actually a lot of the code is). You could simply rewrite the query to get what you need from it. This is a simplified version, but it would be this:

SELECT MAX(hcID) FROM....

That would return the exact value you are trying to get via the DMax

 

[crossy5575]

Thanks Plog,
changed it to a query rather than SQL, just adds another component which i wanted to keep to a minimum. never mind! thanks for your help
Simon