Runtime error 8577 "Data type mismatch in criteria expression"

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;

Hey dasari...........

good to here from you again.....

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

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

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

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;

no......that didn't work either.....unforetuantely

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

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;

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.

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.

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.

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

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

Great! good to hear that dude!

Good Luck!