query not returning any records

[Guest]

I have a qry that looks at 5 tables. I found an article that says if any of
the records contain Null values, the qry will not return those records. It
says to use the Nz function to convert the Null's to zeros or zero length
strings.

My questions is, how? I can't find any examples of how to do this. Does the
Nz function go in the default value for the field in the table? If so, what
is the format?

I've tried what I could think of, but nothing has worked, and I just don't
have enough experience doing this yet.

Cheers

 

[Brian Bastl]

Paul,

you'd use the Null-To-Zero function in your query in its own column.

NewField: Nz([SomeField],0)
NewField: Nz([SomeField],"")

However, If you're joining 5 tables, you'll probably also want to change the
joins to show all records from primary table and any matching records from
other table where joined fields are equal.

Brian

 

[Guest]

Thanks for the info Brian,

Are you saying that for every field, I have to create a new column like:

Nz([PtPHN], "")

I guess I am not understanding your suggestion.

The qry I built using the wizard, has over 100 fields in it.

The main table 'tblEpistry' has the RecordID linked to the ID field in each
of the 4 unit tables 'tblUnit1' through 'tblUnit4'

Can you provide a little more guidance?

Cheers

Paul,

you'd use the Null-To-Zero function in your query in its own column.

NewField: Nz([SomeField],0)
NewField: Nz([SomeField],"")

However, If you're joining 5 tables, you'll probably also want to change the
joins to show all records from primary table and any matching records from
other table where joined fields are equal.

Brian

I have a qry that looks at 5 tables. I found an article that says if any of
the records contain Null values, the qry will not return those records. It
says to use the Nz function to convert the Null's to zeros or zero length
strings.

My questions is, how? I can't find any examples of how to do this. Does the
Nz function go in the default value for the field in the table? If so, what
is the format?

I've tried what I could think of, but nothing has worked, and I just don't
have enough experience doing this yet.

Cheers

 

[Brian Bastl]

Paul,

are your 'Unit' tables structurally the same? If so, then you'd first want
to create a Union query with those four tables and save it. Then create a
new query joining the Union query to tblEpistry, but change the join from an
INNER join to a LEFT join by double-clicking the join line and selecting the
second option, which would be to show all records from tblEpistry and any
matching records from your union query where the joined fields are equal.

uQryUnits:
Select tblUnit1.* From tblUnit1
Union
Select tblUnit2.* From tblUnit2
Union
etc...

That should accomplish what you want. Post back if that doesn't help.

Brian

Thanks for the info Brian,

Are you saying that for every field, I have to create a new column like:

Nz([PtPHN], "")

I guess I am not understanding your suggestion.

The qry I built using the wizard, has over 100 fields in it.

The main table 'tblEpistry' has the RecordID linked to the ID field in each
of the 4 unit tables 'tblUnit1' through 'tblUnit4'

Can you provide a little more guidance?

Cheers

Paul,

you'd use the Null-To-Zero function in your query in its own column.

NewField: Nz([SomeField],0)
NewField: Nz([SomeField],"")

However, If you're joining 5 tables, you'll probably also want to change the
joins to show all records from primary table and any matching records from
other table where joined fields are equal.

Brian

I have a qry that looks at 5 tables. I found an article that says if

any
of

the records contain Null values, the qry will not return those records. It
says to use the Nz function to convert the Null's to zeros or zero length
strings.

My questions is, how? I can't find any examples of how to do this.

Does
the

Nz function go in the default value for the field in the table? If so, what
is the format?

I've tried what I could think of, but nothing has worked, and I just don't
have enough experience doing this yet.

Cheers

 

[Guest]

Almost there, thanks again for the help.

The form now opens with all the tblEpistry records, however the records from
the tlbUnit# only show #Name?

I re-did what you outlined below, but I keep getting the same results.

Any ideas?

Cheers

Paul,

are your 'Unit' tables structurally the same? If so, then you'd first want
to create a Union query with those four tables and save it. Then create a
new query joining the Union query to tblEpistry, but change the join from an
INNER join to a LEFT join by double-clicking the join line and selecting the
second option, which would be to show all records from tblEpistry and any
matching records from your union query where the joined fields are equal.

