2 Things I'm trying to do in Access

[NBABA21]

I'm creating an Access database that will function as a query builder
through a Forms interface. I have approximately 20 dropdowns which
will allow users to select their criteria, and then a command button
that will run a query. The unbound dropdowns default to the text "All"
which, I hope, will act as if there is no criteria in the query. I
tried an IIF stamement like this:

IIF([forms]![main]![oppty id]="All","*",[forms]![main]![oppty id])

This does not return the correct results. I want the True part of the
stament to return all values, whether null or otherwise. (I.e. as if
there was no criteria in that query field at all.) Does anyone know
how to do this?

Second question...

I have checkboxes next to each of the 20 criteria fields. The
intention for these checkboxes is to change the visible property of the
field once the query is run. In other words, if the User puts a check
next to the field "Oppty Id" it will appear once the query is run. If
there is no check, the fields visible property will remain False. Is
this possible to do in a query? (I know it can be done in a report).
Any ideas would be appreciated!

 

[Allen Browne]

It would be possible to modify the SQL View of the query so that it returns
True where the control is null, True where the control is "All", or a match
where the control has another value:
WHERE (([forms]![main]![oppty id] Is Null)
OR ([forms]![main]![oppty id] = "All")
OR ([SomeField] = [forms]![main]![oppty id]))

However, this kind of thing will be way to unweildy and inefficient for 20
drop-downs. An efficient solution would be to build the WHERE clause from
only those boxes that have a value. You could leave the criteria out of the
query all together, and instead build up a string to use as the Filter for
your form, or the WhereCondition for OpenReport. If necessary, you can build
the entire SQL statement and assign it to the RecordSource of your form or
report, or even the SQL property of the QueryDef.

The code below shows how to build up the WHERE string from the non-blank
boxes. You can adapt it so it ignores "ALL" in your combos as well. It
illustrates how to use the " character to delimit strings, and the #
character for dates (as well as explicitly formatting them so they work in
all countries.)

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Dim strSql As String
Const conJetDate = "\#mm\/dd\/yyyy\#"

'Text field example.
If Not IsNull(Me.txtFiilterSurname) Then
strWhere = strWhere & "([Surname] = """ & _
Me.txtFiilterSurname & """) AND "
End If

'Date field example
If Not IsNull(Me.txtFilterBirthDate) Then
strWhere = strWhere & "([BirthDate] = " & _
Format(Me.txtFilterBirthDate, conJetDate) & ") AND "
End If

'Number field example
If Not IsNull(Me.txtFilterAmount) Then
strWhere = strWhere & "([Amount] = " & _
Me.txtFilterAmount & ") AND "
End If

'etc for other controls.

'Now chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
'Debug.Print strWhere
End If

'Finally apply as the filter to the form.
If Me.Dirty Then
Me.Dirty = False
End If
Me.Filter = strWhere
Me.FilterOn = True

'Or, apply to a report
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

'Or, build the full query statement
strSql = "SELECT * FROM Table1 WHERE " & strWhere & " ORDER BY Field1;"
'and apply to a query
CurrentDb.QueryDefs("Query1").SQL = strSql
'or a form
Me.RecordSource = strSql
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm creating an Access database that will function as a query builder
through a Forms interface. I have approximately 20 dropdowns which
will allow users to select their criteria, and then a command button
that will run a query. The unbound dropdowns default to the text "All"
which, I hope, will act as if there is no criteria in the query. I
tried an IIF stamement like this:

IIF([forms]![main]![oppty id]="All","*",[forms]![main]![oppty id])

This does not return the correct results. I want the True part of the
stament to return all values, whether null or otherwise. (I.e. as if
there was no criteria in that query field at all.) Does anyone know
how to do this?

Second question...

I have checkboxes next to each of the 20 criteria fields. The
intention for these checkboxes is to change the visible property of the
field once the query is run. In other words, if the User puts a check
next to the field "Oppty Id" it will appear once the query is run. If
there is no check, the fields visible property will remain False. Is
this possible to do in a query? (I know it can be done in a report).
Any ideas would be appreciated!

 

[PC Datasheet]

Look at QueryDef in the Help file. You need to dynamically build the SQL for
your query based on the selections made on your form. As for your first
question, you don't need "All". When you dynamically build the SQL, include
those fields in the Select string but do not set any criteria for those
fields in the Where string. Doing this you will get the effect of "All". As
for your second question, you don't need to do anything there. If your SQL
only includes the fields on the form where a criteria is selected, the other
fields will not be included in the final SQL (query).

 

[Guest]

From my understanding of your problem, it might be better and easier for you
to work with filter/sort.

Take a look at this:
http://support.microsoft.com/kb/q147143/
http://support.microsoft.com/kb/q146310/

It will allow you to show exactly what you need.

 

[StopThisAdvertising]

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1100 users have come to me from the newsgroups requesting help
(e-mail address removed)

--
To the OP:

Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html (updated)

Arno R

 

[Nunu]

Thank you very much! This worked perfectly! Dynamically building the
SQL statement will definitely be useful in other projects I'm working
on as well. Thanks again,

Noel

 

[Nunu]

One More question for you folks! Is there any way i can set up a List
Box in MS Access to select (and store the value) for multiple criteria?
For example, I have a dropdown for City. A user can select "Chicago"
from the dropdown, which will pass that parameter off to the SQL
statement. How would i be able to set it up, so that a user can hit
the Ctrl-key and click, and select "Chicago", "New York" and "LA".
Again, thanks for your earlier input Allen. that worked perfectly.

 

[John Vinson]

One More question for you folks! Is there any way i can set up a List
Box in MS Access to select (and store the value) for multiple criteria?
For example, I have a dropdown for City. A user can select "Chicago"
from the dropdown, which will pass that parameter off to the SQL
statement. How would i be able to set it up, so that a user can hit
the Ctrl-key and click, and select "Chicago", "New York" and "LA".
Again, thanks for your earlier input Allen. that worked perfectly.

http://www.mvps.org/access/forms/frm0007.htm

has some sample code to do this. If your listbox returns city names
rather than unique city ID's (do you mean Las Vegas, Nevada? or Las
Vegas, New Mexico?) you'll need quotemarks around the criterion.

Perhaps a bit better is to use the IN operator:

Dim strSQL As String
strSQL = "Select * from Where [City] IN("
For Each varItem In Me.lstCities.ItemsSelected
strSQL = strSQL & "'" & Me.lstCities.ItemData(varItem) & "'"
Next varItem
strSQL = Left(strSQL, Len(strSQL)) - 1 & ")"


John W. Vinson[MVP]