[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
>