condition

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

I have the following query:

SELECT
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
Format(Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.[CountOfLoan Acct #]),"#,###.#") AS [SumOfCountOfLoan Acct #], "line2" AS line
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY
WHERE
(((tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)
Is Not Null And
(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)
Like IIf([forms]![frm_criteria]![tester1] Like "[a-z]*","*" &
[forms]![frm_criteria]![tester1] & "*","*")))
GROUP BY
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
IIf([forms]![frm_criteria]![tester1] Like "[a-z]*","all
resolved",[ResolvedStatus]);

I would like to change the first column to something like:

if a form field has a value:
SELECT
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
....

but if the form field is null:
SELECT "all resolved" AS ResolvedStatus,...

How can I change my query so that the first column in the select statement
depends on whethr or not there is a value in the form's field?

Thanks ni advance,
geebee
 
You'll need to use a new fieldName, Try

Select IIf([forms]![frm_criteria]![tester1] Is Null,"all resolved" ,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)AS NewResolvedStatus
 
hi,

it's not working how I want it to. for example, if there is a value in the
form field, the query returns results filtered by the form field and grouped
by that one value, but if the form field is null, it returns all rows,
instead of grouping all rows by the column alias and then by date. here is
what I have now:

Select IIf([forms]![frm_criteria]![tester1] Is Null,"all resolved" ,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)AS
NewResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
Format(Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.[CountOfLoan Acct #]),"#,###.#") AS [SumOfCountOfLoan Acct #], "line2" AS line
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY
WHERE
(((tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)
Is Not Null And
(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)
Like IIf([forms]![frm_criteria]![tester1] Like "[a-z]*","*" &
[forms]![frm_criteria]![tester1] & "*","*")))
GROUP BY
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
IIf([forms]![frm_criteria]![tester1] Like "[a-z]*","all
resolved",[ResolvedStatus]);



Ofer Cohen said:
You'll need to use a new fieldName, Try

Select IIf([forms]![frm_criteria]![tester1] Is Null,"all resolved" ,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)AS NewResolvedStatus


--
Good Luck
BS"D


geebee said:
hi,

I have the following query:

SELECT
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
Format(Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.[CountOfLoan Acct #]),"#,###.#") AS [SumOfCountOfLoan Acct #], "line2" AS line
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY
WHERE
(((tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)
Is Not Null And
(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)
Like IIf([forms]![frm_criteria]![tester1] Like "[a-z]*","*" &
[forms]![frm_criteria]![tester1] & "*","*")))
GROUP BY
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
IIf([forms]![frm_criteria]![tester1] Like "[a-z]*","all
resolved",[ResolvedStatus]);

I would like to change the first column to something like:

if a form field has a value:
SELECT
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
...

but if the form field is null:
SELECT "all resolved" AS ResolvedStatus,...

How can I change my query so that the first column in the select statement
depends on whethr or not there is a value in the form's field?

Thanks ni advance,
geebee
Click to expand...
Click to expand...
 
Try adding the IIf to the Group By

Select IIf([forms]![frm_criteria]![tester1] Is Null,"all resolved" ,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)AS
NewResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
Format(Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.[CountOfLoan Acct #]),"#,###.#") AS [SumOfCountOfLoan Acct #], "line2" AS line
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY
WHERE
(((tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)
Is Not Null And
(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)
Like IIf([forms]![frm_criteria]![tester1] Like "[a-z]*","*" &
[forms]![frm_criteria]![tester1] & "*","*")))
GROUP BY
IIf([forms]![frm_criteria]![tester1] Is Null,"all resolved" ,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus),
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
IIf([forms]![frm_criteria]![tester1] Like "[a-z]*","all
resolved",[ResolvedStatus]);
--
Good Luck
BS"D


geebee said:
hi,

it's not working how I want it to. for example, if there is a value in the
form field, the query returns results filtered by the form field and grouped
by that one value, but if the form field is null, it returns all rows,
instead of grouping all rows by the column alias and then by date. here is
what I have now:

Select IIf([forms]![frm_criteria]![tester1] Is Null,"all resolved" ,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)AS
NewResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
Format(Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.[CountOfLoan Acct #]),"#,###.#") AS [SumOfCountOfLoan Acct #], "line2" AS line
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY
WHERE
(((tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)
Is Not Null And
(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)
Like IIf([forms]![frm_criteria]![tester1] Like "[a-z]*","*" &
[forms]![frm_criteria]![tester1] & "*","*")))
GROUP BY
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
IIf([forms]![frm_criteria]![tester1] Like "[a-z]*","all
resolved",[ResolvedStatus]);



Ofer Cohen said:
You'll need to use a new fieldName, Try

Select IIf([forms]![frm_criteria]![tester1] Is Null,"all resolved" ,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)AS NewResolvedStatus


--
Good Luck
BS"D


geebee said:
hi,

I have the following query:

SELECT
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
Format(Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.[CountOfLoan Acct #]),"#,###.#") AS [SumOfCountOfLoan Acct #], "line2" AS line
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY
WHERE
(((tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)
Is Not Null And
(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus)
Like IIf([forms]![frm_criteria]![tester1] Like "[a-z]*","*" &
[forms]![frm_criteria]![tester1] & "*","*")))
GROUP BY
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
IIf([forms]![frm_criteria]![tester1] Like "[a-z]*","all
resolved",[ResolvedStatus]);

I would like to change the first column to something like:

if a form field has a value:
SELECT
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedStatus,
...

but if the form field is null:
SELECT "all resolved" AS ResolvedStatus,...

How can I change my query so that the first column in the select statement
depends on whethr or not there is a value in the form's field?

Thanks ni advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
Back
Top