• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

Access Query, count records and total quantity field

i asked this question Monday:

I have a Access table that lists the jobs that we have placed a bid on in the last year and the number of hours that we bid.  I need to count the number of jobs, count the number of jobs that we have been awarded (award date is filled in) and the total number of hours that we have bid on all the jobs.  How can i do this?

and got this response:   SELECT Count(tbltotalspas.[CPP #]) AS [CountOfCPP #], Sum(IIf([DATE CONTRACT AWARD] Is Null,0,1)) AS NoOfContractAwarded, Sum(tbltotalspas.[TOTAL HOURS BID]) AS [SumOfTOTAL HOURS BID], Sum(tbltotalspas.[TOTAL ACTUALS]) AS [SumOfTOTAL ACTUALS]
FROM tbltotalspas;
 but when i click on the "run" button i get this error message:  Data type mismatch in criteria expression.  

Can anyone tell me what to change?  i have tried a couple of things but so far i haven't been able to get it to work.

thanks
0
barnescs
Asked:
barnescs
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
check your table  tbltotalspas and compare it with the table from this thread

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27304035.html#a36525997

0
 
barnescsAuthor Commented:
i took and copied everything from that thread in case i had some punctuation or spacing errors and i still got the mismatch error.
0
 
Rey Obrero (Capricorn1)Commented:
upload the db your are working on.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
barnescsAuthor Commented:
OK, here it is........... spares-proposal-data---Copy.accdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
Can you post the explicit steps to see this issue?

I am not finding a "Run" button on any of your forms...
0
 
Rey Obrero (Capricorn1)Commented:
run this update query first

UPDATE tbltotalspas SET tbltotalspas.[TOTAL HOURS BID] = Null
WHERE (((IsNumeric([TOTAL HOURS BID]))=0));



then run Query1

SELECT Count(tbltotalspas.[CPP #]) AS [CountOfCPP #], Sum(IIf([DATE CONTRACT AWARD] Is Null,0,1)) AS NoOfContractAwarded, Sum(tbltotalspas.[TOTAL HOURS BID]) AS [SumOfTOTAL HOURS BID], Sum(tbltotalspas.[TOTAL ACTUALS]) AS [SumOfTOTAL ACTUALS]
FROM tbltotalspas





0
 
barnescsAuthor Commented:
your telling me that i have to convert the "total hours bid" column into a number field instead of a text field and then the query will work?  is that correct?
0
 
Rey Obrero (Capricorn1)Commented:
<your telling me that i have to convert the "total hours bid" column into a number field instead of a text field and then the query will work?>

Yes that is one way of preventing unwanted characters in the field.

right now, the field  "total hours bid" contains  "-" and zero length string
0
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now