Include Function in Sort

[bw]

I have a Public Function TotalTime(), which I call from a Text Box:
=[TotalTime]

I currently sort two bound fields. How do I include the Function
TotalTime() in my sort order, and how do I specify Ascending/Descending
on this function? Where does the "code" go to do this?

For example, I'd like to sort as follows:
Field1 Ascending
TotalTime Ascending
Field3 Ascending

Thanks,
Bernie

 

[Duane Hookom]

Can you calculate a value from TotalTIme() with a single record from your
report's Record Source?
Can you tell us anything about the function? Could you perhaps use it in
your report's record source query?

 

[John Vinson]

I have a Public Function TotalTime(), which I call from a Text Box:
=[TotalTime]

I currently sort two bound fields. How do I include the Function
TotalTime() in my sort order, and how do I specify Ascending/Descending
on this function? Where does the "code" go to do this?

For example, I'd like to sort as follows:
Field1 Ascending
TotalTime Ascending
Field3 Ascending

Thanks,
Bernie

Don't call it from a textbox - call it in the Query itself. Put a call
to the function in an vacant Field cell:

TTime: TotalTime()

and simply sort by this field.

John W. Vinson[MVP]

 

[bw]

Thanks, Jon.

I did exactly what you said, and I am now getting:
"Undefined Function 'TotalTime' in expression" when I try to preview the
SQL Statement: Query Builder.

My Function is shown below:
Public Function TotalTime()
Dim DTimeFactor, RTimeFactor, FromA, ToA
FromA = [tblAirports.TimeZone]
ToA = [tblAirports_1.TimeZone]
If FromA = "Hawaii" Then DTimeFactor = 1
If FromA = "Alaska" Then DTimeFactor = 2
If FromA = "Pacific" Then DTimeFactor = 3
If FromA = "Mountain" Then DTimeFactor = 4
If FromA = "Central" Then DTimeFactor = 5
If FromA = "Eastern" Then DTimeFactor = 6
If ToA = "Hawaii" Then RTimeFactor = 1
If ToA = "Alaska" Then RTimeFactor = 2
If ToA = "Pacific" Then RTimeFactor = 3
If ToA = "Mountain" Then RTimeFactor = 4
If ToA = "Central" Then RTimeFactor = 5
If ToA = "Eastern" Then RTimeFactor = 6
TotalTime = DateAdd("h", RTimeFactor, DepartTime) - DateAdd("h",
DTimeFactor, ArrivalTime)
TotalTime = Format(TotalTime, "h:nn")
End Function

I have tried to break this function up into its components and enter it
into the SQL Statement as individual fields, like this:

DTF:
IIf([tblAirports.TimeZone]="Hawaii",1,IIf([tblAirports.TimeZone]="Alaska",2,IIf([tblAirports.TimeZone]="Pacific",3,IIf([tblAirports.TimeZone]="Mountain",4,IIf([tblAirports.TimeZone]="Central",5,IIf([tblAirports.TimeZone]="Eastern",6))))))

RTF:
IIf([tblAirports].[TimeZone_1]="Hawaii",1,IIf([tblAirports].[TimeZone_1]="Alaska",2,IIf([tblAirports].[TimeZone_1]="Pacific",3,IIf([tblAirports].[TimeZone_1]="Mountain",4,IIf([tblAirports].[TimeZone_1]="Central",5,IIf([tblAirports].[TimeZone_1]="Eastern",6))))))

Now if I preview this, I'm asked for the Parameter Value for
[tblAirports].[TimeZone_1].

Do you have another suggestion?

I have a Public Function TotalTime(), which I call from a Text Box:
=[TotalTime]

I currently sort two bound fields. How do I include the Function
TotalTime() in my sort order, and how do I specify
Ascending/Descending
on this function? Where does the "code" go to do this?

For example, I'd like to sort as follows:
Field1 Ascending
TotalTime Ascending
Field3 Ascending

Thanks,
Bernie

Don't call it from a textbox - call it in the Query itself. Put a call
to the function in an vacant Field cell:

TTime: TotalTime()

and simply sort by this field.

John W. Vinson[MVP]

 

[Klatuu]

The function has to be in a standard module.

Thanks, Jon.

I did exactly what you said, and I am now getting:
"Undefined Function 'TotalTime' in expression" when I try to preview the
SQL Statement: Query Builder.

