Data type mismatch in criteria expression

G

Guest

I have a query where I calculate time elapsed and am trying to limit the
results to only those outstanding 120 mins. Whenever I add the criteria to
bring back the results over 120 mins, I either get the Data type mismatch in
criteria expression message, or the "The expression is typed incorrectly, or
it is too complex to be evaluated" message. Can someone help me figure out
what is going on here? I've tried converting to integers, getting the value,
the whole nine yards. I'm stuck.
 
D

Douglas J Steele

Show us your SQL, and tell us the data type of the field(s) in question.
 
G

Guest

The SQL is below. The data types of the fields are Date/Time, which I am
trying to generate an integer from.

SELECT
Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesReceived])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDiff("y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date()))),"00000")
AS [Time Outstanding], Hour([DMTimeReceived]) AS HourReceived,
Minute([DMTimeReceived]) AS MinutesReceived, qryDMNotDone.ID,
qryDMNotDone.Priority, qryDMNotDone.DeliveryMedium,
qryDMNotDone.AWNumber_EmailSubject, qryDMNotDone.DMDateReceived,
qryDMNotDone.DMTimeReceived, qryDMNotDone.DMDateLogged,
qryDMNotDone.DMTimeLogged, qryDMNotDone.ETCRequest,
qryDMNotDone.RequestTypeAmend, qryDMNotDone.RequestTypeNewProduct,
qryDMNotDone.RequestTypeNewGL, qryDMNotDone.Requestor,
qryDMNotDone.DMReceiver, qryDMNotDone.DMReferredTo,
qryDMNotDone.NumberOfUpdates, qryDMNotDone.BusinessGroup,
qryDMNotDone.Product, qryDMNotDone.AccountType, qryDMNotDone.AWProducts,
qryDMNotDone.TopTierClientName, qryDMNotDone.AmendmentCode,
qryDMNotDone.ProgeonProduct, qryDMNotDone.MFCrossReference,
qryDMNotDone.AccountNumber, qryDMNotDone.IPorMarket,
qryDMNotDone.DMDateFulfilled, qryDMNotDone.DMTimeFulfilled,
qryDMNotDone.DMReferredBack, qryDMNotDone.DMMinutesSpentReferredBack,
qryDMNotDone.DMProblemDescription, qryDMNotDone.DateReferredForBenOwner,
qryDMNotDone.TimeReferredForBenOwner,
qryDMNotDone.DateReceivedBackFromTaxForBenOwner,
qryDMNotDone.TimeReceivedBackFromTaxForBenOwner, qryDMNotDone.DMComments,
qryDMNotDone.NewGCDeactivationNeeded,
qryDMNotDone.NewSOX054009InternalValidation,
qryDMNotDone.NewSOX054009InternalValidator,
qryDMNotDone.NewSOX054009NonDVPAuthorizer, qryDMNotDone.DMRecordLocked,
qryDMNotDone.FunnelManagerName, qryDMNotDone.FunnelManagerID,
qryDMNotDone.DMActionTaken, qryDMNotDone.RequestStatus,
qryDMNotDone.DMFulfillerAction, qryDMNotDone.CCDSTimeCompleted,
qryDMNotDone.DMDateCompleted, qryDMNotDone.DMTimeCompleted,
qryDMNotDone.ProjectCode, qryDMNotDone.QCRequestedBy, qryDMNotDone.QCProblem,
qryDMNotDone.DateQCd
FROM qryDMNotDone
WHERE
(((Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesReceived])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDiff("y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date()))),"00000"))>120)
AND ((qryDMNotDone.DMReceiver) Is Not Null) AND
((qryDMNotDone.DMDateFulfilled) Is Null) AND ((qryDMNotDone.DMTimeFulfilled)
Is Null) AND ((qryDMNotDone.DateReferredForBenOwner) Is Null) AND
((qryDMNotDone.TimeReferredForBenOwner) Is Null));
 
D

Douglas J Steele

Format converts values to strings.

Why are you using that horrendous calculation? Why not simply use the
DateDiff function?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Harry F. said:
The SQL is below. The data types of the fields are Date/Time, which I am
trying to generate an integer from.

SELECT
Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesRecei
ved])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDiff(
"y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date())
)),"00000")
AS [Time Outstanding], Hour([DMTimeReceived]) AS HourReceived,
Minute([DMTimeReceived]) AS MinutesReceived, qryDMNotDone.ID,
qryDMNotDone.Priority, qryDMNotDone.DeliveryMedium,
qryDMNotDone.AWNumber_EmailSubject, qryDMNotDone.DMDateReceived,
qryDMNotDone.DMTimeReceived, qryDMNotDone.DMDateLogged,
qryDMNotDone.DMTimeLogged, qryDMNotDone.ETCRequest,
qryDMNotDone.RequestTypeAmend, qryDMNotDone.RequestTypeNewProduct,
qryDMNotDone.RequestTypeNewGL, qryDMNotDone.Requestor,
qryDMNotDone.DMReceiver, qryDMNotDone.DMReferredTo,
qryDMNotDone.NumberOfUpdates, qryDMNotDone.BusinessGroup,
qryDMNotDone.Product, qryDMNotDone.AccountType, qryDMNotDone.AWProducts,
qryDMNotDone.TopTierClientName, qryDMNotDone.AmendmentCode,
qryDMNotDone.ProgeonProduct, qryDMNotDone.MFCrossReference,
qryDMNotDone.AccountNumber, qryDMNotDone.IPorMarket,
qryDMNotDone.DMDateFulfilled, qryDMNotDone.DMTimeFulfilled,
qryDMNotDone.DMReferredBack, qryDMNotDone.DMMinutesSpentReferredBack,
qryDMNotDone.DMProblemDescription, qryDMNotDone.DateReferredForBenOwner,
qryDMNotDone.TimeReferredForBenOwner,
qryDMNotDone.DateReceivedBackFromTaxForBenOwner,
qryDMNotDone.TimeReceivedBackFromTaxForBenOwner, qryDMNotDone.DMComments,
qryDMNotDone.NewGCDeactivationNeeded,
qryDMNotDone.NewSOX054009InternalValidation,
qryDMNotDone.NewSOX054009InternalValidator,
qryDMNotDone.NewSOX054009NonDVPAuthorizer, qryDMNotDone.DMRecordLocked,
qryDMNotDone.FunnelManagerName, qryDMNotDone.FunnelManagerID,
qryDMNotDone.DMActionTaken, qryDMNotDone.RequestStatus,
qryDMNotDone.DMFulfillerAction, qryDMNotDone.CCDSTimeCompleted,
qryDMNotDone.DMDateCompleted, qryDMNotDone.DMTimeCompleted,
qryDMNotDone.ProjectCode, qryDMNotDone.QCRequestedBy, qryDMNotDone.QCProblem,
qryDMNotDone.DateQCd
FROM qryDMNotDone
WHERE
(((Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesRe
ceived])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDi
ff("y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date
()))),"00000"))>120)
AND ((qryDMNotDone.DMReceiver) Is Not Null) AND
((qryDMNotDone.DMDateFulfilled) Is Null) AND ((qryDMNotDone.DMTimeFulfilled)
Is Null) AND ((qryDMNotDone.DateReferredForBenOwner) Is Null) AND
((qryDMNotDone.TimeReferredForBenOwner) Is Null));


