DLookup Criteria #Error

[nvr]

I wish to make one field (target field) on a form use a DLookup based on criteria from another field (source field)on the same form. I did have it working, but now it seems to #Error all the time. I cant for the life of me work out what i have changed.

On the form, the target field is NoOfTerms and the source field is Amount

I am setting control source to:

=DLookup("[Count]","[tblMatrix]","Amount=[Amount]") which produces #error.

If I change it to
=DLookup("[Count]","[tblMatrix]","Amount=18") it works.

All help appreciated. Thanks.

 

[RoyVidar]

Try:
[tt]=DLookup("[Count]","[tblMatrix]","Amount=" & [Amount])[/tt]

It can still be error, if no amount is found, if so, try:
[tt]=NZ(DLookup("[Count]","[tblMatrix]","Amount=" & [Amount]),0)[/tt]

Roy-Vidar

 

[nvr]

Hmmn. I've tried these suggestions and now get #Name? error. I'm wondering if the field references on the form have changed in some way ... because it was working ... as were 3 other similar fields. Any ideas?

 

[RoyVidar]

From what I see, one of the culprits for the #Name error, could be using the same name on controls as is the controlsource of a control.

I make it a rule to alter the names of all controls I'm either manipulating programatically, or use in an expression. The most common naming convention, is to prefix text controls with txt, combos with cbo, listboxes with lst... Try that, ie txtAmount as name of the control, and see if it helps.

There's also the use of the reserved word "Count" as field name, which should be avoided.

Another thing here, is that Amount usually is a single/double field, and if so, due to how floating point variables are represented, you may not get a match, but with the NZ function, it should at least give 0, not #Name

I'd suggest trying different variations over the theme, be sure to use the correct field and control names, to find a version that works.

Then - if something did infact work, and now doesn't, without having done any alterations, my first suspect is that there's some corruption going on. Try the following (on a copy of the database) which has resolved all my code corruptions over the last years Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times, though it is marketed as someting else;-)

Roy-Vidar

 

[nvr]

Tried all you suggested and the decided to point it to another control, [MerchantId] ... and guess what? It worked ... although it obviously didnt find a result! So I deleted the [Amount] control and straightaway recreated it ... and it works! Eh? Exactly!

Thanks for your suggestions Roy-Vidar. If you didnt solve it directly, you certainly gave me some new things to think about which resulted in me getting it sorted eventually ... another few hours wasted ... ho hum.