complicated query

B

Bonnie

SELECT DISTINCT Val([LHEIGHT]) AS Expr1, PARTS.ITEMDESC, PARTS.PLENGTH,
Left([pwidth],2) AS Expr4, Right([PWIDTH],2) AS Expr3
FROM PARTS
GROUP BY Val([LHEIGHT]), PARTS.ITEMDESC, PARTS.PLENGTH, Left([pwidth],2),
Right([PWIDTH],2), PARTS.LHEIGHT, PARTS.STATE, PARTS.CERTIFIED
HAVING (((PARTS.ITEMDESC) Like "*LEG HT*") AND
((PARTS.PLENGTH)=[forms]![OrdersFrmNew].[slength]) AND
((Left([pwidth],2))<=[forms]![OrdersFrmNew].[swidth]) AND
((Right([PWIDTH],2))>=[forms]![OrdersFrmNew].[swidth]) AND (Not
(PARTS.LHEIGHT) Is Null) AND
((PARTS.STATE)=[forms]![OrdersFrmNew].[CkSvcDiv]) AND
((PARTS.CERTIFIED)=[forms]![OrdersFrmNew].[CkCert]))
ORDER BY Val([LHEIGHT]);


I know it looks like Greek - the only problem I am having is if
Forms!OrdersFrmNew.Swidth is 18 and RIGHT(PWIDTH,2) = 2, it isn't seeing 18
is greater than 2. I tried to pad with a space, a zero, etc. Anyone have an
idea?

Thanks in advance.

Bonnie
 
J

Jerry Whittle

The Right function returns a string. What does [slength] return? Assuming
that they both are returning strings, you could convert them to a number with
something like:

CDbl(RIGHT(PWIDTH,2)) >= CDbl([forms]![OrdersFrmNew]![swidth] )
 
B

Bonnie

Yup, I think that's what I was looking for. I tried VAL() with no results.
I'll give it a try.

Thanks Jerry, appreciate your help.

Jerry Whittle said:
The Right function returns a string. What does [slength] return? Assuming
that they both are returning strings, you could convert them to a number with
something like:

CDbl(RIGHT(PWIDTH,2)) >= CDbl([forms]![OrdersFrmNew]![swidth] )
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Bonnie said:
SELECT DISTINCT Val([LHEIGHT]) AS Expr1, PARTS.ITEMDESC, PARTS.PLENGTH,
Left([pwidth],2) AS Expr4, Right([PWIDTH],2) AS Expr3
FROM PARTS
GROUP BY Val([LHEIGHT]), PARTS.ITEMDESC, PARTS.PLENGTH, Left([pwidth],2),
Right([PWIDTH],2), PARTS.LHEIGHT, PARTS.STATE, PARTS.CERTIFIED
HAVING (((PARTS.ITEMDESC) Like "*LEG HT*") AND
((PARTS.PLENGTH)=[forms]![OrdersFrmNew].[slength]) AND
((Left([pwidth],2))<=[forms]![OrdersFrmNew].[swidth]) AND
((Right([PWIDTH],2))>=[forms]![OrdersFrmNew].[swidth]) AND (Not
(PARTS.LHEIGHT) Is Null) AND
((PARTS.STATE)=[forms]![OrdersFrmNew].[CkSvcDiv]) AND
((PARTS.CERTIFIED)=[forms]![OrdersFrmNew].[CkCert]))
ORDER BY Val([LHEIGHT]);


I know it looks like Greek - the only problem I am having is if
Forms!OrdersFrmNew.Swidth is 18 and RIGHT(PWIDTH,2) = 2, it isn't seeing 18
is greater than 2. I tried to pad with a space, a zero, etc. Anyone have an
idea?

Thanks in advance.

Bonnie
Click to expand...
Click to expand...
 
B

Bonnie

Sorry Jerry - now it says the expression is too complicated! Can you imagine
that.

Any more ideas?

