Data type mismatch?

W

Warren

I am at a loss as to why I keep getting a data type mismatch error here. The
Circuit.CircuitID field is a long integer autonumber data type, the
InstalledCircuit.CircuitID field is long integer data type, the
InstalledCircuit.Poles is a text data type and I left the default settings
for each of these respective data types of each field. The query was working
with no errors (just not with desired results) before I placed the
Circut.CircuitID Is Null in the last two If statements.

Can anyone see why I would be getting the data type mismatch?

The select statement is designed to limit values of a combo box to an
available circuit; if anyone sees a better way feel free to comment.

Also if it matters being run in A2007 on WinXPPro with all current service
packs

SELECT Circuit.CircuitID, InstalledCircuit.CircuitID, InstalledCircuit.Poles
FROM Circuit LEFT JOIN InstalledCircuit ON Circuit.CircuitID =
InstalledCircuit.CircuitID
WHERE
(((Circuit.CircuitID)=IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID]
And [InstalledCircuit]![Poles]="1",[Circuit]![CircuitID] Is
Null,IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID] And
[InstalledCircuit]![Poles]="2",[Circuit]![CircuitID] Is Null And
([Circuit]![CircuitID]+1) Is
Null,IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID] And
[InstalledCircuit]![Poles]="3",[Circuit]![CircuitID] Is Null And
([Circuit]![CircuitID]-1) Is Null And ([Circuit]![CircuitID]+1) Is
Null,[Circuit]![CircuitID])))))

Thank you in advance
 
J

Jeff Boyce

Warren

If the Circuit.CircuitID is Null, I don't believe you can join the two
tables when one holds a null.

Perhaps you could create one query to derive the values to use in case of
nulls, then build your second query to use that first one.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
W

Warren

Jeff

The query is not looking for nulls it's forcing a value to be null in (or
excluded from) the query output when there is a match in the criteria.
Basically if a circuit is currently listed as an installed circuit then I
don't want the user to be able to assign that circuit again since it is
already in use. The user is assigning the circuit from a combo box and I
want to filter out the circuits using a query for the combo box values to
select from.
I could probobly do a simple not like criteria
Circuit.CircuitID<>InstalledCircuit.CircuitID
but how would I account for the -"1" and +"1" if the number of poles is 2 or 3

Jeff Boyce said:
Warren

If the Circuit.CircuitID is Null, I don't believe you can join the two
tables when one holds a null.

Perhaps you could create one query to derive the values to use in case of
nulls, then build your second query to use that first one.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Warren said:
I am at a loss as to why I keep getting a data type mismatch error here.
The
Circuit.CircuitID field is a long integer autonumber data type, the
InstalledCircuit.CircuitID field is long integer data type, the
InstalledCircuit.Poles is a text data type and I left the default settings
for each of these respective data types of each field. The query was
working
with no errors (just not with desired results) before I placed the
Circut.CircuitID Is Null in the last two If statements.

Can anyone see why I would be getting the data type mismatch?

The select statement is designed to limit values of a combo box to an
available circuit; if anyone sees a better way feel free to comment.

Also if it matters being run in A2007 on WinXPPro with all current service
packs

SELECT Circuit.CircuitID, InstalledCircuit.CircuitID,
InstalledCircuit.Poles
FROM Circuit LEFT JOIN InstalledCircuit ON Circuit.CircuitID =
InstalledCircuit.CircuitID
WHERE
(((Circuit.CircuitID)=IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID]
And [InstalledCircuit]![Poles]="1",[Circuit]![CircuitID] Is
Null,IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID] And
[InstalledCircuit]![Poles]="2",[Circuit]![CircuitID] Is Null And
([Circuit]![CircuitID]+1) Is
Null,IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID] And
[InstalledCircuit]![Poles]="3",[Circuit]![CircuitID] Is Null And
([Circuit]![CircuitID]-1) Is Null And ([Circuit]![CircuitID]+1) Is
Null,[Circuit]![CircuitID])))))

Thank you in advance
Click to expand...
Click to expand...
 
W

Warren

Sorry forgot to mention that in the properties of the query I have it set to
show all records in table Circuit and only those from InstalledCircuit that
are equal

Jeff Boyce said:
Warren

If the Circuit.CircuitID is Null, I don't believe you can join the two
tables when one holds a null.

Perhaps you could create one query to derive the values to use in case of
nulls, then build your second query to use that first one.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Warren said:
I am at a loss as to why I keep getting a data type mismatch error here.
The
Circuit.CircuitID field is a long integer autonumber data type, the
InstalledCircuit.CircuitID field is long integer data type, the
InstalledCircuit.Poles is a text data type and I left the default settings
for each of these respective data types of each field. The query was
working
with no errors (just not with desired results) before I placed the
Circut.CircuitID Is Null in the last two If statements.

Can anyone see why I would be getting the data type mismatch?

The select statement is designed to limit values of a combo box to an
available circuit; if anyone sees a better way feel free to comment.

Also if it matters being run in A2007 on WinXPPro with all current service
packs

SELECT Circuit.CircuitID, InstalledCircuit.CircuitID,
InstalledCircuit.Poles
FROM Circuit LEFT JOIN InstalledCircuit ON Circuit.CircuitID =
InstalledCircuit.CircuitID
WHERE
(((Circuit.CircuitID)=IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID]
And [InstalledCircuit]![Poles]="1",[Circuit]![CircuitID] Is
Null,IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID] And
[InstalledCircuit]![Poles]="2",[Circuit]![CircuitID] Is Null And
([Circuit]![CircuitID]+1) Is
Null,IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID] And
[InstalledCircuit]![Poles]="3",[Circuit]![CircuitID] Is Null And
([Circuit]![CircuitID]-1) Is Null And ([Circuit]![CircuitID]+1) Is
Null,[Circuit]![CircuitID])))))

