Query for 1st field, if blank then query 2nd field but not both.

M

mcduff22

I have 2 fields (Date Created from master table and Review Date from a sub
table). All records have a “Date Created†entry. Not all records have a
“Review Date†entered. I need to create a query that looks at the last
Review Date and if one does not exist THEN look at the Date Created. The
query asks me what “MM YYYY†I want to query records. My issue - I have a
Select Query written and it has 2 lines of criteria one for each field asking
[Enter MM YYYY]. It works well for records that have a Review Date in my
criteria or if there isn’t a Review Date, then it defaults to Date Created.
But, I get records that have the Review Date greater than what I want to
query due to it meeting the Date Created criteria. Any suggestions?

Want the following results….
Date Created Date Reviewed
01 2001 01 2009
01 2009 (blank)

Don’t want….
Date Created Date Reviewed
01 2009 01 2010
 
M

mcduff22

I asked for 01 2009 in my query. I apologize for neglecting to include that
information.
 
K

KARL DEWEY

Open the query in design view, click VIEW - SQL View, highlight all in the
window it opens, copy, and paste in a post.

mcduff22 said:
I asked for 01 2009 in my query. I apologize for neglecting to include that
information.

mcduff22 said:
I have 2 fields (Date Created from master table and Review Date from a sub
table). All records have a “Date Created†entry. Not all records have a
“Review Date†entered. I need to create a query that looks at the last
Review Date and if one does not exist THEN look at the Date Created. The
query asks me what “MM YYYY†I want to query records. My issue - I have a
Select Query written and it has 2 lines of criteria one for each field asking
[Enter MM YYYY]. It works well for records that have a Review Date in my
criteria or if there isn’t a Review Date, then it defaults to Date Created.
But, I get records that have the Review Date greater than what I want to
query due to it meeting the Date Created criteria. Any suggestions?

Want the following results….
Date Created Date Reviewed
01 2001 01 2009
01 2009 (blank)

Don’t want….
Date Created Date Reviewed
01 2009 01 2010
Click to expand...
Click to expand...
 
M

mcduff22

SELECT [Exemption Reviewed subQuery].id, [Exemption Reviewed subQuery].TIN,
[Exemption Reviewed subQuery].Name, [Exemption Reviewed
subQuery].DateCreated, [Exemption Reviewed subQuery].[Created MTD],
[Exemption Reviewed subQuery].MaxOfreviewdate, [Exemption Reviewed
subQuery].[Review MTD], [Exemption Reviewed subQuery].[Bank 1], [Exemption
Reviewed subQuery].[Bank 80], [Exemption Reviewed subQuery].ExemptionType,
[Exemption Reviewed subQuery].[Charter#], [Exemption Reviewed
subQuery].[FDIC#], [Exemption Reviewed subQuery].Market, [Exemption Reviewed
subQuery].[CostCenter#], [Exemption Reviewed subQuery].Mailstop, [Exemption
Reviewed subQuery].Contact, [Exemption Reviewed subQuery].ParentCo,
[Exemption Reviewed subQuery].DateofOrigin, [Exemption Reviewed
subQuery].Ticker, [Exemption Reviewed subQuery].NASDAQ, [Exemption Reviewed
subQuery].NYSE, [Exemption Reviewed subQuery].AMEX, [Exemption Reviewed
subQuery].Comments, [Exemption Reviewed subQuery].EmpID
FROM [Exemption Reviewed subQuery]
WHERE ((([Exemption Reviewed subQuery].[Created MTD])=[enter mm yyyy])) OR
((([Exemption Reviewed subQuery].[Review MTD])=[enter mm yyyy]));


KARL DEWEY said:
Open the query in design view, click VIEW - SQL View, highlight all in the
window it opens, copy, and paste in a post.

mcduff22 said:
I asked for 01 2009 in my query. I apologize for neglecting to include that
information.

mcduff22 said:
I have 2 fields (Date Created from master table and Review Date from a sub
table). All records have a “Date Created†entry. Not all records have a
“Review Date†entered. I need to create a query that looks at the last
Review Date and if one does not exist THEN look at the Date Created. The
query asks me what “MM YYYY†I want to query records. My issue - I have a
Select Query written and it has 2 lines of criteria one for each field asking
[Enter MM YYYY]. It works well for records that have a Review Date in my
criteria or if there isn’t a Review Date, then it defaults to Date Created.
But, I get records that have the Review Date greater than what I want to
query due to it meeting the Date Created criteria. Any suggestions?

