Criteria based on another field

[jlclark4]

I have a append query that i am trying to get set up correctly.



AcctNumber
RecdDate
FiledDate
FinalDate

I want Field 4 to be only those records that are Null or records that are less then RecdDate.

How would I do this? I tried a basic < RecdDate but it did not work.

[aytee111]

You are mixing apples and oranges! "I want Field 4 to be only those records that are Null or records that are less then RecdDate" - Field 4 (FinalDate?) is a field and not a record. Are you trying to populate it with something, or do you want to only append records with that criteria?

[Robeen]

Do you mean records that have a Null value in FinalDate
OR
Where the FinalDate is < RecdDate?

[jlclark4]

Sorry, I know nothing about Append queries. Thus needing the help.

I want to only append records with that criteria.

Field 4 = FinalDate (I orginally had just Field 1-4 on the question, then changed to actual names)

I am looking for:

FinalDate = Null
or
FinalDate < RecdDate

I was putting the criteria under the FinalDate field in the query.

[jlclark4]

Am I looking at this the wrong way?

[Robeen]

In your Criteriayou have a 'Between . . .' and 'Is Null' on the same Criteria Row [in different fields].
That is saying "Between these two dates AND Is Null".
Those two are contradicting each other - because they are on the same Criteria Row.

Click View [top left] and 'SQL View' and you will see what your query actually 'says'.

[jlclark4]

The Received date should be in between that date range.

Like the receive date is 2/1/2012
The Final Date is Null
It should append to the table.
B/c Final Date = Is Null

Or
The Receive date is 2/8/2012
The Final Date is 2/1/2012
It should append to the table
B/c Received Date > Final Date

Does that make sense?

[Robeen]

Can you please open your query in SQL View and paste the SQL statement in here for us to look at?

[jlclark4]

This gives more detail on the actual table names and such. Let me know if this helps or makes things worse.
This code is as is without the > then dtmClmFinDate that I attempted to do.

INSERT INTO [ZZ MasterList_ClaimsRecd] ( LMSAcctNo, UGCertNo, LenderLoanNo, LenderID, ClaimRecdDate, LenderName, ClaimFiledAmt, AcknowledgementDate, strCompNbr )
SELECT dbo_tblAccount.dblAcctNbr, dbo_tblAccount.strCertNbr AS UGCertNo, dbo_tblAccount.strLenderLnNbr AS LenderLoanNo, dbo_tblAccount.strLenderID AS LenderID, dbo_tblAccount.dtmClmRecDate AS ClaimRecdDate, LenderList.LndrName AS LenderName, dbo_tblAccount.dblLenderFiledClmAmt AS ClaimFiledAmt, Date() AS AcknowledgementDate, dbo_tblAccount.strCompNbr
FROM dbo_tblAccount LEFT JOIN LenderList ON dbo_tblAccount.strLenderID = LenderList.LndrNo
WHERE (((dbo_tblAccount.dtmClmRecDate) Between #1/1/2010# And Date()) AND ((dbo_tblAccount.dtmClmFinDate) Is Null));

So, with these actual field names, I am looking for:

dtmClmFinDate = Null
or
drmClmFinDate < dtmClmRecDate

For the dtmClmRecDate range of 1/1/2010 - Today

[Robeen]

What happens when you run this query?

[Robeen]

When you run the Select . . . part of the query - do you get the results that you want?

[jlclark4]

The query above DOES populate what it says. So it works. I need to adjust it to include:

dtmClmFinDate = Null or drmClmFinDate < dtmClmRecDate

For the dtmClmRecDate range of 1/1/2010 - Today

I have never referenced another field in a query before so that's where I am having my problem.

[Robeen]

I just ran a simple query and used two fields in the Table 'Test' to filter the records and it worked just fine.

 
SELECT [Test].ID, [Test].[Order], [Test].[Name], [Test].[Boxes], [Test].[DaysOnHold], [Test].[REASON]
FROM Test
WHERE [Test].[Boxes] >[Test].[DaysOnHold];

Tell me if this is what you need in your WHERE clause:
1. WHERE (((dbo_tblAccount.dtmClmRecDate) Between #1/1/2010# And Date()) AND ((dbo_tblAccount.dtmClmFinDate) Is Null))
. . . you already have this in your query.
AND
2. dtmClmFinDate = Null or drmClmFinDate < dtmClmRecDate
AND
3. dtmClmRecDate range of 1/1/2010 - Today

If it is, then try something like this for your WHERE clause:

 
WHERE (((dbo_tblAccount.dtmClmRecDate) Between #1/1/2010# And Date()) AND ((dbo_tblAccount.dtmClmFinDate) Is Null))
AND ((dtmClmFinDate = Null) or (drmClmFinDate < dtmClmRecDate))
AND (dtmClmRecDate > #1/1/2010#);

Note: I ran the above logic in my test query and it works. What you'll have to evaluate is whether you want AND . . . or whether you want OR - between the three parts of the WHERE clause.

Let me know if it works!!