Solved

Runtime error 8577 "Data type mismatch in criteria expression"

Posted on 2003-12-10
15
5,503 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
  • 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now