[AccessD] Simple Query is being driven by Column Widths rather than Bound Column

William, I'm glad Stuart picked up on the Table fields - I advise people
not to use Lookups at the table field level - and didn't realize that that
was what you were describing.
Here's the reference often quoted against the practice.
http://access.mvps.org/access/lookupfields.htm

Good luck.
On Tue, May 21, 2013 at 6:58 PM, William Benson (VBACreations.Com) <
vbacreations at gmail.com> wrote:

> Ah, always glad to find out (at the beginning!) of a project when I am
> using
> a worst practice!
>
> Thanks Stuart, I have a smaller wound to lick than otherwise would have,
> had
> you not spoken up promptly.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
> McLachlan
> Sent: Tuesday, May 21, 2013 6:12 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Simple Query is being driven by Column Widths rather
> than Bound Column
>
> It has always been thus and is one of the many reasons not to set the
> Lookup/Display Control
> in the table definition.
>
> The datasheet uses that setting to decide how to display that field -  both
> when viewing the table data directly and when displaying that field in any
> query.
>
> The only solution is to set the Lookup/Display Control back to Textbox
>
>
> --
> Stuart
>
> On 21 May 2013 at 16:27, William Benson (VBACreations. wrote:
>
> > This is driving me a little crazy, I know it is so basic and yet I am
> > drawing a blank. I am setting up a Customer table with a
> > State/Province field that looks up 002_State table. The field Lookup
> details are
> >       SELECT [StateID], [StateName] FROM 002_State; ColumnCount = 2;
> Bound
> > Column   1; Column Widths = 0";3"
> >
> >
> > When I run a query    SELECT [State/Province] From [Customer]
> >
> > I see the statename value 'California' instead of the StateID value of 5.
> >
> > I would think queries would show the bound column's value regardless
> > of the Column Widths property of the table field (as opposed to combo
> > boxes and reports, which I know do make use of those properties.
> >
> > I checked via recordsets, and the value does appear to be 5, despite
> > what the query shows.
> >
> >
> > Sub check()
> > Dim r As DAO.Recordset, r2 As DAO.Recordset Dim d As Database Set d =
> > CurrentDb Set r = d.OpenRecordset("SELECT Customers.[State/Province]
> > FROM Customers") r.MoveFirst If Not r.EOF Then
> >     Set r2 = d.OpenRecordset("Select StateName from 002_State where
> > StateID = " & r.Fields(0))
> >     r2.MoveFirst
> >     If Not r2.EOF Then
> >         Debug.Print "The first state in the Customer table:" & Chr(13)
> > &
> > Chr(13) & "Field: " & _
> >             r.Fields(0).Name & " has Value = " & _
> >             r.Fields(0) & ", is of Type = " & _
> >             r.Fields(0).Type & ", and represents the state of " & _
> >             r2.Fields(0) & "' "
> >     End If
> > End If
> > End Sub
> >
> > 'The first state in the Customer table:
> >
> > 'Field: State/Province has Value = 5, is of Type = 4, and represents
> > the state of California'
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>