Select query with max and group by

[LUTINO]

Dear Seniors,
I am new to VBA and I seek your help
My logic is to have primary key on both columns CMP_ID & CmP_ID_Char.

Table Company
Fields Cmp_ID Number , Cmp_ID_char Text

DATA
Cmp_ID Cmp_ID_Char
1 Black
1 Red
2 Black
1 White
2 Red


In Vba code i want to run a SQL Query which can give me max(cmp_id) with group by cmp_id_char.. so i now the max numberr of black, red, white. etc

i run this query in acces builder and resluts with good. but same query does not fecth any records. Recordset Eof & BOF are ture..



Dim Max_Dbs As DAO.Database
Dim max_Rst As DAO.Recordset
Dim Tmp_Cmp_Char As String


Dim MySql As String




Set Max_Dbs = OpenDatabase("d:\access qoute new try\qt.accdb")




MySql = "select max(cmp_id) from tbl_company where cmp_id_char= ' " & Txt_Cmp_Char & " ' group by cmp_id_char"
Set max_Rst = Max_Dbs.OpenRecordset(MySql)






here. Max_rst bof and eof are true...
tell me where am i wrong..
Txt_cmp_Char is a text box to input.(i tried both cases. upper and lower)

[ItsMe]

If you are simply trying to eliminate duplicates, you can do something like the following. I suggest pasting the SQL into SQL View of a query object so you can test the results. From there, it should be easy to determine the max. However, Max is not Count.

SELECT Company.CMP_ID, Company.CmP_ID_Char
FROM Company
GROUP BY Company.CMP_ID, Company.CmP_ID_Char
ORDER BY Company.CMP_ID

[LUTINO]

Thank you for your prompt reply.. and appology if my question was too naive or too vague.

I have applied Primary key on both ids. (its like plate of a vehicel... TAR0001, TAR0002 TAR0003... TAR9999). AND IN ORDER TO CREATE new record i have to know the MAX digit in specific Chaaracter group ... like MAX in TAR.. then I will MAX+1 to get new record id..
this querey works fine in SQL View of ACCESS..but When I used this behind a form. it picks empty recordset.. where. EOF AND BOF both are true

This query picks Company id (character) from a user input text box.. (TAR.. ZMO ETC)
THEN IT CHECK THE MAX DIGIT ID FROM THE TABLE AGAINST THAT CRITERIA...
NO ERROR IN QUERY. BUT RECORDSET IS EMPTY


MySql = "select max(cmp_id) from tbl_company where cmp_id_char= ' " & Txt_Cmp_Char & " ' group by cmp_id_char"
Set max_Rst = Max_Dbs.OpenRecordset(MySql)

[ItsMe]

It seems like you are missing an alias for your function

MySql = "select max(cmp_id) AS MyMax from tbl_company where cmp_id_char= ' " & Txt_Cmp_Char & " ' group by cmp_id_char"

I am not really seeing anything else wrong with the SQL. Is the name of your table tbl_company or is it company?

Another option might be to use DMax and include the value of Txt_Cmp_Char as criteria.
https://msdn.microsoft.com/en-us/lib.../ff835050.aspx

[LUTINO]

It seems like you are missing an alias for your function

MySql = "select max(cmp_id) AS MyMax from tbl_company where cmp_id_char= ' " & Txt_Cmp_Char & " ' group by cmp_id_char"

I am not really seeing anything else wrong with the SQL. Is the name of your table tbl_company or is it company?

Another option might be to use DMax and include the value of Txt_Cmp_Char as criteria.
https://msdn.microsoft.com/en-us/lib.../ff835050.aspx

I added Alias as suggested in query


MsgBox (max_Rst![cmp_id])

Error Msg "Item not found in this collection.." ERROR CODE 3265

Bof & Eof are still true. (means not data fatched)

Table name is Tbl_Company


COMPLETE CODE FOR THIS ROUTINE
-----------------------------------------
Sub Max_id_Gen()
Dim Max_Dbs As DAO.Database
Dim max_Rst As DAO.Recordset
Dim Tmp_Cmp_Char As String
Dim MySql As String
Dim Max_id As Single

Set Max_Dbs = OpenDatabase("d:\access qoute new try\qt.accdb")

Tmp_Cmp_Char = Txt_Cmp_Char ' COPY TEXT DATA TO TEMPORARY VARIABLE'

MySql = "select max(cmp_id) AS ALIAS_COMP_ID from tbl_company where cmp_id_char= ' " & Tmp_Cmp_Char & " ' group by cmp_id_char, CMP_ID"


Set max_Rst = Max_Dbs.OpenRecordset(MySql)


If max_Rst.BOF = True Then
MsgBox "bof"
End If

If max_Rst.EOF = True Then
MsgBox "Eof"
End If

MsgBox (max_Rst![cmp_id])

End Sub
------------------------------------------

[LUTINO]

when i change this query criteria to static value

MySql = "select max(cmp_id) AS ALIAS_COMP_ID from tbl_company where cmp_id_char= 'TAR' group by cmp_id_char"

I gets results correctly.. but when i want to access same query with criteria picked form textbox. my recordset is empty.

MySql = "select max(cmp_id) AS ALIAS_COMP_ID from tbl_company where cmp_id_char= ' " & Tmp_Cmp_Char & " ' group by cmp_id_char, CMP_ID"

[LUTINO]

Dear All,

I found the BUG.. It was space between " and ' in select Query.
I forgot to realise that for processer ' TAR ' and 'TAR' and two different values..

Thank you all