My Function is shown below:
Public Function TotalTime()
Dim DTimeFactor, RTimeFactor, FromA, ToA
FromA = [tblAirports.TimeZone]
ToA = [tblAirports_1.TimeZone]
If FromA = "Hawaii" Then DTimeFactor = 1
If FromA = "Alaska" Then DTimeFactor = 2
If FromA = "Pacific" Then DTimeFactor = 3
If FromA = "Mountain" Then DTimeFactor = 4
If FromA = "Central" Then DTimeFactor = 5
If FromA = "Eastern" Then DTimeFactor = 6
If ToA = "Hawaii" Then RTimeFactor = 1
If ToA = "Alaska" Then RTimeFactor = 2
If ToA = "Pacific" Then RTimeFactor = 3
If ToA = "Mountain" Then RTimeFactor = 4
If ToA = "Central" Then RTimeFactor = 5
If ToA = "Eastern" Then RTimeFactor = 6
TotalTime = DateAdd("h", RTimeFactor, DepartTime) - DateAdd("h",
DTimeFactor, ArrivalTime)
TotalTime = Format(TotalTime, "h:nn")
End Function

I have tried to break this function up into its components and enter it
into the SQL Statement as individual fields, like this:

DTF:
IIf([tblAirports.TimeZone]="Hawaii",1,IIf([tblAirports.TimeZone]="Alaska",2,IIf([tblAirports.TimeZone]="Pacific",3,IIf([tblAirports.TimeZone]="Mountain",4,IIf([tblAirports.TimeZone]="Central",5,IIf([tblAirports.TimeZone]="Eastern",6))))))

RTF:
IIf([tblAirports].[TimeZone_1]="Hawaii",1,IIf([tblAirports].[TimeZone_1]="Alaska",2,IIf([tblAirports].[TimeZone_1]="Pacific",3,IIf([tblAirports].[TimeZone_1]="Mountain",4,IIf([tblAirports].[TimeZone_1]="Central",5,IIf([tblAirports].[TimeZone_1]="Eastern",6))))))

Now if I preview this, I'm asked for the Parameter Value for
[tblAirports].[TimeZone_1].

Do you have another suggestion?

I have a Public Function TotalTime(), which I call from a Text Box:
=[TotalTime]

I currently sort two bound fields. How do I include the Function
TotalTime() in my sort order, and how do I specify
Ascending/Descending
on this function? Where does the "code" go to do this?

For example, I'd like to sort as follows:
Field1 Ascending
TotalTime Ascending
Field3 Ascending

Thanks,
Bernie

Don't call it from a textbox - call it in the Query itself. Put a call
to the function in an vacant Field cell:

TTime: TotalTime()

and simply sort by this field.

John W. Vinson[MVP]

 

[bw]

I put the function in a standard module. But then the function doesn't
recognize [tblAirports.TimeZone], no matter how I reference it (same
with [tblAirports_1]).
For example:
Forms!frmFltSchedule![tblAirports.TimeZone] and all possible
combinations does not recognize tblAirports.
I don't know what to do...

Bernie

The function has to be in a standard module.

Thanks, Jon.

I did exactly what you said, and I am now getting:
"Undefined Function 'TotalTime' in expression" when I try to preview
the
SQL Statement: Query Builder.

My Function is shown below:
Public Function TotalTime()
Dim DTimeFactor, RTimeFactor, FromA, ToA
FromA = [tblAirports.TimeZone]
ToA = [tblAirports_1.TimeZone]
If FromA = "Hawaii" Then DTimeFactor = 1
If FromA = "Alaska" Then DTimeFactor = 2
If FromA = "Pacific" Then DTimeFactor = 3
If FromA = "Mountain" Then DTimeFactor = 4
If FromA = "Central" Then DTimeFactor = 5
If FromA = "Eastern" Then DTimeFactor = 6
If ToA = "Hawaii" Then RTimeFactor = 1
If ToA = "Alaska" Then RTimeFactor = 2
If ToA = "Pacific" Then RTimeFactor = 3
If ToA = "Mountain" Then RTimeFactor = 4
If ToA = "Central" Then RTimeFactor = 5
If ToA = "Eastern" Then RTimeFactor = 6
TotalTime = DateAdd("h", RTimeFactor, DepartTime) - DateAdd("h",
DTimeFactor, ArrivalTime)
TotalTime = Format(TotalTime, "h:nn")
End Function

I have tried to break this function up into its components and enter
it
into the SQL Statement as individual fields, like this:

DTF:
IIf([tblAirports.TimeZone]="Hawaii",1,IIf([tblAirports.TimeZone]="Alaska",2,IIf([tblAirports.TimeZone]="Pacific",3,IIf([tblAirports.TimeZone]="Mountain",4,IIf([tblAirports.TimeZone]="Central",5,IIf([tblAirports.TimeZone]="Eastern",6))))))

RTF:
IIf([tblAirports].[TimeZone_1]="Hawaii",1,IIf([tblAirports].[TimeZone_1]="Alaska",2,IIf([tblAirports].[TimeZone_1]="Pacific",3,IIf([tblAirports].[TimeZone_1]="Mountain",4,IIf([tblAirports].[TimeZone_1]="Central",5,IIf([tblAirports].[TimeZone_1]="Eastern",6))))))

Now if I preview this, I'm asked for the Parameter Value for
[tblAirports].[TimeZone_1].

Do you have another suggestion?

I have a Public Function TotalTime(), which I call from a Text Box:
=[TotalTime]

I currently sort two bound fields. How do I include the Function
TotalTime() in my sort order, and how do I specify
Ascending/Descending
on this function? Where does the "code" go to do this?

For example, I'd like to sort as follows:
Field1 Ascending
TotalTime Ascending
Field3 Ascending

Thanks,
Bernie



Don't call it from a textbox - call it in the Query itself. Put a
call
to the function in an vacant Field cell:

TTime: TotalTime()

and simply sort by this field.

John W. Vinson[MVP]

 

[Klatuu]

You can't just reference a table and field like you are doing in your
function. You need to use, probably a DLookup to determine what the time
factor is, but I can't tell from your other code how you would know what to
search for. If it is a field in the query, you need to pass it to the
function so it will know what to look up. Also, the function has to return a
value. The variables you are assigning values to will just disappear after
each record is processed.

To use a function in a query you have to do these thing:
It has to be in a standard module.
You have to pass it a field value.
You use it in a Calculated field to return a value.

You really need to pass this function 3 things:
The airport, the departure time and the arrival time.
Based on your current query, it doesn't matter which you use, they both
return the same thing. That, however, doesn't seem logical. If you are
traveling west to east, it should be +1 and east to west should be -1.

Why do you have two airport tables that seem to be indentical in nature.

If you can post back with business rules on what you want to do, maybe we
can help you get this set up correctly.

I put the function in a standard module. But then the function doesn't
recognize [tblAirports.TimeZone], no matter how I reference it (same
with [tblAirports_1]).
For example:
Forms!frmFltSchedule![tblAirports.TimeZone] and all possible
combinations does not recognize tblAirports.
I don't know what to do...

Bernie

The function has to be in a standard module.

Thanks, Jon.

I did exactly what you said, and I am now getting:
"Undefined Function 'TotalTime' in expression" when I try to preview
the
SQL Statement: Query Builder.

My Function is shown below:
Public Function TotalTime()
Dim DTimeFactor, RTimeFactor, FromA, ToA
FromA = [tblAirports.TimeZone]
ToA = [tblAirports_1.TimeZone]
If FromA = "Hawaii" Then DTimeFactor = 1
If FromA = "Alaska" Then DTimeFactor = 2
If FromA = "Pacific" Then DTimeFactor = 3
If FromA = "Mountain" Then DTimeFactor = 4
If FromA = "Central" Then DTimeFactor = 5
If FromA = "Eastern" Then DTimeFactor = 6
If ToA = "Hawaii" Then RTimeFactor = 1
If ToA = "Alaska" Then RTimeFactor = 2
If ToA = "Pacific" Then RTimeFactor = 3
If ToA = "Mountain" Then RTimeFactor = 4
If ToA = "Central" Then RTimeFactor = 5
If ToA = "Eastern" Then RTimeFactor = 6
TotalTime = DateAdd("h", RTimeFactor, DepartTime) - DateAdd("h",
DTimeFactor, ArrivalTime)
TotalTime = Format(TotalTime, "h:nn")
End Function

I have tried to break this function up into its components and enter
it
into the SQL Statement as individual fields, like this:

DTF:
IIf([tblAirports.TimeZone]="Hawaii",1,IIf([tblAirports.TimeZone]="Alaska",2,IIf([tblAirports.TimeZone]="Pacific",3,IIf([tblAirports.TimeZone]="Mountain",4,IIf([tblAirports.TimeZone]="Central",5,IIf([tblAirports.TimeZone]="Eastern",6))))))