Want the following results….
Date Created Date Reviewed
01 2001 01 2009
01 2009 (blank)

Don’t want….
Date Created Date Reviewed
01 2009 01 2010
Click to expand...
Click to expand...
Click to expand...
 
M

mcduff22

SELECT [Exemption Reviewed subQuery].id, [Exemption Reviewed subQuery].TIN,
[Exemption Reviewed subQuery].Name, [Exemption Reviewed
subQuery].DateCreated, [Exemption Reviewed subQuery].[Created MTD],
[Exemption Reviewed subQuery].MaxOfreviewdate, [Exemption Reviewed
subQuery].[Review MTD], [Exemption Reviewed subQuery].[Bank 1], [Exemption
Reviewed subQuery].[Bank 80], [Exemption Reviewed subQuery].ExemptionType,
[Exemption Reviewed subQuery].[Charter#], [Exemption Reviewed
subQuery].[FDIC#], [Exemption Reviewed subQuery].Market, [Exemption Reviewed
subQuery].[CostCenter#], [Exemption Reviewed subQuery].Mailstop, [Exemption
Reviewed subQuery].Contact, [Exemption Reviewed subQuery].ParentCo,
[Exemption Reviewed subQuery].DateofOrigin, [Exemption Reviewed
subQuery].Ticker, [Exemption Reviewed subQuery].NASDAQ, [Exemption Reviewed
subQuery].NYSE, [Exemption Reviewed subQuery].AMEX, [Exemption Reviewed
subQuery].Comments, [Exemption Reviewed subQuery].EmpID
FROM [Exemption Reviewed subQuery]
WHERE ((([Exemption Reviewed subQuery].[Created MTD])=[enter mm yyyy])) OR
((([Exemption Reviewed subQuery].[Review MTD])=[enter mm yyyy]));


KARL DEWEY said:
Open the query in design view, click VIEW - SQL View, highlight all in the
window it opens, copy, and paste in a post.

mcduff22 said:
I asked for 01 2009 in my query. I apologize for neglecting to include that
information.

mcduff22 said:
I have 2 fields (Date Created from master table and Review Date from a sub
table). All records have a “Date Created†entry. Not all records have a
“Review Date†entered. I need to create a query that looks at the last
Review Date and if one does not exist THEN look at the Date Created. The
query asks me what “MM YYYY†I want to query records. My issue - I have a
Select Query written and it has 2 lines of criteria one for each field asking
[Enter MM YYYY]. It works well for records that have a Review Date in my
criteria or if there isn’t a Review Date, then it defaults to Date Created.
But, I get records that have the Review Date greater than what I want to
query due to it meeting the Date Created criteria. Any suggestions?

Want the following results….
Date Created Date Reviewed
01 2001 01 2009
01 2009 (blank)

Don’t want….
Date Created Date Reviewed
01 2009 01 2010
Click to expand...
Click to expand...
Click to expand...
 
M

mcduff22

SELECT [Exemption Reviewed subQuery].id, [Exemption Reviewed subQuery].TIN,
[Exemption Reviewed subQuery].Name, [Exemption Reviewed
subQuery].DateCreated, [Exemption Reviewed subQuery].[Created MTD],
[Exemption Reviewed subQuery].MaxOfreviewdate, [Exemption Reviewed
subQuery].[Review MTD], [Exemption Reviewed subQuery].[Bank 1], [Exemption
Reviewed subQuery].[Bank 80], [Exemption Reviewed subQuery].ExemptionType,
[Exemption Reviewed subQuery].[Charter#], [Exemption Reviewed
subQuery].[FDIC#], [Exemption Reviewed subQuery].Market, [Exemption Reviewed
subQuery].[CostCenter#], [Exemption Reviewed subQuery].Mailstop, [Exemption
Reviewed subQuery].Contact, [Exemption Reviewed subQuery].ParentCo,
[Exemption Reviewed subQuery].DateofOrigin, [Exemption Reviewed
subQuery].Ticker, [Exemption Reviewed subQuery].NASDAQ, [Exemption Reviewed
subQuery].NYSE, [Exemption Reviewed subQuery].AMEX, [Exemption Reviewed
subQuery].Comments, [Exemption Reviewed subQuery].EmpID
FROM [Exemption Reviewed subQuery]
WHERE ((([Exemption Reviewed subQuery].[Created MTD])=[enter mm yyyy])) OR
((([Exemption Reviewed subQuery].[Review MTD])=[enter mm yyyy]));