Douglas J Steele said:
Show us your SQL, and tell us the data type of the field(s) in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


criteria
to mismatch
in incorrectly,
or figure
out
Click to expand...
Click to expand...
 
G

Guest

Perhaps I'm missing something, but how in the world would one DateDiff
expression accomplish what I'm trying to?

Douglas J Steele said:
Format converts values to strings.

Why are you using that horrendous calculation? Why not simply use the
DateDiff function?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Harry F. said:
The SQL is below. The data types of the fields are Date/Time, which I am
trying to generate an integer from.

SELECT
Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesRecei
ved])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDiff(
"y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date())
)),"00000")
AS [Time Outstanding], Hour([DMTimeReceived]) AS HourReceived,
Minute([DMTimeReceived]) AS MinutesReceived, qryDMNotDone.ID,
qryDMNotDone.Priority, qryDMNotDone.DeliveryMedium,
qryDMNotDone.AWNumber_EmailSubject, qryDMNotDone.DMDateReceived,
qryDMNotDone.DMTimeReceived, qryDMNotDone.DMDateLogged,
qryDMNotDone.DMTimeLogged, qryDMNotDone.ETCRequest,
qryDMNotDone.RequestTypeAmend, qryDMNotDone.RequestTypeNewProduct,
qryDMNotDone.RequestTypeNewGL, qryDMNotDone.Requestor,
qryDMNotDone.DMReceiver, qryDMNotDone.DMReferredTo,
qryDMNotDone.NumberOfUpdates, qryDMNotDone.BusinessGroup,
qryDMNotDone.Product, qryDMNotDone.AccountType, qryDMNotDone.AWProducts,
qryDMNotDone.TopTierClientName, qryDMNotDone.AmendmentCode,
qryDMNotDone.ProgeonProduct, qryDMNotDone.MFCrossReference,
qryDMNotDone.AccountNumber, qryDMNotDone.IPorMarket,
qryDMNotDone.DMDateFulfilled, qryDMNotDone.DMTimeFulfilled,
qryDMNotDone.DMReferredBack, qryDMNotDone.DMMinutesSpentReferredBack,
qryDMNotDone.DMProblemDescription, qryDMNotDone.DateReferredForBenOwner,
qryDMNotDone.TimeReferredForBenOwner,
qryDMNotDone.DateReceivedBackFromTaxForBenOwner,
qryDMNotDone.TimeReceivedBackFromTaxForBenOwner, qryDMNotDone.DMComments,
qryDMNotDone.NewGCDeactivationNeeded,
qryDMNotDone.NewSOX054009InternalValidation,
qryDMNotDone.NewSOX054009InternalValidator,
qryDMNotDone.NewSOX054009NonDVPAuthorizer, qryDMNotDone.DMRecordLocked,
qryDMNotDone.FunnelManagerName, qryDMNotDone.FunnelManagerID,
qryDMNotDone.DMActionTaken, qryDMNotDone.RequestStatus,
qryDMNotDone.DMFulfillerAction, qryDMNotDone.CCDSTimeCompleted,
qryDMNotDone.DMDateCompleted, qryDMNotDone.DMTimeCompleted,
qryDMNotDone.ProjectCode, qryDMNotDone.QCRequestedBy, qryDMNotDone.QCProblem,
qryDMNotDone.DateQCd
FROM qryDMNotDone
WHERE
(((Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesRe
ceived])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDi
ff("y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date
()))),"00000"))>120)
AND ((qryDMNotDone.DMReceiver) Is Not Null) AND
((qryDMNotDone.DMDateFulfilled) Is Null) AND ((qryDMNotDone.DMTimeFulfilled)
Is Null) AND ((qryDMNotDone.DateReferredForBenOwner) Is Null) AND
((qryDMNotDone.TimeReferredForBenOwner) Is Null));


