Correct sort

  • Thread starter Thread starter DebbieG
  • Start date Start date
D

DebbieG

I have a report (labels) that contains the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.PrintCheck = -1 Then
Me.txtAddress1 = Me.Address1
Me.txtAddress2 = Me.Address2
Me.txtCityStateZip = Me.CityStateZip
ElseIf Me.ParentPrintCheck = -1 Then
Me.txtAddress1 = Me.ParentAddress1
Me.txtAddress2 = Me.ParentAddress2
Me.txtCityStateZip = Me.ParentCityStateZip
End If
End Sub

The user wants to sort this by zip code. Right now I'm sorting it by
ParentZip, then by StudentZip. If they check all of the Parents' addresses
or all of the Students' addresses then it sorts OK. But if there is a
mixture, it still sorts first by ParentZip, then by StudentZip. I can't
figure out how to sort by what is printed.

Anyone know what I can do to get what they want?

Thanks in advance,
Debbie
 
In order to sort in a report, the values must be set in the query. You can
also use expressions in the Sorting and Grouping Levels.
 
I do have the sort set in the query:

ORDER BY
IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len([ParentZipcode])=9,Left([
ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left([ZipCode],5) & "-"
& Right([ZipCode],4),[ZipCode]));

If I use an expression in the Sorting and Grouping, how would I even word
it?

Debbie


In order to sort in a report, the values must be set in the query. You can
also use expressions in the Sorting and Grouping Levels.
 
=IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len([ParentZipcode])=9,
Left([ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left([ZipCode],5) & "-"
& Right([ZipCode],4),[ZipCode]))

I don't usually like expressions that are this complex. I would probably
create a user-defined function that could be used in the query or sorting or
control source.

--
Duane Hookom
MS Access MVP


DebbieG said:
I do have the sort set in the query:

ORDER BY
IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len([ParentZipcode])=9,Left([
ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left([ZipCode],5) & "-"
& Right([ZipCode],4),[ZipCode]));

If I use an expression in the Sorting and Grouping, how would I even word
it?

Debbie


In order to sort in a report, the values must be set in the query. You can
also use expressions in the Sorting and Grouping Levels.

--
Duane Hookom
MS Access MVP


DebbieG said:
I have a report (labels) that contains the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.PrintCheck = -1 Then
Me.txtAddress1 = Me.Address1
Me.txtAddress2 = Me.Address2
Me.txtCityStateZip = Me.CityStateZip
ElseIf Me.ParentPrintCheck = -1 Then
Me.txtAddress1 = Me.ParentAddress1
Me.txtAddress2 = Me.ParentAddress2
Me.txtCityStateZip = Me.ParentCityStateZip
End If
End Sub

The user wants to sort this by zip code. Right now I'm sorting it by
ParentZip, then by StudentZip. If they check all of the Parents' addresses
or all of the Students' addresses then it sorts OK. But if there is a
mixture, it still sorts first by ParentZip, then by StudentZip. I can't
figure out how to sort by what is printed.

Anyone know what I can do to get what they want?

Thanks in advance,
Debbie
Click to expand...
Click to expand...
 
How can I sort by what is printed?


=IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len([ParentZipcode])=9,
Left([ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left([ZipCode],5) & "-"
& Right([ZipCode],4),[ZipCode]))

I don't usually like expressions that are this complex. I would probably
create a user-defined function that could be used in the query or sorting or
control source.

--
Duane Hookom
MS Access MVP


DebbieG said:
I do have the sort set in the query:

ORDER BY
IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len([ParentZipcode])=9,Left([
ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left([ZipCode],5) & "-"
& Right([ZipCode],4),[ZipCode]));

If I use an expression in the Sorting and Grouping, how would I even word
it?

Debbie


In order to sort in a report, the values must be set in the query. You can
also use expressions in the Sorting and Grouping Levels.

--
Duane Hookom
MS Access MVP


DebbieG said:
I have a report (labels) that contains the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.PrintCheck = -1 Then
Me.txtAddress1 = Me.Address1
Me.txtAddress2 = Me.Address2
Me.txtCityStateZip = Me.CityStateZip
ElseIf Me.ParentPrintCheck = -1 Then
Me.txtAddress1 = Me.ParentAddress1
Me.txtAddress2 = Me.ParentAddress2
Me.txtCityStateZip = Me.ParentCityStateZip
End If
End Sub

The user wants to sort this by zip code. Right now I'm sorting it by
ParentZip, then by StudentZip. If they check all of the Parents' addresses
or all of the Students' addresses then it sorts OK. But if there is a
mixture, it still sorts first by ParentZip, then by StudentZip. I can't
figure out how to sort by what is printed.

Anyone know what I can do to get what they want?

Thanks in advance,
Debbie
Click to expand...
Click to expand...
 
