Solved

Runtime error 8577 "Data type mismatch in criteria expression"

Posted on 2003-12-10
15
5,507 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Webi combining all column values in a single cell 3 165
Recommended SQL profile for MySQL 67 688
Adding date as variable in SSIS 20 78
REP-56048: Engine rwEng-0 crashed 3 926
Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

821 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