Record sets-changing null values

[Guest]

I am using a parameter query that gets its values from combo boxes. However,
null values will cause records not to be returned. It is not feasible to
change the null values at the form level so I thought I would use ADO to
change the null values to “No Data†at the record level. Now my questions
1. Does anyone have a bit of generic code that I can reference for looping
through a record set and changing null values to strings? Or is there a
simpler way of doing it?. I assume it would be something like a†for each-inâ€
statement with a “if (null) then (“no-dataâ€)†However I am just learning Vba
I would REALY appreciate a little push on the syntax
2. I am thinking of splitting the database. Do I need to do anything
special, say in defining the connection, if I am connecting to linked-tables?

Many thanks!

 

[SusanV]

Don't change your data! You can use the Nz function instead to return a
value where records are Null. This page shows how:
http://www.techonthenet.com/access/functions/advanced/nz.php

 

[Guest]

Hi Suzan,
This is what I have. I have a query that gets it criteria values from a form
with many, many combo boxes. In the criteria area I have
Like NZ([forms]![reports_switch_main]![text21],"*")
Since all the combo boxes are blank, it will return an “*†for the criteria
unless they chose a value, in which case it will use that value for the
criteria.

The problem arises because an “*†will not recognize null data values in the
table and thus any record that has one will not be returned. I suppose one
way around this would be if somehow instead of an “*†I could just tell it if
null to ignore the criteria altogether. You got me thinking here. I just
tried <null or Like NZ([forms]![reports_switch_main]![text21],"*") > and it
seems to have worked fine. This should accept the parameter value, or return
all records whether they are null or not. Do you see anything here that could
go wrong that I am missing?

Many thanks!!,

 

[SusanV]

Seems to me that should work just fine! Glad I could help,

Susan

Hi Suzan,
This is what I have. I have a query that gets it criteria values from a
form
with many, many combo boxes. In the criteria area I have
Like NZ([forms]![reports_switch_main]![text21],"*")
Since all the combo boxes are blank, it will return an "*" for the
criteria
unless they chose a value, in which case it will use that value for the
criteria.

The problem arises because an "*" will not recognize null data values in
the
table and thus any record that has one will not be returned. I suppose one
way around this would be if somehow instead of an "*" I could just tell it
if
null to ignore the criteria altogether. You got me thinking here. I just
tried <null or Like NZ([forms]![reports_switch_main]![text21],"*") > and
it
seems to have worked fine. This should accept the parameter value, or
return
all records whether they are null or not. Do you see anything here that
could
go wrong that I am missing?

Many thanks!!,

Don't change your data! You can use the Nz function instead to return a
value where records are Null. This page shows how:
http://www.techonthenet.com/access/functions/advanced/nz.php

 

[Guest]

Just realized that I had tried this (see previouse message) before. It won't
work because the query will return a record just because it is null. Back to
square one! Is there anyway to return all records, null or not, if a
parameter is not chosen??

thanks

 

[Guest]

Hi Susan,

Sorry for all this, just sent out a message were i said something about a
parameter not being chosen- was talking to someone and ... What I wanted to
say was how to use a parameter query where a record is null for the field? I
cannot see any other way but to work with a record set and "simply" change
the null to a value or zero length string.
I can only assume that this is a common problem with anyone who is using a
parameter query like this???

Any other suggestions???

Thanks

Seems to me that should work just fine! Glad I could help,

Susan

Hi Suzan,
This is what I have. I have a query that gets it criteria values from a
form
with many, many combo boxes. In the criteria area I have
Like NZ([forms]![reports_switch_main]![text21],"*")
Since all the combo boxes are blank, it will return an "*" for the
criteria
unless they chose a value, in which case it will use that value for the
criteria.

The problem arises because an "*" will not recognize null data values in
the
table and thus any record that has one will not be returned. I suppose one
way around this would be if somehow instead of an "*" I could just tell it
if
null to ignore the criteria altogether. You got me thinking here. I just
tried <null or Like NZ([forms]![reports_switch_main]![text21],"*") > and
it
seems to have worked fine. This should accept the parameter value, or
return
all records whether they are null or not. Do you see anything here that
could
go wrong that I am missing?

Many thanks!!,

Don't change your data! You can use the Nz function instead to return a
value where records are Null. This page shows how:
http://www.techonthenet.com/access/functions/advanced/nz.php
--
hth,
SusanV

