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

Thanks for that Jack!

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jack drawbridge
Sent: Wednesday, May 22, 2013 8:54 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com