WHERE clause not working

Question

I have the following statement as the row source for a list box which refuses to work as expected:

SELECT locations.contract, locations.location, locations.ID FROM locations WHERE (locations.contract=[ID]);

[ID] is a control on the form. I've tried adding some temporary boxes on the form to see what is going wrong:

one (text124) where I can type a value for [ID] - this works fine if I change the WHERE clause to reference this instead of [ID]

one (IDcontracttemp) which references the ID control which displays as expected (numeric value - I've set locations.contract to display in the list box and it is showing as expected)

one with the following statement: =IIf([text124]=[IDcontracttemp],"equal","not equal") - I tried this in case one of the items had been converted to a string. It works as expected.

Any help would be appreciated.

Answer 1

Use the Builder to create that reference:

btw, when you use controls in code or queries like that, always rename them to avoid ambiguities.

Answer 2

Thanks for the quick reply. I've already tried using the query builder and get the same result. Point taken about the renaming of controls, but in this case it is only there as a temporary measure so I can see what is going on beneath the surface.

Any other suggestions would be appreciated.

Answer 3

An update. It seems to work if I force a requery on the list box. Any idea why it is not working when I first open the form?

Answer 4

An update. It seems to work if I force a requery on the list box. Any idea why it is not working when I first open the form?

Hi Vince,

Can it be that ID is not yet defined (or has a zero-value) on the moment the List box is opened?

Imb.

Answer 5

The form is opened by a macro from another form. The macro opens the form, moves to the ID field and sets it to the required record. All of the other controls on the form (mostly subforms) are related back to the ID field so it must be defined. I have managed to get it working now, but by a very convoluted route. I cannot get it to work at all directly from [ID], but by having a non-visible unbound control =[ID] it will work as long as I have a refresh command in the macro that opens the form. I've no idea why this works and referencing [ID] directly doesn't??

Answer 6

Needing an unbound control is a hint that you use a name in your query [ID] which is the same as a control. Thus the column is used, not the control. As I said, rename always used controls. So that their name differs from the bound column name.

Answer 7

It must be something like that, lesson learnt! If I ever start a project like this again more thought will be going into names of things before I start and it's too late to change things.

Thanks for your help.