Query in VBA

[rocky09]

Hi All,

Can anyone help me on this one. I am getting Syntax error when I am trying to assign the SQL Statement.

Option Compare Database

Private Sub Command6_Click()

SearchResults.RowSource = "SELECT ECTM_ECTM_Computer.NAME, ECTM_ECTM_COMPANY.NAME, ECTM_ECTM_CPU.SERIALNUMBER, ECTM_ECTM_CPU.POSITION, Max(ECTM_ECTM_CPUDATA.DATADATE) AS MaxOfDATADATE
FROM ((ECTM_ECTM_Computer INNER JOIN ECTM_ECTM_COMPANY ON ECTM_ECTM_Computer.COMPANYID = ECTM_ECTM_COMPANY.ID) INNER JOIN ECTM_ECTM_CPU ON ECTM_ECTM_Computer.ID = ECTM_ECTM_CPU.ComputerID) INNER JOIN ECTM_ECTM_CPUDATA ON ECTM_ECTM_CPU.ID = ECTM_ECTM_CPUDATA.CPUID
GROUP BY ECTM_ECTM_Computer.NAME, ECTM_ECTM_COMPANY.NAME, ECTM_ECTM_CPU.SERIALNUMBER, ECTM_ECTM_CPU.POSITION
HAVING (((ECTM_ECTM_Computer.NAME) Like "*" & forms!FRM_SearchMulti!SrchText & "*")); "

SearchResults.ColumnCount = 5
End Sub

Thank you in advance,

Best Regards,
Rocky

 

[Uncle Gizmo]

Comment out the line :- SearchResults.ColumnCount=5 and see what happens.

 

[rocky09]

Comment out the line :- SearchResults.ColumnCount=5 and see what happens.

Thank you very much for your reply ,

I tried with out the line SearchResults.ColumnCount = 5, but getting Syntax Error.

 

[namliam]

Well you are missing line continuations..... long sql like this usually is best split up and organized a bit...

Option Compare Database

Private Sub Command6_Click()

SearchResults.RowSource = "SELECT ECTM_ECTM_Computer.NAME, ECTM_ECTM_COMPANY.NAME, ECTM_ECTM_CPU.SERIALNUMBER, ECTM_ECTM_CPU.POSITION, Max(ECTM_ECTM_CPUDATA.DATADATE) AS MaxOfDATADATE " & _
                          "FROM ((ECTM_ECTM_Computer " & _
                          "INNER JOIN ECTM_ECTM_COMPANY ON ECTM_ECTM_Computer.COMPANYID = ECTM_ECTM_COMPANY.ID) " & _
                          "INNER JOIN ECTM_ECTM_CPU ON ECTM_ECTM_Computer.ID = ECTM_ECTM_CPU.ComputerID) " & _
                          "INNER JOIN ECTM_ECTM_CPUDATA ON ECTM_ECTM_CPU.ID = ECTM_ECTM_CPUDATA.CPUID " & _
                          "GROUP BY ECTM_ECTM_Computer.NAME, ECTM_ECTM_COMPANY.NAME, ECTM_ECTM_CPU.SERIALNUMBER, ECTM_ECTM_CPU.POSITION " & _
                          "HAVING (((ECTM_ECTM_Computer.NAME) Like "*" & forms!FRM_SearchMulti!SrchText & "*")); "

SearchResults.ColumnCount = 5
End Sub

Formatting could be better still, but I leave that up to you.
Also using default names for controls is a bad idea and bad practice, strongly suggest you give your controls proper names.

 

[rocky09]

Well you are missing line continuations..... long sql like this usually is best split up and organized a bit...

Option Compare Database

Private Sub Command6_Click()

SearchResults.RowSource = "SELECT ECTM_ECTM_Computer.NAME, ECTM_ECTM_COMPANY.NAME, ECTM_ECTM_CPU.SERIALNUMBER, ECTM_ECTM_CPU.POSITION, Max(ECTM_ECTM_CPUDATA.DATADATE) AS MaxOfDATADATE " & _
                          "FROM ((ECTM_ECTM_Computer " & _
                          "INNER JOIN ECTM_ECTM_COMPANY ON ECTM_ECTM_Computer.COMPANYID = ECTM_ECTM_COMPANY.ID) " & _
                          "INNER JOIN ECTM_ECTM_CPU ON ECTM_ECTM_Computer.ID = ECTM_ECTM_CPU.ComputerID) " & _
                          "INNER JOIN ECTM_ECTM_CPUDATA ON ECTM_ECTM_CPU.ID = ECTM_ECTM_CPUDATA.CPUID " & _
                          "GROUP BY ECTM_ECTM_Computer.NAME, ECTM_ECTM_COMPANY.NAME, ECTM_ECTM_CPU.SERIALNUMBER, ECTM_ECTM_CPU.POSITION " & _
                          "HAVING (((ECTM_ECTM_Computer.NAME) Like "*" & forms!FRM_SearchMulti!SrchText & "*")); "