SELECT DISTINCT Val([LHEIGHT]) AS Expr1, PARTS.ITEMDESC, PARTS.PLENGTH,
CDbl(Left([PWIDTH],2)) AS Expr3, CDbl(Right([pwidth],2)) AS Expr4
FROM PARTS
GROUP BY Val([LHEIGHT]), PARTS.ITEMDESC, PARTS.PLENGTH,
CDbl(Left([PWIDTH],2)), CDbl(Right([pwidth],2))
HAVING (((PARTS.ITEMDESC) Like "*LEG HT*") AND
((PARTS.PLENGTH)=[forms]![OrdersFrmNew].[slength] And Not (PARTS.PLENGTH) Is
Null) AND ((CDbl(Left([PWIDTH],2)))<=CDbl([forms]![OrdersFrmNew].[swidth]))
AND ((CDbl(Right([pwidth],2)))>=CDbl([forms]![OrdersFrmNew].[swidth])))
ORDER BY Val([LHEIGHT]);


Jerry Whittle said:
The Right function returns a string. What does [slength] return? Assuming
that they both are returning strings, you could convert them to a number with
something like:

CDbl(RIGHT(PWIDTH,2)) >= CDbl([forms]![OrdersFrmNew]![swidth] )
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Bonnie said:
SELECT DISTINCT Val([LHEIGHT]) AS Expr1, PARTS.ITEMDESC, PARTS.PLENGTH,
Left([pwidth],2) AS Expr4, Right([PWIDTH],2) AS Expr3
FROM PARTS
GROUP BY Val([LHEIGHT]), PARTS.ITEMDESC, PARTS.PLENGTH, Left([pwidth],2),
Right([PWIDTH],2), PARTS.LHEIGHT, PARTS.STATE, PARTS.CERTIFIED
HAVING (((PARTS.ITEMDESC) Like "*LEG HT*") AND
((PARTS.PLENGTH)=[forms]![OrdersFrmNew].[slength]) AND
((Left([pwidth],2))<=[forms]![OrdersFrmNew].[swidth]) AND
((Right([PWIDTH],2))>=[forms]![OrdersFrmNew].[swidth]) AND (Not
(PARTS.LHEIGHT) Is Null) AND
((PARTS.STATE)=[forms]![OrdersFrmNew].[CkSvcDiv]) AND
((PARTS.CERTIFIED)=[forms]![OrdersFrmNew].[CkCert]))
ORDER BY Val([LHEIGHT]);


I know it looks like Greek - the only problem I am having is if
Forms!OrdersFrmNew.Swidth is 18 and RIGHT(PWIDTH,2) = 2, it isn't seeing 18
is greater than 2. I tried to pad with a space, a zero, etc. Anyone have an
idea?

Thanks in advance.

Bonnie
Click to expand...
Click to expand...
 
J

Jerry Whittle

Try this:

SELECT DISTINCT Val([LHEIGHT]) AS LHEIGHT_Val,
PARTS.ITEMDESC,
PARTS.PLENGTH,
CDbl(Left([PWIDTH],2)) AS PWIDTH_Left,
CDbl(Right([PWIDTH],2)) AS PWIDTH_Right
FROM PARTS
WHERE PARTS.ITEMDESC Like "*LEG HT*"
AND PARTS.PLENGTH = [Forms]![OrdersFrmNew]![slength]
And IsNull(PARTS.PLENGTH) = False
AND CDbl(Left([PWIDTH],2)) <= [forms]![OrdersFrmNew]![swidth]
AND CDbl(Right([PWIDTH],2)) >= [forms]![OrdersFrmNew]![swidth]
ORDER BY 1 ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Bonnie said:
Sorry Jerry - now it says the expression is too complicated! Can you imagine
that.

Any more ideas?

SELECT DISTINCT Val([LHEIGHT]) AS Expr1, PARTS.ITEMDESC, PARTS.PLENGTH,
CDbl(Left([PWIDTH],2)) AS Expr3, CDbl(Right([pwidth],2)) AS Expr4
FROM PARTS
GROUP BY Val([LHEIGHT]), PARTS.ITEMDESC, PARTS.PLENGTH,
CDbl(Left([PWIDTH],2)), CDbl(Right([pwidth],2))
HAVING (((PARTS.ITEMDESC) Like "*LEG HT*") AND
((PARTS.PLENGTH)=[forms]![OrdersFrmNew].[slength] And Not (PARTS.PLENGTH) Is
Null) AND ((CDbl(Left([PWIDTH],2)))<=CDbl([forms]![OrdersFrmNew].[swidth]))
AND ((CDbl(Right([pwidth],2)))>=CDbl([forms]![OrdersFrmNew].[swidth])))
ORDER BY Val([LHEIGHT]);