Douglas J Steele said:
Show us your SQL, and tell us the data type of the field(s) in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query where I calculate time elapsed and am trying to limit the
results to only those outstanding 120 mins. Whenever I add the criteria
to
bring back the results over 120 mins, I either get the Data type mismatch
in
criteria expression message, or the "The expression is typed incorrectly,
or
it is too complex to be evaluated" message. Can someone help me figure
out
what is going on here? I've tried converting to integers, getting the
value,
the whole nine yards. I'm stuck.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

It's possible it's me that's missing something.

I understood that you want to know the number of minutes between the
[DMDatereceived] field and the current time. That would be DateDiff("n",
[DMDatereceived], Now())

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Harry F. said:
Perhaps I'm missing something, but how in the world would one DateDiff
expression accomplish what I'm trying to?

Douglas J Steele said:
Format converts values to strings.

Why are you using that horrendous calculation? Why not simply use the
DateDiff function?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Harry F. said:
The SQL is below. The data types of the fields are Date/Time, which I am
trying to generate an integer from.

SELECT
Click to expand...
Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesRecei
ved])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDiff(
"y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date())
)),"00000")
AS [Time Outstanding], Hour([DMTimeReceived]) AS HourReceived,
Minute([DMTimeReceived]) AS MinutesReceived, qryDMNotDone.ID,
qryDMNotDone.Priority, qryDMNotDone.DeliveryMedium,
qryDMNotDone.AWNumber_EmailSubject, qryDMNotDone.DMDateReceived,
qryDMNotDone.DMTimeReceived, qryDMNotDone.DMDateLogged,
qryDMNotDone.DMTimeLogged, qryDMNotDone.ETCRequest,
qryDMNotDone.RequestTypeAmend, qryDMNotDone.RequestTypeNewProduct,
qryDMNotDone.RequestTypeNewGL, qryDMNotDone.Requestor,
qryDMNotDone.DMReceiver, qryDMNotDone.DMReferredTo,
qryDMNotDone.NumberOfUpdates, qryDMNotDone.BusinessGroup,
qryDMNotDone.Product, qryDMNotDone.AccountType, qryDMNotDone.AWProducts,
qryDMNotDone.TopTierClientName, qryDMNotDone.AmendmentCode,
qryDMNotDone.ProgeonProduct, qryDMNotDone.MFCrossReference,
qryDMNotDone.AccountNumber, qryDMNotDone.IPorMarket,
qryDMNotDone.DMDateFulfilled, qryDMNotDone.DMTimeFulfilled,
qryDMNotDone.DMReferredBack, qryDMNotDone.DMMinutesSpentReferredBack,
qryDMNotDone.DMProblemDescription, qryDMNotDone.DateReferredForBenOwner,
qryDMNotDone.TimeReferredForBenOwner,
qryDMNotDone.DateReceivedBackFromTaxForBenOwner,
qryDMNotDone.TimeReceivedBackFromTaxForBenOwner, qryDMNotDone.DMComments,
qryDMNotDone.NewGCDeactivationNeeded,
qryDMNotDone.NewSOX054009InternalValidation,
qryDMNotDone.NewSOX054009InternalValidator,
qryDMNotDone.NewSOX054009NonDVPAuthorizer, qryDMNotDone.DMRecordLocked,
qryDMNotDone.FunnelManagerName, qryDMNotDone.FunnelManagerID,
qryDMNotDone.DMActionTaken, qryDMNotDone.RequestStatus,
qryDMNotDone.DMFulfillerAction, qryDMNotDone.CCDSTimeCompleted,
qryDMNotDone.DMDateCompleted, qryDMNotDone.DMTimeCompleted,
qryDMNotDone.ProjectCode, qryDMNotDone.QCRequestedBy, qryDMNotDone.QCProblem,
qryDMNotDone.DateQCd
FROM qryDMNotDone
WHERE
Click to expand...
(((Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesRe
ceived])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDi
ff("y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date
()))),"00000"))>120)
AND ((qryDMNotDone.DMReceiver) Is Not Null) AND
((qryDMNotDone.DMDateFulfilled) Is Null) AND ((qryDMNotDone.DMTimeFulfilled)
Is Null) AND ((qryDMNotDone.DateReferredForBenOwner) Is Null) AND
((qryDMNotDone.TimeReferredForBenOwner) Is Null));


