Data Type Mismatch

[Guest]

Good morning,
I have a query that is giving me a data type mismatch error. I do not have join relationships. However, I do have a query criteria that should ensure I get the right values and not just the union set of the 2 'tables' I'm using. The SQL statement will be followed with parameters for the table fields.

SELECT qryForkTravel.Fro, qryForkTravel.Too, qryForkTravel.TotalDist1, tblForkTravel.TMU, qryForkTravel.Generate
FROM qryForkTravel, tblForkTravel
WHERE (((qryForkTravel.TotalDist1)<=[highdist] And (qryForkTravel.TotalDist1)>=[lowdist]));

TEXT: Fro, Too
Number: TotalDist1 (calculated), TMU, highdist, lowdist
Autonumber: Generate

I cannot, for the life of me, figure out where my mismatch is taking place. For reference, the query is to take into account a warehouse starting location and ending location, including all the slots and floor/receiving spaces in the warehouse. Then, based on the racking heights and aisle lengths, I have 'built' a 3D coordinate system that takes the delta for X, Y, and Z for each location from or to which my simulated forklift operator travels. Using these, I can figure out with a function how much time is spent travelling on the floor (X and Y) or raising or lowering the forks (Z) per pallet move.

Any and all help would be greatly appreciated.

Thank you in advance!
Derek

 

[Allen Browne]

Derek, you may need to explicitly typecast your calculated fields here, or
in the lower-level queries.

See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Good morning,
I have a query that is giving me a data type mismatch error. I do not

have join relationships. However, I do have a query criteria that should
ensure I get the right values and not just the union set of the 2 'tables'
I'm using. The SQL statement will be followed with parameters for the table
fields.

SELECT qryForkTravel.Fro, qryForkTravel.Too, qryForkTravel.TotalDist1,

tblForkTravel.TMU, qryForkTravel.Generate

FROM qryForkTravel, tblForkTravel
WHERE (((qryForkTravel.TotalDist1)<=[highdist] And (qryForkTravel.TotalDist1)>=[lowdist]));

TEXT: Fro, Too
Number: TotalDist1 (calculated), TMU, highdist, lowdist
Autonumber: Generate

I cannot, for the life of me, figure out where my mismatch is taking

place. For reference, the query is to take into account a warehouse
starting location and ending location, including all the slots and
floor/receiving spaces in the warehouse. Then, based on the racking heights
and aisle lengths, I have 'built' a 3D coordinate system that takes the
delta for X, Y, and Z for each location from or to which my simulated
forklift operator travels. Using these, I can figure out with a function
how much time is spent travelling on the floor (X and Y) or raising or
lowering the forks (Z) per pallet move.

 

[Guest]

Allen,
Thanks for the help. I'm not sure how to typecast my calculated fields in the query. Or do I need to do this in function modules and put the functions in the queries instead?

Thanks!
Derek

Derek, you may need to explicitly typecast your calculated fields here, or
in the lower-level queries.

See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Good morning,
I have a query that is giving me a data type mismatch error. I do not

have join relationships. However, I do have a query criteria that should
ensure I get the right values and not just the union set of the 2 'tables'
I'm using. The SQL statement will be followed with parameters for the table
fields.

SELECT qryForkTravel.Fro, qryForkTravel.Too, qryForkTravel.TotalDist1,

tblForkTravel.TMU, qryForkTravel.Generate

FROM qryForkTravel, tblForkTravel
WHERE (((qryForkTravel.TotalDist1)<=[highdist] And (qryForkTravel.TotalDist1)>=[lowdist]));

TEXT: Fro, Too
Number: TotalDist1 (calculated), TMU, highdist, lowdist
Autonumber: Generate

I cannot, for the life of me, figure out where my mismatch is taking