I am using a parameter query that gets its values from combo boxes.
However,
null values will cause records not to be returned. It is not feasible
to
change the null values at the form level so I thought I would use ADO
to
change the null values to "No Data" at the record level. Now my
questions
1. Does anyone have a bit of generic code that I can reference for
looping
through a record set and changing null values to strings? Or is there a
simpler way of doing it?. I assume it would be something like a" for
each-in"
statement with a "if (null) then ("no-data")" However I am just
learning
Vba
I would REALY appreciate a little push on the syntax
2. I am thinking of splitting the database. Do I need to do anything
special, say in defining the connection, if I am connecting to
linked-tables?

Many thanks!

 

[SusanV]

Ok, sorry, let's test the combo box for null, not the data:
'''''''''''''''''''''
dim strSQL as String

If isNull(Me.ComboBox) then
strSQL = "SELECT * from YourTable"
Else
strSQL = ".............Whatever your original query was........."
End If
'''''''''''''''''''''

 

[SusanV]

What are you using the query to do? Could you post the SQL?

Hi Susan,

Sorry for all this, just sent out a message were i said something about a
parameter not being chosen- was talking to someone and ... What I wanted
to
say was how to use a parameter query where a record is null for the field?
I
cannot see any other way but to work with a record set and "simply" change
the null to a value or zero length string.
I can only assume that this is a common problem with anyone who is using a
parameter query like this???

Any other suggestions???

Thanks

Seems to me that should work just fine! Glad I could help,

Susan

Hi Suzan,
This is what I have. I have a query that gets it criteria values from a
form
with many, many combo boxes. In the criteria area I have
Like NZ([forms]![reports_switch_main]![text21],"*")
Since all the combo boxes are blank, it will return an "*" for the
criteria
unless they chose a value, in which case it will use that value for the
criteria.

The problem arises because an "*" will not recognize null data values
in
the
table and thus any record that has one will not be returned. I suppose
one
way around this would be if somehow instead of an "*" I could just tell
it
if
null to ignore the criteria altogether. You got me thinking here. I
just
tried <null or Like NZ([forms]![reports_switch_main]![text21],"*") >
and
it
seems to have worked fine. This should accept the parameter value, or
return
all records whether they are null or not. Do you see anything here that
could
go wrong that I am missing?

Many thanks!!,


:

Don't change your data! You can use the Nz function instead to return
a
value where records are Null. This page shows how:
http://www.techonthenet.com/access/functions/advanced/nz.php
--
hth,
SusanV

I am using a parameter query that gets its values from combo boxes.
However,
null values will cause records not to be returned. It is not
feasible
to
change the null values at the form level so I thought I would use
ADO
to
change the null values to "No Data" at the record level. Now my
questions
1. Does anyone have a bit of generic code that I can reference for
looping
through a record set and changing null values to strings? Or is
there a
simpler way of doing it?. I assume it would be something like a" for
each-in"
statement with a "if (null) then ("no-data")" However I am just
learning
Vba
I would REALY appreciate a little push on the syntax
2. I am thinking of splitting the database. Do I need to do anything
special, say in defining the connection, if I am connecting to
linked-tables?

Many thanks!

 

[Guest]

Susan,
You got me here. I don't have a clue what to do with this (unless I use it
behind the form or combo box). Also, the problem is not whether the combo box
is null- it is always null unless a value is selected to use as the criteria
in the query. Therefore Like [forms]![reports_switch_main]![combo44] in the
criteria will return all records based on the chosen value in the combo box
or, if the combo box is null, all records from the table EXEPT those with a
null value- because the "*" will not return a record with a field with a null
value. Thus you can see the problem- if i have a null value in a table's
field and the combo box for it does not contain a value, even though it is
not suposed to be involved in filtering the returned data, all records with
null values for that field will not be returned!!!

Thus it seems that I must either not use the "*" in the criteria or no null
values can be in the tables. i fixed the problem for a few forms with code
but it is just too complicated to do the entire db this way. Again, it seems
that working with record sets is the cleanest way to do this.

Any thoughts........???


thanks.........

 

[SusanV]

salmonella,

You're getting me all confused here - is this combo box set on a single
field lookup table or something? That's the only way I can figure that the
SQL "Select * from Table" is not going to return records with a null value
in some field or another.

