SQL statement where clause

[zeee]

Hi,

I am very new Access. I am trying to complete the WHERE clause of my SQL statement but don't know how to read in the values from the query form fields into this part of the query. This is the sql statement I have so far. My form is called BEM_EXCPETIONS_QUERY_FORM.

SELECT
BEM_EXCEPTIONS.BEM_EXCEPTION_ID, BEM_EXCEPTIONS.BEM_EXCEPTION_DESC, BEM_EXCEPTIONS.BEM_EXCEPTION_TYPE, BEM_EXCEPTIONS.BEM_GROUP_ID, BEM_EXCEPTIONS.BEM_STATUS, BEM_EXCEPTIONS.BEM_AGE, BEM_EXCEPTIONS.BEM_PRIORITY, BEM_EXCEPTIONS.BEM_DATE_TIME_CREATION, BEM_EXCEPTIONS.BEM_CLOSED_AT_TIMESTAMP, BEM_EXCEPTIONS.BEM_LAST_UPDATED, BEM_EXCEPTIONS.BEM_ASSIGNED_USER, BEM_EXCEPTIONS.BEM_EVENT_ID, BEM_EXCEPTIONS.BEM_EVENT_VERSION, BEM_EXCEPTIONS.BEM_EVENT_TYPE, BEM_EXCEPTIONS.BEM_TRADE_ID, BEM_EXCEPTIONS.BEM_PROD_TYPE, BEM_EXCEPTIONS.BEM_ENTITY, BEM_EXCEPTIONS.BEM_BUSINESS_GROUP, BEM_EXCEPTIONS.BEM_BOOK, BEM_EXCEPTIONS.BEM_CPTY_ID, BEM_EXCEPTIONS.BEM_CPTY_LEGAL_NAME, BEM_EXCEPTIONS.BEM_CPTY_SENSITIVITY_LEVEL, BEM_EXCEPTIONS.BEM_CPTY_REGION, BEM_EXCEPTIONS.BEM_CPTY_COUNTRY, BEM_EXCEPTIONS.BEM_CPTY_TYPE, BEM_EXCEPTIONS.BEM_NOMINAL, BEM_EXCEPTIONS.BEM_CCY, BEM_EXCEPTIONS.BEM_VALUE_DATE, BEM_EXCEPTIONS.BEM_MATURITY_DATE, BEM_EXCEPTIONS.BEM_CONTACT_DETAILS, BEM_EXCEPTIONS.BEM_TRANSMISSION_MEDIA, BEM_EXCEPTIONS.BEM_SYS_INSTANCE_NAME
FROM BEM_EXCEPTIONS;

Thanks

 

[sportsguy]

Where are the values in the form fields on the form?

but don't know how to read in the values from the query form fields into this part of the query

are they in list boxes? combo boxes? or just in the form recordset?

sg

 

[The_Doc_Man]

First, for a single-table select, your SQL can be radically shortened. I'll show you an excerpt.

SELECT
BEM_EXCEPTION_ID, BEM_EXCEPTION_DESC, BEM_EXCEPTION_TYPE, BEM_GROUP_ID, ...
... FROM BEM_EXCEPTIONS WHERE ...

When the only table is a constant (in this case, BEM_EXCEPTIONS), you just put the right table name in the FROM clause and use only the field names in the SELECT clause. It is also possible to shorten this even more if your list of selected fields happens to be the same as the complete list of fields that exist in the table. In that case, you are allowed to use

SELECT * FROM BEM_EXCEPTIONS WHERE ...

If this is feeding a form, it doesn't matter if you don't use every field that is returned by the query. What you want to avoid is to reference a field that WASN'T returned by the query (i.e. a control happens to name a field you forgot to add into the query SELECT clause.)

don't know how to read in the values from the query form fields into this part of the query

Technically, you can't do what you just said you wanted to do in a standard query. It is pre-defined and pre-compiled when you store it. But there are still a few ways to handle this.

1. Dynamically build the WHERE clause as a raw text string, update the recordsource on the form, and refresh the form. NOT PREFERRED.

2. Search this forum for the key phrase "Cascading Combo Box" to see literally dozens of threads on this subject, some of which will contain links to replies that contain the solution. PREFERRED.

3. Make the query a PARAMETER QUERY (Access Help Files covers this topic) and then supply the text boxes as parameters. Works best with only a few parameters and is tricky to manage the more parameters you have that are NOT selected by your users for search criteria.

 

[zeee]

The values come from text fields and combo boxes within the form.