Query failing with convert date critieria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need help with a query written by someone else that is failing. Here is where
I think the query is failing. If you need all of the code for the query, let
me know.

From the query window:
Field = R4UD16
Table = dbo_WCDTALIBZ_WCRMIP00
Criteria = >1001010 And Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt])

The code in the SQL view is:
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
 
The code in the SQL view is:
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
--
Click to expand...

What's the datatype of R4UD16? And what's the code in
ConvertDateToWCKRDate?

John W. Vinson[MVP]
 
If R4UD16 is a DateTime datatype then you need the following --

WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>#01/01/1001# And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))

Why do you have two parameters for R4UD16? If you always want the date to
be greater than 1 JAN 1001 then why use a BETWEEN statement?

Have you developed your own function ConvertDateToWCKRDate ?
 
The datatype for R4UD16 is Number,
and the code in the ConverDateToWCKRDate is:
--vb converdate module

Function ConvertWCDate(strDate As String) As String

ConvertWCDate = Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" &
Left(strDate, 4)

End Function
--

The previous query criteria that bombed completely is
1001010 And Between ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]), And Between ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt])
Click to expand...

B. J. Ayers
Goodrich Corp.


John Vinson said:
The code in the SQL view is:
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
--
Click to expand...

What's the datatype of R4UD16? And what's the code in
ConvertDateToWCKRDate?

John W. Vinson[MVP]
Click to expand...
 
This query was written by someone who is no longer working here.

The datatype for R4UD16 is Number

and the vb ConvertDateToWCKRDate module is:

Function ConvertWCDate(strDate As String) As String

ConvertWCDate = Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" &
Left(strDate, 4)

End Function
--
B. J. Ayers
Goodrich Corp.


KARL DEWEY said:
If R4UD16 is a DateTime datatype then you need the following --

WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>#01/01/1001# And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))

Why do you have two parameters for R4UD16? If you always want the date to
be greater than 1 JAN 1001 then why use a BETWEEN statement?

Have you developed your own function ConvertDateToWCKRDate ?

bjayers said:
Need help with a query written by someone else that is failing. Here is where
I think the query is failing. If you need all of the code for the query, let
me know.

From the query window:
Field = R4UD16
Table = dbo_WCDTALIBZ_WCRMIP00
Criteria = >1001010 And Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt])

The code in the SQL view is:
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
Click to expand...
Click to expand...
 
Based on your conversion it appears the you are missing a digit --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And

To be --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And


bjayers said:
This query was written by someone who is no longer working here.

The datatype for R4UD16 is Number

and the vb ConvertDateToWCKRDate module is:

Function ConvertWCDate(strDate As String) As String

ConvertWCDate = Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" &
Left(strDate, 4)

End Function
--
B. J. Ayers
Goodrich Corp.


KARL DEWEY said:
If R4UD16 is a DateTime datatype then you need the following --

WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>#01/01/1001# And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))

Why do you have two parameters for R4UD16? If you always want the date to
be greater than 1 JAN 1001 then why use a BETWEEN statement?

Have you developed your own function ConvertDateToWCKRDate ?

bjayers said:
Need help with a query written by someone else that is failing. Here is where
I think the query is failing. If you need all of the code for the query, let
me know.

From the query window:
Field = R4UD16
Table = dbo_WCDTALIBZ_WCRMIP00
Criteria = >1001010 And Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt])

The code in the SQL view is:
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
Click to expand...
Click to expand...
Click to expand...
 
