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

??? Bound Column 0 <--- first field

On Tue, May 21, 2013 at 4:27 PM, William Benson (VBACreations.Com) <
vbacreations at gmail.com> 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
>