Forum Discussion
#Error in Query (nulls?)
Hello Experts,
I am getting a #error in the below, I do have nulls on the Reference. How could I solve this issue? Not sure if its due to [Reference] having nulls.
WDDateGetError: DLookUp("[WithdrawalDate]","qryWDDate","[Reference] = " & [Invoice No])
Hi Tony,
> the format is short text for both of those fields.
Ok, then I misunderstood your description. I thought you were getting the error only for some records. With this information it is clear that you should get the error for every record because the syntax for text is different than for numbers. You have to put the text parameter in quotation marks:
WDDateGetError: DLookUp("MaxOfWithdrawalDate","qryWDDate","Reference = '" & [Invoice No] & "'"
[edited after XPS35 spotted the wrong column/field name]
Servus
Karl
****************
Access Forever
Access News
Access DevCon
Access-Entwickler-Konferenz AEK
6 Replies
- Although that is not your current problem: Reference is a Reserved Word, and should not be used as an object name. Just like you should not name your table "Table", or "Field". Nothing good can come from trying to confuse the Access parser.
Hi,
The error can have several causes. You shouldn't get it because of Nulls in Reference but maybe because of Nulls in [Invoice No]. To avoid that, you can try this
WDDateGetError: iif(Not IsNull([Invoice No]), DLookUp("[WithdrawalDate]","qryWDDate","[Reference] = " & [Invoice No]))
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon
Access-Entwickler-Konferenz AEKHi Karl, thank you for the response. I thought for certain that would be the solution but unfortunately, I still get the =#error. I confirm I do have nulls on [Invoice No] and [Reference].
the format is short text for both of those fields.
let me know what is the next step. thank you!
If it makes a difference, the query is as follows: (its a grouped query and a max on [WithdrawalDate])
SELECT tblPmtProposalALL.ID2, tblPmtProposalALL.DDNo, tblPmtProposalALL.Reference, tblPmtProposalALL.Amount, Max(tblDraws_CAWC.WithdrawalDate) AS MaxOfWithdrawalDate
FROM (tblDraws INNER JOIN (tblCompanies INNER JOIN tblPmtProposalALL ON tblCompanies.CoID = tblPmtProposalALL.ID2) ON tblDraws.ID = tblPmtProposalALL.DrawIDfk) INNER JOIN tblDraws_CAWC ON tblDraws.ID = tblDraws_CAWC.IDDraws
GROUP BY tblPmtProposalALL.ID2, tblPmtProposalALL.DDNo, tblPmtProposalALL.Reference, tblPmtProposalALL.Amount
ORDER BY tblPmtProposalALL.DDNo;- You do a lookup on WithdrawalDate, but in the query that field is renamed to MaxOfWithdrawalDate. Maybe that is (one of) the problem(s).
