condition

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
 
G

Guest

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
 
G

Guest

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...
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to find Nulls in an IIF statement 12
Use wildcard in conditional query 3
Show value if no results from query 1
Query using input from Forms 7
Queries & multiple Form inputs 1
setting query criteria in an IIf statement 5
IIf statement in Criteria 3
2 Form Questions for the Group... 1

Top