Val function in SQL Select Statement Order By 2

[JDRoss]

I wonder if anyboyd could help me with using the Val function (to convert string to number) in an SQL Select statement as I need to sort on this.

 strsql = strsql & " ORDER BY " _
        & "Val([People]" _
        & "." _
        & "[street no]")  _
        & ")"

I'm getting a 'type mismatch' when I use the above statement.


The rest of the statement is

strsql = "SELECT * FROM " & "people" & " WHERE "
        strsql = strsql & Me.Filter

(The filter is based on a form)

Regards

John

 

[Frink]

Hallo,

Looks ok, can you show us strsql, once it's been set up?

- Frink

 

[JDRoss]

There may have been a typo in the strsql above (first posting).

I think it should be

strsql = strsql & " ORDER BY " _
        & "Val([People]" _
        & "." _
        & "[street no]"  _
        & ")"

One closing bracket too many!

I'm not sure this was the only problem as I've not tested it, but here below is the rest of the strsql creating a query with querydef.

Set rs = db.OpenRecordset(strsql)
    If rs.RecordCount > 0 Then
        '*** delete the previous query
        db.QueryDefs.Delete "querytmp"
        Set qdf = db.CreateQueryDef("querytmp", strsql)
        DoCmd.OpenReport stDocName, A_PREVIEW, "querytmp"
    End If

 

[Frink]

Hallo,

Is 'people' your table/query name, and the name of a field within the table?

If people is not a field name then:
strsql = strsql & " ORDER BY Val([street no])"
which should work, provided [street no] is a unique field name within people.
If [street no] contains nulls, then try:
strsql = strsql & " ORDER BY Val("" & [street no])"
or
strsql = strsql & " ORDER BY Val(Nz([street no],'0'))"

- Frink

 

[PaulBricker]

Val will only return the numeric portion of a text string and only when it is the first values. So Val("123 N. Main St&quot will return 123. Val("E 82 St&quot won't return anything. It does seem very strange have a Val(People) expression. Maybe some examples of the data you are testing with the Val function would help clarify things.

Paul

 

[JDRoss]

Thanks Frink & PaulBricker

I am glad you showed me how to deal with null values in Street no because I do have lots of empty Street no.

As for the question about People. People is a query based on a table at the moment. I understand as a single word name it doesn't need angle brackets. Just habit.

You ask what the data is like. Well, I can't forsee a situation when I will have, 'E 123'. At the moment I have something like 1a, 2b, and an extreme case of 5/6 as Street no in the address. Hence, the reason why street no is a text field type.

[Street no] is a field in the table/query above.

I need to be able to sort on the Street No, then on Address0 and finally on Address1.

Sample data:

Mr & Mrs Tom Smith 22 Home Court, Ourcity
Mr & Mrs Garry Jones 6a Hazel Wood,
Mr & Mrs Leroy Ellen 27b Shore Drive

The only way so far I can achieve this is by creating a query where I use the following function to extract the number from Street No field and return it to SortStreet in the query, People. And in this case the val function works for some reason?

Public Function ExtractNumbers(strText As String) As Long
' Extracts the numbers in a string convert

  Dim i As Integer
  Dim str As String
  str = strText        
  ExtractNumbers = Val(str)
End Function

I am doing this because the Val function is giving a type mismatch or just not working in the sort of the form.

Select Case Me.Sort1
            Case "street no"
            strsql = "Address0,Val(Nz([street no],'0')),Address1"
            Case Else
            strsql = Buildsqlstring(Me.Sort1, Me.Check1)
        End Select

I also need to be able to load a report based on the sort in the form. And so for this I build another query using the querydef as mentioned above in earlier postings. I try to use the sortstreet field from the earlier query for this but it fails to find the field.

Private Sub Update_Report_Click()
On Error GoTo Err_Update_Report_Click

    Dim strsql, strsort As String
    Dim mysql, tmpsql, tmpsort As String
    Dim db As dao.Database
    stDocName = "Update Subreport"
    Dim strMsg As String
    
    
    Dim qdf As QueryDef
    Set db = CurrentDb
    
    ' filter based on form filter
    Select Case Not IsNull(Me.Filter)
            Case True
                mysql = "SELECT people.ID, people.Titles," _
                        & "people.FirstName, people.FirstName2," _
                        & "people.LastName, people.Address0," _
                        & "people.Address1, people.ENV_NO," _
                        & "people.DISTRICT, people.Area, people.allNAME," _
                        & "people.[Street No],people.sortstreet,people.fulladdress FROM people " _
                        & "WHERE "
                Select Case Not IsNull(Me.AreaCombo)
                    Case True
                        mysql = mysql & "area = '" & Me!AreaCombo & "';"
                    Case Else
                        mysql = mysql & "District = '" & Me!DistrictCombo & "';"
                End Select
                Case Else
                mysql = "SELECT * " & "FROM [people]"
        
    End Select
    
 
            If Not Me.OrderBy = "" Then
                If InStr(mysql, ";") Then mysql = Left(mysql, (Len(mysql) - 1))
                        'Strip semi colon off end of string to add the sort
                End If
            
            
            Select Case InStr(Me.OrderBy, "sortstreet") > 0
            Case True
                mysql = mysql & " ORDER BY Val("" & [street no]), address0, address1;"
            Case Else
                    tmpsort = Me.Sort1
                    If tmpsort = "" Then tmpsort = "DISTRICT" ' Default sort to district
                        strsort = Buildsqlstring(tmpsort, Me.Check1)
                        
                        mysql = mysql & " ORDER BY " _
                        & "[people]" _
                        & "." _
                        & strsort
                        mysql = mysql & "; "
        End Select
        
  '*** delete the previous query
        db.QueryDefs.Delete "querytmp"
        Set qdf = db.CreateQueryDef("querytmp", mysql)
        DoCmd.OpenReport stDocName, A_PREVIEW, "querytmp"
    
    
    Set db = Nothing


Exit_Update_Report_Click:
    Exit Sub

Err_Update_Report_Click:
    If Err.Number = 3265 Then   '*** if the error is the query is missing
        Resume Next             '*** then skip the delete line and resume on the next line
    Else
    msgbox Err.Description
    Resume Exit_Update_Report_Click
    End If
End Sub

My overriding question is, why won't Val do the job?

Regards and much appreciated as always.

John

 

[PaulBricker]

I haven't gotten thru it all yet, but as far as sorting goes, in a new column in your query you can put
MySort:Val([Street No])

and set the sort to Ascending or Decending for this field. Now the caveat about sorting is that Access Queries sort from Left to Right so if you are sorting a field in the query like LastName that is to the left of this field that sort will take priority over the MySort field. So if you want your street no to sort first, put MySort in the first column. Then you can put address0 in the next column and sort by that as well.

Paul

 

[JDRoss]

Thanks Paul for the quick turn around.

Since I last posted I've debugged the problem on the report. (I wasn't using the same query as the data source for the Report). That explains why it wasn't finding the field, sortstreet.


