Subform linked on blank fields doesn't find match

I have a subform with several linked fields (I have a tracking number which
is broken into several fields that I need linked). Occassionally, not all of
the linked fields are occupied, but I still want them to pick up the record
in the subform. Is there a way to do that?

For clarification, say I have four linked fields for my tracking number:
123 ABC 456 DEF
If I have a record on my main form with this number:
123 blank 456 DEF
I want the subform to pick up records which match all four fields, but
instead, it doesn't recognize that the blank fields are still a match. If I
populate those blank fields with anything, it finds them:
123 ... 456 DEF
But I don't want to have do that.

Can anyone help?

Thanks,
=Ray=

When you say "blank", I assume you actually mean "Null". Due to the special
nature of Null, you cannot join on a field that's Null.

Try using the Nz function in your query to convert the Null values to some
value that will never occur naturally in the field, such as Nz([Field2],
""), and then join on that field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
news
I have a subform with several linked fields (I have a tracking number which
is broken into several fields that I need linked). Occassionally, not all
of
the linked fields are occupied, but I still want them to pick up the
record
in the subform. Is there a way to do that?

For clarification, say I have four linked fields for my tracking number:
123 ABC 456 DEF
If I have a record on my main form with this number:
123 blank 456 DEF
I want the subform to pick up records which match all four fields, but
instead, it doesn't recognize that the blank fields are still a match. If
I
populate those blank fields with anything, it finds them:
123 ... 456 DEF
But I don't want to have do that.

Can anyone help?

Thanks,
=Ray=

Yes, I mean "Null".

I don't actually have a join relationship between the fields, it's just set
in the "Llink Child/Master Fields" properties.

I modified the fields as you suggested with the Nz function, however, I
don't know how to do that in reverse. That is, I have my query where it
converts the null fields to a ".", but how do I get the form to still allow
data entry, converting any null values to a period?

Thanks for your help.
=Ray=

"Douglas J. Steele" wrote:

When you say "blank", I assume you actually mean "Null". Due to the special
nature of Null, you cannot join on a field that's Null.

Try using the Nz function in your query to convert the Null values to some
value that will never occur naturally in the field, such as Nz([Field2],
""), and then join on that field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
news
I have a subform with several linked fields (I have a tracking number which
is broken into several fields that I need linked). Occassionally, not all
of

the linked fields are occupied, but I still want them to pick up the
record
in the subform. Is there a way to do that?

For clarification, say I have four linked fields for my tracking number:
123 ABC 456 DEF
If I have a record on my main form with this number:
123 blank 456 DEF
I want the subform to pick up records which match all four fields, but
instead, it doesn't recognize that the blank fields are still a match. If
I
populate those blank fields with anything, it finds them:
123 ... 456 DEF
But I don't want to have do that.

Can anyone help?

Thanks,
=Ray=

Based your forms on a query that has the fields with and without the Nz
function.

Use the fields with the Nz function for the
LinkChildFields/LinkMasterFields, but display the fields without the Nz
function so that you can update them.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
...
Yes, I mean "Null".

I don't actually have a join relationship between the fields, it's just
set
in the "Llink Child/Master Fields" properties.

I modified the fields as you suggested with the Nz function, however, I
don't know how to do that in reverse. That is, I have my query where it
converts the null fields to a ".", but how do I get the form to still
allow
data entry, converting any null values to a period?

Thanks for your help.
=Ray=

"Douglas J. Steele" wrote:

When you say "blank", I assume you actually mean "Null". Due to the
special
nature of Null, you cannot join on a field that's Null.

Try using the Nz function in your query to convert the Null values to
some
value that will never occur naturally in the field, such as Nz([Field2],
""), and then join on that field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
news
I have a subform with several linked fields (I have a tracking number
which
is broken into several fields that I need linked). Occassionally, not
all
of

the linked fields are occupied, but I still want them to pick up the
record
in the subform. Is there a way to do that?

For clarification, say I have four linked fields for my tracking
number:
123 ABC 456 DEF
If I have a record on my main form with this number:
123 blank 456 DEF
I want the subform to pick up records which match all four fields, but
instead, it doesn't recognize that the blank fields are still a match.
If
I
populate those blank fields with anything, it finds them:
123 ... 456 DEF
But I don't want to have do that.

