show field based on criteria

[SylvieB]

in the query, i have 3 fields; RMAnumber,RepairStatus (Open or closed) and
Days (calculated from Date Reported when an RMA number is created until
current day) all from the same table; on my query, i need to pull out all
records but in the "Days" field, only the number of days should show for
"Open" RepairStatus. In other words, if the repairstatus for an RMA is
closed, I don't need
to calculate the days since it is closed. Only for when the repairstatus is
open. I still however want to see all records.
How do i do that?
Thanks in advance for any help.

 

[Jeff Boyce]

Open your query in design view.

Under your [Days] field, in the Selection Criterion row/"cell", put "Open".


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SylvieB]

Jeff,
Thank you for your prompt answer. Unfortunately that does not work because
if I put "open" under the [Day] field, the field does not know what it refers
to. when i run the query, it comes up with only one record and that should be
lots of them.

Sylvie

Open your query in design view.

Under your [Days] field, in the Selection Criterion row/"cell", put "Open".


Regards

Jeff Boyce
Microsoft Office/Access MVP

in the query, i have 3 fields; RMAnumber,RepairStatus (Open or closed) and
Days (calculated from Date Reported when an RMA number is created until
current day) all from the same table; on my query, i need to pull out all
records but in the "Days" field, only the number of days should show for
"Open" RepairStatus. In other words, if the repairstatus for an RMA is
closed, I don't need
to calculate the days since it is closed. Only for when the repairstatus
is
open. I still however want to see all records.
How do i do that?
Thanks in advance for any help.

 

[Jeff Boyce]

Sylvie

Is there a chance that your underlying table is using a "lookup" datatype
for that field? If so, what Access stores in the table is NOT what you see,
so looking for "Open" wouldn't work.

Please post the SQL statement of your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Thank you for your prompt answer. Unfortunately that does not work because
if I put "open" under the [Day] field, the field does not know what it
refers
to. when i run the query, it comes up with only one record and that should
be
lots of them.

Sylvie

Open your query in design view.

Under your [Days] field, in the Selection Criterion row/"cell", put
"Open".


Regards

Jeff Boyce
Microsoft Office/Access MVP

in the query, i have 3 fields; RMAnumber,RepairStatus (Open or closed)
and
Days (calculated from Date Reported when an RMA number is created until
current day) all from the same table; on my query, i need to pull out
all
records but in the "Days" field, only the number of days should show
for
"Open" RepairStatus. In other words, if the repairstatus for an RMA is
closed, I don't need
to calculate the days since it is closed. Only for when the
repairstatus
is
open. I still however want to see all records.
How do i do that?
Thanks in advance for any help.

 

[SylvieB]

Jeff,
See Below. I hope that's what you were asking, if not let me know. My query
is simple. I created it running the wizard. Thanks for your help.


SELECT tblMTR.MTRID, tblMTR.RMANumber, tblMTR.MTRTitle, tblMTR.DateReported,
DateDiff("d",[DateReported],Date()) AS Days, tblMTR.[Inspection Result],
tblStatus.*
FROM tblStatus INNER JOIN tblMTR ON tblStatus.StatusID = tblMTR.RepairStatus
WHERE (((tblMTR.[Inspection Result])=1))
ORDER BY DateDiff("d",[DateReported],Date());

Sylvie

Is there a chance that your underlying table is using a "lookup" datatype
for that field? If so, what Access stores in the table is NOT what you see,
so looking for "Open" wouldn't work.

Please post the SQL statement of your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Thank you for your prompt answer. Unfortunately that does not work because
if I put "open" under the [Day] field, the field does not know what it
refers
to. when i run the query, it comes up with only one record and that should
be
lots of them.

Sylvie

Open your query in design view.

Under your [Days] field, in the Selection Criterion row/"cell", put
"Open".


Regards

Jeff Boyce
Microsoft Office/Access MVP



in the query, i have 3 fields; RMAnumber,RepairStatus (Open or closed)
and
Days (calculated from Date Reported when an RMA number is created until
current day) all from the same table; on my query, i need to pull out
all
records but in the "Days" field, only the number of days should show
for
"Open" RepairStatus. In other words, if the repairstatus for an RMA is
closed, I don't need
to calculate the days since it is closed. Only for when the
repairstatus
is
open. I still however want to see all records.
How do i do that?
Thanks in advance for any help.

 

[Jeff Boyce]

So in keeping with my previous post, if either tblStatus.StatusID or
tblMTR.RepairStatus are "lookup" datatypes (check the table definitions to
see), or if [StatusID] and [RepairStatus] are NOT the same datatype, I'd
expect there to be problems. Please check these next...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
See Below. I hope that's what you were asking, if not let me know. My
query
is simple. I created it running the wizard. Thanks for your help.


SELECT tblMTR.MTRID, tblMTR.RMANumber, tblMTR.MTRTitle,
tblMTR.DateReported,
DateDiff("d",[DateReported],Date()) AS Days, tblMTR.[Inspection Result],
tblStatus.*
FROM tblStatus INNER JOIN tblMTR ON tblStatus.StatusID =
tblMTR.RepairStatus
WHERE (((tblMTR.[Inspection Result])=1))
ORDER BY DateDiff("d",[DateReported],Date());

Sylvie

Is there a chance that your underlying table is using a "lookup" datatype
for that field? If so, what Access stores in the table is NOT what you
see,
so looking for "Open" wouldn't work.

Please post the SQL statement of your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Thank you for your prompt answer. Unfortunately that does not work
because
if I put "open" under the [Day] field, the field does not know what it
refers
to. when i run the query, it comes up with only one record and that
should
be
lots of them.

Sylvie

:

Open your query in design view.

Under your [Days] field, in the Selection Criterion row/"cell", put
"Open".


Regards

Jeff Boyce
Microsoft Office/Access MVP



in the query, i have 3 fields; RMAnumber,RepairStatus (Open or
closed)
and
Days (calculated from Date Reported when an RMA number is created
until
current day) all from the same table; on my query, i need to pull
out
all
records but in the "Days" field, only the number of days should show
for
"Open" RepairStatus. In other words, if the repairstatus for an RMA
is
closed, I don't need
to calculate the days since it is closed. Only for when the
repairstatus
is
open. I still however want to see all records.
How do i do that?
Thanks in advance for any help.