[RESOLVED] Simple Criteria Expression?

[lilmark]

Using access 2000.

I created a query in design mode and have two tables linked by acct number. I have all the fields from both tables listed below in the field columns.

I added two addition caluclated fields.

Funding Date: DateAdd("yyyy",1,[FundDate]) 'this adds a year to the date
Date Diff: DateDiff("d",[ExpireDate],[Funding Date]) 'displays the difference in days between the two dates

On the criteria for Date Diff I put... >= 60 .... so it will display any record which has expired 60 or more days before the Funding Date.

However, when I try to run the query it asks me to enter the Parameter Value for Funding Date. Why? If I run the query without the criteria it shows them all no problem, but as soon as I use the criteria I get that message. Am I over looking something?

[salvelinus]

Probably either a misspelling or incorrect use of the field name. I don't build queries in design view, but if your calculated field is "Funding Date", you would need to enclose it in brackets - "[Funding Date]". Access requires brackets for any fieldnames separated with a space, or if it's a keyword.

[lilmark]

Probably either a misspelling or incorrect use of the field name. I don't build queries in design view, but if your calculated field is "Funding Date", you would need to enclose it in brackets - "[Funding Date]". Access requires brackets for any fieldnames separated with a space, or if it's a keyword.

The spelling is correct. That was the first thing I double checked. If you look at the the code above the Funding Date is eclosed in brackets in the calculations. The part of Funding Date which is not in brackets (the one before the colon) cannot be enclosed with brackets.

[salvelinus]

Well, post the SQL, that would be easier to check. One possibility is that you're using a calculated field in another calculated field, and when the query gets to that point, it may not be available yet for the second calculation.?

[lilmark]

Well, post the SQL, that would be easier to check. One possibility is that you're using a calculated field in another calculated field, and when the query gets to that point, it may not be available yet for the second calculation.?

Here is the SQL coding.....I HATE SQL...just so you know


SELECT tblADTFunding.MonitoredAcct, tblADTFunding.FundDate, tblADTFunding.[Inv#], tblADTFunding.ADTFundAmt, tblADTFunding.ADTACHFundAmt, tblADTFunding.Notes, tblADTFunding.Status, tblBankInfo.ACHType, tblBankInfo.ABANum, tblBankInfo.ACHAcctNum, tblBankInfo.ExpireDate, tblBankInfo.ACHDay, tblBankInfo.CW2Num, DateAdd("yyyy",1,[FundDate]) AS [Funding Date], DateDiff("d",[ExpireDate],[Funding Date]) AS [Date Diff]
FROM tblADTFunding INNER JOIN tblBankInfo ON tblADTFunding.MonitoredAcct = tblBankInfo.MonitoredAcct
WHERE (((DateDiff("d",[ExpireDate],[Funding Date]))>=60));

[si_the_geek]

The problem is that fields (or calculated values) are not given their aliases (eg: [Funding Date]) until after the SQL has run.. so you need to use the full calculation instead of the alias you have given it, eg:

.. AS [Funding Date], DateDiff("d",[ExpireDate],DateAdd("yyyy",1,[FundDate])) AS [Date Diff]

[lilmark]

That made it work. It was calculating it before the previous calculation had even been given the value. Makes so much sense when someone spells it out for you in crayons. Thanks!