Problem with Criteria

[Ray]

I use a form to enter criteria for a query. I use the following following IIF
statement
to filter the PriKey on the query

IIF(forms![Form1]![Text0] is null, [PriKey], forms![Form1]![Text0])

When I put the number 1 in Text0, the query returns the number 1. If I set
the value of Text0 to Null, the query returns all records. BUT, when I put
<>3 in Text0, I get the error message "The close action was cancelled". What
can I do to remedy this?

 

[Marshall Barton]

I use a form to enter criteria for a query. I use the following following IIF
statement
to filter the PriKey on the query

IIF(forms![Form1]![Text0] is null, [PriKey], forms![Form1]![Text0])

When I put the number 1 in Text0, the query returns the number 1. If I set
the value of Text0 to Null, the query returns all records. BUT, when I put
<>3 in Text0, I get the error message "The close action was cancelled". What
can I do to remedy this?

You can not "remedy" that kind of thing. The result of a
function (in this case IIf) returns a **value**, not part of
an expression.

Maybe there's a way to get where you want to go, but you'll
have to explain WHAT you are trying to accomplish instead of
HOW you thought you could do it.

 

[Ray]

I used this statement in the criteria section of the query. It functions like
an Imput box for me and had worked very well with numbers and text. I put
"bob" in the text box, and I get all "bob"'s in the related Name field of the
query. Since it worked for numbers and text, I logically tried it using a
formula. I was wrong. Didnt work.

Using a form, I would like to open a query and have the "AuditorID" field
display all numbers >3. I dont want to put the ">3" in the design view of the
query. I want to put it in the form. Sometimes the text box will be Null,
sometimes it will have a number and sometimes it will have a formula.

Can this be done?

I use a form to enter criteria for a query. I use the following following IIF
statement
to filter the PriKey on the query

IIF(forms![Form1]![Text0] is null, [PriKey], forms![Form1]![Text0])

When I put the number 1 in Text0, the query returns the number 1. If I set
the value of Text0 to Null, the query returns all records. BUT, when I put
<>3 in Text0, I get the error message "The close action was cancelled". What
can I do to remedy this?

You can not "remedy" that kind of thing. The result of a
function (in this case IIf) returns a **value**, not part of
an expression.

Maybe there's a way to get where you want to go, but you'll
have to explain WHAT you are trying to accomplish instead of
HOW you thought you could do it.

 

[Ray]

Sorry, not formula. Expression.

I used this statement in the criteria section of the query. It functions like
an Imput box for me and had worked very well with numbers and text. I put
"bob" in the text box, and I get all "bob"'s in the related Name field of the
query. Since it worked for numbers and text, I logically tried it using a
formula. I was wrong. Didnt work.

Using a form, I would like to open a query and have the "AuditorID" field
display all numbers >3. I dont want to put the ">3" in the design view of the
query. I want to put it in the form. Sometimes the text box will be Null,
sometimes it will have a number and sometimes it will have a formula.

Can this be done?

I use a form to enter criteria for a query. I use the following following IIF
statement
to filter the PriKey on the query

IIF(forms![Form1]![Text0] is null, [PriKey], forms![Form1]![Text0])

When I put the number 1 in Text0, the query returns the number 1. If I set
the value of Text0 to Null, the query returns all records. BUT, when I put
<>3 in Text0, I get the error message "The close action was cancelled". What
can I do to remedy this?

You can not "remedy" that kind of thing. The result of a
function (in this case IIf) returns a **value**, not part of
an expression.

Maybe there's a way to get where you want to go, but you'll
have to explain WHAT you are trying to accomplish instead of
HOW you thought you could do it.

 

[Marshall Barton]

I used this statement in the criteria section of the query. It functions like
an Imput box for me and had worked very well with numbers and text. I put
"bob" in the text box, and I get all "bob"'s in the related Name field of the
query. Since it worked for numbers and text, I logically tried it using a
formula. I was wrong. Didnt work.

Using a form, I would like to open a query and have the "AuditorID" field
display all numbers >3. I dont want to put the ">3" in the design view of the
query. I want to put it in the form. Sometimes the text box will be Null,
sometimes it will have a number and sometimes it will have a formula.

Can this be done?

Yes, but you will have to to use VBA code to construct the
query and its WHERE clause. The BuildCriteria function
(search in VBA Help) can take care of the really complex
part of the problem.

What will be the query's purpose once you get it working?

 

[Ray]

