Help with criteria in query builder

[Jasonm]

Hi all!

I am trying to use the following expression as criteria in a query:

IIf([forms].[frmreportsmenu].[cbolocation].[listindex]>0,[forms].[frmreportsmenu].[cbolocation],Is
Not Null)

The problem is that I never return the value - Is Not Null... at least it
does not appear that I am returning that value. If I use "Is Not Null" alone
I get all records in this field. If a selection is made in cboLocation then
only that location is returned. Based on that it seems that everything
should be correct... but still not all records!

I have tried enclosing the phrase in "Is Not Null" and 'Is Not Null' and I
have also tried the following expression, but it seems to work just like the
above expression:

nz([forms].[frmreportsmenu].[cbolocation],"'Is Not Null")

Does anyone here have any ideas about why this would not return the needed
value? All I want to do is return either a single location or all
locations... seems simple enough. (it's always the simple ones...)

Thanks in advance for any assistance that you can offer
Jasonm

 

[Graham R Seach]

Jason,

If there are no records in the combo's list portion, then ListIndex will be
zero (0). It can never be Null.

If what you're trying to do is check for a value (or Null) in the combo's
Value property, then the following will work:
Nz([Forms].[frmReportsMEnu].cboLocation, 0)

....but of course, it all depends on what is in the combo's BoundColumn. If
it's a number, then the above will work, but if its text, we'll have to use
this:
Nz([Forms].[frmReportsMEnu].cboLocation, "")

Can you be a bit more specific about exactly what you're trying to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Jasonm]

Graham, What I am looking for is to have the criteria for the query column
either be the item selected in the combo box or all of the records in the
dataset.

my intent with the two variants that I posted earlier was to return either
the selected item in the combo box or the value is not null to be used as
the criteria. If I use the phrase " Is Not Null" - and only that phrase - I
do get all of the records that I want. It is when I combine this with any
other function that evaluates wether or not an item has been selected in the
combo box that I run into trouble. I can return the value from the combo box
and get only results related to the selected location, but if I never select
a location I never get the value "is not null" to return all of the records.

It seems like this is harder to explain that it seems to be to impliment...
I am still trying, but if you have another suggestion please let me know.
the field that I am trying to filter is text, so if I use the "" it filters
all the records.

Thanks for the thoughts. I look forward to anything else that you might be
able to offer.

Jasonm

Jason,

If there are no records in the combo's list portion, then ListIndex will
be zero (0). It can never be Null.

If what you're trying to do is check for a value (or Null) in the combo's
Value property, then the following will work:
Nz([Forms].[frmReportsMEnu].cboLocation, 0)

...but of course, it all depends on what is in the combo's BoundColumn. If
it's a number, then the above will work, but if its text, we'll have to
use this:
Nz([Forms].[frmReportsMEnu].cboLocation, "")

Can you be a bit more specific about exactly what you're trying to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Hi all!

I am trying to use the following expression as criteria in a query:

IIf([forms].[frmreportsmenu].[cbolocation].[listindex]>0,[forms].[frmreportsmenu].[cbolocation],Is
Not Null)

The problem is that I never return the value - Is Not Null... at least it
does not appear that I am returning that value. If I use "Is Not Null"
alone I get all records in this field. If a selection is made in
cboLocation then only that location is returned. Based on that it seems
that everything should be correct... but still not all records!

I have tried enclosing the phrase in "Is Not Null" and 'Is Not Null' and
I have also tried the following expression, but it seems to work just
like the above expression:

nz([forms].[frmreportsmenu].[cbolocation],"'Is Not Null")

Does anyone here have any ideas about why this would not return the
needed value? All I want to do is return either a single location or all
locations... seems simple enough. (it's always the simple ones...)

Thanks in advance for any assistance that you can offer
Jasonm

 

[Jasonm]

Just a quick note of follow up.

I have ended up getting what I needed (almost) by writing the selected
values from the list box to a textbox(invisible) on the form and using the
text boxes for the criteria. It is not exactly what I want, but for now it
is working.

I will keep trying to impliment the code as it has been mentioned in these
posts.

Jasonm

Jason,

If there are no records in the combo's list portion, then ListIndex will
be zero (0). It can never be Null.

If what you're trying to do is check for a value (or Null) in the combo's
Value property, then the following will work:
Nz([Forms].[frmReportsMEnu].cboLocation, 0)

...but of course, it all depends on what is in the combo's BoundColumn. If
it's a number, then the above will work, but if its text, we'll have to
use this:
Nz([Forms].[frmReportsMEnu].cboLocation, "")

Can you be a bit more specific about exactly what you're trying to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Hi all!

I am trying to use the following expression as criteria in a query:

IIf([forms].[frmreportsmenu].[cbolocation].[listindex]>0,[forms].[frmreportsmenu].[cbolocation],Is
Not Null)

The problem is that I never return the value - Is Not Null... at least it
does not appear that I am returning that value. If I use "Is Not Null"
alone I get all records in this field. If a selection is made in
cboLocation then only that location is returned. Based on that it seems
that everything should be correct... but still not all records!

I have tried enclosing the phrase in "Is Not Null" and 'Is Not Null' and
I have also tried the following expression, but it seems to work just
like the above expression:

nz([forms].[frmreportsmenu].[cbolocation],"'Is Not Null")

Does anyone here have any ideas about why this would not return the
needed value? All I want to do is return either a single location or all
locations... seems simple enough. (it's always the simple ones...)

Thanks in advance for any assistance that you can offer
Jasonm

 

[Graham R Seach]

Jason,

I think you'll have to build the query manually, in one of two ways.

Firstly, if you're doing it all in code:
sSQL = "SELECT * FROM tblMyTable"
If Not IsNull(me.cboMyComboBox) Then
sSQL = sSQL & " WHERE somefield = """ & Me.cboMyComboBox & """"
End If

Secondly, if you have a saved query, then all you need to do is feed the
combo's value to it if it's not null:
If Not IsNull(Me.cboMyCombo) Then
sSQL = "SELECT * FROM qryMyQuery WHERE somefield = """ &
Me.cboMyCombo & """"
Else
'...otherwise just call the query without any arguments:
sSQL = "SELECT * FROM qryMyQuery"
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Graham, What I am looking for is to have the criteria for the query column
either be the item selected in the combo box or all of the records in the
dataset.

my intent with the two variants that I posted earlier was to return either
the selected item in the combo box or the value is not null to be used as
the criteria. If I use the phrase " Is Not Null" - and only that phrase -
I do get all of the records that I want. It is when I combine this with
any other function that evaluates wether or not an item has been selected
in the combo box that I run into trouble. I can return the value from the
combo box and get only results related to the selected location, but if I
never select a location I never get the value "is not null" to return all
of the records.

It seems like this is harder to explain that it seems to be to
impliment... I am still trying, but if you have another suggestion please
let me know. the field that I am trying to filter is text, so if I use the
"" it filters all the records.

Thanks for the thoughts. I look forward to anything else that you might be
able to offer.

Jasonm

Jason,

If there are no records in the combo's list portion, then ListIndex will
be zero (0). It can never be Null.

If what you're trying to do is check for a value (or Null) in the combo's
Value property, then the following will work:
Nz([Forms].[frmReportsMEnu].cboLocation, 0)

...but of course, it all depends on what is in the combo's BoundColumn.
If it's a number, then the above will work, but if its text, we'll have
to use this:
Nz([Forms].[frmReportsMEnu].cboLocation, "")

Can you be a bit more specific about exactly what you're trying to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Hi all!

I am trying to use the following expression as criteria in a query:

IIf([forms].[frmreportsmenu].[cbolocation].[listindex]>0,[forms].[frmreportsmenu].[cbolocation],Is
Not Null)

The problem is that I never return the value - Is Not Null... at least
it does not appear that I am returning that value. If I use "Is Not
Null" alone I get all records in this field. If a selection is made in
cboLocation then only that location is returned. Based on that it seems
that everything should be correct... but still not all records!

I have tried enclosing the phrase in "Is Not Null" and 'Is Not Null' and
I have also tried the following expression, but it seems to work just
like the above expression:

nz([forms].[frmreportsmenu].[cbolocation],"'Is Not Null")

Does anyone here have any ideas about why this would not return the
needed value? All I want to do is return either a single location or all
locations... seems simple enough. (it's always the simple ones...)

Thanks in advance for any assistance that you can offer
Jasonm