What's the SQL statement you are trying to fill out?

What's the basis of the combo box? Is it a table or a value list?

Susan,
You got me here. I don't have a clue what to do with this (unless I use it
behind the form or combo box). Also, the problem is not whether the combo
box
is null- it is always null unless a value is selected to use as the
criteria
in the query. Therefore Like [forms]![reports_switch_main]![combo44] in
the
criteria will return all records based on the chosen value in the combo
box
or, if the combo box is null, all records from the table EXEPT those with
a
null value- because the "*" will not return a record with a field with a
null
value. Thus you can see the problem- if i have a null value in a table's
field and the combo box for it does not contain a value, even though it is
not suposed to be involved in filtering the returned data, all records
with
null values for that field will not be returned!!!

Thus it seems that I must either not use the "*" in the criteria or no
null
values can be in the tables. i fixed the problem for a few forms with code
but it is just too complicated to do the entire db this way. Again, it
seems
that working with record sets is the cleanest way to do this.

Any thoughts........???


thanks.........

Ok, sorry, let's test the combo box for null, not the data:
'''''''''''''''''''''
dim strSQL as String

If isNull(Me.ComboBox) then
strSQL = "SELECT * from YourTable"
Else
strSQL = ".............Whatever your original query was........."
End If
'''''''''''''''''''''

 

[Guest]

Hi Susan,
Sorry for not being clearer. It is probably because I am new to all this.
The problem can really be condensed down to is there a way to return a record
when using a parameter query if the record has a null value in the field
which the criteria (parameter) is used on?

In brief (with just one report, table, field, etc as an example), I have a
switchboard that allows you to open a report and filter the results based on
the value in a combo box (populated from a field in a table). The combo box
is null unless someone chooses a value, and the report uses a query as the
record source wherein the “criteria†for the field is
Like NZ([forms]![switchbord]![combobox],"*")
Thus if no value is chosen in the combo box, then the criteria in the query
is “*â€, which is all records, EXCEPT those with a null value for the field
the criteria is working on. This will of coarse mean that certain records
will be lost in the Report simply because they had a null value in the combo
box (resulting in a “*†in the query criteria).

I really don’t think that there is any easy way around this at the
form/query level but I was thinking that through ADO I could loop through the
records in the tables and change all null values to a string value- which I
am just now exploring on how to use. But before I did this I just wanted to
check to be sure that there was not some easy way to use a parameter query on
records that had null values and still be able to return them.

Thanks and I hope this helps!

salmonella,

You're getting me all confused here - is this combo box set on a single
field lookup table or something? That's the only way I can figure that the
SQL "Select * from Table" is not going to return records with a null value
in some field or another.

What's the SQL statement you are trying to fill out?

What's the basis of the combo box? Is it a table or a value list?

Susan,
You got me here. I don't have a clue what to do with this (unless I use it
behind the form or combo box). Also, the problem is not whether the combo
box
is null- it is always null unless a value is selected to use as the
criteria
in the query. Therefore Like [forms]![reports_switch_main]![combo44] in
the
criteria will return all records based on the chosen value in the combo
box
or, if the combo box is null, all records from the table EXEPT those with
a
null value- because the "*" will not return a record with a field with a
null
value. Thus you can see the problem- if i have a null value in a table's
field and the combo box for it does not contain a value, even though it is
not suposed to be involved in filtering the returned data, all records
with
null values for that field will not be returned!!!

Thus it seems that I must either not use the "*" in the criteria or no
null
values can be in the tables. i fixed the problem for a few forms with code
but it is just too complicated to do the entire db this way. Again, it
seems
that working with record sets is the cleanest way to do this.

Any thoughts........???


thanks.........

Ok, sorry, let's test the combo box for null, not the data:
'''''''''''''''''''''
dim strSQL as String

If isNull(Me.ComboBox) then
strSQL = "SELECT * from YourTable"
Else
strSQL = ".............Whatever your original query was........."
End If
'''''''''''''''''''''




Just realized that I had tried this (see previouse message) before. It
won't
work because the query will return a record just because it is null.
Back
to
square one! Is there anyway to return all records, null or not, if a
parameter is not chosen??

thanks

:

Don't change your data! You can use the Nz function instead to return
a
value where records are Null. This page shows how:
http://www.techonthenet.com/access/functions/advanced/nz.php
--
hth,
SusanV

I am using a parameter query that gets its values from combo boxes.
However,
null values will cause records not to be returned. It is not
feasible
to
change the null values at the form level so I thought I would use
ADO
to
change the null values to "No Data" at the record level. Now my
questions
1. Does anyone have a bit of generic code that I can reference for
looping
through a record set and changing null values to strings? Or is
there a
simpler way of doing it?. I assume it would be something like a" for
each-in"
statement with a "if (null) then ("no-data")" However I am just
learning
Vba
I would REALY appreciate a little push on the syntax
2. I am thinking of splitting the database. Do I need to do anything
special, say in defining the connection, if I am connecting to
linked-tables?

Many thanks!

 

[SusanV]

ok that's quite a bit clearer. So basically you have a combo box which
defaults to no entry, and a button users click to open a report - which is
then based on the value of the combo box, right? I have a similar function
in one of my forms where users can search for reports based on manufacturer,
though I have a textbox control rather than a dropdown list. Although this
may not be the slickest way to do it, it works a treat. Basically it dumps
out via SQL to a temp table, then the report uses that temp table as it's
record source:


Private Sub btnSearchMfr_Click()
DoCmd.SetWarnings False
Dim Mfr As String
Dim sql As String

If IsNull(Me.txtMfr) Then
Mfr = ""
Else: Mfr = Me.txtMfr
End If

sql = "INSERT INTO tblSearchRep (" _
& "RepNum, Class, Title, Mfr, Model)" _
& "SELECT RepNum, Class, Title, Manufacturer, Model FROM tblPMO
WHERE " _
& "tblPMO.manufacturer LIKE " & "'" & "*" & Mfr & "*" & "'" _
& "ORDER BY manufacturer"

DoCmd.RunSQL "delete from tblSearchRep"
DoCmd.RunSQL sql
DoCmd.OpenReport "repSearchReports", acViewPreview
DoCmd.SetWarnings True
End Sub

The temp table is re-used for several buttons with similar function, but
providing different search parameters (model, equipment, etc). By using a
direct insert, VBA deletes and recreates the table on the fly, and if the
table doesn't pre-exist simply creates it:

Maybe this will work for you. Another option would be to test the combobox
for Null as I showed in my previous post, but instead of using a SQL
statement to set a where condition.

Hi Susan,
Sorry for not being clearer. It is probably because I am new to all this.
The problem can really be condensed down to is there a way to return a
record
when using a parameter query if the record has a null value in the field
which the criteria (parameter) is used on?

In brief (with just one report, table, field, etc as an example), I have a
switchboard that allows you to open a report and filter the results based
on
the value in a combo box (populated from a field in a table). The combo
box
is null unless someone chooses a value, and the report uses a query as the
record source wherein the "criteria" for the field is
Like NZ([forms]![switchbord]![combobox],"*")
Thus if no value is chosen in the combo box, then the criteria in the
query
is "*", which is all records, EXCEPT those with a null value for the field
the criteria is working on. This will of coarse mean that certain records
will be lost in the Report simply because they had a null value in the
combo
box (resulting in a "*" in the query criteria).

I really don't think that there is any easy way around this at the
form/query level but I was thinking that through ADO I could loop through
the
records in the tables and change all null values to a string value- which
I
am just now exploring on how to use. But before I did this I just wanted
to
check to be sure that there was not some easy way to use a parameter query
on
records that had null values and still be able to return them.

Thanks and I hope this helps!

salmonella,

You're getting me all confused here - is this combo box set on a single
field lookup table or something? That's the only way I can figure that
the
SQL "Select * from Table" is not going to return records with a null
value
in some field or another.

What's the SQL statement you are trying to fill out?

What's the basis of the combo box? Is it a table or a value list?

Susan,
You got me here. I don't have a clue what to do with this (unless I use
it
behind the form or combo box). Also, the problem is not whether the
combo
box
is null- it is always null unless a value is selected to use as the
criteria
in the query. Therefore Like [forms]![reports_switch_main]![combo44] in
the
criteria will return all records based on the chosen value in the combo
box
or, if the combo box is null, all records from the table EXEPT those
with
a
null value- because the "*" will not return a record with a field with
a
null
value. Thus you can see the problem- if i have a null value in a
table's
field and the combo box for it does not contain a value, even though it
is
not suposed to be involved in filtering the returned data, all records
with
null values for that field will not be returned!!!