place. For reference, the query is to take into account a warehouse
starting location and ending location, including all the slots and
floor/receiving spaces in the warehouse. Then, based on the racking heights
and aisle lengths, I have 'built' a 3D coordinate system that takes the
delta for X, Y, and Z for each location from or to which my simulated
forklift operator travels. Using these, I can figure out with a function
how much time is spent travelling on the floor (X and Y) or raising or
lowering the forks (Z) per pallet move.

Any and all help would be greatly appreciated.

Thank you in advance!
Derek

 

[Allen Browne]

For whole numbers, use CLng().
For fractional numbers, use CDbl().
For currency values, use CCur().

Example of a calculated field:
TotalDist1: CLng(Nz([Dist1],0) + Nz(Dist2],0))

Example of a WHERE clause:
WHERE CLng(qryForkTravel.TotalDist1) Between CLng([highdist]) And
CLng([lowdist])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thanks for the help. I'm not sure how to typecast my calculated fields in

the query. Or do I need to do this in function modules and put the
functions in the queries instead?

Thanks!
Derek

Derek, you may need to explicitly typecast your calculated fields here, or
in the lower-level queries.

See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Good morning,
I have a query that is giving me a data type mismatch error. I do not

have join relationships. However, I do have a query criteria that should
ensure I get the right values and not just the union set of the 2 'tables'
I'm using. The SQL statement will be followed with parameters for the table
fields.

SELECT qryForkTravel.Fro, qryForkTravel.Too, qryForkTravel.TotalDist1,

tblForkTravel.TMU, qryForkTravel.Generate

FROM qryForkTravel, tblForkTravel
WHERE (((qryForkTravel.TotalDist1)<=[highdist] And (qryForkTravel.TotalDist1)>=[lowdist]));

TEXT: Fro, Too
Number: TotalDist1 (calculated), TMU, highdist, lowdist
Autonumber: Generate

I cannot, for the life of me, figure out where my mismatch is taking

place. For reference, the query is to take into account a warehouse
starting location and ending location, including all the slots and
floor/receiving spaces in the warehouse. Then, based on the racking heights
and aisle lengths, I have 'built' a 3D coordinate system that takes the
delta for X, Y, and Z for each location from or to which my simulated
forklift operator travels. Using these, I can figure out with a function
how much time is spent travelling on the floor (X and Y) or raising or
lowering the forks (Z) per pallet move.

Any and all help would be greatly appreciated.

Thank you in advance!
Derek

 

[Dale Fye]

Derek,

Is there any chance you have some of the [highdist] or
[lowdist] fields as NULL values? Not that that should
matter, just checking.

You can use the conversion functions to explicitly type
the values of a calculation. For example:

TotalDist1: cInt(some calcluation) will explicitly type
TotalDist1 as an integer.

HTH
Dale

-----Original Message-----
Good morning,
I have a query that is giving me a data type mismatch

error. I do not have join relationships. However, I do
have a query criteria that should ensure I get the right
values and not just the union set of the 2 'tables' I'm
using. The SQL statement will be followed with parameters
for the table fields.

SELECT qryForkTravel.Fro

, qryForkTravel.Too
, qryForkTravel.TotalDist1
, tblForkTravel.TMU
, qryForkTravel.Generate

FROM qryForkTravel , tblForkTravel
WHERE qryForkTravel.TotalDist1 <= [highdist]

AND qryForkTravel.TotalDist1 >= [lowdist];

TEXT: Fro, Too
Number: TotalDist1 (calculated), TMU, highdist, lowdist
Autonumber: Generate

I cannot, for the life of me, figure out where my

mismatch is taking place. For reference, the query is to
take into account a warehouse starting location and ending
location, including all the slots and floor/receiving
spaces in the warehouse. Then, based on the racking
heights and aisle lengths, I have 'built' a 3D coordinate
system that takes the delta for X, Y, and Z for each
location from or to which my simulated forklift operator
travels. Using these, I can figure out with a function
how much time is spent travelling on the floor (X and Y)
or raising or lowering the forks (Z) per pallet move.