data type mismatch in query

[juanakhusted via AccessMonster.com]

My original query reads as follow:

SELECT [qryFG/RMList].[Formulation#], RACK.Code, [qryFG/RMList].PRODUCT,
[qryFG/RMList].ContainerDesc, [qryFG/RMList].[PkgID#], RACK.[RACK#], RACK.
[Batch#], RACK.CURRENTINVENTORY, RACK.RESERVEINVENTORY, RACK.[Tote#], CDate
(Left([Batch#],2) & "/" & Mid([Batch#],3,2) & "/" & Mid([Batch#],5,2)) AS
Strip, IIf([Strip]<Date()-60,CInt(1),CInt(0)) AS AgedDate
FROM RACK LEFT JOIN [qryFG/RMList] ON RACK.Code = [qryFG/RMList].Code
WHERE (((RACK.[Batch#])<>"0") AND ((RACK.CURRENTINVENTORY)>0))
ORDER BY RACK.Code;

Basically, I have converted the Batch # field to return a date. I eventually
want to return record values that are 60+ days old. This works just fine
until I try to filter only those records where the AgedDate expression
returns the value of 1. This is where I get the date type mismatch error
statement. Any auggestions?

 

[KARL DEWEY]

You have a calculted field named Strip and then are using that alias in
another calculation. Access does not like this in most cases as the alias is
being call before it is created. So, use the same calulation instead of the
alias.

Is [Batch#] a text field? You are checking to see if it is more or less
than "0" which is a string.

 

[Ken Sheridan]

I'd suggest three amendments:

1. Compute the Strip column more simply with:

CDate(Format([Batch#],"00/00/00")) AS Strip

2. Return a Boolean value rather than an integer as the AgedDate column:

DateAdd("d",60,CDate(Format([Batch#],"00/00/00"))) < Date() AS AgedDate

3. Use this same expression as the criterion:

WHERE DateAdd("d",60,CDate(Format([Batch#],"00/00/00"))) < Date()

However you do it this does assume that all the Batch# values will convert
to a valid date and none are Null.

Ken Sheridan
Stafford, England

 

[juanakhusted via AccessMonster.com]

[Batch#] is a text field. I have taken the first 6 characters and converted
them to denote a date. I am trying now to only show the records with
converted dates that are older than 60 days from the current date.

You have a calculted field named Strip and then are using that alias in
another calculation. Access does not like this in most cases as the alias is
being call before it is created. So, use the same calulation instead of the
alias.

Is [Batch#] a text field? You are checking to see if it is more or less
than "0" which is a string.

My original query reads as follow:

[quoted text clipped - 12 lines]

returns the value of 1. This is where I get the date type mismatch error
statement. Any auggestions?

 

[juanakhusted via AccessMonster.com]

Unfortunately the Batch# field has either 6 characters or 8 characters.
Those records with 8 characters return "ERROR" when I use "CDate(Format(
[Batch#],"00/00/00"))"

I'd suggest three amendments:

1. Compute the Strip column more simply with:

CDate(Format([Batch#],"00/00/00")) AS Strip

2. Return a Boolean value rather than an integer as the AgedDate column:

DateAdd("d",60,CDate(Format([Batch#],"00/00/00"))) < Date() AS AgedDate

3. Use this same expression as the criterion:

WHERE DateAdd("d",60,CDate(Format([Batch#],"00/00/00"))) < Date()

However you do it this does assume that all the Batch# values will convert
to a valid date and none are Null.

Ken Sheridan
Stafford, England

My original query reads as follow:

[quoted text clipped - 12 lines]

returns the value of 1. This is where I get the date type mismatch error
statement. Any auggestions?

 

[juanakhusted via AccessMonster.com]

I revised the query as follows:
AgedDate: IIf(CDate(Left([Batch#],2) & "/" & Mid([Batch#],3,2) & "/" & Mid(
[Batch#],5,2))<Date()-60,1,0) and the query runs fine. However, when using
the query to only return the records with the AgedDate expression showing '1'
I still get the data type mismatch error.

You have a calculted field named Strip and then are using that alias in
another calculation. Access does not like this in most cases as the alias is
being call before it is created. So, use the same calulation instead of the
alias.

Is [Batch#] a text field? You are checking to see if it is more or less
than "0" which is a string.

My original query reads as follow:

[quoted text clipped - 12 lines]

returns the value of 1. This is where I get the date type mismatch error
statement. Any auggestions?

 

[juanakhusted via AccessMonster.com]

I revised your amendments with
CDate(Format(Left([Batch#],6),"00\/00\/00") AS Strip
AND
DateAdd("d",60,CDate(Format(Left([Batch#],6),"00\/00\/00")))<Date() AS Aged
Date

This works until I enter in the criterion WHERE DateAdd("d",60,CDate(Format
(Left([Batch#],6),"00\/00\/00")))<Date() - then I still get the "Datatype
mismatch in criteria expression.

I'd suggest three amendments:

1. Compute the Strip column more simply with:

CDate(Format([Batch#],"00/00/00")) AS Strip

2. Return a Boolean value rather than an integer as the AgedDate column:

DateAdd("d",60,CDate(Format([Batch#],"00/00/00"))) < Date() AS AgedDate

3. Use this same expression as the criterion:

WHERE DateAdd("d",60,CDate(Format([Batch#],"00/00/00"))) < Date()

However you do it this does assume that all the Batch# values will convert
to a valid date and none are Null.

Ken Sheridan
Stafford, England

My original query reads as follow:

[quoted text clipped - 12 lines]

returns the value of 1. This is where I get the date type mismatch error
statement. Any auggestions?

 

[John Spencer]

SELECT [qryFG/RMList].[Formulation#]
, RACK.Code, [qryFG/RMList].PRODUCT,
[qryFG/RMList].ContainerDesc
, [qryFG/RMList].[PkgID#]
, RACK.[RACK#]
, RACK.[Batch#]
, RACK.CURRENTINVENTORY
, RACK.RESERVEINVENTORY
, RACK.[Tote#]
,
IIF(IsDate(Format(Left([Batch#],6),"@@/@@/@@")),CDate(Format(Left([Batch#],6),"@@/@@/@@")),Null)
AS Strip
, IIF(IIF(IsDate(Format(Left([Batch#],6),"@@/@@/@@")),
CDate(Format(Left([Batch#],6),"@@/@@/@@")),Null)<Date()-60,1,0) as AgedDate
FROM RACK LEFT JOIN [qryFG/RMList] ON RACK.Code = [qryFG/RMList].Code
WHERE (((RACK.[Batch#])<>"0") AND ((RACK.CURRENTINVENTORY)>0))
AND
IIF(IsDate(Format(Left([Batch#],6),"@@/@@/@@")),CDate(Format(Left([Batch#],6),"@@/@@/@@")),Null)
< Date()-60
ORDER BY RACK.Code;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[juanakhusted via AccessMonster.com]

Works like a charm! I understand now. Thanks!

SELECT [qryFG/RMList].[Formulation#]
, RACK.Code, [qryFG/RMList].PRODUCT,
[qryFG/RMList].ContainerDesc
, [qryFG/RMList].[PkgID#]
, RACK.[RACK#]
, RACK.[Batch#]
, RACK.CURRENTINVENTORY
, RACK.RESERVEINVENTORY
, RACK.[Tote#]
,
IIF(IsDate(Format(Left([Batch#],6),"@@/@@/@@")),CDate(Format(Left([Batch#],6),"@@/@@/@@")),Null)
AS Strip
, IIF(IIF(IsDate(Format(Left([Batch#],6),"@@/@@/@@")),
CDate(Format(Left([Batch#],6),"@@/@@/@@")),Null)<Date()-60,1,0) as AgedDate
FROM RACK LEFT JOIN [qryFG/RMList] ON RACK.Code = [qryFG/RMList].Code
WHERE (((RACK.[Batch#])<>"0") AND ((RACK.CURRENTINVENTORY)>0))
AND
IIF(IsDate(Format(Left([Batch#],6),"@@/@@/@@")),CDate(Format(Left([Batch#],6),"@@/@@/@@")),Null)
< Date()-60
ORDER BY RACK.Code;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

My original query reads as follow:

[quoted text clipped - 12 lines]

returns the value of 1. This is where I get the date type mismatch error
statement. Any auggestions?

 

[KARL DEWEY]

Re-read my post about using the alias vs reusing the calculation.
--
KARL DEWEY
Build a little - Test a little

I revised the query as follows:
AgedDate: IIf(CDate(Left([Batch#],2) & "/" & Mid([Batch#],3,2) & "/" & Mid(
[Batch#],5,2))<Date()-60,1,0) and the query runs fine. However, when using
the query to only return the records with the AgedDate expression showing '1'
I still get the data type mismatch error.

You have a calculted field named Strip and then are using that alias in
another calculation. Access does not like this in most cases as the alias is
being call before it is created. So, use the same calulation instead of the
alias.

Is [Batch#] a text field? You are checking to see if it is more or less
than "0" which is a string.

My original query reads as follow:

[quoted text clipped - 12 lines]

returns the value of 1. This is where I get the date type mismatch error
statement. Any auggestions?

 

[KARL DEWEY]

However, when using the query to only return the records with the AgedDate
expression showing '1' I still get the data type mismatch error.
You did not post your query with criteria on AgedDate so I missed something.

If you are enclosing a one in quotes like this "1" then that makes it text.
...CInt(1),CInt(0)) AS AgedDate returns a numerical 1 that will not match
the text "1".

Drop the quotes.

--
KARL DEWEY
Build a little - Test a little

I revised the query as follows:
AgedDate: IIf(CDate(Left([Batch#],2) & "/" & Mid([Batch#],3,2) & "/" & Mid(
[Batch#],5,2))<Date()-60,1,0) and the query runs fine. However, when using
the query to only return the records with the AgedDate expression showing '1'
I still get the data type mismatch error.

You have a calculted field named Strip and then are using that alias in
another calculation. Access does not like this in most cases as the alias is
being call before it is created. So, use the same calulation instead of the
alias.

Is [Batch#] a text field? You are checking to see if it is more or less
than "0" which is a string.

My original query reads as follow:

[quoted text clipped - 12 lines]

returns the value of 1. This is where I get the date type mismatch error
statement. Any auggestions?