Can anyone help?

Thanks,
=Ray=

Thank you... that works perfectly!
=Ray=

"Douglas J. Steele" wrote:

Based your forms on a query that has the fields with and without the Nz
function.

Use the fields with the Nz function for the
LinkChildFields/LinkMasterFields, but display the fields without the Nz
function so that you can update them.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
...
Yes, I mean "Null".

I don't actually have a join relationship between the fields, it's just
set
in the "Llink Child/Master Fields" properties.

I modified the fields as you suggested with the Nz function, however, I
don't know how to do that in reverse. That is, I have my query where it
converts the null fields to a ".", but how do I get the form to still
allow
data entry, converting any null values to a period?

Thanks for your help.
=Ray=

"Douglas J. Steele" wrote:

When you say "blank", I assume you actually mean "Null". Due to the
special
nature of Null, you cannot join on a field that's Null.

Try using the Nz function in your query to convert the Null values to
some
value that will never occur naturally in the field, such as Nz([Field2],
""), and then join on that field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
news I have a subform with several linked fields (I have a tracking number
which
is broken into several fields that I need linked). Occassionally, not
all
of

the linked fields are occupied, but I still want them to pick up the
record
in the subform. Is there a way to do that?

For clarification, say I have four linked fields for my tracking
number:
123 ABC 456 DEF
If I have a record on my main form with this number:
123 blank 456 DEF
I want the subform to pick up records which match all four fields, but
instead, it doesn't recognize that the blank fields are still a match.
If
I
populate those blank fields with anything, it finds them:
123 ... 456 DEF
But I don't want to have do that.

Can anyone help?

Thanks,
=Ray=

Okay, now I have another problem...

I have a subform with one field from another table (Table 2), but with
linked calculation fields (to fix the null problem) to the master form, but
when I enter data in the subform, it tries to update the calculated fields in
Table 2, and gives an error message because it can't update those fields.

How can I tell it to use the data fields, as represented on the main form,
when data is entered on the subform?

Hopefully that makes sense.
=Ray=

"Douglas J. Steele" wrote:

Based your forms on a query that has the fields with and without the Nz
function.

Use the fields with the Nz function for the
LinkChildFields/LinkMasterFields, but display the fields without the Nz
function so that you can update them.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
...
Yes, I mean "Null".

I don't actually have a join relationship between the fields, it's just
set
in the "Llink Child/Master Fields" properties.

I modified the fields as you suggested with the Nz function, however, I
don't know how to do that in reverse. That is, I have my query where it
converts the null fields to a ".", but how do I get the form to still
allow
data entry, converting any null values to a period?

Thanks for your help.
=Ray=

"Douglas J. Steele" wrote:

When you say "blank", I assume you actually mean "Null". Due to the
special
nature of Null, you cannot join on a field that's Null.

Try using the Nz function in your query to convert the Null values to
some
value that will never occur naturally in the field, such as Nz([Field2],
""), and then join on that field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
news I have a subform with several linked fields (I have a tracking number
which
is broken into several fields that I need linked). Occassionally, not
all
of

the linked fields are occupied, but I still want them to pick up the
record
in the subform. Is there a way to do that?

For clarification, say I have four linked fields for my tracking
number:
123 ABC 456 DEF
If I have a record on my main form with this number:
123 blank 456 DEF
I want the subform to pick up records which match all four fields, but
instead, it doesn't recognize that the blank fields are still a match.
If
I
populate those blank fields with anything, it finds them:
123 ... 456 DEF
But I don't want to have do that.

Can anyone help?

Thanks,
=Ray=

No, it doesn't really make sense to me.

