Multi select box Query problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm having a problem running a query using a Multi Select List Box. Right
now I'm just trying for one item, so my Criteria for the Year field looks
like this...

Forms!frmCreateReport.[crcYear].ItemData(2)

If I use the Immediate Window and write this in, it gives me the answer of
2005... which is what I would like the query to use for it's query.

However when I input this in the query it gives me a user defined function
error. Any one know the solution to this problem?

Thanks in advance.
 
Once the MultiSelect property is set to other than None, you can't refer to
list boxes in that way in queries. Since your intent is to allow
multiselection, you may as well do it properly now, which means building up
the Where clause in VBA, and changing the SQL associated with the query.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" for an
example.
 
Hey Douglas,

Thanks for the help. However I plugged in the code, attached it to a Text
Box, so now my parameter looks like

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident]=January OR [Month of Incident]=March


Then in the actual query I put in the criteria
Forms!frmCreateReport.cTest.Value

cTest is the textbox with the above value.

However the query comes up blank. Any ideas?

Douglas J. Steele said:
Once the MultiSelect property is set to other than None, you can't refer to
list boxes in that way in queries. Since your intent is to allow
multiselection, you may as well do it properly now, which means building up
the Where clause in VBA, and changing the SQL associated with the query.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" for an
example.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


AKphidelt said:
Hi, I'm having a problem running a query using a Multi Select List Box.
Right
now I'm just trying for one item, so my Criteria for the Year field looks
like this...

Forms!frmCreateReport.[crcYear].ItemData(2)

If I use the Immediate Window and write this in, it gives me the answer of
2005... which is what I would like the query to use for it's query.

However when I input this in the query it gives me a user defined function
error. Any one know the solution to this problem?

Thanks in advance.
Click to expand...
Click to expand...
 
If [Month of Incident] contains the month, spelled out in letters, you need
quotes around the values for which you're checking:

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident]="January" OR [Month of Incident]="March"

Alternatively, you could use

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of Incident] In
("January", "March")

If [Month of Incident] contains something else, what does it contain?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


AKphidelt said:
Hey Douglas,

Thanks for the help. However I plugged in the code, attached it to a Text
Box, so now my parameter looks like

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident]=January OR [Month of Incident]=March


Then in the actual query I put in the criteria
Forms!frmCreateReport.cTest.Value

cTest is the textbox with the above value.

However the query comes up blank. Any ideas?

Douglas J. Steele said:
Once the MultiSelect property is set to other than None, you can't refer
to
list boxes in that way in queries. Since your intent is to allow
multiselection, you may as well do it properly now, which means building
up
the Where clause in VBA, and changing the SQL associated with the query.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" for
an
example.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


AKphidelt said:
Hi, I'm having a problem running a query using a Multi Select List Box.
Right
now I'm just trying for one item, so my Criteria for the Year field
looks
like this...

Forms!frmCreateReport.[crcYear].ItemData(2)

If I use the Immediate Window and write this in, it gives me the answer
of
2005... which is what I would like the query to use for it's query.

However when I input this in the query it gives me a user defined
function
error. Any one know the solution to this problem?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Hey, thanks a ton for your help. Month does mean the actual month name.
However when I enter the syntax in to the query criteria... I keep getting an
error message that says "The syntax of the subquery in this expression is
incorrect". "Check the subquery's syntax and enclose the subquery in
parentheses"

But when I use parenthesis it says file can't be found. Any clue?

Thanks

Douglas J. Steele said:
If [Month of Incident] contains the month, spelled out in letters, you need
quotes around the values for which you're checking:

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident]="January" OR [Month of Incident]="March"

Alternatively, you could use

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of Incident] In
("January", "March")

If [Month of Incident] contains something else, what does it contain?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


AKphidelt said:
Hey Douglas,

Thanks for the help. However I plugged in the code, attached it to a Text
Box, so now my parameter looks like

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident]=January OR [Month of Incident]=March


Then in the actual query I put in the criteria
Forms!frmCreateReport.cTest.Value

cTest is the textbox with the above value.

However the query comes up blank. Any ideas?

Douglas J. Steele said:
Once the MultiSelect property is set to other than None, you can't refer
to
list boxes in that way in queries. Since your intent is to allow
multiselection, you may as well do it properly now, which means building
up
the Where clause in VBA, and changing the SQL associated with the query.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" for
an
example.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi, I'm having a problem running a query using a Multi Select List Box.
Right
now I'm just trying for one item, so my Criteria for the Year field
looks
like this...