It will be an append query. Each auditor goes thru various training and
certifications. We track them for each auditor. This will put the
certifications for each auditor into a certifications table. I do not enter
this data myself so I need to make a form to make it easier for the
supervisors to enter many records at one time. One text box "fills" the
prikey for the certification to the query, another fills the date, another
the trainer, etc. The query gets all the auditors prikeys from the auditors
table. Sometimes, some auditors are absent for the training. They need to be
excluded from the append query. Hence the need to have a text box for "<>2
And <> 7 And <>18" (The prikey for each missing auditor who was absent).
Everything works just fine except for the "<>2 And <> 7 And <>18" thing. I
have found ways to build this into SelectionCriteria when opening forms and
reports but not directly into queries themselves. Docmd.openquery doesnt
appear to support selection criteria (filter).

 

[Ray]

I am not able to test this at home, or I would not ask, but can you use an
Append Query as a record source for a Form? If I could, could I use the
filter there?

 

[Marshall Barton]

It will be an append query. Each auditor goes thru various training and
certifications. We track them for each auditor. This will put the
certifications for each auditor into a certifications table. I do not enter
this data myself so I need to make a form to make it easier for the
supervisors to enter many records at one time. One text box "fills" the
prikey for the certification to the query, another fills the date, another
the trainer, etc. The query gets all the auditors prikeys from the auditors
table. Sometimes, some auditors are absent for the training. They need to be
excluded from the append query. Hence the need to have a text box for "<>2
And <> 7 And <>18" (The prikey for each missing auditor who was absent).
Everything works just fine except for the "<>2 And <> 7 And <>18" thing. I
have found ways to build this into SelectionCriteria when opening forms and
reports but not directly into queries themselves. Docmd.openquery doesnt
appear to support selection criteria (filter).

Since an INSERT INTO (Append) query is an action query, you
should be using either RunSQL (asynchronous) or the Execute
method (synchronous). Both of those methods accept a string
that contains an SQL statment (instead of the name of a
saved querydef) so you can use code to construct the SQL
statment and then run it.

An outline of the kind of code you could use would look
vaguely like this air code:

Dim wc As String, cond As String, ctnm As String
Dim ctl As Control
Dim db As Database, tdf As TableDef
Dim fn As String
Const SQL As String = "INSERT INTO [name of table] " _
"SELECT flda, fldb, .... "

