Solved

How to overcome data type mismatch in criteria expression in an Access 2007 report?

Posted on 2011-02-17
19
1,569 Views
Last Modified: 2013-11-28
All,

I get a Data type mismatch in criteria expression while previewing an Access report but the query source doesn't have an error.  How do I determine what control in the report is causing the error? error messageee-datatype2.png
0
Comment
Question by:stephenlecomptejr
  • 9
  • 5
  • 2
  • +1
19 Comments
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 34916847
It's probably the actual room qty because you are counting it. If it's not an int it will fail.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 34916897
Yes I thought it was too but I deleted it off the report and still gave me the error.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 34916914
Here's the SQL statement code:

SELECT Rm_Num_Sort_New([ROOMLIST].[Room_Number]) AS RmSort, ROOMLIST.Room_Number, ROOMLIST.Room_Name, ROOMLIST.Room_Generic, PROJ_RM.Room_Name, PROJ_RM.Rm_Quantity, ROOMLIST.Floor, Val([ROOMLIST].[Floor]) AS NumFl
FROM ROOMLIST LEFT JOIN PROJ_RM ON ROOMLIST.Room_Generic=PROJ_RM.Room_Number
WHERE (((PROJ_RM.Rm_Quantity)>0))
ORDER BY Rm_Num_Sort_New([ROOMLIST].[Room_Number]), ROOMLIST.Room_Number;

Open in new window

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 34916919
Thus the above is the recordsource of the report!

Here's the function the above SQL statement is using:

Public Function Rm_Num_Sort_New(sVlu As String) As Long
On Error GoTo Err_This
 
  Dim i As Integer
  Dim sPart As String
  Dim lAsc As Long
  
  lAsc = Asc(Left(sVlu, 1))
  
  If lAsc > 47 And lAsc < 58 Then
  
    'For i = 1 To Len(sVlu)
 
    '    If Asc(Mid(sVlu, i, 1)) > 47 And Asc(Mid(sVlu, i, 1)) < 58 Then
    '        sPart = sPart & Mid(sVlu, i, 1)
    '
    '    End If
    
    'Next i
    sPart = Val(sVlu)
    
  Else
  
    If lAsc > 64 And lAsc < 91 Then
    
        sPart = "999999"
  
    End If
    If lAsc > 96 And lAsc < 123 Then
    
          sPart = "999999"
          
    End If
    
  End If
  Rm_Num_Sort_New = Val(sPart)
  
Exit_This:
    Exit Function
    
Err_This:
    Call Error_Action(Err, Err.Description, "modECdatabases @ Rm_Num_Sort_New", Erl())
    Resume Exit_This
End Function

Open in new window

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 34916930
What I"m trying to do is that in many cases a room number has a combination of letters and numbers - thus have to set the data type as text.

When I want to sort the room numbers as text - it throws 1 with the listing of 10, 100... so I had to extra portions and develop something that would take into consideration all kinds of formatting.
Sometimes a room number is at the end of the text instead of the beginning - sometimes in the middle.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 34918671

Typically this means you are trying to preform a mathematical operation on Text field in a control.

So if you try something like this as the control source for a texbox, you will get the error.
=Sum([LastName])
The kicker here is that some fields contain numbers but the Datatype is actually text (like Part Numbers).
So check the controlsources of your controls.

The other culprit might be that something similar may be lurking in the Sorting/Grouping settings.
Like trying to Group by 100's on a text field.
So check things like that as well.

Finally, I see a few "unbound" controls there.

If you are loading these in code, make sure the datatypes are appropriate as well.
meaning something like this won't cut it:
Me.Text19 = Me.LastName * Me.Amount
;-)

JeffCoachman
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 250 total points
ID: 34933309
Try making a copy of the report, and delete controls one by one, until the error goes away.  Then the last one deleted is likely the source of the error.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34933320
Also, make sure that you give controls the appropriate prefixes -- bound controls that have the same name as their fields can cause reference errors.  My LNC Rename add-in can help with applying the correct prefixes to controls on forms and reports.  Here is a link for downloading the Access 2007/2010 version:

http://www.helenfeddema.com/Files/code63.zip
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 34937330
I did try to delete the one by one and that still didn't work - leaving me to believe it was the sorting/grouping section.

But hadn't deleted that one by one.

I won't be able to try anything though till Wednesday because of a deadline...so please be patient with me on finishing up the reply and points.

Thanks,.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34957897
...and what about checking the controlsources?...
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 34963784
Okay so I removed two fields that were causing the issue in the report but not in the query.
But I don't feel like I've gotten the help I need.

Please humor me - the first one that can tell me a best method to sort text when it contains a mixture of numbers and letters gets the points.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 34964139
Specifically looking for a function that attempts to sort text with a mixture of alphabetic, symbols and numerical characters.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34967595
<Please humor me - the first one that can tell me a best method to sort text when it contains a mixture of numbers and letters gets the points.>
Well, according to you, your original question:...
<How to overcome data type mismatch in criteria expression in an Access 2007 report?>
...has been resolved, so any points should be awarded for this specific issue.