Jerry Whittle said:
The Right function returns a string. What does [slength] return? Assuming
that they both are returning strings, you could convert them to a number with
something like:

CDbl(RIGHT(PWIDTH,2)) >= CDbl([forms]![OrdersFrmNew]![swidth] )
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Bonnie said:
SELECT DISTINCT Val([LHEIGHT]) AS Expr1, PARTS.ITEMDESC, PARTS.PLENGTH,
Left([pwidth],2) AS Expr4, Right([PWIDTH],2) AS Expr3
FROM PARTS
GROUP BY Val([LHEIGHT]), PARTS.ITEMDESC, PARTS.PLENGTH, Left([pwidth],2),
Right([PWIDTH],2), PARTS.LHEIGHT, PARTS.STATE, PARTS.CERTIFIED
HAVING (((PARTS.ITEMDESC) Like "*LEG HT*") AND
((PARTS.PLENGTH)=[forms]![OrdersFrmNew].[slength]) AND
((Left([pwidth],2))<=[forms]![OrdersFrmNew].[swidth]) AND
((Right([PWIDTH],2))>=[forms]![OrdersFrmNew].[swidth]) AND (Not
(PARTS.LHEIGHT) Is Null) AND
((PARTS.STATE)=[forms]![OrdersFrmNew].[CkSvcDiv]) AND
((PARTS.CERTIFIED)=[forms]![OrdersFrmNew].[CkCert]))
ORDER BY Val([LHEIGHT]);


I know it looks like Greek - the only problem I am having is if
Forms!OrdersFrmNew.Swidth is 18 and RIGHT(PWIDTH,2) = 2, it isn't seeing 18
is greater than 2. I tried to pad with a space, a zero, etc. Anyone have an
idea?

Thanks in advance.

Bonnie
Click to expand...
Click to expand...
Click to expand...
 
B

Bonnie

Copied and pasted it and it's still giving me the too complex error!!??

Any other ideas?

Thanks,

Bonnie

Jerry Whittle said:
Try this:

SELECT DISTINCT Val([LHEIGHT]) AS LHEIGHT_Val,
PARTS.ITEMDESC,
PARTS.PLENGTH,
CDbl(Left([PWIDTH],2)) AS PWIDTH_Left,
CDbl(Right([PWIDTH],2)) AS PWIDTH_Right
FROM PARTS
WHERE PARTS.ITEMDESC Like "*LEG HT*"
AND PARTS.PLENGTH = [Forms]![OrdersFrmNew]![slength]
And IsNull(PARTS.PLENGTH) = False
AND CDbl(Left([PWIDTH],2)) <= [forms]![OrdersFrmNew]![swidth]
AND CDbl(Right([PWIDTH],2)) >= [forms]![OrdersFrmNew]![swidth]
ORDER BY 1 ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Bonnie said:
Sorry Jerry - now it says the expression is too complicated! Can you imagine
that.

Any more ideas?

SELECT DISTINCT Val([LHEIGHT]) AS Expr1, PARTS.ITEMDESC, PARTS.PLENGTH,
CDbl(Left([PWIDTH],2)) AS Expr3, CDbl(Right([pwidth],2)) AS Expr4
FROM PARTS
GROUP BY Val([LHEIGHT]), PARTS.ITEMDESC, PARTS.PLENGTH,
CDbl(Left([PWIDTH],2)), CDbl(Right([pwidth],2))
HAVING (((PARTS.ITEMDESC) Like "*LEG HT*") AND
((PARTS.PLENGTH)=[forms]![OrdersFrmNew].[slength] And Not (PARTS.PLENGTH) Is
Null) AND ((CDbl(Left([PWIDTH],2)))<=CDbl([forms]![OrdersFrmNew].[swidth]))
AND ((CDbl(Right([pwidth],2)))>=CDbl([forms]![OrdersFrmNew].[swidth])))
ORDER BY Val([LHEIGHT]);


