Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Runtime error 8577 "Data type mismatch in criteria expression"

Posted on 2003-12-10
15
Medium Priority
?
5,512 Views
Last Modified: 2008-02-01
This produces a runtime error (8577 - Data type mismatch in criteria expression) when I try to run a data report from vb or a query is run from Access 2002 (if a number is deleted from a field while in vb and left blank) ......works fine in Access 2002 (using same query) if database is opened and change is made (field left blank) directly to database  AND.......... runs report in vb just fine if I make change directly to database first..........


any help is greatly appreciated............

SELECT  (
    select avg( ReqToDisp )
   from PCLifeNet
    where
        month( date ) = month(  dateadd( "m", 0, now()))
        and year( date ) = year( dateadd( "m", 0, now()))
    ) AS Month0, (
    select avg( ReqToDisp )
    from PCLifeNet
   where
        month( date ) = month(  dateadd( "m", -1, now()))
        and year( date ) = year( dateadd( "m", -1, now()))
    ) AS Month1, (
    select avg( ReqToDisp )
    from PCLifeNet
where
         year( date ) = year( dateadd( "yyyy", 0, now()))
)AS Year0
From PCAvg;

0
Comment
Question by:emspilot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 4

Expert Comment

by:dasari
ID: 9917109
Try this.....

SELECT  (
    select avg( CInt(ReqToDisp))
   from PCLifeNet
    where
        month( date ) = month(  dateadd( "m", 0, now()))
        and year( date ) = year( dateadd( "m", 0, now()))
        and date IS NOT NULL
    ) AS Month0, (
    select avg( CInt(ReqToDisp) )
    from PCLifeNet
   where
        month( date ) = month(  dateadd( "m", -1, now()))
        and year( date ) = year( dateadd( "m", -1, now()))
        and date IS NOT NULL
    ) AS Month1, (
    select avg( CInt(ReqToDisp) )
    from PCLifeNet
where
         year( date ) = year( dateadd( "yyyy", 0, now()))
        and date IS NOT NULL
)AS Year0
From PCAvg;
0
 

Author Comment

by:emspilot
ID: 9917565
Hey dasari...........

good to here from you again.....

the data type for PCLifenet.ReqToDisp is adVarWCar........so that would be CVar right????
0
 
LVL 4

Expert Comment

by:dasari
ID: 9917656
Nope, we can't find average for a varchar field, so we need to convert it to number before doing that.....did u tried executing the above query.....

also, can u post the sample data for that field....I mean PCLifenet.ReqToDisp
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:emspilot
ID: 9917727
I tried the above query and got the same error............I also tried CDbl.....and you're right the varchar didn't work either.

The data in the field is just time(s) recorded in minutes. ie: 3,2,1

Ya know......this is really strange....I have another data report that I use in the program to list times (no math involved) and it excepts the blank fields without a problem....
0
 
LVL 4

Expert Comment

by:dasari
ID: 9917762
Yeah! tha's strange.....

OKay, try this now.....

SELECT  (
    select avg( CInt(Trim(ReqToDisp)))
   from PCLifeNet
    where
        month( date ) = month(  dateadd( "m", 0, now()))
        and year( date ) = year( dateadd( "m", 0, now()))
        and date IS NOT NULL and ReqToDisp IS NOT NULL
    ) AS Month0, (
    select avg( CInt(Trim(ReqToDisp)) )
    from PCLifeNet
   where
        month( date ) = month(  dateadd( "m", -1, now()))
        and year( date ) = year( dateadd( "m", -1, now()))
        and date IS NOT NULL and ReqToDisp IS NOT NULL
    ) AS Month1, (
    select avg( CInt(Trim(ReqToDisp)) )
    from PCLifeNet
where
         year( date ) = year( dateadd( "yyyy", 0, now()))
        and date IS NOT NULL and ReqToDisp IS NOT NULL
)AS Year0
From PCAvg;
0
 

Author Comment

by:emspilot
ID: 9917864
no......that didn't work either.....unforetuantely
0
 
LVL 4

Expert Comment

by:dasari
ID: 9917955
No clues again buddy! However there might be chance that date field field is invalid..so how about this..

SELECT  (
    select avg( CInt(Trim(ReqToDisp)))
   from PCLifeNet
    where
        month( date ) = month(  dateadd( "m", 0, now()))
        and year( date ) = year( dateadd( "m", 0, now()))
        and date IS NOT NULL and ReqToDisp IS NOT NULL
        and IsDate([PCLifeNet]![date])=True
    ) AS Month0, (
    select avg( CInt(Trim(ReqToDisp)) )
    from PCLifeNet
   where
        month( date ) = month(  dateadd( "m", -1, now()))
        and year( date ) = year( dateadd( "m", -1, now()))
        and date IS NOT NULL and ReqToDisp IS NOT NULL
        and IsDate([PCLifeNet]![date])=True
    ) AS Month1, (
    select avg( CInt(Trim(ReqToDisp)) )
    from PCLifeNet
where
         year( date ) = year( dateadd( "yyyy", 0, now()))
        and date IS NOT NULL and ReqToDisp IS NOT NULL
        and IsDate([PCLifeNet]![date])=True
)AS Year0
From PCAvg;