Set db = CurrentDb
Set tdf = db.TableDefs("name of table"

For Each ctl In Me
If ctl.Tag <> "" Then
If Not IsNull(ctl) Then
fn = "[" & ctl.Tag & "]"
cond = BuildCriteria(fn, tdf.Fields(fn).Type, ctl)
wc = wc & " AND (" & cond & ")"
End If
End If
Next ctl

db.Execute SQL & " WHERE " & Mid(wc, 6)

Note: the code assumes that the controls that can be used
for a criteria must have their Tag property set to the name
of the table field that the criteria applies to.

 

[Ray]

Now that is really nice. It took me a while to understand it. There is a lot
of stuff here that I can use. Thank you so much.

It will be an append query. Each auditor goes thru various training and
certifications. We track them for each auditor. This will put the
certifications for each auditor into a certifications table. I do not enter
this data myself so I need to make a form to make it easier for the
supervisors to enter many records at one time. One text box "fills" the
prikey for the certification to the query, another fills the date, another
the trainer, etc. The query gets all the auditors prikeys from the auditors
table. Sometimes, some auditors are absent for the training. They need to be
excluded from the append query. Hence the need to have a text box for "<>2
And <> 7 And <>18" (The prikey for each missing auditor who was absent).
Everything works just fine except for the "<>2 And <> 7 And <>18" thing. I
have found ways to build this into SelectionCriteria when opening forms and
reports but not directly into queries themselves. Docmd.openquery doesnt
appear to support selection criteria (filter).

Since an INSERT INTO (Append) query is an action query, you
should be using either RunSQL (asynchronous) or the Execute
method (synchronous). Both of those methods accept a string
that contains an SQL statment (instead of the name of a
saved querydef) so you can use code to construct the SQL
statment and then run it.

An outline of the kind of code you could use would look
vaguely like this air code:

Dim wc As String, cond As String, ctnm As String
Dim ctl As Control
Dim db As Database, tdf As TableDef
Dim fn As String
Const SQL As String = "INSERT INTO [name of table] " _
"SELECT flda, fldb, .... "

Set db = CurrentDb
Set tdf = db.TableDefs("name of table"

For Each ctl In Me
If ctl.Tag <> "" Then
If Not IsNull(ctl) Then
fn = "[" & ctl.Tag & "]"
cond = BuildCriteria(fn, tdf.Fields(fn).Type, ctl)
wc = wc & " AND (" & cond & ")"
End If
End If
Next ctl

db.Execute SQL & " WHERE " & Mid(wc, 6)

Note: the code assumes that the controls that can be used
for a criteria must have their Tag property set to the name
of the table field that the criteria applies to.

 

[Marshall Barton]

Once you fall prey to the power of BuildCriteria, you need
to think about a training course for anyone (besides
youself?) that will use that search form. BuildCriteria is
the same routine that the Access query designer uses to
translate a criteria "cell" into a Where clause condition
and the ins and out of that can baffle most anybody ;-)
--
Marsh
MVP [MS Access]

Now that is really nice. It took me a while to understand it. There is a lot
of stuff here that I can use. Thank you so much.

It will be an append query. Each auditor goes thru various training and
certifications. We track them for each auditor. This will put the
certifications for each auditor into a certifications table. I do not enter
this data myself so I need to make a form to make it easier for the
supervisors to enter many records at one time. One text box "fills" the
prikey for the certification to the query, another fills the date, another
the trainer, etc. The query gets all the auditors prikeys from the auditors
table. Sometimes, some auditors are absent for the training. They need to be
excluded from the append query. Hence the need to have a text box for "<>2
And <> 7 And <>18" (The prikey for each missing auditor who was absent).
Everything works just fine except for the "<>2 And <> 7 And <>18" thing. I
have found ways to build this into SelectionCriteria when opening forms and
reports but not directly into queries themselves. Docmd.openquery doesnt
appear to support selection criteria (filter).

Since an INSERT INTO (Append) query is an action query, you
should be using either RunSQL (asynchronous) or the Execute
method (synchronous). Both of those methods accept a string
that contains an SQL statment (instead of the name of a
saved querydef) so you can use code to construct the SQL
statment and then run it.

An outline of the kind of code you could use would look
vaguely like this air code:

Dim wc As String, cond As String, ctnm As String
Dim ctl As Control
Dim db As Database, tdf As TableDef
Dim fn As String
Const SQL As String = "INSERT INTO [name of table] " _
"SELECT flda, fldb, .... "

Set db = CurrentDb
Set tdf = db.TableDefs("name of table"

For Each ctl In Me
If ctl.Tag <> "" Then
If Not IsNull(ctl) Then
fn = "[" & ctl.Tag & "]"
cond = BuildCriteria(fn, tdf.Fields(fn).Type, ctl)
wc = wc & " AND (" & cond & ")"
End If
End If
Next ctl

db.Execute SQL & " WHERE " & Mid(wc, 6)

Note: the code assumes that the controls that can be used
for a criteria must have their Tag property set to the name
of the table field that the criteria applies to.

 

[Ray]

I thought my IIF's were pretty spiffy, and they will still serve me well, but
this sure adds an extra dimension. I have always hated input boxes because
they are time consuming and so limited (and just not cool). "For Each ctl In
Me" is also great. "Lights will be a flashing" on my forms when I put this to
use. It is so compact. Thanks again.

Once you fall prey to the power of BuildCriteria, you need
to think about a training course for anyone (besides
youself?) that will use that search form. BuildCriteria is
the same routine that the Access query designer uses to
translate a criteria "cell" into a Where clause condition
and the ins and out of that can baffle most anybody ;-)
--
Marsh
MVP [MS Access]

Now that is really nice. It took me a while to understand it. There is a lot
of stuff here that I can use. Thank you so much.

Ray wrote:

It will be an append query. Each auditor goes thru various training and
certifications. We track them for each auditor. This will put the
certifications for each auditor into a certifications table. I do not enter
this data myself so I need to make a form to make it easier for the
supervisors to enter many records at one time. One text box "fills" the
prikey for the certification to the query, another fills the date, another
the trainer, etc. The query gets all the auditors prikeys from the auditors
table. Sometimes, some auditors are absent for the training. They need to be
excluded from the append query. Hence the need to have a text box for "<>2
And <> 7 And <>18" (The prikey for each missing auditor who was absent).
Everything works just fine except for the "<>2 And <> 7 And <>18" thing. I
have found ways to build this into SelectionCriteria when opening forms and
reports but not directly into queries themselves. Docmd.openquery doesnt
appear to support selection criteria (filter).


Since an INSERT INTO (Append) query is an action query, you
should be using either RunSQL (asynchronous) or the Execute
method (synchronous). Both of those methods accept a string
that contains an SQL statment (instead of the name of a
saved querydef) so you can use code to construct the SQL
statment and then run it.

An outline of the kind of code you could use would look
vaguely like this air code:

Dim wc As String, cond As String, ctnm As String
Dim ctl As Control
Dim db As Database, tdf As TableDef
Dim fn As String
Const SQL As String = "INSERT INTO [name of table] " _
"SELECT flda, fldb, .... "

Set db = CurrentDb
Set tdf = db.TableDefs("name of table"

For Each ctl In Me
If ctl.Tag <> "" Then
If Not IsNull(ctl) Then
fn = "[" & ctl.Tag & "]"
cond = BuildCriteria(fn, tdf.Fields(fn).Type, ctl)
wc = wc & " AND (" & cond & ")"
End If
End If
Next ctl

db.Execute SQL & " WHERE " & Mid(wc, 6)

Note: the code assumes that the controls that can be used
for a criteria must have their Tag property set to the name
of the table field that the criteria applies to.

 

[Ray]

Why the
Mid(wc, 6)

in the final line?
db.Execute SQL & " WHERE " & Mid(wc, 6)

Once you fall prey to the power of BuildCriteria, you need
to think about a training course for anyone (besides
youself?) that will use that search form. BuildCriteria is
the same routine that the Access query designer uses to
translate a criteria "cell" into a Where clause condition
and the ins and out of that can baffle most anybody ;-)
--
Marsh
MVP [MS Access]

Now that is really nice. It took me a while to understand it. There is a lot
of stuff here that I can use. Thank you so much.

Ray wrote:

It will be an append query. Each auditor goes thru various training and
certifications. We track them for each auditor. This will put the
certifications for each auditor into a certifications table. I do not enter
this data myself so I need to make a form to make it easier for the
supervisors to enter many records at one time. One text box "fills" the
prikey for the certification to the query, another fills the date, another
the trainer, etc. The query gets all the auditors prikeys from the auditors
table. Sometimes, some auditors are absent for the training. They need to be
excluded from the append query. Hence the need to have a text box for "<>2
And <> 7 And <>18" (The prikey for each missing auditor who was absent).
Everything works just fine except for the "<>2 And <> 7 And <>18" thing. I
have found ways to build this into SelectionCriteria when opening forms and
reports but not directly into queries themselves. Docmd.openquery doesnt
appear to support selection criteria (filter).


Since an INSERT INTO (Append) query is an action query, you
should be using either RunSQL (asynchronous) or the Execute
method (synchronous). Both of those methods accept a string
that contains an SQL statment (instead of the name of a
saved querydef) so you can use code to construct the SQL
statment and then run it.

An outline of the kind of code you could use would look
vaguely like this air code:

Dim wc As String, cond As String, ctnm As String
Dim ctl As Control
Dim db As Database, tdf As TableDef
Dim fn As String
Const SQL As String = "INSERT INTO [name of table] " _
"SELECT flda, fldb, .... "

Set db = CurrentDb
Set tdf = db.TableDefs("name of table"

For Each ctl In Me
If ctl.Tag <> "" Then
If Not IsNull(ctl) Then
fn = "[" & ctl.Tag & "]"
cond = BuildCriteria(fn, tdf.Fields(fn).Type, ctl)
wc = wc & " AND (" & cond & ")"
End If
End If
Next ctl

db.Execute SQL & " WHERE " & Mid(wc, 6)

Note: the code assumes that the controls that can be used
for a criteria must have their Tag property set to the name
of the table field that the criteria applies to.

 

[Marshall Barton]

The constructed where string looks like:
" AND f1=123 AND Not f2 IN(2,4,6) And . . ."
so the Mid(wc, 6) is needed to strip off the initial " AND "
--
Marsh
MVP [MS Access]

Why the
Mid(wc, 6)

in the final line?
db.Execute SQL & " WHERE " & Mid(wc, 6)

"Marshall Barton" wrote: [snip]
Const SQL As String = "INSERT INTO [name of table] " _
"SELECT flda, fldb, .... "

Set db = CurrentDb
Set tdf = db.TableDefs("name of table"

For Each ctl In Me
If ctl.Tag <> "" Then
If Not IsNull(ctl) Then
fn = "[" & ctl.Tag & "]"
cond = BuildCriteria(fn, tdf.Fields(fn).Type, ctl)
wc = wc & " AND (" & cond & ")"
End If
End If
Next ctl

db.Execute SQL & " WHERE " & Mid(wc, 6)

Note: the code assumes that the controls that can be used
for a criteria must have their Tag property set to the name
of the table field that the criteria applies to.