SearchResults.ColumnCount = 5
End Sub

Formatting could be better still, but I leave that up to you.
Also using default names for controls is a bad idea and bad practice, strongly suggest you give your controls proper names.

Hi namliam,

Thank you very much for your reply. When I tried your above modified Code. I am getting the Error. "Run-time error '13': Type Mismatch.

Can you please help me.

Best Regards,
Rocky

 

[namliam]

Whoops overlooked the two " inside the sql

Option Compare Database

Private Sub Command6_Click()

SearchResults.RowSource = "SELECT ECTM_ECTM_Computer.NAME, ECTM_ECTM_COMPANY.NAME, ECTM_ECTM_CPU.SERIALNUMBER, ECTM_ECTM_CPU.POSITION, Max(ECTM_ECTM_CPUDATA.DATADATE) AS MaxOfDATADATE " & _
                          "FROM ((ECTM_ECTM_Computer " & _
                          "INNER JOIN ECTM_ECTM_COMPANY ON ECTM_ECTM_Computer.COMPANYID = ECTM_ECTM_COMPANY.ID) " & _
                          "INNER JOIN ECTM_ECTM_CPU ON ECTM_ECTM_Computer.ID = ECTM_ECTM_CPU.ComputerID) " & _
                          "INNER JOIN ECTM_ECTM_CPUDATA ON ECTM_ECTM_CPU.ID = ECTM_ECTM_CPUDATA.CPUID " & _
                          "GROUP BY ECTM_ECTM_Computer.NAME, ECTM_ECTM_COMPANY.NAME, ECTM_ECTM_CPU.SERIALNUMBER, ECTM_ECTM_CPU.POSITION " & _
                          "HAVING (((ECTM_ECTM_Computer.NAME) Like ""*"" & forms!FRM_SearchMulti!SrchText & ""*"")); "

SearchResults.ColumnCount = 5
End Sub

I hope that does it

 

[rocky09]

Whoops overlooked the two " inside the sql

Option Compare Database

Private Sub Command6_Click()

SearchResults.RowSource = "SELECT ECTM_ECTM_Computer.NAME, ECTM_ECTM_COMPANY.NAME, ECTM_ECTM_CPU.SERIALNUMBER, ECTM_ECTM_CPU.POSITION, Max(ECTM_ECTM_CPUDATA.DATADATE) AS MaxOfDATADATE " & _
                          "FROM ((ECTM_ECTM_Computer " & _
                          "INNER JOIN ECTM_ECTM_COMPANY ON ECTM_ECTM_Computer.COMPANYID = ECTM_ECTM_COMPANY.ID) " & _
                          "INNER JOIN ECTM_ECTM_CPU ON ECTM_ECTM_Computer.ID = ECTM_ECTM_CPU.ComputerID) " & _
                          "INNER JOIN ECTM_ECTM_CPUDATA ON ECTM_ECTM_CPU.ID = ECTM_ECTM_CPUDATA.CPUID " & _
                          "GROUP BY ECTM_ECTM_Computer.NAME, ECTM_ECTM_COMPANY.NAME, ECTM_ECTM_CPU.SERIALNUMBER, ECTM_ECTM_CPU.POSITION " & _
                          "HAVING (((ECTM_ECTM_Computer.NAME) Like ""*"" & forms!FRM_SearchMulti!SrchText & ""*"")); "

SearchResults.ColumnCount = 5
End Sub

I hope that does it

Wow.. now. no error.
Thank you. But, I am not getting results in the list box.

 

[namliam]

That I cant help you with, not in details anyways
Some options
Perhaps there are no results?
Did you add a requery of the combobox someplace?

 

[rocky09]

That I cant help you with, not in details anyways
Some options
Perhaps there are no results?
Did you add a requery of the combobox someplace?

Hi namliam;

Thank you very much. I will try myself and will update you soon.

I didn't add the requery. I don't have combobox on the Form. I have only one Text Box, a button and list box.

 

[namliam]

well replace combobox by listbox in that case in my earlier statement.