:

Show us your SQL, and tell us the data type of the field(s) in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query where I calculate time elapsed and am trying to
Click to expand...
limit
the
results to only those outstanding 120 mins. Whenever I add the criteria
to
bring back the results over 120 mins, I either get the Data type mismatch
in
criteria expression message, or the "The expression is typed incorrectly,
or
it is too complex to be evaluated" message. Can someone help me figure
out
what is going on here? I've tried converting to integers, getting the
value,
the whole nine yards. I'm stuck.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug,

Thanks for the help thus far. The issue I'm having is not that piece of the
calculation, but rather how to exclude the time we are not in the office,
which is the second part of the equation
)-(810*(DateDiff("y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date())). Is there an easier way to navigate around that?

Douglas J Steele said:
It's possible it's me that's missing something.

I understood that you want to know the number of minutes between the
[DMDatereceived] field and the current time. That would be DateDiff("n",
[DMDatereceived], Now())

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Harry F. said:
Perhaps I'm missing something, but how in the world would one DateDiff
expression accomplish what I'm trying to?

Douglas J Steele said:
Format converts values to strings.

Why are you using that horrendous calculation? Why not simply use the
DateDiff function?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The SQL is below. The data types of the fields are Date/Time, which I am
trying to generate an integer from.

SELECT

Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesRecei
ved])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDiff(
"y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date())
)),"00000")
AS [Time Outstanding], Hour([DMTimeReceived]) AS HourReceived,
Minute([DMTimeReceived]) AS MinutesReceived, qryDMNotDone.ID,
qryDMNotDone.Priority, qryDMNotDone.DeliveryMedium,
qryDMNotDone.AWNumber_EmailSubject, qryDMNotDone.DMDateReceived,
qryDMNotDone.DMTimeReceived, qryDMNotDone.DMDateLogged,
qryDMNotDone.DMTimeLogged, qryDMNotDone.ETCRequest,
qryDMNotDone.RequestTypeAmend, qryDMNotDone.RequestTypeNewProduct,
qryDMNotDone.RequestTypeNewGL, qryDMNotDone.Requestor,
qryDMNotDone.DMReceiver, qryDMNotDone.DMReferredTo,
qryDMNotDone.NumberOfUpdates, qryDMNotDone.BusinessGroup,
qryDMNotDone.Product, qryDMNotDone.AccountType, qryDMNotDone.AWProducts,
qryDMNotDone.TopTierClientName, qryDMNotDone.AmendmentCode,
qryDMNotDone.ProgeonProduct, qryDMNotDone.MFCrossReference,
qryDMNotDone.AccountNumber, qryDMNotDone.IPorMarket,
qryDMNotDone.DMDateFulfilled, qryDMNotDone.DMTimeFulfilled,
qryDMNotDone.DMReferredBack, qryDMNotDone.DMMinutesSpentReferredBack,
qryDMNotDone.DMProblemDescription, qryDMNotDone.DateReferredForBenOwner,
qryDMNotDone.TimeReferredForBenOwner,
qryDMNotDone.DateReceivedBackFromTaxForBenOwner,
qryDMNotDone.TimeReceivedBackFromTaxForBenOwner, qryDMNotDone.DMComments,
qryDMNotDone.NewGCDeactivationNeeded,
qryDMNotDone.NewSOX054009InternalValidation,
qryDMNotDone.NewSOX054009InternalValidator,
qryDMNotDone.NewSOX054009NonDVPAuthorizer, qryDMNotDone.DMRecordLocked,
qryDMNotDone.FunnelManagerName, qryDMNotDone.FunnelManagerID,
qryDMNotDone.DMActionTaken, qryDMNotDone.RequestStatus,
qryDMNotDone.DMFulfillerAction, qryDMNotDone.CCDSTimeCompleted,
qryDMNotDone.DMDateCompleted, qryDMNotDone.DMTimeCompleted,
qryDMNotDone.ProjectCode, qryDMNotDone.QCRequestedBy,
qryDMNotDone.QCProblem,
qryDMNotDone.DateQCd
FROM qryDMNotDone
WHERE