Jerry Whittle said:
The Right function returns a string. What does [slength] return? Assuming
that they both are returning strings, you could convert them to a number with
something like:

CDbl(RIGHT(PWIDTH,2)) >= CDbl([forms]![OrdersFrmNew]![swidth] )
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

SELECT DISTINCT Val([LHEIGHT]) AS Expr1, PARTS.ITEMDESC, PARTS.PLENGTH,
Left([pwidth],2) AS Expr4, Right([PWIDTH],2) AS Expr3
FROM PARTS
GROUP BY Val([LHEIGHT]), PARTS.ITEMDESC, PARTS.PLENGTH, Left([pwidth],2),
Right([PWIDTH],2), PARTS.LHEIGHT, PARTS.STATE, PARTS.CERTIFIED
HAVING (((PARTS.ITEMDESC) Like "*LEG HT*") AND
((PARTS.PLENGTH)=[forms]![OrdersFrmNew].[slength]) AND
((Left([pwidth],2))<=[forms]![OrdersFrmNew].[swidth]) AND
((Right([PWIDTH],2))>=[forms]![OrdersFrmNew].[swidth]) AND (Not
(PARTS.LHEIGHT) Is Null) AND
((PARTS.STATE)=[forms]![OrdersFrmNew].[CkSvcDiv]) AND
((PARTS.CERTIFIED)=[forms]![OrdersFrmNew].[CkCert]))
ORDER BY Val([LHEIGHT]);


I know it looks like Greek - the only problem I am having is if
Forms!OrdersFrmNew.Swidth is 18 and RIGHT(PWIDTH,2) = 2, it isn't seeing 18
is greater than 2. I tried to pad with a space, a zero, etc. Anyone have an
idea?

Thanks in advance.

Bonnie
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

Copied and pasted it and it's still giving me the too complex error!!??
Click to expand...

PMFJI but putting explicit Parameters in the query definition can help prevent
the Too Complex error. Try

PARAMETERS [Forms]![OrdersFrmNew]![slength] Float,
[forms]![OrdersFrmNew]![swidth] Float;
SELECT DISTINCT Val([LHEIGHT]) AS LHEIGHT_Val,
PARTS.ITEMDESC,
PARTS.PLENGTH,
CDbl(Left([PWIDTH],2)) AS PWIDTH_Left,
CDbl(Right([PWIDTH],2)) AS PWIDTH_Right
FROM PARTS
WHERE PARTS.ITEMDESC Like "*LEG HT*"
AND PARTS.PLENGTH = [Forms]![OrdersFrmNew]![slength]
And PARTS.PLENGTH IS NOT NULL
AND CDbl(Left([PWIDTH],2)) <= [forms]![OrdersFrmNew]![swidth]
AND CDbl(Right([PWIDTH],2)) >= [forms]![OrdersFrmNew]![swidth]
ORDER BY 1 ;


I have to wonder about the CDbl - are the swidth and plength values in fact
decimal numbers with fractions? If they're integers use Long instead of Float
and CLng() instead of CDbl().

John W. Vinson [MVP]
 
B

Bonnie

Dang - still too complex! What does PMFJI mean?

Thanks,

B

John W. Vinson said:
Copied and pasted it and it's still giving me the too complex error!!??
Click to expand...

PMFJI but putting explicit Parameters in the query definition can help prevent
the Too Complex error. Try

PARAMETERS [Forms]![OrdersFrmNew]![slength] Float,
[forms]![OrdersFrmNew]![swidth] Float;
SELECT DISTINCT Val([LHEIGHT]) AS LHEIGHT_Val,
PARTS.ITEMDESC,
PARTS.PLENGTH,
CDbl(Left([PWIDTH],2)) AS PWIDTH_Left,
CDbl(Right([PWIDTH],2)) AS PWIDTH_Right
FROM PARTS
WHERE PARTS.ITEMDESC Like "*LEG HT*"
AND PARTS.PLENGTH = [Forms]![OrdersFrmNew]![slength]
And PARTS.PLENGTH IS NOT NULL
AND CDbl(Left([PWIDTH],2)) <= [forms]![OrdersFrmNew]![swidth]
AND CDbl(Right([PWIDTH],2)) >= [forms]![OrdersFrmNew]![swidth]
ORDER BY 1 ;


