Runtime error 8577 "Data type mismatch in criteria expression"

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;

emspilotAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dasariCommented:
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
emspilotAuthor Commented:
Hey dasari...........

good to here from you again.....

the data type for PCLifenet.ReqToDisp is adVarWCar........so that would be CVar right????
0
dasariCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

emspilotAuthor Commented:
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
dasariCommented:
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
emspilotAuthor Commented:
no......that didn't work either.....unforetuantely
0
dasariCommented:
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
dasariCommented:
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
emspilotAuthor Commented:
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
emspilotAuthor Commented:
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
emspilotAuthor Commented:
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
dasariCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rvooijsCommented:
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
emspilotAuthor Commented:
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
dasariCommented:
Great! good to hear that dude!

Good Luck!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.