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

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