SQL Select Query Prompting for NonExistent Parameter
in [Queries]
|
From: John W. Vinson on 18 Mar 2010 01:10 On Wed, 17 Mar 2010 23:07:26 -0400, "David" <NoWhere(a)earthlink.net> wrote: >Of interest, is that the Query returned the correct record >but with the fldVehicleTag column in the first position. >Anyone to replicate this in SQL or is this a field "reorder" function >that is part of Access? My guess is that you got this result because you included both the fldVehicleTag explicitly (for the criterion), and the * operator which returns all fields. It probably put the field you specified first and then all the other fields (or it may have included fldVehicleTag twice, aliasing one of them as Expr1). You can select all of the fields individually or as a group - select them all from the field list and drag them to the grid, and leave off the *. You can then arrange the fields in any order you like. -- John W. Vinson [MVP]
From: John Spencer on 18 Mar 2010 10:23 Ah, but you can rearrange columns in datasheet view and if you save the changes the Access will remember the changes to the datasheet view. That doesn't explain the order the poster sees if he just opened a new query and used the "query designer" John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County John W. Vinson wrote: > On Wed, 17 Mar 2010 23:07:26 -0400, "David" <NoWhere(a)earthlink.net> wrote: > >> Thanks Mr. Vinson -- Worked GREAT! >> >> One other followup. Instead of SQL, I tried Access's >> Query Designer (hope I have this name correct). >> I entered the test Tag Number under Criteria. > > The query grid is simply a tool to construct SQL. The SQL is a) the real > query, and b) much easier to post and discuss on newsgroups. > >> Of interest, is that the Query returned the correct record >> but with the fldVehicleTag column in the first position. >> Anyone to replicate this in SQL or is this a field "reorder" function >> that is part of Access? > > The order of columns in the query grid should match the order of fieldnames in > the SQL SELECT clause, and should also control the order in which the fields > appear in the query grid. I'm not aware of any automagical reordering!
From: John Spencer on 18 Mar 2010 10:25 John, Good one. I'll have to keep this in mind when I am answering questions. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County John W. Vinson wrote: > On Wed, 17 Mar 2010 23:07:26 -0400, "David" <NoWhere(a)earthlink.net> wrote: > >> Of interest, is that the Query returned the correct record >> but with the fldVehicleTag column in the first position. >> Anyone to replicate this in SQL or is this a field "reorder" function >> that is part of Access? > > My guess is that you got this result because you included both the > fldVehicleTag explicitly (for the criterion), and the * operator which returns > all fields. It probably put the field you specified first and then all the > other fields (or it may have included fldVehicleTag twice, aliasing one of > them as Expr1). > > You can select all of the fields individually or as a group - select them all > from the field list and drag them to the grid, and leave off the *. You can > then arrange the fields in any order you like.
From: David on 20 Mar 2010 09:30
Thanks Mr. Vinson and Mr. Spencer. Problem resolved. Appreciate your efforts on my behalf. Normally use SQL and Access as backend to VB, so not comfortable with "inter workings" of Access interface. Need to allocate some time in that area. Have a nice day. David "John Spencer" <spencer(a)chpdm.edu> wrote in message news:OJ1umbqxKHA.5936(a)TK2MSFTNGP04.phx.gbl... > John, > > Good one. I'll have to keep this in mind when I am answering questions. > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > John W. Vinson wrote: >> On Wed, 17 Mar 2010 23:07:26 -0400, "David" <NoWhere(a)earthlink.net> >> wrote: >> >>> Of interest, is that the Query returned the correct record >>> but with the fldVehicleTag column in the first position. >>> Anyone to replicate this in SQL or is this a field "reorder" function >>> that is part of Access? >> >> My guess is that you got this result because you included both the >> fldVehicleTag explicitly (for the criterion), and the * operator which >> returns >> all fields. It probably put the field you specified first and then all >> the >> other fields (or it may have included fldVehicleTag twice, aliasing one >> of >> them as Expr1). >> >> You can select all of the fields individually or as a group - select them >> all >> from the field list and drag them to the grid, and leave off the *. You >> can >> then arrange the fields in any order you like. |