I have to wonder about the CDbl - are the swidth and plength values in fact
decimal numbers with fractions? If they're integers use Long instead of Float
and CLng() instead of CDbl().

John W. Vinson [MVP]
Click to expand...
 
B

Bonnie

Oops, Sorry John - just noticed the question at the bottom. I'm going to go
check these and will let you know.

Thanks.

John W. Vinson said:
Copied and pasted it and it's still giving me the too complex error!!??
Click to expand...

PMFJI but putting explicit Parameters in the query definition can help prevent
the Too Complex error. Try

PARAMETERS [Forms]![OrdersFrmNew]![slength] Float,
[forms]![OrdersFrmNew]![swidth] Float;
SELECT DISTINCT Val([LHEIGHT]) AS LHEIGHT_Val,
PARTS.ITEMDESC,
PARTS.PLENGTH,
CDbl(Left([PWIDTH],2)) AS PWIDTH_Left,
CDbl(Right([PWIDTH],2)) AS PWIDTH_Right
FROM PARTS
WHERE PARTS.ITEMDESC Like "*LEG HT*"
AND PARTS.PLENGTH = [Forms]![OrdersFrmNew]![slength]
And PARTS.PLENGTH IS NOT NULL
AND CDbl(Left([PWIDTH],2)) <= [forms]![OrdersFrmNew]![swidth]
AND CDbl(Right([PWIDTH],2)) >= [forms]![OrdersFrmNew]![swidth]
ORDER BY 1 ;


I have to wonder about the CDbl - are the swidth and plength values in fact
decimal numbers with fractions? If they're integers use Long instead of Float
and CLng() instead of CDbl().

John W. Vinson [MVP]
Click to expand...
 
B

Bonnie

sheight is LONG INTEGER
pwidth is TEXT


I tried CLNG on PWIDTH and it's still too complex?

John W. Vinson said:
Copied and pasted it and it's still giving me the too complex error!!??
Click to expand...

PMFJI but putting explicit Parameters in the query definition can help prevent
the Too Complex error. Try

PARAMETERS [Forms]![OrdersFrmNew]![slength] Float,
[forms]![OrdersFrmNew]![swidth] Float;
SELECT DISTINCT Val([LHEIGHT]) AS LHEIGHT_Val,
PARTS.ITEMDESC,
PARTS.PLENGTH,
CDbl(Left([PWIDTH],2)) AS PWIDTH_Left,
CDbl(Right([PWIDTH],2)) AS PWIDTH_Right
FROM PARTS
WHERE PARTS.ITEMDESC Like "*LEG HT*"
AND PARTS.PLENGTH = [Forms]![OrdersFrmNew]![slength]
And PARTS.PLENGTH IS NOT NULL
AND CDbl(Left([PWIDTH],2)) <= [forms]![OrdersFrmNew]![swidth]
AND CDbl(Right([PWIDTH],2)) >= [forms]![OrdersFrmNew]![swidth]
ORDER BY 1 ;


I have to wonder about the CDbl - are the swidth and plength values in fact
decimal numbers with fractions? If they're integers use Long instead of Float
and CLng() instead of CDbl().

John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

sheight is LONG INTEGER
pwidth is TEXT


I tried CLNG on PWIDTH and it's still too complex?
Click to expand...

What are some typical values of the fields? What is their real-life meaning?

John W. Vinson [MVP]
 
B

Bonnie

Hi John,

I checked the records and find that (duh) it doesn't matter what the plength
is, all the widths have the same leg ht options so I took out the plength
check and it works. Thanks so much for sticking with me to try to figure it
out.

Bonnie
 

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

Else Without If 8

Top