Frink's suggestion for nulls, Val(Nz([street no],'0')) seems to work now. It means it returns 0 to the field if it is null and Access seems happier with this, as it should, when it comes to the sort.

I was aware that Access sorted left to right, but a curious thing, if I don't put it in this order, I don't get sortstreet first.

mysql = mysql & " ORDER BY address0,sortstreet, address1;"

However, this doesn't work right when there are no values in Address0

College View, City Road
College View, City Road
2 Riverside, City Road
St Martins, College View

I'm sure I am missing something here?

So I am reporting progress since I last posted.

Thanks once again for all your help.

John

 

[PaulBricker]

John, from what I've read you are trying to sort your Form by the numeric value in your street no field. If the form is based on a query then all you should have to do is what I posted above, making the adjustments for the null fields. You shouldn't need to use any code at all. I think that's where I'm a little confused right now. You show an sql string
mysql = mysql & " ORDER BY address0,sortstreet, address1;"

I'm not sure where you are using this but if you just want to sort the Form, you should be able to do that by adding
MySort:Val(Nz([Street No],0)) and sort this ascending and then put your address fields next to that and sort them ascending also.
Maybe I'm missing something but you shouldn't need a function to sort your form to incorporate the Val function.

Paul

 

[JDRoss]

Thanks once again Paul for your help.

I know I shouldn't have to use a function to do the converstion to numeric but it works so why fix it.

I need to emulate what is on the form for the report so I have to run code to do this. That is why you see the statement:

mysql = mysql & " ORDER BY address0,sortstreet, address1;"

I am not that familiar with sorting but I am happy with what I've got so far. I would appreciate help with the Report object to grouplevel controls at run time.

Ok!

Thanks once again

John

 

[PaulBricker]

Sorry John, what am I missing about the "help with the Report object to grouplevel controls at run time"? Did you have another post about that. Post a link to it and I'll look at it. Grouping at runtime is a bear to try and accomplish (as you have probably found out).

Paul

 

[JDRoss]

Dear Paul

Re: Grouplevel controls on Report

Sorry, I was just planning ahead.

