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

It's probably the actual room qty because you are counting it. If it's not an int it will fail.

Yes I thought it was too but I deleted it off the report and still gave me the error.

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;

Select allOpen in new window

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

Select allOpen in new window

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.

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

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

...and what about checking the controlsources?...

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.

Specifically looking for a function that attempts to sort text with a mixture of alphabetic, symbols and numerical characters.

<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

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.

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

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

<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