The issue of sorting Text/Numbers is a totally separate issue and should be posted as a new question.
Besides, in a single field you can either sort by Text OR by Number, not by both. (and certainly not including "Symbols")
You can however sort a Number field first, then set a text field to sort after (inside) it.
This is done in the Sorting and Grouping options.
Simply Click "add a Sort" for your primary sort field , then click Add a Sort again for your secondary sort field.


<But I don't feel like I've gotten the help I need.>
Care to elaborate?
You asked a question.
We gave an answer
You stated that the answer resolved the issue...
AFICT, you did get the help you needed.
What more did you "need'?

JeffCoachman

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 34972749
Just because I say please humor me that means I am being rude?

To elaborate, I was looking more than just delete the controls one-by-one to which I already knew.  I tried initially a couple of times before even posting.   Even after Helen's comment, I tried more methodically - I went from top to bottom deleting the controls and the issue was still therre.  Then I started bottom to top and deleted the controls one by one to the top.  The issue still remained.  

Then after boag's comment I tried deleting just the sorting and grouping but still got the error.

So what I was left from all the comments was one absolutely wrong comment by aarontomosky that it certainly wasn't the actual rm qty and the two left only provided a solution to delete everything off a report making it virtually useless.  And this report had worked all up until this time.

Once I went through and deleted the controls in different sequences I was able to find the two fields causing the problem.   But was left with another problem the actual reason why those fields existed in the first place which was to sort by room numbers that has a text field.  I would have been willing to split the points halfway between Helen and Boag.  But was asking for a little more - if no one was not willing to do such then fine - I would have gone in that direction and split the points that way.

But only boag's last comment about how the sorting works with doing so first by number and then by text truly solves my issue.

It's like going to doctor and always getting colds and receiving medicine.  This issue is like a cold and got some antibotics (a bacterial and virul) that basically kills the good and bad blood cells in half - but what I really need is knowing the source of the colds - not having the right diet and exercise are causing my problem.  I'm looking for a bigger picture solution to the problem.  I'm asking for the proper diet that will eliminate the possibility of future colds.  

The whole reason why those fields were the problem of sorting in the first place.

I asked that and received such from boag's last request.

But saying please humor me seems to have made you all upset.  It wasn't my intention on making you irritated as much as I was not getting anything as to my last comment until I had to get a moderator involved.  Didn't mean to cause any conflicts - and the proof in the past is that I'm always gracious with points and grading on the matter -especially with Helen in this manner.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34973175
1. <Just because I say please humor me that means I am being rude?>
?
I don't think anybody stated that you were being rude...


2. Your post here:
<Okay so I removed two fields that were causing the issue in the report but not in the query.>
...Seemed to indicate that the issue was resolved.
If it was not, then please state this explicitly.

3. <And this report had worked all up until this time.>
This is new information not mentioned before.
Did this stop working after you upgraded to 2007?

4. Please bear in mind that, in your last post you stated:
   "I don't feel like I've gotten the help I need."
Then you seemed to change gears suddenly and ask about sorting...
(You made no mention in that post that Sorting was the underlying issue)
Sound fair?

5. Worth noting is the fact that I actually did try to provide some info on the sorting issue as a show of good faith.
;-)

6. The fact that your sorting requirements are so complex that a custom function is required is another indication that perhaps this should be dealt with in a separate, new question.

7. <But saying please humor me seems to have made you all upset.>
Again, I see no indication that this comment made anyone "upset".

8. <Didn't mean to cause any conflicts - and the proof in the past is that I'm always gracious with points and grading on the matter -especially with Helen in this manner.>
Yes, I agree, this is why I was a bit confused as to why there was an issue here.

Perhaps this was all a misunderstanding...
Perhaps the distinction between "Additional info needed" and creating a new Related question was not clear-cut in this case.

So as far as the original question is concerned:
    "How to overcome data type mismatch in criteria expression in an Access 2007 report?"
Has this issue been resolved?
If so, and now the issue of custom sorting seems to be the real underlying issue, ...
Then you should really post a separate question.  First to see if a custom sorting function is really needed.  And if so, how it should be applied in your report to give you the output you require.

;-)

JeffCoachman



0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 34973601
I had vee mod jump all over me saying I was rude

I can't do anything about the past just go forward

If I had one wish is that vee mod stop harassing me about misconduct and start treating me fair

If you like I could send to yourself or another moderator his direct emails
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34974576
<I had vee mod jump all over me saying I was rude>
Again, Perhaps I missed something, but I don't see where anyone ever said you were rude...

<If I had one wish is that vee mod stop harassing me about misconduct and start treating me fair>
This seems to be an issue outside of this particular Q, so I can't/won't comment.
But if it is any consolation, ...the mods actually have more issues with Experts here than they do with members.

Jeff
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now