Can you use DMax with ADO if your recordset is an Access database?

[bs0d]

I'm having issues with it, an error indicates the table cannot be found.

DMax('field', 'tablename', 'criteria')

 

[ranman256]

If you are in Access, there's no need to use ADO....just make a query.

 

[bs0d]

If you are in Access, there's no need to use ADO....just make a query.

I'm in Excel, with an ADO recordset via Access database.

 

[ranman256]

I put the DMAX in a query , then used ADO to get the query and it worked.
NOT the command you used...the sql version. (yours above is an Access command)

 

[bs0d]

I put the DMAX in a query , then used ADO to get the query and it worked.
NOT the command you used...the sql version. (yours above is an Access command)

I see what you've done. Your actual ADO query was SELECT * FROM dMAX_Query, rather than using DMax in your ADO SQL statement.
I'm not in a position to do this as I'm querying records for a unique ID, a variable in the query. Hmm

 

[Fazza]

It might be best if you explain what is being done - inputs & outputs - to help understand what approach to use.

 

[bs0d]

It might be best if you explain what is being done - inputs & outputs - to help understand what approach to use.

In my query references a table of comments, and table of daily readings. As a part of the query, I need to return the max value from Field9 for each daily reading that a comment also exists for the specific Prop_KEY. If the max value is < 1, return 1.

The query runs fine in Access:

"SELECT ReadingDate, Field1, Field2, Field3, " _
        & "Field4, Field5, Field6, Field7, " _
        & "Field8, Field9, Field10, Field11, " _
        & "Field12, Field13, Field14, " _
        & "Field15, Prop_KEY, IIf([Prop_KEY]=[WH_IDX],IIf(DMax('Field9','tblDailyReadings','Prop_KEY=" & Prop_KEY & "')<1,1,DMax('Field9','tblDailyReadings','Prop_KEY=" & Prop_KEY & "'))) AS MaxValComment " _
        & "FROM tblComments RIGHT JOIN tblDailyReadings ON tblComments.CommentDate = tblDailyReadings.ReadingDate " _
        & "WHERE (((tblDailyReadings.Prop_KEY)=" & Prop_KEY & ")) " _
        & "GROUP BY ReadingDate, Field1, Field2, Field3, " _
        & "Field4, Field5, Field6, Field7, " _
        & "Field8, Field9, Field10, Field11, " _
        & "Field12, Field13, Field14, " _
        & "Field15, Prop_KEY, IIf([Prop_KEY]=[WH_IDX],IIf(DMax('Field9','tblDailyReadings','Prop_KEY=" & Prop_KEY & "')<1,1,DMax('Field9','tblDailyReadings','Prop_KEY=" & Prop_KEY & "')));"

I've narrowed the 'cannot find table' error to the tblDailyReadings reference in DMax.

 

[Fazza]

Please provide further explanation of the requirement if you want further help

Below is the best I can do for now. Please give sample inputs & outputs - to help understand what approach to use. My current understanding is not clear.

I assume this is a query that is run once only: not modified slightly and run repeatedly which I think would be wrong - if that were the case another table would be included in the SQL. Prop_KEY is understood to be numeric, not text.

Debug.Print Join$(Array( _
            "SELECT C.ReadingDate, C.Field1, C.Field2, C.Field3, C.Field4, C.Field5, C.Field6, C.Field7, C.Field8, C.Field10, C.Field11, C.Field12, C.Field13, C.Field14, C.Field15, C.Prop_KEY, MIN(1,MAX(C.Field9)) AS [MaxValComment]", _
            "FROM tblComments C RIGHT JOIN tblDailyReadings R ON C.CommentDate = R.ReadingDate", _
            "WHERE R.Prop_KEY=" & Prop_KEY, _
            "GROUP BY C.ReadingDate, C.Field1, C.Field2, C.Field3, C.Field4, C.Field5, C.Field6, C.Field7, C.Field8, C.Field10, C.Field11, C.Field12, C.Field13, C.Field14, C.Field15, C.Prop_KEY"), vbCr)

 

[bs0d]

Re: Please provide further explanation of the requirement if you want further help

Below is the best I can do for now. Please give sample inputs & outputs - to help understand what approach to use. My current understanding is not clear.

I assume this is a query that is run once only: not modified slightly and run repeatedly which I think would be wrong - if that were the case another table would be included in the SQL. Prop_KEY is understood to be numeric, not text.

Debug.Print Join$(Array( _
            "SELECT C.ReadingDate, C.Field1, C.Field2, C.Field3, C.Field4, C.Field5, C.Field6, C.Field7, C.Field8, C.Field10, C.Field11, C.Field12, C.Field13, C.Field14, C.Field15, C.Prop_KEY, MIN(1,MAX(C.Field9)) AS [MaxValComment]", _
            "FROM tblComments C RIGHT JOIN tblDailyReadings R ON C.CommentDate = R.ReadingDate", _
            "WHERE R.Prop_KEY=" & Prop_KEY, _
            "GROUP BY C.ReadingDate, C.Field1, C.Field2, C.Field3, C.Field4, C.Field5, C.Field6, C.Field7, C.Field8, C.Field10, C.Field11, C.Field12, C.Field13, C.Field14, C.Field15, C.Prop_KEY"), vbCr)

INPUT
The input is Prop_KEY, a numeric variable (double data type). The user selects a property from a combo box. The property key of the selected property is stored in the Prop_KEY variable. So prop_KEY can be any of ~200 different numbers depending on which property the user selects.

OUTPUT
Then the query is executed, returning daily readings for that property (all that exist in the tblDailyReadings table). There is an associated table tblComments, where comments may exist for certain dates. If a match is found by PropKEY = WH_IDX (prop key of the tblComments), then display the max value from field9 for that date.

 

[xenou]

Re: Please provide further explanation of the requirement if you want further help

It's a confusing query I guess too:

• The error message doesn't seem to be very helpful - but I don't know that you can use single quotes inside DMax arguments. They normally need to be in double quotes. So you could still try to rehabilitate DMax though I seriously doubt ADO can run a DMax function.
• In your last post, why join the tables on reading dates if, as you say, the tables are related on PropKey?
• FYI I don't think a double has any business being an ID because you don't want to use floating point numbers for joining tables or with WHERE criteria that use equality comparisons.
• I would recommend you use tables aliases or table names so we can know what fields come from what tables.
• Your reply to ranman about why not to put the query on the Access side doesn't really make sense. It seems worth a try anyway.
• The strange thing is that this sounds like dead simple SQL to write. Can you provide a plain English explanation again and give the table structure (field names and data types - ignoring the fields that don't really matter). There has to be a very simple clue here that I am missing. Maybe again because I don't know what fields are in which tables - we need the tables identified.