Also, does our queries give valid output when run in access.........
0
 
LVL 4

Expert Comment

by:dasari
ID: 9917986
I just changed the query to validate ReqToDisp for numeric data, so try this instead.......

SELECT  (
    select avg( CInt(Trim(ReqToDisp)))
   from PCLifeNet
    where
        month( date ) = month(  dateadd( "m", 0, now()))
        and year( date ) = year( dateadd( "m", 0, now()))
        and date IS NOT NULL and ReqToDisp IS NOT NULL
        and IsDate([PCLifeNet]![date])=True
        and ISNUMBER(ReqToDisp) = True
    ) AS Month0, (
    select avg( CInt(Trim(ReqToDisp)) )
    from PCLifeNet
   where
        month( date ) = month(  dateadd( "m", -1, now()))
        and year( date ) = year( dateadd( "m", -1, now()))
        and date IS NOT NULL and ReqToDisp IS NOT NULL
        and IsDate([PCLifeNet]![date])=True
        and ISNUMBER(ReqToDisp) = True
    ) AS Month1, (
    select avg( CInt(Trim(ReqToDisp)) )
    from PCLifeNet
where
         year( date ) = year( dateadd( "yyyy", 0, now()))
        and date IS NOT NULL and ReqToDisp IS NOT NULL
        and IsDate([PCLifeNet]![date])=True
        and ISNUMBER(ReqToDisp) = True
)AS Year0
From PCAvg;
0
 

Author Comment

by:emspilot
ID: 9925089
Okay.........The second to the last query you sent works in vb if there is a number in the field.......If I delete a number, using vb, that already exists and try to get an average using a blank field I get the runtime error....works in access....but then again so did the original query.

The last query gives a "Undefined function "ISNUMERIC' in expression in both vb and access regardless if the field has a number or a blank.
0
 

Author Comment

by:emspilot
ID: 9925146
Let's rephrase that........works great in access...... unless number is deleted in vb...vb closed, access opened and query run in access........this process produces data type run time error in access. (This has always been true.............since the beginning.
0
 

Author Comment

by:emspilot
ID: 9925351
and.....if I delete a number in access, close access....open vb and run the report.........the report accurately accepts the blank field....no error.
0
 
LVL 4

Accepted Solution

by:
dasari earned 2000 total points
ID: 9925850
oops! I spelled it ISNUMBER instead of IsNumeric, so this the updated query with the appropriate function name, so lets try this dude!

SELECT  (
    select avg( CInt(Trim(ReqToDisp)))
   from PCLifeNet
    where
        month( date ) = month(  dateadd( "m", 0, now()))
        and year( date ) = year( dateadd( "m", 0, now()))
        and date IS NOT NULL and ReqToDisp IS NOT NULL
        and IsDate([PCLifeNet]![date])=True
        and IsNumeric(ReqToDisp) = True
    ) AS Month0, (
    select avg( CInt(Trim(ReqToDisp)) )
    from PCLifeNet
   where
        month( date ) = month(  dateadd( "m", -1, now()))
        and year( date ) = year( dateadd( "m", -1, now()))
        and date IS NOT NULL and ReqToDisp IS NOT NULL
        and IsDate([PCLifeNet]![date])=True
        and IsNumeric(ReqToDisp) = True
    ) AS Month1, (
    select avg( CInt(Trim(ReqToDisp)) )
    from PCLifeNet
where
         year( date ) = year( dateadd( "yyyy", 0, now()))
        and date IS NOT NULL and ReqToDisp IS NOT NULL
        and IsDate([PCLifeNet]![date])=True
        and IsNumeric(ReqToDisp) = True
)AS Year0
From PCAvg;
0
 
LVL 6

Expert Comment

by:rvooijs
ID: 9928078
Hi,

I noticed the error reports something wrong in the criteria expression and you are calling functions in your criteria. (month(), year(), dateadd() and now())

Now I know that within Access it is possible to call functions from within a query, but this doesn't always work when you call Jet from VB.

Could you try opening the query like:

SELECT  (
    select avg( ReqToDisp )
   from PCLifeNet
    where
        month( date ) = 12
        and year( date ) = 2003
    ) AS Month0, (
    select avg( ReqToDisp )
    from PCLifeNet
   where
        month( date ) = 11
        and year( date ) = 2003
    ) AS Month1, (
    select avg( ReqToDisp )
    from PCLifeNet
where
         year( date ) = 2003
)AS Year0
From PCAvg;

If this does work then your solutions is to build you query string as in:
    SQL = "SELECT  (select avg( ReqToDisp )  from PCLifeNet  where month( date ) = " & month(now)
    etc.

Robert
0
 

Author Comment

by:emspilot
ID: 9932543
Hey dasari.....................IT WORKED!!!!!!!!!!!!!!!!!!!!

You really did a fantastic job!!!!!!!!!!!!!!!!!

Thank you so much for sticking with me on this!!!!!!!!!!!!!!!

I couldn't have done this without your expertise.

Again Thank You!!!!!

emspilot
0
 
LVL 4

Expert Comment

by:dasari
ID: 9934823
Great! good to hear that dude!

Good Luck!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question