I'm still new to Microsoft Access so I don't have a great understanding of its workings but have spent the past couple of months working on a project to manage the company's telphone bills. I'm essentially compiling the usage from every company phone each month into a final table by running the numbers through a sequence of queries set up by a senior staff member (I'm just a part time uni student).

However, while adding in data from the month just past, I made an error somewhere along the line and its giving me the message "Data type mismatch in criteria expression" when trying to run one of the queries. I've been struggling to work my way around this so any help would be greatly appreciated

SELECT [T001 Data consol per month].[phone number], Sum([T001 Data consol per month].Sep) AS SumOfSep, Sum([T001 Data consol per month].Oct) AS SumOfOct, Sum([T001 Data consol per month].Nov) AS SumOfNov, Sum([T001 Data consol per month].Dec) AS SumOfDec, Sum([T001 Data consol per month].Jan) AS SumOfJan, Sum([T001 Data consol per month].Feb) AS SumOfFeb, Sum([T001 Data consol per month].Mar) AS SumOfMar, Sum([T001 Data consol per month].Apr) AS SumOfApr INTO [T002 Data consol per month sum]
FROM [T001 Data consol per month]
GROUP BY [T001 Data consol per month].[phone number]
HAVING ((([T001 Data consol per month].[phone number])<>"1018736981"));

That is the SQL

Not much to go on here. Here are some questions that might help you troubleshoot:
What are the data types of the columns? Is there a mismatch between source/target?
What is the format of the incoming data? Is it formatted the same way as last time you performed this task? If no, what's different?
Have you looked at the incoming data directly, to see if there is a problem with the input file (such as truncated file, overlength data or alpha where it should be numeric)?
Has your senior person looked at it yet?

I would only be guessing if I tried to help any further. However, it appears that (if nothing else has changed) that the problem is with your input.

Hope this gives you some ideas.

Thanks a lot for the reply

In the table I'm sourcing data from, the first column is text (phone number) and the rest are binary (usage each month). However I'm not sure what you mean by target (this gives you an idea of how novice I am). If you're referring to the table I'm attempting to make with the query, then I'm not sure how to check that.

Yes the format is all the same as previous times I've done this task. And no my senior is on leave at the moment which has lead to me resorting to online forums haha

I suggest you do a select distinct on the phone number column and sort it, then look and see if there are odd characters in it. You may want to just "eyeball" it to see if anything jumps out at you.

Summing binary should never be a problem (it treats everything as ones and zeroes anyway, so even if there are alpha characters, they're just ones and zeroes).

That's about all I can do for you. If this doesn't help, you'll just have to wait for your senior person to come back.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.