Forms!frmCreateReport.[crcYear].ItemData(2)

If I use the Immediate Window and write this in, it gives me the answer
of
2005... which is what I would like the query to use for it's query.

However when I input this in the query it gives me a user defined
function
error. Any one know the solution to this problem?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Are you typing that entire SQL string into the Criteria cell under a
specific field in your query grid? It's no wonder Access is confused: since
you're returning a number of fields in the subselect (since you're using
Select *, rather than Select FieldName), Access has no way of knowing what
it's supposed to do with the subselect. If you're trying to limit the
selection to only those rows returned by the subselect, you'd want to type

IN (Select FieldName FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident] In ("January", "March"))

as the criteria (although you'd be better off, in my opinion, joining
tblMasterlist to your other table(s), rather than trying to do it as a
subselect)

If you're trying to use that SQL some other way, please explain what that
other way is.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


AKphidelt said:
Hey, thanks a ton for your help. Month does mean the actual month name.
However when I enter the syntax in to the query criteria... I keep getting
an
error message that says "The syntax of the subquery in this expression is
incorrect". "Check the subquery's syntax and enclose the subquery in
parentheses"

But when I use parenthesis it says file can't be found. Any clue?

Thanks

Douglas J. Steele said:
If [Month of Incident] contains the month, spelled out in letters, you
need
quotes around the values for which you're checking:

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident]="January" OR [Month of Incident]="March"

Alternatively, you could use

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of Incident]
In
("January", "March")

If [Month of Incident] contains something else, what does it contain?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


AKphidelt said:
Hey Douglas,

Thanks for the help. However I plugged in the code, attached it to a
Text
Box, so now my parameter looks like

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident]=January OR [Month of Incident]=March


Then in the actual query I put in the criteria
Forms!frmCreateReport.cTest.Value

cTest is the textbox with the above value.

However the query comes up blank. Any ideas?

:

Once the MultiSelect property is set to other than None, you can't
refer
to
list boxes in that way in queries. Since your intent is to allow
multiselection, you may as well do it properly now, which means
building
up
the Where clause in VBA, and changing the SQL associated with the
query.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web"
for
an
example.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi, I'm having a problem running a query using a Multi Select List
Box.
Right
now I'm just trying for one item, so my Criteria for the Year field
looks
like this...

Forms!frmCreateReport.[crcYear].ItemData(2)

If I use the Immediate Window and write this in, it gives me the
answer
of
2005... which is what I would like the query to use for it's query.

However when I input this in the query it gives me a user defined
function
error. Any one know the solution to this problem?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Good Morning,

Well I've only been using access for 2 weeks so bare with me if I sound like
an idiot. But what I currently have is a form that allows people to create
their own reports based on Multi Select List Boxes that are tied to a query.
My problem I think is I'm using the queries Criteria box in the query design
window to try an get the SQL syntax from the form. I built a way around this
by making hidden text boxes next to each item in the multi select and then
having many OR criterias in the query. But it becomes a problem when I want
the user to be allowed to select certain months also. Hope this makes things
a little clearer. I've been messing around with it and tried everything in
the criteria but an SQL syntax I use just won't work?

Thanks again for your time.

Douglas J. Steele said:
Are you typing that entire SQL string into the Criteria cell under a
specific field in your query grid? It's no wonder Access is confused: since
you're returning a number of fields in the subselect (since you're using
Select *, rather than Select FieldName), Access has no way of knowing what
it's supposed to do with the subselect. If you're trying to limit the
selection to only those rows returned by the subselect, you'd want to type

IN (Select FieldName FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident] In ("January", "March"))

as the criteria (although you'd be better off, in my opinion, joining
tblMasterlist to your other table(s), rather than trying to do it as a
subselect)

If you're trying to use that SQL some other way, please explain what that
other way is.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


AKphidelt said:
Hey, thanks a ton for your help. Month does mean the actual month name.
However when I enter the syntax in to the query criteria... I keep getting
an
error message that says "The syntax of the subquery in this expression is
incorrect". "Check the subquery's syntax and enclose the subquery in
parentheses"

But when I use parenthesis it says file can't be found. Any clue?

Thanks

Douglas J. Steele said:
If [Month of Incident] contains the month, spelled out in letters, you
need
quotes around the values for which you're checking:

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident]="January" OR [Month of Incident]="March"