Do you have both the "live" fields and the "calculated" fields on both the
form and subform? (the calculated fields don't need to be visible)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
...
Okay, now I have another problem...

I have a subform with one field from another table (Table 2), but with
linked calculation fields (to fix the null problem) to the master form,
but
when I enter data in the subform, it tries to update the calculated fields
in
Table 2, and gives an error message because it can't update those fields.

How can I tell it to use the data fields, as represented on the main form,
when data is entered on the subform?

Hopefully that makes sense.
=Ray=

"Douglas J. Steele" wrote:

Based your forms on a query that has the fields with and without the Nz
function.

Use the fields with the Nz function for the
LinkChildFields/LinkMasterFields, but display the fields without the Nz
function so that you can update them.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
...
Yes, I mean "Null".

I don't actually have a join relationship between the fields, it's just
set
in the "Llink Child/Master Fields" properties.

I modified the fields as you suggested with the Nz function, however, I
don't know how to do that in reverse. That is, I have my query where it
converts the null fields to a ".", but how do I get the form to still
allow
data entry, converting any null values to a period?

Thanks for your help.
=Ray=

"Douglas J. Steele" wrote:

When you say "blank", I assume you actually mean "Null". Due to the
special
nature of Null, you cannot join on a field that's Null.

Try using the Nz function in your query to convert the Null values to
some
value that will never occur naturally in the field, such as
Nz([Field2],
""), and then join on that field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
news I have a subform with several linked fields (I have a tracking number
which
is broken into several fields that I need linked). Occassionally,
not
all
of

the linked fields are occupied, but I still want them to pick up the
record
in the subform. Is there a way to do that?

For clarification, say I have four linked fields for my tracking
number:
123 ABC 456 DEF
If I have a record on my main form with this number:
123 blank 456 DEF
I want the subform to pick up records which match all four fields,
but
instead, it doesn't recognize that the blank fields are still a
match.
If
I
populate those blank fields with anything, it finds them:
123 ... 456 DEF
But I don't want to have do that.

Can anyone help?

Thanks,
=Ray=

I have two tables. Both tables have the several fields that make up the
tracking number, which may contain null fields. (Table 2 stores many
replicates of a single type of measurement for each tracking number, while
Table 1 stores several types of measurements with 1 replicate each.)

I have a query for each table that adds calculated fields to correct the
problem I was having with the null fields.

My main form is based on the query for Table 1, and has "live" fields for
data entry. The subform is based on the query for Table 2 and has only 1
field for data entry, but is linked to the main form record using the
calculated fields. There are no fields from the tracking no. on the subform,
live or calculated, just the one field of Table 2 that contains multiple
replicates... the other fields are used for linking purposes only and are not
on the form as visible or invisible text boxes.

The subform does its linking properly (although I have to switch to a
different record and back again for a change on the main form to be reflected
in the subform). The problem is when I try to add a record by entering data
on the subform, it tries to fill in data for the linked fields (the tracking
no), but since they're calculated fields, it barks, saying it can't update
them. I need it to fill in the appropriate tracking number (according to the
main form's record) into the live fields of Table 2, but it's trying to do
that for the linked, calculated fields.

Well, that's the long version, but hopefully it makes it more clear what I'm
doing.
Thanks for your help,
=Ray=

"Douglas J. Steele" wrote:

No, it doesn't really make sense to me.

Do you have both the "live" fields and the "calculated" fields on both the
form and subform? (the calculated fields don't need to be visible)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
...
Okay, now I have another problem...

I have a subform with one field from another table (Table 2), but with
linked calculation fields (to fix the null problem) to the master form,
but
when I enter data in the subform, it tries to update the calculated fields
in
Table 2, and gives an error message because it can't update those fields.

How can I tell it to use the data fields, as represented on the main form,
when data is entered on the subform?

Hopefully that makes sense.
=Ray=

"Douglas J. Steele" wrote:

Based your forms on a query that has the fields with and without the Nz
function.

Use the fields with the Nz function for the
LinkChildFields/LinkMasterFields, but display the fields without the Nz
function so that you can update them.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
...
Yes, I mean "Null".

I don't actually have a join relationship between the fields, it's just
set
in the "Llink Child/Master Fields" properties.

I modified the fields as you suggested with the Nz function, however, I
don't know how to do that in reverse. That is, I have my query where it
converts the null fields to a ".", but how do I get the form to still
allow
data entry, converting any null values to a period?

Thanks for your help.
=Ray=

"Douglas J. Steele" wrote:

When you say "blank", I assume you actually mean "Null". Due to the
special
nature of Null, you cannot join on a field that's Null.

Try using the Nz function in your query to convert the Null values to
some
value that will never occur naturally in the field, such as
Nz([Field2],
""), and then join on that field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
news I have a subform with several linked fields (I have a tracking number
which
is broken into several fields that I need linked). Occassionally,
not
all
of

the linked fields are occupied, but I still want them to pick up the
record
in the subform. Is there a way to do that?

For clarification, say I have four linked fields for my tracking
number:
123 ABC 456 DEF
If I have a record on my main form with this number:
123 blank 456 DEF
I want the subform to pick up records which match all four fields,
but
instead, it doesn't recognize that the blank fields are still a
match.
If
I
populate those blank fields with anything, it finds them:
123 ... 456 DEF
But I don't want to have do that.

Can anyone help?

Thanks,
=Ray=

Adding a record to the subform shouldn't make any difference to the parent
form, assuming that the necessary record on the parent form exists.

I'm afraid I really have no suggestions.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"=Ray=" wrote in message
...
I have two tables. Both tables have the several fields that make up the
tracking number, which may contain null fields. (Table 2 stores many
replicates of a single type of measurement for each tracking number, while
Table 1 stores several types of measurements with 1 replicate each.)

I have a query for each table that adds calculated fields to correct the
problem I was having with the null fields.

My main form is based on the query for Table 1, and has "live" fields for
data entry. The subform is based on the query for Table 2 and has only 1
field for data entry, but is linked to the main form record using the
calculated fields. There are no fields from the tracking no. on the
subform,
live or calculated, just the one field of Table 2 that contains multiple
replicates... the other fields are used for linking purposes only and are
not
on the form as visible or invisible text boxes.

The subform does its linking properly (although I have to switch to a
different record and back again for a change on the main form to be
reflected
in the subform). The problem is when I try to add a record by entering
data
on the subform, it tries to fill in data for the linked fields (the
tracking
no), but since they're calculated fields, it barks, saying it can't update
them. I need it to fill in the appropriate tracking number (according to
the
main form's record) into the live fields of Table 2, but it's trying to do
that for the linked, calculated fields.

Well, that's the long version, but hopefully it makes it more clear what
I'm
doing.
Thanks for your help,
=Ray=

"Douglas J. Steele" wrote:

No, it doesn't really make sense to me.

Do you have both the "live" fields and the "calculated" fields on both
the
form and subform? (the calculated fields don't need to be visible)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
...
Okay, now I have another problem...

I have a subform with one field from another table (Table 2), but with
linked calculation fields (to fix the null problem) to the master form,
but
when I enter data in the subform, it tries to update the calculated
fields
in
Table 2, and gives an error message because it can't update those
fields.

How can I tell it to use the data fields, as represented on the main
form,
when data is entered on the subform?

Hopefully that makes sense.
=Ray=

"Douglas J. Steele" wrote:

Based your forms on a query that has the fields with and without the
Nz
function.

Use the fields with the Nz function for the
LinkChildFields/LinkMasterFields, but display the fields without the
Nz
function so that you can update them.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
...
Yes, I mean "Null".

I don't actually have a join relationship between the fields, it's
just
set
in the "Llink Child/Master Fields" properties.

I modified the fields as you suggested with the Nz function,
however, I
don't know how to do that in reverse. That is, I have my query where
it
converts the null fields to a ".", but how do I get the form to
still
allow
data entry, converting any null values to a period?

Thanks for your help.
=Ray=

"Douglas J. Steele" wrote:

When you say "blank", I assume you actually mean "Null". Due to the
special
nature of Null, you cannot join on a field that's Null.

Try using the Nz function in your query to convert the Null values
to
some
value that will never occur naturally in the field, such as
Nz([Field2],
""), and then join on that field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
news I have a subform with several linked fields (I have a tracking
number
which
is broken into several fields that I need linked). Occassionally,
not
all
of

the linked fields are occupied, but I still want them to pick up
the
record
in the subform. Is there a way to do that?

For clarification, say I have four linked fields for my tracking
number:
123 ABC 456 DEF
If I have a record on my main form with this number:
123 blank 456 DEF
I want the subform to pick up records which match all four
fields,
but
instead, it doesn't recognize that the blank fields are still a
match.
If
I
populate those blank fields with anything, it finds them:
123 ... 456 DEF
But I don't want to have do that.

Can anyone help?

Thanks,
=Ray=

No, it doesn't do anything to the parent form. When I said it tries to fill
in data, I was referring to the query that the subform is based on. Since
that query has several fields other than the one that is visible on the form,
when I add a record by entering data into that one field, it tries to enter
values for the other fields that are in the query... the ones that the
subform is linked to the master form by. But since those are calculated
fields, it can't enter data. I want it to enter that data, but in the
corresponding "live" data entry fields.

Thanks for your efforts in helping me through this... they have helped and
are appreciated.
=Ray=

"Douglas J. Steele" wrote:

Adding a record to the subform shouldn't make any difference to the parent
form, assuming that the necessary record on the parent form exists.

I'm afraid I really have no suggestions.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"=Ray=" wrote in message
...
I have two tables. Both tables have the several fields that make up the
tracking number, which may contain null fields. (Table 2 stores many
replicates of a single type of measurement for each tracking number, while
Table 1 stores several types of measurements with 1 replicate each.)

I have a query for each table that adds calculated fields to correct the
problem I was having with the null fields.

My main form is based on the query for Table 1, and has "live" fields for
data entry. The subform is based on the query for Table 2 and has only 1
field for data entry, but is linked to the main form record using the
calculated fields. There are no fields from the tracking no. on the
subform,
live or calculated, just the one field of Table 2 that contains multiple
replicates... the other fields are used for linking purposes only and are
not
on the form as visible or invisible text boxes.

The subform does its linking properly (although I have to switch to a
different record and back again for a change on the main form to be
reflected
in the subform). The problem is when I try to add a record by entering
data
on the subform, it tries to fill in data for the linked fields (the
tracking
no), but since they're calculated fields, it barks, saying it can't update
them. I need it to fill in the appropriate tracking number (according to
the
main form's record) into the live fields of Table 2, but it's trying to do
that for the linked, calculated fields.

Well, that's the long version, but hopefully it makes it more clear what
I'm
doing.
Thanks for your help,
=Ray=

"Douglas J. Steele" wrote:

No, it doesn't really make sense to me.

Do you have both the "live" fields and the "calculated" fields on both
the
form and subform? (the calculated fields don't need to be visible)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
...
Okay, now I have another problem...

I have a subform with one field from another table (Table 2), but with
linked calculation fields (to fix the null problem) to the master form,
but
when I enter data in the subform, it tries to update the calculated
fields
in
Table 2, and gives an error message because it can't update those
fields.

How can I tell it to use the data fields, as represented on the main
form,
when data is entered on the subform?

Hopefully that makes sense.
=Ray=

"Douglas J. Steele" wrote:

Based your forms on a query that has the fields with and without the
Nz
function.

Use the fields with the Nz function for the
LinkChildFields/LinkMasterFields, but display the fields without the
Nz
function so that you can update them.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
...
Yes, I mean "Null".

I don't actually have a join relationship between the fields, it's
just
set
in the "Llink Child/Master Fields" properties.

I modified the fields as you suggested with the Nz function,
however, I
don't know how to do that in reverse. That is, I have my query where
it
converts the null fields to a ".", but how do I get the form to
still
allow
data entry, converting any null values to a period?

Thanks for your help.
=Ray=

"Douglas J. Steele" wrote:

When you say "blank", I assume you actually mean "Null". Due to the
special
nature of Null, you cannot join on a field that's Null.

Try using the Nz function in your query to convert the Null values
to
some
value that will never occur naturally in the field, such as
Nz([Field2],
""), and then join on that field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"=Ray=" wrote in message
news I have a subform with several linked fields (I have a tracking
number
which
is broken into several fields that I need linked). Occassionally,
not
all
of

the linked fields are occupied, but I still want them to pick up
the
record
in the subform. Is there a way to do that?

For clarification, say I have four linked fields for my tracking
number:
123 ABC 456 DEF
If I have a record on my main form with this number:
123 blank 456 DEF
I want the subform to pick up records which match all four
fields,
but
instead, it doesn't recognize that the blank fields are still a
match.
If
I
populate those blank fields with anything, it finds them:
123 ... 456 DEF
But I don't want to have do that.


Can anyone help?

Thanks,
=Ray=