Re: Include Function in Sort

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


"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:1B00F305-1CB0-4F18-8822-7C729A0C6DE2@xxxxxxxxxxxxxxxx
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.

"bw" wrote:

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


"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0334A39A-E0D6-4A1E-93D5-DFD12347140C@xxxxxxxxxxxxxxxx
> The function has to be in a standard module.
>
> "bw" wrote:
>
>>
>> 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?
>>
>>
>> "John Vinson" <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in >> message
>> news:v1qdk2lcm281lh8gfr5n7p4l1kljktva84@xxxxxxxxxx
>> > On Mon, 30 Oct 2006 22:44:16 -0700, "bw" <iamnu@xxxxxxxxxxxx>
>> > wrote:
>> >
>> >>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]
>> >
>> >
>>
>>



.

Relevant Pages

  • Re: Include Function in Sort
    ... You can't just reference a table and field like you are doing in your ... If it is a field in the query, you need to pass it to the ... Public Function TotalTime() ... I currently sort two bound fields. ...
    (microsoft.public.access.formscoding)
  • Re: Include Function in Sort
    ... Now if I use TotalTime as the record source for my text field, ... If it is a field in the query, you need to pass it to the ... The airport, the departure time and the arrival time. ... I currently sort two bound fields. ...
    (microsoft.public.access.formscoding)
  • Re: Authentication Problem Is Making Me A Little Nuts
    ... an obvious difference is that the first box was running Win2K3 and ... if I specify an actual DC name in the LDAP query ... then it works okay. ... > This was working just fine, but when I moved my app to a different server ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Add New Record
    ... You can enter a new record if you open the query directly. ... You are able to edit existing records okay in this form. ... Under some conditions, the converter can fail and you end up with a Label control bound to a field, which doesn't work at all of course. ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.forms)
  • Data Dependencies Function
    ... I created a table, Table1, with 2 fields, Task and Done. ... Using this query: ... Okay, lets look to see how it works. ... then it is a parent. ...
    (microsoft.public.access.modulesdaovba)