Problem text field value = zero

[isetea]

Hi,
I have two tables with text fields as well as number fields. I try to match the two tables. Sometimes two matched textfields show an unmatch even if there is no value in both fields. Sometimes means I have e.g. 20 rows, 10 are filled with names and 10 are blank. For only some of the blank fields it shows an unmatch.
I tried to match the same fields in excel and it doesn't show any unmatch.
Any idea about this phenomena?
Cheers

[PEB]

Hi,

When you link tables or match table fields, it assumes that there isn't null values!

So to match your fields you have to do sth more complicated in this case:

AS Field: IIF(isnull([mytable1].[Field1]),"",[mytable1].[Field1])

AS Criteria: IIF(isnull([mytable2].[Field1]),"",[mytable2].[Field1])

:)

[isetea]

Hi,

Thanks for your reply. I'm not such a profi with access and most of my knowledge comes from watching, reading and trying. I read about "mytable", but I do not completly understand what it means. So I don't use MyTable or MyAccount. All the codes of my queries contain none such values. What can I do with the code you send me?

Cheers

Hi,

When you link tables or match table fields, it assumes that there isn't null values!

So to match your fields you have to do sth more complicated in this case:

AS Field: IIF(isnull([mytable1].[Field1]),"",[mytable1].[Field1])

AS Criteria: IIF(isnull([mytable2].[Field1]),"",[mytable2].[Field1])

:)

[PEB]

Hi,

Sorry, you have reason...

So please replace mytable with the name of your table that You use in your query and Field1 with the name of your field... I've introduced them because I don't know the names of your tables and fields :)

If there is other pb don't hesitate :)
:)

Cheers

Hi,

Thanks for your reply. I'm not such a profi with access and most of my knowledge comes from watching, reading and trying. I read about "mytable", but I do not completly understand what it means. So I don't use MyTable or MyAccount. All the codes of my queries contain none such values. What can I do with the code you send me?

Cheers

[isetea]

I'm sorry. But it is not working. Maybe I did it wrong, but just in case I will explain again what I do:

1) select query, select table_A and table_B which have identical field names
2) select field_1 of table A and field_1 of table_B as field_old_1 and insert a third field called change_1
3) insert function for field change_1: IIf([table_A].[field_1]=[table_B].[field_1],"","X")
4) repeat with step 2 and 3 till every field is matched

The result is the problem. SOMETIMES if the values in field_1 of both tables are blank (saying no value) the result in change_1 shows an X. But only sometimes... In other cases where both field_1 are blank (saying no values) there is no "X" in field change_1.

I inserted your code in the matching query, because in the underlying tables I have only data from table_A or table_B, so I didn't know how to input a criteria referring to the other table.

Is this understandable? I hope so and also that you can help me with my gallimaufry... :(

Hi,

Sorry, you have reason...

So please replace mytable with the name of your table that You use in your query and Field1 with the name of your field... I've introduced them because I don't know the names of your tables and fields :)

If there is other pb don't hesitate :)
:)

Cheers

[PEB]

Hi,

Sorry the last times i didn't understand you!

So try:

IIf(nz([table_A].[field_1])=nz([table_B].[field_1]),"","X")

This eliminates the null values in the tables!

:)

[isetea]

Great!! This was just to easy to get it on my own!
Thanks again.
:)

Hi,

Sorry the last times i didn't understand you!

So try:

IIf(nz([table_A].[field_1])=nz([table_B].[field_1]),"","X")

This eliminates the null values in the tables!

:)