(((Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesRe
ceived])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDi
ff("y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date
()))),"00000"))>120)
AND ((qryDMNotDone.DMReceiver) Is Not Null) AND
((qryDMNotDone.DMDateFulfilled) Is Null) AND
((qryDMNotDone.DMTimeFulfilled)
Is Null) AND ((qryDMNotDone.DateReferredForBenOwner) Is Null) AND
((qryDMNotDone.TimeReferredForBenOwner) Is Null));


:

Show us your SQL, and tell us the data type of the field(s) in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query where I calculate time elapsed and am trying to limit
the
results to only those outstanding 120 mins. Whenever I add the
criteria
to
bring back the results over 120 mins, I either get the Data type
mismatch
in
criteria expression message, or the "The expression is typed
incorrectly,
or
it is too complex to be evaluated" message. Can someone help me
figure
out
what is going on here? I've tried converting to integers, getting the
value,
the whole nine yards. I'm stuck.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

Assuming you're running this query from within Access (as opposed to, say,
on an ASP page or through a VB programm), my suggestion would be to write
your own function to do the computations.

Not sure exactly what your calculations equate to, but you could take a look
at http://www.mvps.org/access/datetime/date0012.htm at "The Access Web" for
one approach, or you could read my September, 2004 "Access Answers" column
in Pinnacle Publication's "Smart Access". You can download the column (and
sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Both of the references I cite above only deal with days, not time, but you
should be able to extend them to accomodate your needs.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Harry F. said:
Doug,

