Parameters in Queries

  • Thread starter Thread starter Paul Murphy via AccessMonster.com
  • Start date Start date
P

Paul Murphy via AccessMonster.com

Hello,

I am having a little trouble with an SQL query:

SELECT Funds.Fund_Name
FROM Funds
WHERE Parameter=Yes;

'Parameter' is simply a parameter which is entered in a prompt, and is the
name of the column in the 'Funds' table which the query is run against. It
does not however return any rows for me ???

NB. The column in the table is set to Yes/No

Any help would be greatly appreciated....

Thanks,
Paul
 
Although the format of the boolean field in a table's design view is Yes/No,
what the field actually stores is a -1 (for yes or true) or 0 (for no or
false).

So what you want to do is filter for True or False, which the Jet database
engine will convert to -1 or 0 respectively when it does the comparison.

Not sure what you mean by entering a parameter with a prompt by using the
SQL statement that you've posted, as that has nothing that would cause a
parameter box to appear. Perhaps this would work:

SELECT Funds.Fund_Name
FROM Funds
WHERE [Parameter]=[Enter True of False:];

Also, don't use Parameter as the name of a field in your table. It and many
other words are reserved words in ACCESS, and can create serious confusion
for ACCESS and Jet. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763
 
Hello,

I am having a little trouble with an SQL query:

SELECT Funds.Fund_Name
FROM Funds
WHERE Parameter=Yes;

'Parameter' is simply a parameter which is entered in a prompt, and is the
name of the column in the 'Funds' table which the query is run against. It
does not however return any rows for me ???

NB. The column in the table is set to Yes/No

Any help would be greatly appreciated....
Click to expand...

A parameter can be used to provide a *value* to be searched, it cannot
be used to provide a *fieldname* to be searched.

The need to do so strongly suggests that your table is storing data in
fieldnames, which is emphatically bad design. It would probably be
better to have a many to many relationship between Funds and
Characteristics, using a third table FundCharacteristics with the
Fund_Name and the Characteristic as a joint two-field primary key.

If you need to keep this "wide-flat" table, the only way to do the
query as you propose is to write VBA code to read the parameter and
construct the SQL of the query, and then use that SQL as the
recordsource for a form or report.

John W. Vinson[MVP]
 
Thanks very much for your tips guys, i shall take a differnt approach to to
the problem....

Paul
 
Back
Top