Include Function in Sort

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bw - 31 Oct 2006 06:44 GMT

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

Reply to this Message

Duane Hookom - 31 Oct 2006 07:03 GMT

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?

Signature

Duane Hookom
MS Access MVP

>I have a Public Function TotalTime(), which I call from a Text Box:
>=[TotalTime]
[quoted text clipped - 10 lines]
> Thanks,
> Bernie

Reply to this Message

John Vinson - 31 Oct 2006 07:08 GMT

>I have a Public Function TotalTime(), which I call from a Text Box:
>=[TotalTime]
[quoted text clipped - 10 lines]
>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]

Reply to this Message

bw - 31 Oct 2006 12:38 GMT

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]
[quoted text clipped - 20 lines]
>
> John W. Vinson[MVP]

Reply to this Message

Klatuu - 31 Oct 2006 15:58 GMT

The function has to be in a standard module.

> Thanks, Jon.
>
[quoted text clipped - 62 lines]
> >
> > John W. Vinson[MVP]

Reply to this Message

bw - 31 Oct 2006 17:18 GMT

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.
>
[quoted text clipped - 67 lines]
>> >
>> > John W. Vinson[MVP]

Reply to this Message

Klatuu - 31 Oct 2006 18:34 GMT

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
[quoted text clipped - 77 lines]
> >> >
> >> > John W. Vinson[MVP]

Reply to this Message

bw - 31 Oct 2006 20:06 GMT

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
[quoted text clipped - 116 lines]
>> >> >
>> >> > John W. Vinson[MVP]

Reply to this Message

Klatuu - 31 Oct 2006 20:51 GMT

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
[quoted text clipped - 154 lines]
> >> >> >
> >> >> > John W. Vinson[MVP]

Reply to this Message