Here is how I changed the SQL view of the query
SELECT qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")) AS TYPE,
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] AS [RHRMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM AS
RHCNAM, dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, Sum(qryTATTracking.[NO OF DAYS]) AS [STATUS DAYS],
WCKRConvertDate([R4UD16]) AS CLOSEDT
FROM (qryTATTracking LEFT JOIN dbo_WCDTALIBZ_WCRMIP00 ON qryTATTracking.RMA
= dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]) LEFT JOIN qryKRROatBER ON
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] = qryKRROatBER.[RORMA#]
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
AND ((dbo_WCDTALIBZ_WCRMIP00.R4STCD)="CLOSE" Or
(dbo_WCDTALIBZ_WCRMIP00.R4STCD)='C') AND ((qryKRROatBER.[RORMA#]) Is Null))
GROUP BY qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")),
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM,
dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, WCKRConvertDate([R4UD16])
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") AND
((WCKRConvertDate([R4UD16])) AND Between [Forms]![frmMainMenu]![txtKRBegDt]
AND [Forms]![frmMainMenu]![txtKREndDt]))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;

Now it's telling me that I missing an operating and is highlighting the
Between in the following:
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") AND
((WCKRConvertDate([R4UD16])) AND Between [Forms]![frmMainMenu]![txtKRBegDt]
AND [Forms]![frmMainMenu]![txtKREndDt]))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;
--
B. J. Ayers
Goodrich Corp.


KARL DEWEY said:
Based on your conversion it appears the you are missing a digit --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And

To be --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And


bjayers said:
This query was written by someone who is no longer working here.

The datatype for R4UD16 is Number

and the vb ConvertDateToWCKRDate module is:

Function ConvertWCDate(strDate As String) As String

ConvertWCDate = Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" &
Left(strDate, 4)

End Function
--
B. J. Ayers
Goodrich Corp.


KARL DEWEY said:
If R4UD16 is a DateTime datatype then you need the following --

WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>#01/01/1001# And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))

Why do you have two parameters for R4UD16? If you always want the date to
be greater than 1 JAN 1001 then why use a BETWEEN statement?

Have you developed your own function ConvertDateToWCKRDate ?

:

Need help with a query written by someone else that is failing. Here is where
I think the query is failing. If you need all of the code for the query, let
me know.

From the query window:
Field = R4UD16
Table = dbo_WCDTALIBZ_WCRMIP00
Criteria = >1001010 And Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt])

The code in the SQL view is:
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
Click to expand...
Click to expand...
Click to expand...
 
AND ((WCKRConvertDate([R4UD16])) AND Between
This element is not doing anything. You omitted your earlier >10010101

bjayers said:
Here is how I changed the SQL view of the query
SELECT qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")) AS TYPE,
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] AS [RHRMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM AS
RHCNAM, dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, Sum(qryTATTracking.[NO OF DAYS]) AS [STATUS DAYS],
WCKRConvertDate([R4UD16]) AS CLOSEDT
FROM (qryTATTracking LEFT JOIN dbo_WCDTALIBZ_WCRMIP00 ON qryTATTracking.RMA
= dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]) LEFT JOIN qryKRROatBER ON
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] = qryKRROatBER.[RORMA#]
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
AND ((dbo_WCDTALIBZ_WCRMIP00.R4STCD)="CLOSE" Or
(dbo_WCDTALIBZ_WCRMIP00.R4STCD)='C') AND ((qryKRROatBER.[RORMA#]) Is Null))
GROUP BY qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")),
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM,
dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, WCKRConvertDate([R4UD16])
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") AND
((WCKRConvertDate([R4UD16])) AND Between [Forms]![frmMainMenu]![txtKRBegDt]
AND [Forms]![frmMainMenu]![txtKREndDt]))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;

Now it's telling me that I missing an operating and is highlighting the
Between in the following:
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") AND
((WCKRConvertDate([R4UD16])) AND Between [Forms]![frmMainMenu]![txtKRBegDt]
AND [Forms]![frmMainMenu]![txtKREndDt]))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;
--
B. J. Ayers
Goodrich Corp.


KARL DEWEY said:
Based on your conversion it appears the you are missing a digit --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And

To be --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And


bjayers said:
This query was written by someone who is no longer working here.

The datatype for R4UD16 is Number

and the vb ConvertDateToWCKRDate module is:

Function ConvertWCDate(strDate As String) As String

ConvertWCDate = Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" &
Left(strDate, 4)

End Function
--
B. J. Ayers
Goodrich Corp.


:

If R4UD16 is a DateTime datatype then you need the following --

WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>#01/01/1001# And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))

Why do you have two parameters for R4UD16? If you always want the date to
be greater than 1 JAN 1001 then why use a BETWEEN statement?

Have you developed your own function ConvertDateToWCKRDate ?

:

Need help with a query written by someone else that is failing. Here is where
I think the query is failing. If you need all of the code for the query, let
me know.

From the query window:
Field = R4UD16
Table = dbo_WCDTALIBZ_WCRMIP00
Criteria = >1001010 And Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt])

