|Posted: 01/14/2015, 10:19 AM
I have a query that works perfect in MS SQL Server Management Studio. So I placed it in the Before Select Event in a grid and it appears to be ignored. I get all records. There is no where selection in the Visual Query Builder.
Any suggestion as to what I am doing wrong?
Sample of SQL below. I have 4 conditions with nested parenthesis to group the logic.
*** Working SQL ***
WHERE Expiration > GETDATE()
AND ( [Group_ID] IS NULL OR ( [Group_ID] IS NOT NULL AND [Group_ID] = '4' ))
AND ( [Dept_id] IS NULL OR ( [Dept_id] IS NOT NULL AND [Dept_id] = '13' ))
AND ( [SHG_id] IS NULL OR ( [SHG_id] IS NOT NULL AND [SHG_id] = '46' ))
AND ( [Company_id] IS NULL OR ( [Company_id] IS NOT NULL AND [Company_id] = '7' ))
ORDER BY date_added desc
*** SQL in Before Select Event ***
Dim sql, connection, errormessage
Set Connection = New clsDBSmart
'sql="UPDATE roster SET exam1passfail = 1, status=6, exam1date='"&exam1date&"' where rosterid="&rosterid
sql = "SELECT ID,Group_ID,Company_id,SHG_id,Dept_id,Message,Message_type,author,Date_added,Expiration FROM announcements WHERE ( Group_ID IS NULL OR ( Group_ID IS NOT NULL AND Group_ID = '4' ))"
Sql = sql + "AND ( Dept_id IS NULL OR ( Dept_id IS NOT NULL AND Dept_id = '13' ))"
Sql = sql + "AND ( SHG_id IS NULL OR ( SHG_id IS NOT NULL AND SHG_id = '46' ))"
Sql = sql + "AND ( Company_id IS NULL OR ( Company_id IS NOT NULL AND Company_id = '7' ))"
ErrorMessage = CCProcessError(Connection)
Set Connection = Nothing
Training and LMS Developer