Thanks for the help thus far. The issue I'm having is not that piece of the
calculation, but rather how to exclude the time we are not in the office,
which is the second part of the equation
)-(810*(DateDiff("y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDa
Click to expand...
tereceived],Date())). Is there an easier way to navigate around that?
Douglas J Steele said:
It's possible it's me that's missing something.

I understood that you want to know the number of minutes between the
[DMDatereceived] field and the current time. That would be DateDiff("n",
[DMDatereceived], Now())

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Harry F. said:
Perhaps I'm missing something, but how in the world would one DateDiff
expression accomplish what I'm trying to?

:

Format converts values to strings.

Why are you using that horrendous calculation? Why not simply use the
DateDiff function?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The SQL is below. The data types of the fields are Date/Time,
Click to expand...
which
I am
trying to generate an integer from.

SELECT
Click to expand...
Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesRecei ved])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDiff( "y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date())
)),"00000")
AS [Time Outstanding], Hour([DMTimeReceived]) AS HourReceived,
Minute([DMTimeReceived]) AS MinutesReceived, qryDMNotDone.ID,
qryDMNotDone.Priority, qryDMNotDone.DeliveryMedium,
qryDMNotDone.AWNumber_EmailSubject, qryDMNotDone.DMDateReceived,
qryDMNotDone.DMTimeReceived, qryDMNotDone.DMDateLogged,
qryDMNotDone.DMTimeLogged, qryDMNotDone.ETCRequest,
qryDMNotDone.RequestTypeAmend, qryDMNotDone.RequestTypeNewProduct,
qryDMNotDone.RequestTypeNewGL, qryDMNotDone.Requestor,
qryDMNotDone.DMReceiver, qryDMNotDone.DMReferredTo,
qryDMNotDone.NumberOfUpdates, qryDMNotDone.BusinessGroup,
qryDMNotDone.Product, qryDMNotDone.AccountType, qryDMNotDone.AWProducts,
qryDMNotDone.TopTierClientName, qryDMNotDone.AmendmentCode,
qryDMNotDone.ProgeonProduct, qryDMNotDone.MFCrossReference,
qryDMNotDone.AccountNumber, qryDMNotDone.IPorMarket,
qryDMNotDone.DMDateFulfilled, qryDMNotDone.DMTimeFulfilled,
qryDMNotDone.DMReferredBack, qryDMNotDone.DMMinutesSpentReferredBack,
qryDMNotDone.DMProblemDescription, qryDMNotDone.DateReferredForBenOwner,
qryDMNotDone.TimeReferredForBenOwner,
qryDMNotDone.DateReceivedBackFromTaxForBenOwner,
qryDMNotDone.TimeReceivedBackFromTaxForBenOwner, qryDMNotDone.DMComments,
qryDMNotDone.NewGCDeactivationNeeded,
qryDMNotDone.NewSOX054009InternalValidation,
qryDMNotDone.NewSOX054009InternalValidator,
qryDMNotDone.NewSOX054009NonDVPAuthorizer,
Click to expand...
qryDMNotDone.DMRecordLocked,
qryDMNotDone.FunnelManagerName, qryDMNotDone.FunnelManagerID,
qryDMNotDone.DMActionTaken, qryDMNotDone.RequestStatus,
qryDMNotDone.DMFulfillerAction, qryDMNotDone.CCDSTimeCompleted,
qryDMNotDone.DMDateCompleted, qryDMNotDone.DMTimeCompleted,
qryDMNotDone.ProjectCode, qryDMNotDone.QCRequestedBy,
qryDMNotDone.QCProblem,
qryDMNotDone.DateQCd
FROM qryDMNotDone
WHERE
Click to expand...
(((Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesRe ceived])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDi ff("y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date
()))),"00000"))>120)
AND ((qryDMNotDone.DMReceiver) Is Not Null) AND
((qryDMNotDone.DMDateFulfilled) Is Null) AND
((qryDMNotDone.DMTimeFulfilled)
Is Null) AND ((qryDMNotDone.DateReferredForBenOwner) Is Null) AND
((qryDMNotDone.TimeReferredForBenOwner) Is Null));


