Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Table lookup value on a form - text or interger field?

Discussion in 'Information Technology' started by Bob Richardson, Nov 15, 2005.

  1. Bob Richardson

    Bob Richardson
    Expand Collapse
    Guest

    In Table Design I've used a table lookup to define a field...TimeSlot. In
    datasheet view it correctly uses a drop down list with the correct text
    values, yet it stores a number in the file...the record's key. This appears
    to be correct.

    On a form I have a drop down list for TimeSlot, as well as a 2nd drop-down
    list that needs to use the text value of the TimeSlot to determine it's
    values. The name of the TimeSlot field, on the form, is TS.

    This part of the query for the 2nd drop-down list isn't working.

    WHERE Left(TS,1)=Left(Q_Classes.ClassCode,1)

    Is TS a numeric value, since a numeric value is stored in the file, OR is TS
    a text value, since that's what is visible on the form? How can I get the
    text value for TS?
     
  2. Loading...

    Similar Threads Forum Date
    As A Child, Public Marches Of Sikhism Made Me Uncomfortable. They Still Do . Why ? Whats The Logic Hard Talk Oct 16, 2016
    Arts/Society Backyard Vegetable Garden Language, Arts & Culture Dec 27, 2013
    Heritage Now a Vegetable Market, Ranjit Singh's Royal Haveli a Picture of Neglect History of Sikhism Nov 11, 2013
    Sikhism Helium: 1984 and the "Periodic Table of Hate" (Jaspreet Singh) Book Reviews & Editorials Oct 28, 2013
    Heritage How our entire history was dumped in a horse stable History of Sikhism Oct 28, 2013

  3. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    Have a look here, then consider a redesign:

    http://www.mvps.org/access/lookupfields.htm
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access

    "Bob Richardson" <bobr at whidbey dot com> wrote in message
    news:rcadnfbmG-ol6OXeRVn-jg@whidbeytel.com...
    > In Table Design I've used a table lookup to define a field...TimeSlot. In
    > datasheet view it correctly uses a drop down list with the correct text
    > values, yet it stores a number in the file...the record's key. This

    appears
    > to be correct.
    >
    > On a form I have a drop down list for TimeSlot, as well as a 2nd drop-down
    > list that needs to use the text value of the TimeSlot to determine it's
    > values. The name of the TimeSlot field, on the form, is TS.
    >
    > This part of the query for the 2nd drop-down list isn't working.
    >
    > WHERE Left(TS,1)=Left(Q_Classes.ClassCode,1)
    >
    > Is TS a numeric value, since a numeric value is stored in the file, OR is

    TS
    > a text value, since that's what is visible on the form? How can I get the
    > text value for TS?
    >
    >
     
  4. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    TimeSlot will be whatever data type as the primary key of the table where
    TimeSlot gets its description (numeric value. This confusion is one of the
    reason why many people feel Lookup FIelds are a very bad idea (see
    http://www.mvps.org/access/lookupfields.htm at "The Access Web")

    Since you should never be working directly with tables, use a combobox on a
    form, so that you know what's going on "under the covers".

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Bob Richardson" <bobr at whidbey dot com> wrote in message
    news:rcadnfbmG-ol6OXeRVn-jg@whidbeytel.com...
    > In Table Design I've used a table lookup to define a field...TimeSlot. In
    > datasheet view it correctly uses a drop down list with the correct text
    > values, yet it stores a number in the file...the record's key. This

    appears
    > to be correct.
    >
    > On a form I have a drop down list for TimeSlot, as well as a 2nd drop-down
    > list that needs to use the text value of the TimeSlot to determine it's
    > values. The name of the TimeSlot field, on the form, is TS.
    >
    > This part of the query for the 2nd drop-down list isn't working.
    >
    > WHERE Left(TS,1)=Left(Q_Classes.ClassCode,1)
    >
    > Is TS a numeric value, since a numeric value is stored in the file, OR is

    TS
    > a text value, since that's what is visible on the form? How can I get the
    > text value for TS?
    >
    >
     
  5. Bob Richardson

    Bob Richardson
    Expand Collapse
    Guest

    I've taken your advice and switched the Table field from a lookup table to a
    Text field. Then on the form, I use a ComboBox.

    I'm working with a subform2 where the control name of col A is TS, which is
    now a text field that can be taken from a combobox. In col B I have another
    combo box which needs to see the text value of Col A. My SELECT query on
    col B is still not working.

    I'm trying to refer to the value as Me!TS but something is wrong. I thought
    I could use this simple syntax since Col A and Col b are both on the same
    form...subform2.


    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%23baF2SR6FHA.1188@TK2MSFTNGP12.phx.gbl...
    > TimeSlot will be whatever data type as the primary key of the table where
    > TimeSlot gets its description (numeric value. This confusion is one of the
    > reason why many people feel Lookup FIelds are a very bad idea (see
    > http://www.mvps.org/access/lookupfields.htm at "The Access Web")
    >
    > Since you should never be working directly with tables, use a combobox on
    > a
    > form, so that you know what's going on "under the covers".
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Bob Richardson" <bobr at whidbey dot com> wrote in message
    > news:rcadnfbmG-ol6OXeRVn-jg@whidbeytel.com...
    >> In Table Design I've used a table lookup to define a field...TimeSlot.
    >> In
    >> datasheet view it correctly uses a drop down list with the correct text
    >> values, yet it stores a number in the file...the record's key. This

    > appears
    >> to be correct.
    >>
    >> On a form I have a drop down list for TimeSlot, as well as a 2nd
    >> drop-down
    >> list that needs to use the text value of the TimeSlot to determine it's
    >> values. The name of the TimeSlot field, on the form, is TS.
    >>
    >> This part of the query for the 2nd drop-down list isn't working.
    >>
    >> WHERE Left(TS,1)=Left(Q_Classes.ClassCode,1)
    >>
    >> Is TS a numeric value, since a numeric value is stored in the file, OR is

    > TS
    >> a text value, since that's what is visible on the form? How can I get the
    >> text value for TS?
    >>
    >>

    >
    >
     
  6. Bob Richardson

    Bob Richardson
    Expand Collapse
    Guest

    It seems that my query can refer to the Col A value with just TS (not
    Me!TS). However, it's not finding the correct value. Is there something I
    should do with the bound column?

    "Bob Richardson" <bobr at whidbey dot com> wrote in message
    news:bPmdneOg0rRmfeXenZ2dnUVZ_tadnZ2d@whidbeytel.com...
    > I've taken your advice and switched the Table field from a lookup table to
    > a Text field. Then on the form, I use a ComboBox.
    >
    > I'm working with a subform2 where the control name of col A is TS, which
    > is now a text field that can be taken from a combobox. In col B I have
    > another combo box which needs to see the text value of Col A. My SELECT
    > query on col B is still not working.
    >
    > I'm trying to refer to the value as Me!TS but something is wrong. I
    > thought I could use this simple syntax since Col A and Col b are both on
    > the same form...subform2.
    >
    >
    > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:%23baF2SR6FHA.1188@TK2MSFTNGP12.phx.gbl...
    >> TimeSlot will be whatever data type as the primary key of the table where
    >> TimeSlot gets its description (numeric value. This confusion is one of
    >> the
    >> reason why many people feel Lookup FIelds are a very bad idea (see
    >> http://www.mvps.org/access/lookupfields.htm at "The Access Web")
    >>
    >> Since you should never be working directly with tables, use a combobox on
    >> a
    >> form, so that you know what's going on "under the covers".
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "Bob Richardson" <bobr at whidbey dot com> wrote in message
    >> news:rcadnfbmG-ol6OXeRVn-jg@whidbeytel.com...
    >>> In Table Design I've used a table lookup to define a field...TimeSlot.
    >>> In
    >>> datasheet view it correctly uses a drop down list with the correct text
    >>> values, yet it stores a number in the file...the record's key. This

    >> appears
    >>> to be correct.
    >>>
    >>> On a form I have a drop down list for TimeSlot, as well as a 2nd
    >>> drop-down
    >>> list that needs to use the text value of the TimeSlot to determine it's
    >>> values. The name of the TimeSlot field, on the form, is TS.
    >>>
    >>> This part of the query for the 2nd drop-down list isn't working.
    >>>
    >>> WHERE Left(TS,1)=Left(Q_Classes.ClassCode,1)
    >>>
    >>> Is TS a numeric value, since a numeric value is stored in the file, OR
    >>> is

    >> TS
    >>> a text value, since that's what is visible on the form? How can I get
    >>> the
    >>> text value for TS?
    >>>
    >>>

    >>
    >>

    >
    >
     
  7. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    Me is a short form for the current form or report. It replaces
    [Forms]![FormName] or [Reports]![ReportName]. It can only be used from
    within code on that form or report. Therefore, any other reference, even on
    the property sheet or in a query, must use the long form as shown above. I
    hope that solves your problem.
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access

    "Bob Richardson" <bobr at whidbey dot com> wrote in message
    news:2LednXjc6vAbcuXenZ2dnUVZ_sWdnZ2d@whidbeytel.com...
    > It seems that my query can refer to the Col A value with just TS (not
    > Me!TS). However, it's not finding the correct value. Is there something I
    > should do with the bound column?
    >
    > "Bob Richardson" <bobr at whidbey dot com> wrote in message
    > news:bPmdneOg0rRmfeXenZ2dnUVZ_tadnZ2d@whidbeytel.com...
    > > I've taken your advice and switched the Table field from a lookup table

    to
    > > a Text field. Then on the form, I use a ComboBox.
    > >
    > > I'm working with a subform2 where the control name of col A is TS, which
    > > is now a text field that can be taken from a combobox. In col B I have
    > > another combo box which needs to see the text value of Col A. My SELECT
    > > query on col B is still not working.
    > >
    > > I'm trying to refer to the value as Me!TS but something is wrong. I
    > > thought I could use this simple syntax since Col A and Col b are both on
    > > the same form...subform2.
    > >
    > >
    > > "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > > news:%23baF2SR6FHA.1188@TK2MSFTNGP12.phx.gbl...
    > >> TimeSlot will be whatever data type as the primary key of the table

    where
    > >> TimeSlot gets its description (numeric value. This confusion is one of
    > >> the
    > >> reason why many people feel Lookup FIelds are a very bad idea (see
    > >> http://www.mvps.org/access/lookupfields.htm at "The Access Web")
    > >>
    > >> Since you should never be working directly with tables, use a combobox

    on
    > >> a
    > >> form, so that you know what's going on "under the covers".
    > >>
    > >> --
    > >> Doug Steele, Microsoft Access MVP
    > >> http://I.Am/DougSteele
    > >> (no e-mails, please!)
    > >>
    > >>
    > >> "Bob Richardson" <bobr at whidbey dot com> wrote in message
    > >> news:rcadnfbmG-ol6OXeRVn-jg@whidbeytel.com...
    > >>> In Table Design I've used a table lookup to define a field...TimeSlot.
    > >>> In
    > >>> datasheet view it correctly uses a drop down list with the correct

    text
    > >>> values, yet it stores a number in the file...the record's key. This
    > >> appears
    > >>> to be correct.
    > >>>
    > >>> On a form I have a drop down list for TimeSlot, as well as a 2nd
    > >>> drop-down
    > >>> list that needs to use the text value of the TimeSlot to determine

    it's
    > >>> values. The name of the TimeSlot field, on the form, is TS.
    > >>>
    > >>> This part of the query for the 2nd drop-down list isn't working.
    > >>>
    > >>> WHERE Left(TS,1)=Left(Q_Classes.ClassCode,1)
    > >>>
    > >>> Is TS a numeric value, since a numeric value is stored in the file, OR
    > >>> is
    > >> TS
    > >>> a text value, since that's what is visible on the form? How can I get
    > >>> the
    > >>> text value for TS?
    > >>>
    > >>>
    > >>
    > >>

    > >
    > >

    >
    >
     

Share This Page