data type mismatch in selection

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

Guest

This query used to work, but quit about few weeks ago. I can't track any
software change, and it fails on all computers that run it.

I'm calculating the # of work hours (8.5 hrs/workday) from the time a job is
started until Now() to identify it's age in hours. The following calculation
works fine.

Elapsed: 8.5*(DateDiff("d",[Date of Entry],Date()))
+24*(TimeValue(Now())-TimeValue([Call Received Time]))
-17*DateDiff("ww",[date of entry],Now())

It takes 8.5 * the number of days from when it started to today. Then adds
the number of hours between the time it started and now. Then it subtracts
17 hrs for each weekend. This calculation works correctly.

I can run the query and it looks good. However, when I try to select only
records where the [Elapsed] is in some range, the query fails with a data
type mismatch.

For example: Between 8 and 8.5
or <8
or any other numeric range.

Two weeks ago, I posted this and was advised to explicitly type cast as a
value using CCur(........). I tried this and it worked for 1 day, then
started failing with the same "Data type mismatch in selection criteria".

I have tried casting with the Val() function, but it gives the same error.
If I remove the selection criteria, the query will run and calculates the
correct elapsed time.

Next, I tried using the Val() function in the original query and built a new
query that uses the old query as input and tried the selection criteria in
the new query, but it gives the same error.

Next, I added a field to the query as follows
Test: CDbl([Elapsed])
This shows a valid numeric result, but when I added the criteria, it failed
because it didn't recognize [Elapsed]. I assumed this is something with the
grammer/syntax checker becauses [Elapsed] is a calculated field in the same
query, so I sent the results to another query and put the search criteria in
that query, but it still fails with the data type mismatch.

I’m stumped and frustrated. My original query worked for 1-2 weeks, then
quit. The Ccur() type casting worked for 1 days then quit.

Any assistance will be greatly appreciated. Thanks for your help.

Jim
 
My guess is that a null value is slipped into the equasion. So, maybe
adding the criteria results in a null field, and then the query fails.
 
That fixed it. I found some data errors. Thanks!

[MVP] S.Clark said:
My guess is that a null value is slipped into the equasion. So, maybe
adding the criteria results in a null field, and then the query fails.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting

Capt Jim Cook said:
This query used to work, but quit about few weeks ago. I can't track any
software change, and it fails on all computers that run it.

I'm calculating the # of work hours (8.5 hrs/workday) from the time a job
is
started until Now() to identify it's age in hours. The following
calculation
works fine.

Elapsed: 8.5*(DateDiff("d",[Date of Entry],Date()))
+24*(TimeValue(Now())-TimeValue([Call Received Time]))
-17*DateDiff("ww",[date of entry],Now())

It takes 8.5 * the number of days from when it started to today. Then
adds
the number of hours between the time it started and now. Then it
subtracts
17 hrs for each weekend. This calculation works correctly.

I can run the query and it looks good. However, when I try to select only
records where the [Elapsed] is in some range, the query fails with a data
type mismatch.

For example: Between 8 and 8.5
or <8
or any other numeric range.

Two weeks ago, I posted this and was advised to explicitly type cast as a
value using CCur(........). I tried this and it worked for 1 day, then
started failing with the same "Data type mismatch in selection criteria".

I have tried casting with the Val() function, but it gives the same error.
If I remove the selection criteria, the query will run and calculates the
correct elapsed time.

Next, I tried using the Val() function in the original query and built a
new
query that uses the old query as input and tried the selection criteria in
the new query, but it gives the same error.

Next, I added a field to the query as follows
Test: CDbl([Elapsed])
This shows a valid numeric result, but when I added the criteria, it
failed
because it didn't recognize [Elapsed]. I assumed this is something with
the
grammer/syntax checker becauses [Elapsed] is a calculated field in the
same
query, so I sent the results to another query and put the search criteria
in
that query, but it still fails with the data type mismatch.

I'm stumped and frustrated. My original query worked for 1-2 weeks, then
quit. The Ccur() type casting worked for 1 days then quit.

Any assistance will be greatly appreciated. Thanks for your help.

Jim
Click to expand...
Click to expand...
 
Back
Top