Thus it seems that I must either not use the "*" in the criteria or no
null
values can be in the tables. i fixed the problem for a few forms with
code
but it is just too complicated to do the entire db this way. Again, it
seems
that working with record sets is the cleanest way to do this.

Any thoughts........???


thanks.........




:

Ok, sorry, let's test the combo box for null, not the data:
'''''''''''''''''''''
dim strSQL as String

If isNull(Me.ComboBox) then
strSQL = "SELECT * from YourTable"
Else
strSQL = ".............Whatever your original query was........."
End If
'''''''''''''''''''''




Just realized that I had tried this (see previouse message) before.
It
won't
work because the query will return a record just because it is null.
Back
to
square one! Is there anyway to return all records, null or not, if a
parameter is not chosen??

thanks

:

Don't change your data! You can use the Nz function instead to
return
a
value where records are Null. This page shows how:
http://www.techonthenet.com/access/functions/advanced/nz.php
--
hth,
SusanV

I am using a parameter query that gets its values from combo
boxes.
However,
null values will cause records not to be returned. It is not
feasible
to
change the null values at the form level so I thought I would use
ADO
to
change the null values to "No Data" at the record level. Now my
questions
1. Does anyone have a bit of generic code that I can reference
for
looping
through a record set and changing null values to strings? Or is
there a
simpler way of doing it?. I assume it would be something like a"
for
each-in"
statement with a "if (null) then ("no-data")" However I am just
learning
Vba
I would REALY appreciate a little push on the syntax
2. I am thinking of splitting the database. Do I need to do
anything
special, say in defining the connection, if I am connecting to
linked-tables?

Many thanks!

 

[Guest]

Thanks Susan!
Sorry for all the trouble. I will look into these suggestions and see if it
will work for me.

Thanks again for all your help

ok that's quite a bit clearer. So basically you have a combo box which
defaults to no entry, and a button users click to open a report - which is
then based on the value of the combo box, right? I have a similar function
in one of my forms where users can search for reports based on manufacturer,
though I have a textbox control rather than a dropdown list. Although this
may not be the slickest way to do it, it works a treat. Basically it dumps
out via SQL to a temp table, then the report uses that temp table as it's
record source:


Private Sub btnSearchMfr_Click()
DoCmd.SetWarnings False
Dim Mfr As String
Dim sql As String

If IsNull(Me.txtMfr) Then
Mfr = ""
Else: Mfr = Me.txtMfr
End If

sql = "INSERT INTO tblSearchRep (" _
& "RepNum, Class, Title, Mfr, Model)" _
& "SELECT RepNum, Class, Title, Manufacturer, Model FROM tblPMO
WHERE " _
& "tblPMO.manufacturer LIKE " & "'" & "*" & Mfr & "*" & "'" _
& "ORDER BY manufacturer"

DoCmd.RunSQL "delete from tblSearchRep"
DoCmd.RunSQL sql
DoCmd.OpenReport "repSearchReports", acViewPreview
DoCmd.SetWarnings True
End Sub

The temp table is re-used for several buttons with similar function, but
providing different search parameters (model, equipment, etc). By using a
direct insert, VBA deletes and recreates the table on the fly, and if the
table doesn't pre-exist simply creates it:

Maybe this will work for you. Another option would be to test the combobox
for Null as I showed in my previous post, but instead of using a SQL
statement to set a where condition.

Hi Susan,
Sorry for not being clearer. It is probably because I am new to all this.
The problem can really be condensed down to is there a way to return a
record
when using a parameter query if the record has a null value in the field
which the criteria (parameter) is used on?

In brief (with just one report, table, field, etc as an example), I have a
switchboard that allows you to open a report and filter the results based
on
the value in a combo box (populated from a field in a table). The combo
box
is null unless someone chooses a value, and the report uses a query as the
record source wherein the "criteria" for the field is
Like NZ([forms]![switchbord]![combobox],"*")
Thus if no value is chosen in the combo box, then the criteria in the
query
is "*", which is all records, EXCEPT those with a null value for the field
the criteria is working on. This will of coarse mean that certain records
will be lost in the Report simply because they had a null value in the
combo
box (resulting in a "*" in the query criteria).