Generally, "what is printed" is bound to a text box. As long as this text
box is not bound to an aggregate such as =Sum(YourField), you can generally
paste the control source into the sorting and grouping expression just as I
suggested in my previous email. If you print something else, we need to
understand how you are printing it.

--
Duane Hookom
MS Access MVP


DebbieG said:
How can I sort by what is printed?


=IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len([ParentZipcode])=9,
Left([ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left([ZipCode],5) & "-"
& Right([ZipCode],4),[ZipCode]))

I don't usually like expressions that are this complex. I would probably
create a user-defined function that could be used in the query or sorting or
control source.

--
Duane Hookom
MS Access MVP


DebbieG said:
I do have the sort set in the query:

ORDER BY
Click to expand...
IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len([ParentZipcode])=9,Left([
ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left([ZipCode],5) & "-"
& Right([ZipCode],4),[ZipCode]));

If I use an expression in the Sorting and Grouping, how would I even word
it?

Debbie


In order to sort in a report, the values must be set in the query. You can
also use expressions in the Sorting and Grouping Levels.

--
Duane Hookom
MS Access MVP


DebbieG said:
I have a report (labels) that contains the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.PrintCheck = -1 Then
Me.txtAddress1 = Me.Address1
Me.txtAddress2 = Me.Address2
Me.txtCityStateZip = Me.CityStateZip
ElseIf Me.ParentPrintCheck = -1 Then
Me.txtAddress1 = Me.ParentAddress1
Me.txtAddress2 = Me.ParentAddress2
Me.txtCityStateZip = Me.ParentCityStateZip
End If
End Sub

The user wants to sort this by zip code. Right now I'm sorting it by
ParentZip, then by StudentZip. If they check all of the Parents' addresses
or all of the Students' addresses then it sorts OK. But if there is a
mixture, it still sorts first by ParentZip, then by StudentZip. I can't
figure out how to sort by what is printed.

Anyone know what I can do to get what they want?

Thanks in advance,
Debbie
Click to expand...
Click to expand...
Click to expand...
 
Just a thought, I think what Debbie wants is to sort on
the zipcode for the person being billed, this may be the
parent or the student.
The way I would do that is in the query I would have the
fields like address, name and zip with an IIF statement
ZipBilled=IIF(ParentPrintCheck,ParentZip,StudentZip)
do this for each variable as applicable.
Now you can sort on ZipBilled, and you report should be as
stated.
Fons
-----Original Message-----
Generally, "what is printed" is bound to a text box. As long as this text
box is not bound to an aggregate such as =Sum(YourField), you can generally
paste the control source into the sorting and grouping expression just as I
suggested in my previous email. If you print something else, we need to
understand how you are printing it.

--
Duane Hookom
MS Access MVP


How can I sort by what is printed?


=IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len ([ParentZipcode])=9,
Left([ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left
Click to expand...
([ZipCode],5) &
"-"
& Right([ZipCode],4),[ZipCode]))

I don't usually like expressions that are this complex. I would probably
create a user-defined function that could be used in
Click to expand...
the query or sorting
or
control source.

--
Duane Hookom
MS Access MVP


I do have the sort set in the query:

ORDER BY
Click to expand...
IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len ([ParentZipcode])=9,Left([
ParentZipCode],5) & "-" & Right([ParentZipCode],4), [ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])
Click to expand...
=9,Left([ZipCode],5) &
"-"
& Right([ZipCode],4),[ZipCode]));

If I use an expression in the Sorting and Grouping,
Click to expand...
Click to expand...
how would I even
word in the query. You
can StudentZip. I
can't

.
Click to expand...
 
Do you have an example of the user-defined function you referred to. It
sounds like something that I would find very useful.

Debbie


=IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len([ParentZipcode])=9,
Left([ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left([ZipCode],5) & "-"
& Right([ZipCode],4),[ZipCode]))

I don't usually like expressions that are this complex. I would probably
create a user-defined function that could be used in the query or sorting or
control source.

--
Duane Hookom
MS Access MVP


DebbieG said:
I do have the sort set in the query:

ORDER BY
IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len([ParentZipcode])=9,Left([
ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left([ZipCode],5) & "-"
& Right([ZipCode],4),[ZipCode]));

If I use an expression in the Sorting and Grouping, how would I even word
it?

Debbie


In order to sort in a report, the values must be set in the query. You can
also use expressions in the Sorting and Grouping Levels.

--
Duane Hookom
MS Access MVP


DebbieG said:
I have a report (labels) that contains the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.PrintCheck = -1 Then
Me.txtAddress1 = Me.Address1
Me.txtAddress2 = Me.Address2
Me.txtCityStateZip = Me.CityStateZip
ElseIf Me.ParentPrintCheck = -1 Then
Me.txtAddress1 = Me.ParentAddress1
Me.txtAddress2 = Me.ParentAddress2
Me.txtCityStateZip = Me.ParentCityStateZip
End If
End Sub

