SQL Select Query Prompting for NonExistent Parameter
in [Queries]
|
From: David on 17 Mar 2010 22:25 I'm using Access 97. I've entered the following SQL Query into the Query Tab. =================== SELECT * FROM tblVehicles WHERE fldVehicleTag = ABC123; ==================== When I double click the Query to execute: Instead of returning the recordset Access prompts me for a Parameter. When I enter the tag # --> ABC123 it returns the correct record. Why am I being prompted for a Parameter and how do I stop it? Thanks David
From: John W. Vinson on 17 Mar 2010 22:43 On Wed, 17 Mar 2010 22:25:13 -0400, "David" <NoWhere(a)earthlink.net> wrote: >I'm using Access 97. > >I've entered the following SQL Query into the Query Tab. > >=================== > >SELECT * FROM tblVehicles WHERE fldVehicleTag = ABC123; > >==================== > >When I double click the Query to execute: > >Instead of returning the recordset Access prompts me for >a Parameter. When I enter the tag # --> ABC123 >it returns the correct record. > >Why am I being prompted for a Parameter and how do I stop it? > >Thanks >David You need quotemarks around the text criterion: SELECT * FROM tblVehicles WHERE fldVehicleTag = 'ABC123'; Criteria on Text fields must be delimited by ' or "; Date/Time fields must be delimited by # (for date literals); Number or Currency fields use no delimiter. -- John W. Vinson [MVP]
From: David on 17 Mar 2010 23:07 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. 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? "John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:0n43q51gek1m51foi4o3fudgklg5kn9kop(a)4ax.com... > On Wed, 17 Mar 2010 22:25:13 -0400, "David" <NoWhere(a)earthlink.net> wrote: > >>I'm using Access 97. >> >>I've entered the following SQL Query into the Query Tab. >> >>=================== >> >>SELECT * FROM tblVehicles WHERE fldVehicleTag = ABC123; >> >>==================== >> >>When I double click the Query to execute: >> >>Instead of returning the recordset Access prompts me for >>a Parameter. When I enter the tag # --> ABC123 >>it returns the correct record. >> >>Why am I being prompted for a Parameter and how do I stop it? >> >>Thanks >>David > > You need quotemarks around the text criterion: > > > SELECT * FROM tblVehicles WHERE fldVehicleTag = 'ABC123'; > > Criteria on Text fields must be delimited by ' or "; Date/Time fields must > be > delimited by # (for date literals); Number or Currency fields use no > delimiter. > > > -- > > John W. Vinson [MVP]
From: John W. Vinson on 17 Mar 2010 23:24 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! -- John W. Vinson [MVP]
From: David on 18 Mar 2010 00:10
Thank you for your time. Have a nice evening. David "John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:6473q5hjj4mpf6oda5eg8kngu3pgq14l8r(a)4ax.com... > 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! > -- > > John W. Vinson [MVP] |