I really don't think that there is any easy way around this at the
form/query level but I was thinking that through ADO I could loop through
the
records in the tables and change all null values to a string value- which
I
am just now exploring on how to use. But before I did this I just wanted
to
check to be sure that there was not some easy way to use a parameter query
on
records that had null values and still be able to return them.

Thanks and I hope this helps!

salmonella,

You're getting me all confused here - is this combo box set on a single
field lookup table or something? That's the only way I can figure that
the
SQL "Select * from Table" is not going to return records with a null
value
in some field or another.

What's the SQL statement you are trying to fill out?

What's the basis of the combo box? Is it a table or a value list?



Susan,
You got me here. I don't have a clue what to do with this (unless I use
it
behind the form or combo box). Also, the problem is not whether the
combo
box
is null- it is always null unless a value is selected to use as the
criteria
in the query. Therefore Like [forms]![reports_switch_main]![combo44] in
the
criteria will return all records based on the chosen value in the combo
box
or, if the combo box is null, all records from the table EXEPT those
with
a
null value- because the "*" will not return a record with a field with
a
null
value. Thus you can see the problem- if i have a null value in a
table's
field and the combo box for it does not contain a value, even though it
is
not suposed to be involved in filtering the returned data, all records
with
null values for that field will not be returned!!!

Thus it seems that I must either not use the "*" in the criteria or no
null
values can be in the tables. i fixed the problem for a few forms with
code
but it is just too complicated to do the entire db this way. Again, it
seems
that working with record sets is the cleanest way to do this.

Any thoughts........???


thanks.........




:

Ok, sorry, let's test the combo box for null, not the data:
'''''''''''''''''''''
dim strSQL as String

If isNull(Me.ComboBox) then
strSQL = "SELECT * from YourTable"
Else
strSQL = ".............Whatever your original query was........."
End If
'''''''''''''''''''''




Just realized that I had tried this (see previouse message) before.
It
won't
work because the query will return a record just because it is null.
Back
to
square one! Is there anyway to return all records, null or not, if a
parameter is not chosen??

thanks

:

Don't change your data! You can use the Nz function instead to
return
a
value where records are Null. This page shows how:
http://www.techonthenet.com/access/functions/advanced/nz.php
--
hth,
SusanV

I am using a parameter query that gets its values from combo
boxes.
However,
null values will cause records not to be returned. It is not
feasible
to
change the null values at the form level so I thought I would use
ADO
to
change the null values to "No Data" at the record level. Now my
questions
1. Does anyone have a bit of generic code that I can reference
for
looping
through a record set and changing null values to strings? Or is
there a
simpler way of doing it?. I assume it would be something like a"
for
each-in"
statement with a "if (null) then ("no-data")" However I am just
learning
Vba
I would REALY appreciate a little push on the syntax
2. I am thinking of splitting the database. Do I need to do
anything
special, say in defining the connection, if I am connecting to
linked-tables?

Many thanks!

 

[SusanV]

No trouble at all! Let us know how you make out,

Susan

Thanks Susan!
Sorry for all the trouble. I will look into these suggestions and see if
it
will work for me.

Thanks again for all your help

ok that's quite a bit clearer. So basically you have a combo box which
defaults to no entry, and a button users click to open a report - which
is
then based on the value of the combo box, right? I have a similar
function
in one of my forms where users can search for reports based on
manufacturer,
though I have a textbox control rather than a dropdown list. Although
this
may not be the slickest way to do it, it works a treat. Basically it
dumps
out via SQL to a temp table, then the report uses that temp table as it's
record source:


Private Sub btnSearchMfr_Click()
DoCmd.SetWarnings False
Dim Mfr As String
Dim sql As String

If IsNull(Me.txtMfr) Then
Mfr = ""
Else: Mfr = Me.txtMfr
End If

sql = "INSERT INTO tblSearchRep (" _
& "RepNum, Class, Title, Mfr, Model)" _
& "SELECT RepNum, Class, Title, Manufacturer, Model FROM tblPMO
WHERE " _
& "tblPMO.manufacturer LIKE " & "'" & "*" & Mfr & "*" & "'" _
& "ORDER BY manufacturer"