uQryUnits:
Select tblUnit1.* From tblUnit1
Union
Select tblUnit2.* From tblUnit2
Union
etc...

That should accomplish what you want. Post back if that doesn't help.

Brian

Thanks for the info Brian,

Are you saying that for every field, I have to create a new column like:

Nz([PtPHN], "")

I guess I am not understanding your suggestion.

The qry I built using the wizard, has over 100 fields in it.

The main table 'tblEpistry' has the RecordID linked to the ID field in each
of the 4 unit tables 'tblUnit1' through 'tblUnit4'

Can you provide a little more guidance?

Cheers

Paul,

you'd use the Null-To-Zero function in your query in its own column.

NewField: Nz([SomeField],0)
NewField: Nz([SomeField],"")

However, If you're joining 5 tables, you'll probably also want to change the
joins to show all records from primary table and any matching records from
other table where joined fields are equal.

Brian


I have a qry that looks at 5 tables. I found an article that says if any
of
the records contain Null values, the qry will not return those records. It
says to use the Nz function to convert the Null's to zeros or zero length
strings.

My questions is, how? I can't find any examples of how to do this. Does
the
Nz function go in the default value for the field in the table? If so,
what
is the format?

I've tried what I could think of, but nothing has worked, and I just don't
have enough experience doing this yet.

Cheers

 

[Brian Bastl]

Paul,

I didn't realize that you wanted to use the query as the recordsource for
your form. Unfortunately, most forms based on multiple tables will not be
updatable. A usable form should be bound to either one table or an updatable
query.

If you don't care that your form will not be updatable, base the
recordsource for your main form on tblEpistry, and then create a subform
using the union query of your multiple tblUnit# as its recordsource.

If you do need to use the form for edits and additions, then you'll still
want the main form based on tblEpistry, but you'll want your subform
recordsource to only be one of the Unit# tables. If all of your Unit# tables
have the exact same fields, you could programmatically change the
recordsource for your subform to a different Unit table, or you could create
4 separate subforms bound to each respective Unit# table, and
programmatically change the subform control's source object.

HTH,
Brian

Almost there, thanks again for the help.

The form now opens with all the tblEpistry records, however the records from
the tlbUnit# only show #Name?

I re-did what you outlined below, but I keep getting the same results.

Any ideas?

Cheers

Paul,

are your 'Unit' tables structurally the same? If so, then you'd first want
to create a Union query with those four tables and save it. Then create a
new query joining the Union query to tblEpistry, but change the join from an
INNER join to a LEFT join by double-clicking the join line and selecting the
second option, which would be to show all records from tblEpistry and any
matching records from your union query where the joined fields are equal.

uQryUnits:
Select tblUnit1.* From tblUnit1
Union
Select tblUnit2.* From tblUnit2
Union
etc...

That should accomplish what you want. Post back if that doesn't help.

Brian

Thanks for the info Brian,

Are you saying that for every field, I have to create a new column like:

Nz([PtPHN], "")

I guess I am not understanding your suggestion.

The qry I built using the wizard, has over 100 fields in it.

The main table 'tblEpistry' has the RecordID linked to the ID field in each
of the 4 unit tables 'tblUnit1' through 'tblUnit4'

Can you provide a little more guidance?

Cheers


:

Paul,

you'd use the Null-To-Zero function in your query in its own column.

NewField: Nz([SomeField],0)
NewField: Nz([SomeField],"")

However, If you're joining 5 tables, you'll probably also want to

change
the

joins to show all records from primary table and any matching

records
from

other table where joined fields are equal.

Brian


I have a qry that looks at 5 tables. I found an article that says

if
any

of
the records contain Null values, the qry will not return those records. It
says to use the Nz function to convert the Null's to zeros or zero length
strings.

My questions is, how? I can't find any examples of how to do this. Does
the
Nz function go in the default value for the field in the table? If so,
what
is the format?

I've tried what I could think of, but nothing has worked, and I

just
don't

have enough experience doing this yet.

Cheers