Hi guys,
i just faced a problem when trying to execute the following query.I searched for the exception on the internet and found that using a reserved keyword might raise this issue,and using brackets may solve this problem.But i use brackets all the time in my sql statements but still the exception 'IErrorInfo.GetDescription failed with E_FAIL(0x80004005).' comes up.
Please give me some hand so i could proceed to my task.

qry2 = "SELECT * FROM [per_diem_accomodation] WHERE [project number]='" & projcode & "' AND [employee number]='" & empcode & "'" & _
        "AND [current month]=(SELECT MAX(current month) FROM [per_diem_accomodation] WHERE [project number]='" & projcode & "' AND [employee number]='" & empcode & "')"

Try

qry2 = "SELECT * FROM [per_diem_accomodation]  " &
       " WHERE [project number]  = '" & projcode & "'" &
       "   AND [employee number] = '" & empcode  & "'" & 
       "   AND [current month]   =  " &
       "       (SELECT MAX([current month])    " &
       "          FROM [per_diem_accomodation] " &
       "         WHERE [project number]  = '" & projcode & "'" &
       "           AND [employee number] = '" & empcode  & "')"

You left out the square brackets inside the MAX statement. I find that creatinig column names with embedded blanks is usually a bad idea. I finid it easier to debug SQL statements when they are formatted as above across several lines. Ii find them even easier to debug when using parameterized queries.

'info: you can use column name as without spaces. 
'For example 
'use :          projectnumber 
'instead of     [project number]
'in your database table and code also. it will help you sure

Thank you Reverend,it worked.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.