DoCmd.RunSQL "delete from tblSearchRep"
DoCmd.RunSQL sql
DoCmd.OpenReport "repSearchReports", acViewPreview
DoCmd.SetWarnings True
End Sub

The temp table is re-used for several buttons with similar function, but
providing different search parameters (model, equipment, etc). By using a
direct insert, VBA deletes and recreates the table on the fly, and if the
table doesn't pre-exist simply creates it:

Maybe this will work for you. Another option would be to test the
combobox
for Null as I showed in my previous post, but instead of using a SQL
statement to set a where condition.

Hi Susan,
Sorry for not being clearer. It is probably because I am new to all
this.
The problem can really be condensed down to is there a way to return a
record
when using a parameter query if the record has a null value in the
field
which the criteria (parameter) is used on?

In brief (with just one report, table, field, etc as an example), I
have a
switchboard that allows you to open a report and filter the results
based
on
the value in a combo box (populated from a field in a table). The combo
box
is null unless someone chooses a value, and the report uses a query as
the
record source wherein the "criteria" for the field is
Like NZ([forms]![switchbord]![combobox],"*")
Thus if no value is chosen in the combo box, then the criteria in the
query
is "*", which is all records, EXCEPT those with a null value for the
field
the criteria is working on. This will of coarse mean that certain
records
will be lost in the Report simply because they had a null value in the
combo
box (resulting in a "*" in the query criteria).

I really don't think that there is any easy way around this at the
form/query level but I was thinking that through ADO I could loop
through
the
records in the tables and change all null values to a string value-
which
I
am just now exploring on how to use. But before I did this I just
wanted
to
check to be sure that there was not some easy way to use a parameter
query
on
records that had null values and still be able to return them.

Thanks and I hope this helps!


:

salmonella,

You're getting me all confused here - is this combo box set on a
single
field lookup table or something? That's the only way I can figure that
the
SQL "Select * from Table" is not going to return records with a null
value
in some field or another.

What's the SQL statement you are trying to fill out?

What's the basis of the combo box? Is it a table or a value list?



Susan,
You got me here. I don't have a clue what to do with this (unless I
use
it
behind the form or combo box). Also, the problem is not whether the
combo
box
is null- it is always null unless a value is selected to use as the
criteria
in the query. Therefore Like [forms]![reports_switch_main]![combo44]
in
the
criteria will return all records based on the chosen value in the
combo
box
or, if the combo box is null, all records from the table EXEPT
those
with
a
null value- because the "*" will not return a record with a field
with
a
null
value. Thus you can see the problem- if i have a null value in a
table's
field and the combo box for it does not contain a value, even though
it
is
not suposed to be involved in filtering the returned data, all
records
with
null values for that field will not be returned!!!

Thus it seems that I must either not use the "*" in the criteria or
no
null
values can be in the tables. i fixed the problem for a few forms
with
code
but it is just too complicated to do the entire db this way. Again,
it
seems
that working with record sets is the cleanest way to do this.

Any thoughts........???


thanks.........




:

Ok, sorry, let's test the combo box for null, not the data:
'''''''''''''''''''''
dim strSQL as String

If isNull(Me.ComboBox) then
strSQL = "SELECT * from YourTable"
Else
strSQL = ".............Whatever your original query
was........."
End If
'''''''''''''''''''''




Just realized that I had tried this (see previouse message)
before.
It
won't
work because the query will return a record just because it is
null.
Back
to
square one! Is there anyway to return all records, null or not,
if a
parameter is not chosen??

thanks

:

Don't change your data! You can use the Nz function instead to
return
a
value where records are Null. This page shows how:
http://www.techonthenet.com/access/functions/advanced/nz.php
--
hth,
SusanV

I am using a parameter query that gets its values from combo
boxes.
However,
null values will cause records not to be returned. It is not
feasible
to
change the null values at the form level so I thought I would
use
ADO
to
change the null values to "No Data" at the record level. Now
my
questions
1. Does anyone have a bit of generic code that I can reference
for
looping
through a record set and changing null values to strings? Or
is
there a
simpler way of doing it?. I assume it would be something like
a"
for
each-in"
statement with a "if (null) then ("no-data")" However I am
just
learning
Vba
I would REALY appreciate a little push on the syntax
2. I am thinking of splitting the database. Do I need to do
anything
special, say in defining the connection, if I am connecting to
linked-tables?

Many thanks!