I am curious about your comment on experience with Grouping at runtime, could you be more specific about the problems I might run into?

If it is too difficult I will take evasive action now.

Regards and thanks once again.

John

Great things come of lateral thinking!

 

[PaulBricker]

Well it's kind of difficult not knowing exactly what you want to do but here are a few things. If what you are planning is to actually add GroupLevels and textboxes at RunTime then you will have to do that work in Design View and then switch to Preview. You can't add grouping levels in Preview mode. Second, getting the group levels is fairly simple, but putting textboxes in those group levels can be vexing. I haven't really figured out how to place them exactly where I want them. You will also have to delete textboxes out of the group headers which isn't difficult as long as you know the name of the textbox.
There is a limit to the number of times you will be able to perform this activity also. The limit on controls over the lifetime of a Report is 754. If you see the report running multiple times a day with changes each time you run it, you could run thru your limit in 12 months or less.
As I said, it's hard to know what to tell you without an idea of what you plan. This could all be worthless if you have something else conceived.

Paul

 

[JDRoss]

Dear Paul

I suppose what I am most interested in is how to reference the Report object.

At the moment I would like to be able to change the section header and footer controls for instance. Give the header a different name depending on the filter selected in my form for Area and District. Or causing a page break when the Group finishes.

I am not really thinking any more complex than this at present.

In "Access 97 Developers Handbook" they use (page 536) the following example

With Reports(adhcdocname)
.grouplevel(0).controlSource = strfield
.grouplevel(0).SortOrder = me!chkdescending
.etc
End with


However, when I try to pass the report name in a variable I get an error saying the report is not open or doesn't exist.


They also use this expression

Reports(adhhcdocname).painting = false

I am not sure what painting does.


Regards

John

 

[PaulBricker]

Interesting code. Here's one of the lines I use.

strName = Reports![WorkOrderList].Section(GroupHeader4).Controls.Item(0).Name

When I replace it with

With Reports
.Section(GroupHeader4).controls.Item(0).Name

I get a runtime error 'Doesn't support this Method or Property'. Also I'm not sure what GroupLevel is but a GroupHeader doesn't have a ControlSource Property.

As for the error message. You should be able to use either of these methods.

Reports!ReportNameHere!ControlNameHere
or
Dim myRpt as Report
Set myRpt = Reports!ReportNameHere
myRpt!ControlNameHere

but in either case, the report must be open in design view or preview view. To make changes to the report at runtime I'm 99% sure it has to be opened in Design View. If you want to post more of the code I will look it over. Getz, Gilbert and Litwin are excellent authors and I'd be surprised if they have an error in their code but I don't have the book and can't look at it.

Paul

 

[lameid]

JDRoss,

Paul is right about adding sorting and grouping in the report instead of doing it in code.

The only advantage to a code solution is if it has to be extremely dynamic. I find it is usually easier to create the useful sorts of the report and give users the option of running each of them as opposed to coding anything.

That said, you asked about why use a query over code?
Queries are often saved compiled. Specifically if you save it, run it and save it again without making anychanges it is compiled (that's in the book you referenced). Compiled queries execute faster. Whereas a query built in code is compiled at runtime. The latter has its uses but makes maintenance a nightmare.

Secondly, more people know how to use the QBE and mistakes are more apparent. Much like the fields have to be sorted left to right in the QBE, the same is true for the SQL statement.

So,
mysql = mysql & " ORDER BY address0,sortstreet, address1;"

Sorts by address0 then sortstreet then address1

Sounds like you want

mysql = mysql & " ORDER BY sortstreet, address0, address1;"

Your oder by clause also sorts everything Ascending which is the default.

mysql = mysql & " ORDER BY sortstreet Asc, address0 Asc, address1 Desc;"

The above would sort sortstreet Ascending then Address0 ascending and address1 descending. Probably not very useful but a good example.

"At the moment I would like to be able to change the section header and footer controls for instance. Give the header a different name depending on the filter selected in my form for Area and District. Or causing a page break when the Group finishes. "

If you truly have a list of filters in your form (say a combo box), then add a column to show the header. Then reference the header in the control source of your report.

=Forms!Formname!cboFilter.column(1)

That will give you the second column. The column property starts at 0 for first. This in and of itself is not all that bad but Bound column 1 is refers to the first column. Just a little inconsistancy there.

Similarly you might use a combination of IIF and Instr to change your header in a control source.

I'm assuming your wanting to change what you display as a header. That doesn't look like what your trying to accomplish in code. If I'm mistaken, please tell us the desired results so one of us can give the correct answer.