Hi,
The problem is that you are setting up a condition of "comments =
computed comments". So where there is a comments column that is null, the
computation, and therefore the query, bombs; for reasons previously stated.
You need to put the computation in the select portion of the query if you
actually want to see the results. Now, I see that you want to list rows
where there are comments and where there are not not any comments. So we
will have to deal with that in a slightly different method. This method will
ensure that Nulls get converted into zero-length strings ("") in order to
prevent the computation from bombing. You can do this by concatenating a
zero-length string to the column. So the computation will become:
Trim(Right([COMMENTS] & "", Len([COMMENTS] & "") - InStrRev([COMMENTS] & "",
",")))
And you will not use a condition on the comments column so that you get
all rows.
Also, for some reason you are doing a grouping where you are not using
any aggregate functions (Max(), Min(), Avg(), etc.). So change that to a
regular select query instead of a summary query. Try:
SELECT dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname] AS [Player Name], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname] AS [Adjusted by],
Trim(Right([COMMENTS],Len([COMMENTS])-InStrRev([COMMENTS],","))) AS
ACTUAL_COMMENTS
FROM ((((dbo_CDS_AUDITDETAIL INNER JOIN dbo_CDS_AUDITTRAIL ON
dbo_CDS_AUDITDETAIL.AuditTrail_ID = dbo_CDS_AUDITTRAIL.AuditTrail_ID) INNER
JOIN dbo_CDS_USER ON dbo_CDS_AUDITTRAIL.User_ID = dbo_CDS_USER.User_ID) INNER
JOIN dbo_CDS_AUDITOBJECT ON dbo_CDS_AUDITTRAIL.AuditTrail_ID =
dbo_CDS_AUDITOBJECT.AuditTrail_ID) INNER JOIN dbo_CDS_PLAYER ON
dbo_CDS_AUDITOBJECT.Meta_ID = dbo_CDS_PLAYER.Player_ID) INNER JOIN
dbo_PBT_PROMO_ADJUSTMENT ON dbo_CDS_PLAYER.Player_ID =
dbo_PBT_PROMO_ADJUSTMENT.Player_ID
WHERE dbo_CDS_AUDITTRAIL.AuditGamingdate Between #9/3/2009# And
#9/4/2009# AND dbo_CDS_AUDITTRAIL.AuditEvent_ID=2630;
Hopefully I got that all right. Note that sometimes when trying to get
something to work, it is easier to attempt it in a simple query until you
figure it out and understand what is happening. Then transfer it into your
more complicated query. So if I did not get it right, try doing a simple
select of the computed column from the table that contains the comments.
Good Luck,
Clifford Bass
slotmgr70 said:
Hi Clifford,
I'm still getting an error, but it's probably because I'm not very good with
SQL scripts. Here's the script I have so far. Can you take a look at it and
suggest a solution:
SELECT dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname] AS [Player Name], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname] AS [Adjusted by]
FROM ((((dbo_CDS_AUDITDETAIL INNER JOIN dbo_CDS_AUDITTRAIL ON
dbo_CDS_AUDITDETAIL.AuditTrail_ID = dbo_CDS_AUDITTRAIL.AuditTrail_ID) INNER
JOIN dbo_CDS_USER ON dbo_CDS_AUDITTRAIL.User_ID = dbo_CDS_USER.User_ID) INNER
JOIN dbo_CDS_AUDITOBJECT ON dbo_CDS_AUDITTRAIL.AuditTrail_ID =
dbo_CDS_AUDITOBJECT.AuditTrail_ID) INNER JOIN dbo_CDS_PLAYER ON
dbo_CDS_AUDITOBJECT.Meta_ID = dbo_CDS_PLAYER.Player_ID) INNER JOIN
dbo_PBT_PROMO_ADJUSTMENT ON dbo_CDS_PLAYER.Player_ID =
dbo_PBT_PROMO_ADJUSTMENT.Player_ID
WHERE
(((dbo_CDS_AUDITDETAIL.Comments)=Trim(Right([COMMENTS],Len([COMMENTS])-InStrRev([COMMENTS],",")))))
GROUP BY dbo_CDS_AUDITTRAIL.AuditGamingdate,
dbo_PBT_PROMO_ADJUSTMENT.Player_ID, [dbo_CDS_PLAYER.lastname] & ", " &
[dbo_CDS_PLAYER.firstname], [dbo_CDS_USER.lastname] & " ," &
[dbo_CDS_USER.firstname], dbo_CDS_AUDITTRAIL.AuditEvent_ID
HAVING (((dbo_CDS_AUDITTRAIL.AuditGamingdate) Between #9/3/2009# And
#9/4/2009#) AND ((dbo_CDS_AUDITTRAIL.AuditEvent_ID)=2630));