:

Show us your SQL, and tell us the data type of the field(s) in question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query where I calculate time elapsed and am trying to limit
the
results to only those outstanding 120 mins. Whenever I add the
criteria
to
bring back the results over 120 mins, I either get the Data type
mismatch
in
criteria expression message, or the "The expression is typed
incorrectly,
or
it is too complex to be evaluated" message. Can someone help me
figure
out
what is going on here? I've tried converting to integers,
Click to expand...
getting
the
value,
the whole nine yards. I'm stuck.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug,

Thank you for the help. I will take a look.

Douglas J Steele said:
Assuming you're running this query from within Access (as opposed to, say,
on an ASP page or through a VB programm), my suggestion would be to write
your own function to do the computations.

Not sure exactly what your calculations equate to, but you could take a look
at http://www.mvps.org/access/datetime/date0012.htm at "The Access Web" for
one approach, or you could read my September, 2004 "Access Answers" column
in Pinnacle Publication's "Smart Access". You can download the column (and
sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Both of the references I cite above only deal with days, not time, but you
should be able to extend them to accomodate your needs.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Harry F. said:
Doug,

Thanks for the help thus far. The issue I'm having is not that piece of the
calculation, but rather how to exclude the time we are not in the office,
which is the second part of the equation
)-(810*(DateDiff("y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDa
Click to expand...
tereceived],Date())). Is there an easier way to navigate around that?
Douglas J Steele said:
It's possible it's me that's missing something.

I understood that you want to know the number of minutes between the
[DMDatereceived] field and the current time. That would be DateDiff("n",
[DMDatereceived], Now())

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Perhaps I'm missing something, but how in the world would one DateDiff
expression accomplish what I'm trying to?

:

Format converts values to strings.

Why are you using that horrendous calculation? Why not simply use the
DateDiff function?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The SQL is below. The data types of the fields are Date/Time, which
I am
trying to generate an integer from.

SELECT


Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesRecei

ved])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDiff(

"y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date())
)),"00000")
AS [Time Outstanding], Hour([DMTimeReceived]) AS HourReceived,
Minute([DMTimeReceived]) AS MinutesReceived, qryDMNotDone.ID,
qryDMNotDone.Priority, qryDMNotDone.DeliveryMedium,
qryDMNotDone.AWNumber_EmailSubject, qryDMNotDone.DMDateReceived,
qryDMNotDone.DMTimeReceived, qryDMNotDone.DMDateLogged,
qryDMNotDone.DMTimeLogged, qryDMNotDone.ETCRequest,
qryDMNotDone.RequestTypeAmend, qryDMNotDone.RequestTypeNewProduct,
qryDMNotDone.RequestTypeNewGL, qryDMNotDone.Requestor,
qryDMNotDone.DMReceiver, qryDMNotDone.DMReferredTo,
qryDMNotDone.NumberOfUpdates, qryDMNotDone.BusinessGroup,
qryDMNotDone.Product, qryDMNotDone.AccountType,
qryDMNotDone.AWProducts,
qryDMNotDone.TopTierClientName, qryDMNotDone.AmendmentCode,
qryDMNotDone.ProgeonProduct, qryDMNotDone.MFCrossReference,
qryDMNotDone.AccountNumber, qryDMNotDone.IPorMarket,
qryDMNotDone.DMDateFulfilled, qryDMNotDone.DMTimeFulfilled,
qryDMNotDone.DMReferredBack, qryDMNotDone.DMMinutesSpentReferredBack,
qryDMNotDone.DMProblemDescription,
qryDMNotDone.DateReferredForBenOwner,
qryDMNotDone.TimeReferredForBenOwner,
qryDMNotDone.DateReceivedBackFromTaxForBenOwner,
qryDMNotDone.TimeReceivedBackFromTaxForBenOwner,
qryDMNotDone.DMComments,
qryDMNotDone.NewGCDeactivationNeeded,
qryDMNotDone.NewSOX054009InternalValidation,
qryDMNotDone.NewSOX054009InternalValidator,
qryDMNotDone.NewSOX054009NonDVPAuthorizer,
qryDMNotDone.DMRecordLocked,
qryDMNotDone.FunnelManagerName, qryDMNotDone.FunnelManagerID,
qryDMNotDone.DMActionTaken, qryDMNotDone.RequestStatus,
qryDMNotDone.DMFulfillerAction, qryDMNotDone.CCDSTimeCompleted,
qryDMNotDone.DMDateCompleted, qryDMNotDone.DMTimeCompleted,
qryDMNotDone.ProjectCode, qryDMNotDone.QCRequestedBy,
qryDMNotDone.QCProblem,
qryDMNotDone.DateQCd
FROM qryDMNotDone
WHERE


(((Format(((Hour(Time())*60)+Minute(Time()))-(([HourReceived]*60)+[MinutesRe

ceived])+((DateValue(Date())-DateValue([DMDateReceived]))*1440)-(810*(DateDi

ff("y",[DMDatereceived],Date())))-(1260*(DateDiff("ww",[DMDatereceived],Date
()))),"00000"))>120)
AND ((qryDMNotDone.DMReceiver) Is Not Null) AND
((qryDMNotDone.DMDateFulfilled) Is Null) AND
((qryDMNotDone.DMTimeFulfilled)
Is Null) AND ((qryDMNotDone.DateReferredForBenOwner) Is Null) AND
((qryDMNotDone.TimeReferredForBenOwner) Is Null));


:

Show us your SQL, and tell us the data type of the field(s) in
question.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query where I calculate time elapsed and am trying to
limit
the
results to only those outstanding 120 mins. Whenever I add the
criteria
to
bring back the results over 120 mins, I either get the Data type
mismatch
in
criteria expression message, or the "The expression is typed
incorrectly,
or
it is too complex to be evaluated" message. Can someone help me
figure
out
what is going on here? I've tried converting to integers, getting
the
value,
the whole nine yards. I'm stuck.
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

Data Type Mismatch in Criteria Expression 4
'Data type mismatch in criteria expression' Error 7
Data type mismatch in criteria expression 1
Access error message: "Data type mismatch in criteria expression" 5
Data type mismatch in criteria expression 2
Data Type mismatch error 2
Access 2003-Criteria Expression 5
Replace Function and Data Type Mismatch in Results 7

Top