The user wants to sort this by zip code. Right now I'm sorting it by
ParentZip, then by StudentZip. If they check all of the Parents' addresses
or all of the Students' addresses then it sorts OK. But if there is a
mixture, it still sorts first by ParentZip, then by StudentZip. I can't
figure out how to sort by what is printed.

Anyone know what I can do to get what they want?

Thanks in advance,
Debbie
Click to expand...
Click to expand...
 
You thought right! I put =IIF(ParentPrintCheck,ParentZip,StudentZip) as the
expression in Sorting and Grouping and it works great.

I have never used an expression in Sorting and Grouping (always used fields)
but now it makes perfect sense. Duane had mentioned using an expression but
I had no idea what he was talking about. Seeing your response turned the
light on.

Thanks to both of you.


Just a thought, I think what Debbie wants is to sort on
the zipcode for the person being billed, this may be the
parent or the student.
The way I would do that is in the query I would have the
fields like address, name and zip with an IIF statement
ZipBilled=IIF(ParentPrintCheck,ParentZip,StudentZip)
do this for each variable as applicable.
Now you can sort on ZipBilled, and you report should be as
stated.
Fons
-----Original Message-----
Generally, "what is printed" is bound to a text box. As long as this text
box is not bound to an aggregate such as =Sum(YourField), you can generally
paste the control source into the sorting and grouping expression just as I
suggested in my previous email. If you print something else, we need to
understand how you are printing it.

--
Duane Hookom
MS Access MVP


How can I sort by what is printed?


=IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len ([ParentZipcode])=9,
Left([ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left
Click to expand...
([ZipCode],5) &
"-"
& Right([ZipCode],4),[ZipCode]))

I don't usually like expressions that are this complex. I would probably
create a user-defined function that could be used in
Click to expand...
the query or sorting
or
control source.

--
Duane Hookom
MS Access MVP


I do have the sort set in the query:

ORDER BY
Click to expand...
IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len ([ParentZipcode])=9,Left([
ParentZipCode],5) & "-" & Right([ParentZipCode],4), [ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])
Click to expand...
=9,Left([ZipCode],5) &
"-"
& Right([ZipCode],4),[ZipCode]));

If I use an expression in the Sorting and Grouping,
Click to expand...
Click to expand...
how would I even
word in the query. You
can StudentZip. I
can't

.
Click to expand...
 
I'm not sure that I understand your logic, however this might get you
started
Public Function GetZip( pvarParentZip as Variant, _
pvarZip as Variant) as String
If Len(pvarParentZip) = 5 Then
GetZip = pvarParentZip
Else
If Len(pvarParentZip) = 9 Then
GetZip = Left(pvarParentZip,5) & "-" & Right(pvarParentZip,4)
End If
End If
End Function

--
Duane Hookom
MS Access MVP
--

DebbieG said:
Do you have an example of the user-defined function you referred to. It
sounds like something that I would find very useful.

Debbie


=IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len([ParentZipcode])=9,
Left([ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left([ZipCode],5) & "-"
& Right([ZipCode],4),[ZipCode]))

I don't usually like expressions that are this complex. I would probably
create a user-defined function that could be used in the query or sorting or
control source.

--
Duane Hookom
MS Access MVP


DebbieG said:
I do have the sort set in the query:

ORDER BY
Click to expand...
IIf(Len([ParentZipCode])=5,[ParentZipCode],IIf(Len([ParentZipcode])=9,Left([
ParentZipCode],5) & "-" & Right([ParentZipCode],4),[ParentZipCode])),
IIf(Len([ZipCode])=5,[ZipCode],IIf(Len([Zipcode])=9,Left([ZipCode],5) & "-"
& Right([ZipCode],4),[ZipCode]));

If I use an expression in the Sorting and Grouping, how would I even word
it?

Debbie


In order to sort in a report, the values must be set in the query. You can
also use expressions in the Sorting and Grouping Levels.

--
Duane Hookom
MS Access MVP


DebbieG said:
I have a report (labels) that contains the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.PrintCheck = -1 Then
Me.txtAddress1 = Me.Address1
Me.txtAddress2 = Me.Address2
Me.txtCityStateZip = Me.CityStateZip
ElseIf Me.ParentPrintCheck = -1 Then
Me.txtAddress1 = Me.ParentAddress1
Me.txtAddress2 = Me.ParentAddress2
Me.txtCityStateZip = Me.ParentCityStateZip
End If
End Sub

The user wants to sort this by zip code. Right now I'm sorting it by
ParentZip, then by StudentZip. If they check all of the Parents' addresses
or all of the Students' addresses then it sorts OK. But if there is a
mixture, it still sorts first by ParentZip, then by StudentZip. I can't
figure out how to sort by what is printed.

Anyone know what I can do to get what they want?

Thanks in advance,
Debbie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top