none
WHERE clause not working RRS feed

  • 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.

    Tuesday, December 8, 2015 10:04 AM

Answers

  • 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.


    • Edited by Stefan Hoffmann Tuesday, December 8, 2015 12:54 PM
    • Proposed as answer by David_JunFeng Wednesday, December 9, 2015 7:22 AM
    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:42 PM
    Tuesday, December 8, 2015 12:54 PM

All replies

  • Use the Builder to create that reference:

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

    Tuesday, December 8, 2015 10:15 AM
  • 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.

    Tuesday, December 8, 2015 10:22 AM
  • 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?
    Tuesday, December 8, 2015 11:12 AM
  • 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.

    Tuesday, December 8, 2015 11:54 AM
  • 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??

    Tuesday, December 8, 2015 12:39 PM
  • 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.


    • Edited by Stefan Hoffmann Tuesday, December 8, 2015 12:54 PM
    • Proposed as answer by David_JunFeng Wednesday, December 9, 2015 7:22 AM
    • Marked as answer by David_JunFeng Wednesday, December 23, 2015 2:42 PM
    Tuesday, December 8, 2015 12:54 PM
  • 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.

    Tuesday, December 8, 2015 1:23 PM