RTF:
IIf([tblAirports].[TimeZone_1]="Hawaii",1,IIf([tblAirports].[TimeZone_1]="Alaska",2,IIf([tblAirports].[TimeZone_1]="Pacific",3,IIf([tblAirports].[TimeZone_1]="Mountain",4,IIf([tblAirports].[TimeZone_1]="Central",5,IIf([tblAirports].[TimeZone_1]="Eastern",6))))))

Now if I preview this, I'm asked for the Parameter Value for
[tblAirports].[TimeZone_1].

Do you have another suggestion?


I have a Public Function TotalTime(), which I call from a Text Box:
=[TotalTime]

I currently sort two bound fields. How do I include the Function
TotalTime() in my sort order, and how do I specify
Ascending/Descending
on this function? Where does the "code" go to do this?

For example, I'd like to sort as follows:
Field1 Ascending
TotalTime Ascending
Field3 Ascending

Thanks,
Bernie



Don't call it from a textbox - call it in the Query itself. Put a
call
to the function in an vacant Field cell:

TTime: TotalTime()

and simply sort by this field.

John W. Vinson[MVP]

 

[bw]

Okay, let's try something completely different.
I want to abandon my effort to call a function altogether. I'll try to
realize the same result by putting the information into individual
fields within the SQL Statement for the Record Source of the form (which
is what I probably should have done from the beginning).

So these are the 5 fields I Added:

FromA: [tblAirports.TimeZone]

ToA: [tblAirports_1.TimeZone]

DTimeFactor:
IIf([FromA]="Hawaii",1,IIf([FromA]="Alaska",2,IIf([FromA]="Pacific",3,IIf([FromA]="Mountain",4,IIf([FromA]="Central",5,IIf([FromA]="Eastern",6))))))

RTimeFactor:
IIf([ToA]="Hawaii",1,IIf([ToA]="Alaska",2,IIf([ToA]="Pacific",3,IIf([ToA]="Mountain",4,IIf([ToA]="Central",5,IIf([ToA]="Eastern",6))))))

TotalTime:
Format(DateAdd("h",[RTimeFactor],[DepartTime])-DateAdd("h",[DTimeFactor],[ArrivalTime]),"h:nn")

Now if I use TotalTime as the record source for my text field, I Get
Exactly The Result I Want! HOWEVER, If I then specify that TotalTime
should be sorted Ascending, I then get errors as follows:
1. I am asked to enter a Parameter Value for RTimeFactor.
2. I am asked to enter a Parameter Value for DTimeFactor.
3. I get an error from Microsoft Access as follows: "This expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many complicated elements. Try
simplfying the expression by assigning parts of the expression to
ariables."

How would I change my expressions to make them simpler? Why does it
work okay, until I sort?

I appreciate your help.
Bernie

You can't just reference a table and field like you are doing in your
function. You need to use, probably a DLookup to determine what the
time
factor is, but I can't tell from your other code how you would know
what to
search for. If it is a field in the query, you need to pass it to the
function so it will know what to look up. Also, the function has to
return a
value. The variables you are assigning values to will just disappear
after
each record is processed.

To use a function in a query you have to do these thing:
It has to be in a standard module.
You have to pass it a field value.
You use it in a Calculated field to return a value.

You really need to pass this function 3 things:
The airport, the departure time and the arrival time.
Based on your current query, it doesn't matter which you use, they
both
return the same thing. That, however, doesn't seem logical. If you
are
traveling west to east, it should be +1 and east to west should be -1.

Why do you have two airport tables that seem to be indentical in
nature.

If you can post back with business rules on what you want to do, maybe
we
can help you get this set up correctly.

I put the function in a standard module. But then the function
doesn't
recognize [tblAirports.TimeZone], no matter how I reference it (same
with [tblAirports_1]).
For example:
Forms!frmFltSchedule![tblAirports.TimeZone] and all possible
combinations does not recognize tblAirports.
I don't know what to do...

Bernie

The function has to be in a standard module.

:


Thanks, Jon.

I did exactly what you said, and I am now getting:
"Undefined Function 'TotalTime' in expression" when I try to
preview
the
SQL Statement: Query Builder.