Alternatively, you could use

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of Incident]
In
("January", "March")

If [Month of Incident] contains something else, what does it contain?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hey Douglas,

Thanks for the help. However I plugged in the code, attached it to a
Text
Box, so now my parameter looks like

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident]=January OR [Month of Incident]=March


Then in the actual query I put in the criteria
Forms!frmCreateReport.cTest.Value

cTest is the textbox with the above value.

However the query comes up blank. Any ideas?

:

Once the MultiSelect property is set to other than None, you can't
refer
to
list boxes in that way in queries. Since your intent is to allow
multiselection, you may as well do it properly now, which means
building
up
the Where clause in VBA, and changing the SQL associated with the
query.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web"
for
an
example.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi, I'm having a problem running a query using a Multi Select List
Box.
Right
now I'm just trying for one item, so my Criteria for the Year field
looks
like this...

Forms!frmCreateReport.[crcYear].ItemData(2)

If I use the Immediate Window and write this in, it gives me the
answer
of
2005... which is what I would like the query to use for it's query.

However when I input this in the query it gives me a user defined
function
error. Any one know the solution to this problem?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, no, that doesn't make things any clearer to me.

Fact of the matter is, using multiselect list boxes in queries in Access
requires the use of VBA. Since you're going to have to use VBA anyhow, you
might as well build the entire SQL string (including the month-related
criteria) in VBA.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


AKphidelt said:
Good Morning,

Well I've only been using access for 2 weeks so bare with me if I sound
like
an idiot. But what I currently have is a form that allows people to create
their own reports based on Multi Select List Boxes that are tied to a
query.
My problem I think is I'm using the queries Criteria box in the query
design
window to try an get the SQL syntax from the form. I built a way around
this
by making hidden text boxes next to each item in the multi select and then
having many OR criterias in the query. But it becomes a problem when I
want
the user to be allowed to select certain months also. Hope this makes
things
a little clearer. I've been messing around with it and tried everything in
the criteria but an SQL syntax I use just won't work?

Thanks again for your time.

Douglas J. Steele said:
Are you typing that entire SQL string into the Criteria cell under a
specific field in your query grid? It's no wonder Access is confused:
since
you're returning a number of fields in the subselect (since you're using
Select *, rather than Select FieldName), Access has no way of knowing
what
it's supposed to do with the subselect. If you're trying to limit the
selection to only those rows returned by the subselect, you'd want to
type

IN (Select FieldName FROM tblMasterlist.[Month of Incident] WHERE [Month
of
Incident] In ("January", "March"))

as the criteria (although you'd be better off, in my opinion, joining
tblMasterlist to your other table(s), rather than trying to do it as a
subselect)

If you're trying to use that SQL some other way, please explain what that
other way is.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


AKphidelt said:
Hey, thanks a ton for your help. Month does mean the actual month name.
However when I enter the syntax in to the query criteria... I keep
getting
an
error message that says "The syntax of the subquery in this expression
is
incorrect". "Check the subquery's syntax and enclose the subquery in
parentheses"

But when I use parenthesis it says file can't be found. Any clue?

Thanks

:

If [Month of Incident] contains the month, spelled out in letters, you
need
quotes around the values for which you're checking:

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident]="January" OR [Month of Incident]="March"

Alternatively, you could use

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident]
In
("January", "March")

If [Month of Incident] contains something else, what does it contain?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hey Douglas,

Thanks for the help. However I plugged in the code, attached it to a
Text
Box, so now my parameter looks like

Select * FROM tblMasterlist.[Month of Incident] WHERE [Month of
Incident]=January OR [Month of Incident]=March


Then in the actual query I put in the criteria
Forms!frmCreateReport.cTest.Value

cTest is the textbox with the above value.

However the query comes up blank. Any ideas?

:

Once the MultiSelect property is set to other than None, you can't
refer
to
list boxes in that way in queries. Since your intent is to allow
multiselection, you may as well do it properly now, which means
building
up
the Where clause in VBA, and changing the SQL associated with the
query.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access
Web"
for
an
example.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi, I'm having a problem running a query using a Multi Select
List
Box.
Right
now I'm just trying for one item, so my Criteria for the Year
field
looks
like this...

Forms!frmCreateReport.[crcYear].ItemData(2)

If I use the Immediate Window and write this in, it gives me the
answer
of
2005... which is what I would like the query to use for it's
query.

However when I input this in the query it gives me a user defined
function
error. Any one know the solution to this problem?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top