SQL Select Query Prompting for NonExistent Parameter

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

On Wed, 17 Mar 2010 22:25:13 -0400, "David" 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]

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" wrote in message
...
On Wed, 17 Mar 2010 22:25:13 -0400, "David" 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]

On Wed, 17 Mar 2010 23:07:26 -0400, "David" 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]

Thank you for your time.

Have a nice evening.

David

"John W. Vinson" wrote in message
...
On Wed, 17 Mar 2010 23:07:26 -0400, "David" 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]

On Wed, 17 Mar 2010 23:07:26 -0400, "David" 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]

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" 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,

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" 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.

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" wrote in message
...
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"
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.