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

William Benson (VBACreations.Com) vbacreations at gmail.com
Tue May 21 15:27:28 CDT 2013


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'



More information about the AccessD mailing list