The code in the SQL view is:
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
Click to expand...
Click to expand...
Click to expand...
 
First of all, I want to say how much I appreciate your help and feedback. At
least the query doesn't bomb completely like it did before, however it still
in not giving me any data in the datasheet view. Here is how sql view of the
query looks now.

SELECT qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")) AS TYPE,
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] AS [RHRMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM AS
RHCNAM, dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, Sum(qryTATTracking.[NO OF DAYS]) AS [STATUS DAYS],
WCKRConvertDate([R4UD16]) AS CLOSEDT
FROM (qryTATTracking LEFT JOIN dbo_WCDTALIBZ_WCRMIP00 ON
qryTATTracking.RMA=dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]) LEFT JOIN qryKRROatBER ON
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]=qryKRROatBER.[RORMA#]
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) And "ConvertDateToWCKRDate" Between
(Forms!frmMainMenu!txtKRBegDt) And (Forms!frmMainMenu!txtKREndDt)) And
((dbo_WCDTALIBZ_WCRMIP00.R4STCD)="CLOSE" Or
(dbo_WCDTALIBZ_WCRMIP00.R4STCD)='C') And ((qryKRROatBER.[RORMA#]) Is Null))
GROUP BY qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")),
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM,
dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, WCKRConvertDate([R4UD16])
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") And
((WCKRConvertDate([R4UD16]))>10010101 Between Forms!frmMainMenu!txtKRBegDt
And Forms!frmMainMenu!txtKREndDt))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;

--
B. J. Ayers
Goodrich Corp.


KARL DEWEY said:
AND ((WCKRConvertDate([R4UD16])) AND Between
Click to expand...
Click to expand...
This element is not doing anything. You omitted your earlier >10010101

bjayers said:
Here is how I changed the SQL view of the query
SELECT qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")) AS TYPE,
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] AS [RHRMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM AS
RHCNAM, dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, Sum(qryTATTracking.[NO OF DAYS]) AS [STATUS DAYS],
WCKRConvertDate([R4UD16]) AS CLOSEDT
FROM (qryTATTracking LEFT JOIN dbo_WCDTALIBZ_WCRMIP00 ON qryTATTracking.RMA
= dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]) LEFT JOIN qryKRROatBER ON
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] = qryKRROatBER.[RORMA#]
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
AND ((dbo_WCDTALIBZ_WCRMIP00.R4STCD)="CLOSE" Or
(dbo_WCDTALIBZ_WCRMIP00.R4STCD)='C') AND ((qryKRROatBER.[RORMA#]) Is Null))
GROUP BY qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")),
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM,
dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, WCKRConvertDate([R4UD16])
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") AND
((WCKRConvertDate([R4UD16])) AND Between [Forms]![frmMainMenu]![txtKRBegDt]
AND [Forms]![frmMainMenu]![txtKREndDt]))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;

Now it's telling me that I missing an operating and is highlighting the
Between in the following:
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") AND
((WCKRConvertDate([R4UD16])) AND Between [Forms]![frmMainMenu]![txtKRBegDt]
AND [Forms]![frmMainMenu]![txtKREndDt]))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;
--
B. J. Ayers
Goodrich Corp.


KARL DEWEY said:
Based on your conversion it appears the you are missing a digit --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And

To be --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And


:

This query was written by someone who is no longer working here.

The datatype for R4UD16 is Number

and the vb ConvertDateToWCKRDate module is:

Function ConvertWCDate(strDate As String) As String

ConvertWCDate = Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" &
Left(strDate, 4)

End Function
--
B. J. Ayers
Goodrich Corp.


:

If R4UD16 is a DateTime datatype then you need the following --

WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>#01/01/1001# And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))

Why do you have two parameters for R4UD16? If you always want the date to
be greater than 1 JAN 1001 then why use a BETWEEN statement?

Have you developed your own function ConvertDateToWCKRDate ?

:

Need help with a query written by someone else that is failing. Here is where
I think the query is failing. If you need all of the code for the query, let
me know.

From the query window:
Field = R4UD16
Table = dbo_WCDTALIBZ_WCRMIP00
Criteria = >1001010 And Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt])

The code in the SQL view is:
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
Click to expand...
Click to expand...
Click to expand...
 
I would recommend riping out a part at a time of the conditions/parameters
until it gives you something. When it does then try the orignal without the
last thing you deleted.

Build a little - test a little.

bjayers said:
First of all, I want to say how much I appreciate your help and feedback. At
least the query doesn't bomb completely like it did before, however it still
in not giving me any data in the datasheet view. Here is how sql view of the
query looks now.

SELECT qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")) AS TYPE,
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] AS [RHRMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM AS
RHCNAM, dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, Sum(qryTATTracking.[NO OF DAYS]) AS [STATUS DAYS],
WCKRConvertDate([R4UD16]) AS CLOSEDT
FROM (qryTATTracking LEFT JOIN dbo_WCDTALIBZ_WCRMIP00 ON
qryTATTracking.RMA=dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]) LEFT JOIN qryKRROatBER ON
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]=qryKRROatBER.[RORMA#]
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) And "ConvertDateToWCKRDate" Between
(Forms!frmMainMenu!txtKRBegDt) And (Forms!frmMainMenu!txtKREndDt)) And
((dbo_WCDTALIBZ_WCRMIP00.R4STCD)="CLOSE" Or
(dbo_WCDTALIBZ_WCRMIP00.R4STCD)='C') And ((qryKRROatBER.[RORMA#]) Is Null))
GROUP BY qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")),
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM,
dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, WCKRConvertDate([R4UD16])
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") And
((WCKRConvertDate([R4UD16]))>10010101 Between Forms!frmMainMenu!txtKRBegDt
And Forms!frmMainMenu!txtKREndDt))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;

--
B. J. Ayers
Goodrich Corp.


KARL DEWEY said:
AND ((WCKRConvertDate([R4UD16])) AND Between
Click to expand...
This element is not doing anything. You omitted your earlier >10010101

bjayers said:
Here is how I changed the SQL view of the query
SELECT qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")) AS TYPE,
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] AS [RHRMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM AS
RHCNAM, dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, Sum(qryTATTracking.[NO OF DAYS]) AS [STATUS DAYS],
WCKRConvertDate([R4UD16]) AS CLOSEDT
FROM (qryTATTracking LEFT JOIN dbo_WCDTALIBZ_WCRMIP00 ON qryTATTracking.RMA
= dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]) LEFT JOIN qryKRROatBER ON
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] = qryKRROatBER.[RORMA#]
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
AND ((dbo_WCDTALIBZ_WCRMIP00.R4STCD)="CLOSE" Or
(dbo_WCDTALIBZ_WCRMIP00.R4STCD)='C') AND ((qryKRROatBER.[RORMA#]) Is Null))
GROUP BY qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")),
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM,
dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, WCKRConvertDate([R4UD16])
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") AND
((WCKRConvertDate([R4UD16])) AND Between [Forms]![frmMainMenu]![txtKRBegDt]
AND [Forms]![frmMainMenu]![txtKREndDt]))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;

Now it's telling me that I missing an operating and is highlighting the
Between in the following:
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") AND
((WCKRConvertDate([R4UD16])) AND Between [Forms]![frmMainMenu]![txtKRBegDt]
AND [Forms]![frmMainMenu]![txtKREndDt]))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;
--
B. J. Ayers
Goodrich Corp.


:

Based on your conversion it appears the you are missing a digit --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And

To be --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And


:

This query was written by someone who is no longer working here.

The datatype for R4UD16 is Number

and the vb ConvertDateToWCKRDate module is:

Function ConvertWCDate(strDate As String) As String

ConvertWCDate = Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" &
Left(strDate, 4)

End Function
--
B. J. Ayers
Goodrich Corp.


:

If R4UD16 is a DateTime datatype then you need the following --

WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>#01/01/1001# And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))

Why do you have two parameters for R4UD16? If you always want the date to
be greater than 1 JAN 1001 then why use a BETWEEN statement?

Have you developed your own function ConvertDateToWCKRDate ?

:

Need help with a query written by someone else that is failing. Here is where
I think the query is failing. If you need all of the code for the query, let
me know.

From the query window:
Field = R4UD16
Table = dbo_WCDTALIBZ_WCRMIP00
Criteria = >1001010 And Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt])

The code in the SQL view is:
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
Click to expand...
Click to expand...
Click to expand...
 
Will do. I will update you on my results. I have also located a tool
through a goggle search called MS Access Query Analyzer Tool that I am going
to download and try.
--
B. J. Ayers
Goodrich Corp.


KARL DEWEY said:
I would recommend riping out a part at a time of the conditions/parameters
until it gives you something. When it does then try the orignal without the
last thing you deleted.

Build a little - test a little.

bjayers said:
First of all, I want to say how much I appreciate your help and feedback. At
least the query doesn't bomb completely like it did before, however it still
in not giving me any data in the datasheet view. Here is how sql view of the
query looks now.

SELECT qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")) AS TYPE,
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] AS [RHRMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM AS
RHCNAM, dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, Sum(qryTATTracking.[NO OF DAYS]) AS [STATUS DAYS],
WCKRConvertDate([R4UD16]) AS CLOSEDT
FROM (qryTATTracking LEFT JOIN dbo_WCDTALIBZ_WCRMIP00 ON
qryTATTracking.RMA=dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]) LEFT JOIN qryKRROatBER ON
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]=qryKRROatBER.[RORMA#]
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) And "ConvertDateToWCKRDate" Between
(Forms!frmMainMenu!txtKRBegDt) And (Forms!frmMainMenu!txtKREndDt)) And
((dbo_WCDTALIBZ_WCRMIP00.R4STCD)="CLOSE" Or
(dbo_WCDTALIBZ_WCRMIP00.R4STCD)='C') And ((qryKRROatBER.[RORMA#]) Is Null))
GROUP BY qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")),
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM,
dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, WCKRConvertDate([R4UD16])
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") And
((WCKRConvertDate([R4UD16]))>10010101 Between Forms!frmMainMenu!txtKRBegDt
And Forms!frmMainMenu!txtKREndDt))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;

--
B. J. Ayers
Goodrich Corp.


KARL DEWEY said:
AND ((WCKRConvertDate([R4UD16])) AND Between
This element is not doing anything. You omitted your earlier >10010101

:

Here is how I changed the SQL view of the query
SELECT qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")) AS TYPE,
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] AS [RHRMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM AS
RHCNAM, dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, Sum(qryTATTracking.[NO OF DAYS]) AS [STATUS DAYS],
WCKRConvertDate([R4UD16]) AS CLOSEDT
FROM (qryTATTracking LEFT JOIN dbo_WCDTALIBZ_WCRMIP00 ON qryTATTracking.RMA
= dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]) LEFT JOIN qryKRROatBER ON
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] = qryKRROatBER.[RORMA#]
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
AND ((dbo_WCDTALIBZ_WCRMIP00.R4STCD)="CLOSE" Or
(dbo_WCDTALIBZ_WCRMIP00.R4STCD)='C') AND ((qryKRROatBER.[RORMA#]) Is Null))
GROUP BY qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")),
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM,
dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, WCKRConvertDate([R4UD16])
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") AND
((WCKRConvertDate([R4UD16])) AND Between [Forms]![frmMainMenu]![txtKRBegDt]
AND [Forms]![frmMainMenu]![txtKREndDt]))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;

Now it's telling me that I missing an operating and is highlighting the
Between in the following:
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") AND
((WCKRConvertDate([R4UD16])) AND Between [Forms]![frmMainMenu]![txtKRBegDt]
AND [Forms]![frmMainMenu]![txtKREndDt]))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;
--
B. J. Ayers
Goodrich Corp.


:

Based on your conversion it appears the you are missing a digit --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And

To be --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And


:

This query was written by someone who is no longer working here.

The datatype for R4UD16 is Number

and the vb ConvertDateToWCKRDate module is:

Function ConvertWCDate(strDate As String) As String

ConvertWCDate = Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" &
Left(strDate, 4)

End Function
--
B. J. Ayers
Goodrich Corp.


:

If R4UD16 is a DateTime datatype then you need the following --

WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>#01/01/1001# And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))

Why do you have two parameters for R4UD16? If you always want the date to
be greater than 1 JAN 1001 then why use a BETWEEN statement?

Have you developed your own function ConvertDateToWCKRDate ?

:

Need help with a query written by someone else that is failing. Here is where
I think the query is failing. If you need all of the code for the query, let
me know.

From the query window:
Field = R4UD16
Table = dbo_WCDTALIBZ_WCRMIP00
Criteria = >1001010 And Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt])

The code in the SQL view is:
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
Click to expand...
Click to expand...
Click to expand...
 
Hi Karl,

This is what I have accomplished so far:

Originally the criteria for the R4UD16 field which caused the report to not
even open was: Where: >10010101 AND BETWEEN "ConvertDateToWCKRDate"
([Forms]![frmMainMenu]![txtKRBegDt]) And ([Forms]![frmMainMenu]![txtKREndDt]).

The R4UD16 field is a date field in Mapics (mainframe database) and the
format is yyyymmdd.

Since there is another query/report that does work and the only real
difference is that is doesn't have the convert date module or the R4UD16
field; I removed the Convert Date criteria from the R4UD16 field (see field
critieria below), and I removed the convert date fields from the query (there
might be some problem(s) with how the code was written). The report now
generates. I have submitted a snapshot of this report to the user for their
feedback on whether or not the info in the report is correct. Now I just
have 2 other queries to fix. I will keep you posted.

Table: dbo_WCDTALIBZ_WCRMIP00
Field: R4UD16
Where: >10010101


Convert date fields that were removed from query:

First Convert Date Field: "ConvertDateToWCKRDate"
Where
Criteria:Between ([Forms]![frmMainMenu]![txtKRBegDt]) And
([Forms]![frmMainMenu]![txtKREndDt])

Second Convert Date Field: (WCKRConvertDate([R4UD16]))>10010101
Expression
Criteria: Between [Forms]![frmMainMenu]![txtKRBegDt] And
[Forms]![frmMainMenu]![txtKREndDt]

The convert date vb module is (I am not a VB programmer, this was written by
a previous analyst):

Option Compare Database
Option Explicit


Function WCKRConvertDate(StrDate As String) As Date
On Error GoTo WCKR_ERR

WCKRConvertDate = CDate(Val(Mid(StrDate, 5, 2)) & "/" &
Val(Mid(StrDate, 7, 2)) & "/" & Val(Mid(StrDate, 3, 2)))
Exit Function
WCKR_ERR:
If Err.Number = 13 Then
Resume Next
End If
Stop

End Function

--
B. J. Ayers
Goodrich Corp.


KARL DEWEY said:
I would recommend riping out a part at a time of the conditions/parameters
until it gives you something. When it does then try the orignal without the
last thing you deleted.

Build a little - test a little.

bjayers said:
First of all, I want to say how much I appreciate your help and feedback. At
least the query doesn't bomb completely like it did before, however it still
in not giving me any data in the datasheet view. Here is how sql view of the
query looks now.

SELECT qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")) AS TYPE,
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] AS [RHRMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM AS
RHCNAM, dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, Sum(qryTATTracking.[NO OF DAYS]) AS [STATUS DAYS],
WCKRConvertDate([R4UD16]) AS CLOSEDT
FROM (qryTATTracking LEFT JOIN dbo_WCDTALIBZ_WCRMIP00 ON
qryTATTracking.RMA=dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]) LEFT JOIN qryKRROatBER ON
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]=qryKRROatBER.[RORMA#]
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) And "ConvertDateToWCKRDate" Between
(Forms!frmMainMenu!txtKRBegDt) And (Forms!frmMainMenu!txtKREndDt)) And
((dbo_WCDTALIBZ_WCRMIP00.R4STCD)="CLOSE" Or
(dbo_WCDTALIBZ_WCRMIP00.R4STCD)='C') And ((qryKRROatBER.[RORMA#]) Is Null))
GROUP BY qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")),
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM,
dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, WCKRConvertDate([R4UD16])
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") And
((WCKRConvertDate([R4UD16]))>10010101 Between Forms!frmMainMenu!txtKRBegDt
And Forms!frmMainMenu!txtKREndDt))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;

--
B. J. Ayers
Goodrich Corp.


KARL DEWEY said:
AND ((WCKRConvertDate([R4UD16])) AND Between
This element is not doing anything. You omitted your earlier >10010101

:

Here is how I changed the SQL view of the query
SELECT qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")) AS TYPE,
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] AS [RHRMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM AS
RHCNAM, dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, Sum(qryTATTracking.[NO OF DAYS]) AS [STATUS DAYS],
WCKRConvertDate([R4UD16]) AS CLOSEDT
FROM (qryTATTracking LEFT JOIN dbo_WCDTALIBZ_WCRMIP00 ON qryTATTracking.RMA
= dbo_WCDTALIBZ_WCRMIP00.[R4RMA#]) LEFT JOIN qryKRROatBER ON
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#] = qryKRROatBER.[RORMA#]
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
AND ((dbo_WCDTALIBZ_WCRMIP00.R4STCD)="CLOSE" Or
(dbo_WCDTALIBZ_WCRMIP00.R4STCD)='C') AND ((qryKRROatBER.[RORMA#]) Is Null))
GROUP BY qryTATTracking.FACID, IIf([ITCLS] Like "1*" Or [ITCLS] Like
"9*","GR",IIf([ITCLS] Like "2*","AC","OTHER")),
dbo_WCDTALIBZ_WCRMIP00.[R4RMA#], dbo_WCDTALIBZ_WCRMIP00.R4CNAM,
dbo_WCDTALIBZ_WCRMIP00.R4ITNO, dbo_WCDTALIBZ_WCRMIP00.R4ITDS,
qryTATTracking.WHS, qryTATTracking.[PART NO], qryTATTracking.[SERIAL NO],
qryTATTracking.RECEIVED, WCKRConvertDate([R4UD16])
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") AND
((WCKRConvertDate([R4UD16])) AND Between [Forms]![frmMainMenu]![txtKRBegDt]
AND [Forms]![frmMainMenu]![txtKREndDt]))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;

Now it's telling me that I missing an operating and is highlighting the
Between in the following:
HAVING (((dbo_WCDTALIBZ_WCRMIP00.R4CNAM) Not Like "*Goodrich*") AND
((WCKRConvertDate([R4UD16])) AND Between [Forms]![frmMainMenu]![txtKRBegDt]
AND [Forms]![frmMainMenu]![txtKREndDt]))
ORDER BY qryTATTracking.FACID, dbo_WCDTALIBZ_WCRMIP00.R4CNAM;
--
B. J. Ayers
Goodrich Corp.


:

Based on your conversion it appears the you are missing a digit --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And

To be --
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>10010101 And


:

This query was written by someone who is no longer working here.

The datatype for R4UD16 is Number

and the vb ConvertDateToWCKRDate module is:

Function ConvertWCDate(strDate As String) As String

ConvertWCDate = Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" &
Left(strDate, 4)

End Function
--
B. J. Ayers
Goodrich Corp.


:

If R4UD16 is a DateTime datatype then you need the following --

WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>#01/01/1001# And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))

Why do you have two parameters for R4UD16? If you always want the date to
be greater than 1 JAN 1001 then why use a BETWEEN statement?

Have you developed your own function ConvertDateToWCKRDate ?

:

Need help with a query written by someone else that is failing. Here is where
I think the query is failing. If you need all of the code for the query, let
me know.

From the query window:
Field = R4UD16
Table = dbo_WCDTALIBZ_WCRMIP00
Criteria = >1001010 And Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt])

The code in the SQL view is:
WHERE (((dbo_WCDTALIBZ_WCRMIP00.R4UD16)>1001010 And
(dbo_WCDTALIBZ_WCRMIP00.R4UD16) Between
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKRBegDt]) And
ConvertDateToWCKRDate([Forms]![frmMainMenu]![txtKREndDt]))
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

CrossTab Query problem 3
Cascading combo boxes 11
How do I filter a report to show only specific date 3
Access 2007 Iintermittent print problem. 1
Cross Tab Queries Problems 2
Query used to produce report based on combobox selection 1
Help with this function... 2
Run-time error 3061 6

Back
Top