My Function is shown below:
Public Function TotalTime()
Dim DTimeFactor, RTimeFactor, FromA, ToA
FromA = [tblAirports.TimeZone]
ToA = [tblAirports_1.TimeZone]
If FromA = "Hawaii" Then DTimeFactor = 1
If FromA = "Alaska" Then DTimeFactor = 2
If FromA = "Pacific" Then DTimeFactor = 3
If FromA = "Mountain" Then DTimeFactor = 4
If FromA = "Central" Then DTimeFactor = 5
If FromA = "Eastern" Then DTimeFactor = 6
If ToA = "Hawaii" Then RTimeFactor = 1
If ToA = "Alaska" Then RTimeFactor = 2
If ToA = "Pacific" Then RTimeFactor = 3
If ToA = "Mountain" Then RTimeFactor = 4
If ToA = "Central" Then RTimeFactor = 5
If ToA = "Eastern" Then RTimeFactor = 6
TotalTime = DateAdd("h", RTimeFactor, DepartTime) -
DateAdd("h",
DTimeFactor, ArrivalTime)
TotalTime = Format(TotalTime, "h:nn")
End Function

I have tried to break this function up into its components and
enter
it
into the SQL Statement as individual fields, like this:

DTF:
IIf([tblAirports.TimeZone]="Hawaii",1,IIf([tblAirports.TimeZone]="Alaska",2,IIf([tblAirports.TimeZone]="Pacific",3,IIf([tblAirports.TimeZone]="Mountain",4,IIf([tblAirports.TimeZone]="Central",5,IIf([tblAirports.TimeZone]="Eastern",6))))))

RTF:
IIf([tblAirports].[TimeZone_1]="Hawaii",1,IIf([tblAirports].[TimeZone_1]="Alaska",2,IIf([tblAirports].[TimeZone_1]="Pacific",3,IIf([tblAirports].[TimeZone_1]="Mountain",4,IIf([tblAirports].[TimeZone_1]="Central",5,IIf([tblAirports].[TimeZone_1]="Eastern",6))))))

Now if I preview this, I'm asked for the Parameter Value for
[tblAirports].[TimeZone_1].

Do you have another suggestion?


message
I have a Public Function TotalTime(), which I call from a Text
Box:
=[TotalTime]

I currently sort two bound fields. How do I include the
Function
TotalTime() in my sort order, and how do I specify
Ascending/Descending
on this function? Where does the "code" go to do this?

For example, I'd like to sort as follows:
Field1 Ascending
TotalTime Ascending
Field3 Ascending

Thanks,
Bernie



Don't call it from a textbox - call it in the Query itself. Put
a
call
to the function in an vacant Field cell:

TTime: TotalTime()

and simply sort by this field.

John W. Vinson[MVP]

 

[Klatuu]

Sorting on a calculated field can be problematic. Try setting the order you
want in the form.

Now, I still don't understand the two airport tables and I also don't
believe the math is correct when you are adjusting for time zones.

Here is what I mean. If I depart from New York (EST) at 1:00 PM and I fly
to Chicago(CST) and it takes 1 hour, then I land in Chicago at 2:00 PM EST,
but the local time is 1:00PM. Taking the difference between the 2, it took
me 0 hours. Then I add an hour, so the total time is correct; however, on
the return trip, I leave Chicago at 8:00 AM (CST) and will arrive in New Yort
at 10:00 AM (EST). Now it looks like the flight two 2 hours and if I add
another hour, it is 3 hours, but if I subract an hour, it is 1 hour, which is
correct. So, my point is If your are traveling East to West, you add an hour
per zone and West to East, sutract an hour per zone.

For now, we will ignore those places where Daylight Savings Time is not
used.

Okay, let's try something completely different.
I want to abandon my effort to call a function altogether. I'll try to
realize the same result by putting the information into individual
fields within the SQL Statement for the Record Source of the form (which
is what I probably should have done from the beginning).

So these are the 5 fields I Added:

FromA: [tblAirports.TimeZone]

ToA: [tblAirports_1.TimeZone]

DTimeFactor:
IIf([FromA]="Hawaii",1,IIf([FromA]="Alaska",2,IIf([FromA]="Pacific",3,IIf([FromA]="Mountain",4,IIf([FromA]="Central",5,IIf([FromA]="Eastern",6))))))

RTimeFactor:
IIf([ToA]="Hawaii",1,IIf([ToA]="Alaska",2,IIf([ToA]="Pacific",3,IIf([ToA]="Mountain",4,IIf([ToA]="Central",5,IIf([ToA]="Eastern",6))))))

TotalTime:
Format(DateAdd("h",[RTimeFactor],[DepartTime])-DateAdd("h",[DTimeFactor],[ArrivalTime]),"h:nn")

