Type Mismatch Error depends on relation scheme appearance

M

Mishanya

1. I have a simple Add Query wich copies a data from tbl1 (wich is
linked-to-Excel table) to tbl2 (wich is local Access table). The tables are
related by Id field.
The query works fine, when I show only the updating table (tbl1) in the
design query view.
But when I show in the design query view the updated table as well (for
example, to check the relation type) and try to run the query, I get Mismath
Type error 3615.
The SQL gets this addition:
LEFT JOIN tbl2 ON tbl1.Id = tbl2.2
What's wrong?

2. If I change the relation between tables directly in the Query design
view, does it affect the main database table relation scheme and vice-versa -
if I change it in the main scheme, does it affect the existing query?
 
D

Douglas J. Steele

Assuming that tbl2 actually has fields named 1, 2, etc., try putting square
brackets around the field name:

LEFT JOIN tbl2 ON tbl1.Id = tbl2.[2]
 
M

Mishanya

Thank You, Douglas.
About the 2nd Question - I see that changing the relationship beetween the
tables directly from the Query Design View does not apply to the actual main
relationship scheme - is it build in pecularity or some misfunction?

Douglas J. Steele said:
Assuming that tbl2 actually has fields named 1, 2, etc., try putting square
brackets around the field name:

LEFT JOIN tbl2 ON tbl1.Id = tbl2.[2]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mishanya said:
1. I have a simple Add Query wich copies a data from tbl1 (wich is
linked-to-Excel table) to tbl2 (wich is local Access table). The tables
are
related by Id field.
The query works fine, when I show only the updating table (tbl1) in the
design query view.
But when I show in the design query view the updated table as well (for
example, to check the relation type) and try to run the query, I get
Mismath
Type error 3615.
The SQL gets this addition:
LEFT JOIN tbl2 ON tbl1.Id = tbl2.2
What's wrong?

2. If I change the relation between tables directly in the Query design
view, does it affect the main database table relation scheme and
vice-versa -
if I change it in the main scheme, does it affect the existing query?
 
C

cyrus05 via AccessMonster.com

Changing the relation of tables in design queries does not have any effect in
the relationship of the tables of the database structure. Relationships of
the tables in the design veiw of query is part of query design, not the
database structure.
Thank You, Douglas.
About the 2nd Question - I see that changing the relationship beetween the
tables directly from the Query Design View does not apply to the actual main
relationship scheme - is it build in pecularity or some misfunction?
Assuming that tbl2 actually has fields named 1, 2, etc., try putting square
brackets around the field name:
[quoted text clipped - 19 lines]
 
M

Mishanya

Got it. Thanks, Syrus. Although, as I'm new to Access, it seems confusing: if
I set the relationship in the Query Design View, it applies only to this
particular query? So, it can work Left-join in the query, but Right-join or
no relation at all in the general relationships structure?

cyrus05 via AccessMonster.com said:
Changing the relation of tables in design queries does not have any effect in
the relationship of the tables of the database structure. Relationships of
the tables in the design veiw of query is part of query design, not the
database structure.
Thank You, Douglas.
About the 2nd Question - I see that changing the relationship beetween the
tables directly from the Query Design View does not apply to the actual main
relationship scheme - is it build in pecularity or some misfunction?
Assuming that tbl2 actually has fields named 1, 2, etc., try putting square
brackets around the field name:
[quoted text clipped - 19 lines]
vice-versa -
if I change it in the main scheme, does it affect the existing query?
 
C

cyrus05 via AccessMonster.com

Yes Mishanya, relationship in query desig are only limited to that particular
query and has no effect on your database structured relationship or
relationship of the same tables in any other query.
Got it. Thanks, Syrus. Although, as I'm new to Access, it seems confusing: if
I set the relationship in the Query Design View, it applies only to this
particular query? So, it can work Left-join in the query, but Right-join or
no relation at all in the general relationships structure?
Changing the relation of tables in design queries does not have any effect in
the relationship of the tables of the database structure. Relationships of
[quoted text clipped - 11 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top