KARL DEWEY said:
Open the query in design view, click VIEW - SQL View, highlight all in the
window it opens, copy, and paste in a post.

mcduff22 said:
I asked for 01 2009 in my query. I apologize for neglecting to include that
information.

mcduff22 said:
I have 2 fields (Date Created from master table and Review Date from a sub
table). All records have a “Date Created†entry. Not all records have a
“Review Date†entered. I need to create a query that looks at the last
Review Date and if one does not exist THEN look at the Date Created. The
query asks me what “MM YYYY†I want to query records. My issue - I have a
Select Query written and it has 2 lines of criteria one for each field asking
[Enter MM YYYY]. It works well for records that have a Review Date in my
criteria or if there isn’t a Review Date, then it defaults to Date Created.
But, I get records that have the Review Date greater than what I want to
query due to it meeting the Date Created criteria. Any suggestions?

Want the following results….
Date Created Date Reviewed
01 2001 01 2009
01 2009 (blank)

Don’t want….
Date Created Date Reviewed
01 2009 01 2010
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
WHERE IIF([Exemption Reviewed subQuery].[Review MTD] Is Null, [Exemption
Reviewed subQuery].[Created MTD], [Exemption Reviewed subQuery].[Review MTD])
=[enter mm yyyy];


mcduff22 said:
SELECT [Exemption Reviewed subQuery].id, [Exemption Reviewed subQuery].TIN,
[Exemption Reviewed subQuery].Name, [Exemption Reviewed
subQuery].DateCreated, [Exemption Reviewed subQuery].[Created MTD],
[Exemption Reviewed subQuery].MaxOfreviewdate, [Exemption Reviewed
subQuery].[Review MTD], [Exemption Reviewed subQuery].[Bank 1], [Exemption
Reviewed subQuery].[Bank 80], [Exemption Reviewed subQuery].ExemptionType,
[Exemption Reviewed subQuery].[Charter#], [Exemption Reviewed
subQuery].[FDIC#], [Exemption Reviewed subQuery].Market, [Exemption Reviewed
subQuery].[CostCenter#], [Exemption Reviewed subQuery].Mailstop, [Exemption
Reviewed subQuery].Contact, [Exemption Reviewed subQuery].ParentCo,
[Exemption Reviewed subQuery].DateofOrigin, [Exemption Reviewed
subQuery].Ticker, [Exemption Reviewed subQuery].NASDAQ, [Exemption Reviewed
subQuery].NYSE, [Exemption Reviewed subQuery].AMEX, [Exemption Reviewed
subQuery].Comments, [Exemption Reviewed subQuery].EmpID
FROM [Exemption Reviewed subQuery]
WHERE ((([Exemption Reviewed subQuery].[Created MTD])=[enter mm yyyy])) OR
((([Exemption Reviewed subQuery].[Review MTD])=[enter mm yyyy]));


KARL DEWEY said:
Open the query in design view, click VIEW - SQL View, highlight all in the
window it opens, copy, and paste in a post.

mcduff22 said:
I asked for 01 2009 in my query. I apologize for neglecting to include that
information.

:

I have 2 fields (Date Created from master table and Review Date from a sub
table). All records have a “Date Created†entry. Not all records have a
“Review Date†entered. I need to create a query that looks at the last
Review Date and if one does not exist THEN look at the Date Created. The
query asks me what “MM YYYY†I want to query records. My issue - I have a
Select Query written and it has 2 lines of criteria one for each field asking
[Enter MM YYYY]. It works well for records that have a Review Date in my
criteria or if there isn’t a Review Date, then it defaults to Date Created.
But, I get records that have the Review Date greater than what I want to
query due to it meeting the Date Created criteria. Any suggestions?

Want the following results….
Date Created Date Reviewed
01 2001 01 2009
01 2009 (blank)

Don’t want….
Date Created Date Reviewed
01 2009 01 2010
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

In a query, show master records even if a child record doesn't exi 2
display records from a query 2
reformat data field in a query? 1
Parameterizing in a Cross Tab Query 2
Update query with date criteria 3
Start Date/ End Date query 7
If/Then/Else Query 6
Should I use a query for this? If so, how? 2

Top