Now if I use TotalTime as the record source for my text field, I Get
Exactly The Result I Want! HOWEVER, If I then specify that TotalTime
should be sorted Ascending, I then get errors as follows:
1. I am asked to enter a Parameter Value for RTimeFactor.
2. I am asked to enter a Parameter Value for DTimeFactor.
3. I get an error from Microsoft Access as follows: "This expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many complicated elements. Try
simplfying the expression by assigning parts of the expression to
ariables."

How would I change my expressions to make them simpler? Why does it
work okay, until I sort?

I appreciate your help.
Bernie

You can't just reference a table and field like you are doing in your
function. You need to use, probably a DLookup to determine what the
time
factor is, but I can't tell from your other code how you would know
what to
search for. If it is a field in the query, you need to pass it to the
function so it will know what to look up. Also, the function has to
return a
value. The variables you are assigning values to will just disappear
after
each record is processed.

To use a function in a query you have to do these thing:
It has to be in a standard module.
You have to pass it a field value.
You use it in a Calculated field to return a value.

You really need to pass this function 3 things:
The airport, the departure time and the arrival time.
Based on your current query, it doesn't matter which you use, they
both
return the same thing. That, however, doesn't seem logical. If you
are
traveling west to east, it should be +1 and east to west should be -1.

Why do you have two airport tables that seem to be indentical in
nature.

If you can post back with business rules on what you want to do, maybe
we
can help you get this set up correctly.

I put the function in a standard module. But then the function
doesn't
recognize [tblAirports.TimeZone], no matter how I reference it (same
with [tblAirports_1]).
For example:
Forms!frmFltSchedule![tblAirports.TimeZone] and all possible
combinations does not recognize tblAirports.
I don't know what to do...

Bernie


The function has to be in a standard module.

:


Thanks, Jon.

I did exactly what you said, and I am now getting:
"Undefined Function 'TotalTime' in expression" when I try to
preview
the
SQL Statement: Query Builder.

My Function is shown below:
Public Function TotalTime()
Dim DTimeFactor, RTimeFactor, FromA, ToA
FromA = [tblAirports.TimeZone]
ToA = [tblAirports_1.TimeZone]
If FromA = "Hawaii" Then DTimeFactor = 1
If FromA = "Alaska" Then DTimeFactor = 2
If FromA = "Pacific" Then DTimeFactor = 3
If FromA = "Mountain" Then DTimeFactor = 4
If FromA = "Central" Then DTimeFactor = 5
If FromA = "Eastern" Then DTimeFactor = 6
If ToA = "Hawaii" Then RTimeFactor = 1
If ToA = "Alaska" Then RTimeFactor = 2
If ToA = "Pacific" Then RTimeFactor = 3
If ToA = "Mountain" Then RTimeFactor = 4
If ToA = "Central" Then RTimeFactor = 5
If ToA = "Eastern" Then RTimeFactor = 6
TotalTime = DateAdd("h", RTimeFactor, DepartTime) -
DateAdd("h",
DTimeFactor, ArrivalTime)
TotalTime = Format(TotalTime, "h:nn")
End Function

I have tried to break this function up into its components and
enter
it
into the SQL Statement as individual fields, like this:

DTF:
IIf([tblAirports.TimeZone]="Hawaii",1,IIf([tblAirports.TimeZone]="Alaska",2,IIf([tblAirports.TimeZone]="Pacific",3,IIf([tblAirports.TimeZone]="Mountain",4,IIf([tblAirports.TimeZone]="Central",5,IIf([tblAirports.TimeZone]="Eastern",6))))))

RTF:
IIf([tblAirports].[TimeZone_1]="Hawaii",1,IIf([tblAirports].[TimeZone_1]="Alaska",2,IIf([tblAirports].[TimeZone_1]="Pacific",3,IIf([tblAirports].[TimeZone_1]="Mountain",4,IIf([tblAirports].[TimeZone_1]="Central",5,IIf([tblAirports].[TimeZone_1]="Eastern",6))))))

Now if I preview this, I'm asked for the Parameter Value for
[tblAirports].[TimeZone_1].

Do you have another suggestion?


message
I have a Public Function TotalTime(), which I call from a Text
Box:
=[TotalTime]

I currently sort two bound fields. How do I include the
Function
TotalTime() in my sort order, and how do I specify
Ascending/Descending
on this function? Where does the "code" go to do this?

For example, I'd like to sort as follows:
Field1 Ascending
TotalTime Ascending
Field3 Ascending

Thanks,
Bernie



Don't call it from a textbox - call it in the Query itself. Put
a
call
to the function in an vacant Field cell:

TTime: TotalTime()

and simply sort by this field.

John W. Vinson[MVP]