Thank you in advance
Click to expand...
Click to expand...
 
J

Jeff Boyce

Warren

The 'data type mismatch' error message generally shows when there are two
different data types.

Have you tried simplifying the query (or rebuilding it from scratch) to see
at which point it breaks?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Warren said:
Sorry forgot to mention that in the properties of the query I have it set
to
show all records in table Circuit and only those from InstalledCircuit
that
are equal

Jeff Boyce said:
Warren

If the Circuit.CircuitID is Null, I don't believe you can join the two
tables when one holds a null.

Perhaps you could create one query to derive the values to use in case of
nulls, then build your second query to use that first one.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Warren said:
I am at a loss as to why I keep getting a data type mismatch error here.
The
Circuit.CircuitID field is a long integer autonumber data type, the
InstalledCircuit.CircuitID field is long integer data type, the
InstalledCircuit.Poles is a text data type and I left the default
settings
for each of these respective data types of each field. The query was
working
with no errors (just not with desired results) before I placed the
Circut.CircuitID Is Null in the last two If statements.

Can anyone see why I would be getting the data type mismatch?

The select statement is designed to limit values of a combo box to an
available circuit; if anyone sees a better way feel free to comment.

Also if it matters being run in A2007 on WinXPPro with all current
service
packs

SELECT Circuit.CircuitID, InstalledCircuit.CircuitID,
InstalledCircuit.Poles
FROM Circuit LEFT JOIN InstalledCircuit ON Circuit.CircuitID =
InstalledCircuit.CircuitID
WHERE
(((Circuit.CircuitID)=IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID]
And [InstalledCircuit]![Poles]="1",[Circuit]![CircuitID] Is
Null,IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID] And
[InstalledCircuit]![Poles]="2",[Circuit]![CircuitID] Is Null And
([Circuit]![CircuitID]+1) Is
Null,IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID] And
[InstalledCircuit]![Poles]="3",[Circuit]![CircuitID] Is Null And
([Circuit]![CircuitID]-1) Is Null And ([Circuit]![CircuitID]+1) Is
Null,[Circuit]![CircuitID])))))

Thank you in advance
Click to expand...
Click to expand...
Click to expand...
 
W

Warren

I did have a data type issue poles was number and not text (to many hours
staring at this thing:(). I removed the quotes from the 1, 2 and 3 pole
values and the query ran; it did not exclude the -1 and +1 values, only where
there was a direct match eg. excluded 1 if circuit matched on 1, excluded 1
but not 2 if circuit match was circuit 1 and poles 2, excluded 2 but not 1
and 3 if circuit matched on 2 and poles was 3
I will keep tinkering and see what happens
Jeff Boyce said:
Warren

The 'data type mismatch' error message generally shows when there are two
different data types.

Have you tried simplifying the query (or rebuilding it from scratch) to see
at which point it breaks?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Warren said:
Sorry forgot to mention that in the properties of the query I have it set
to
show all records in table Circuit and only those from InstalledCircuit
that
are equal

Jeff Boyce said:
Warren

If the Circuit.CircuitID is Null, I don't believe you can join the two
tables when one holds a null.

Perhaps you could create one query to derive the values to use in case of
nulls, then build your second query to use that first one.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am at a loss as to why I keep getting a data type mismatch error here.
The
Circuit.CircuitID field is a long integer autonumber data type, the
InstalledCircuit.CircuitID field is long integer data type, the
InstalledCircuit.Poles is a text data type and I left the default
settings
for each of these respective data types of each field. The query was
working
with no errors (just not with desired results) before I placed the
Circut.CircuitID Is Null in the last two If statements.

Can anyone see why I would be getting the data type mismatch?

The select statement is designed to limit values of a combo box to an
available circuit; if anyone sees a better way feel free to comment.

Also if it matters being run in A2007 on WinXPPro with all current
service
packs

SELECT Circuit.CircuitID, InstalledCircuit.CircuitID,
InstalledCircuit.Poles
FROM Circuit LEFT JOIN InstalledCircuit ON Circuit.CircuitID =
InstalledCircuit.CircuitID
WHERE
(((Circuit.CircuitID)=IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID]
And [InstalledCircuit]![Poles]="1",[Circuit]![CircuitID] Is
Null,IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID] And
[InstalledCircuit]![Poles]="2",[Circuit]![CircuitID] Is Null And
([Circuit]![CircuitID]+1) Is
Null,IIf([Circuit]![CircuitID]=[InstalledCircuit]![CircuitID] And
[InstalledCircuit]![Poles]="3",[Circuit]![CircuitID] Is Null And
([Circuit]![CircuitID]-1) Is Null And ([Circuit]![CircuitID]+1) Is
Null,[Circuit]![CircuitID])))))

Thank you in advance
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Format combined numbers as a number 5
Relatioships and like/unlike data 2
Query to Select Only First Instance of Field 3
Evaluating the results of a querie 1
Have A Form Open a Query 2
Forms based on a query 3
Pull multiple records from one table to another? 2
trouble splitting data into separate series 5

Top