OrderBy creates calculation error - URGENT

[Cadburys]

Hi

I have continuous form where the coloum headings are in the form header -
each heading has the onclick event to sort that column in ascending order
using OrderBy (the syntax is correct). In the form footer I have a text box
that calculates one column and another text box that counts the number of
records.

When the form opens the calculations are fine but once you use the orderby
the calculation text boxes just show Error#.

Is it not possible to retain the calculations and use OrderBy?

PLEASE HELP!!

Thanks
Cadburys

 

[Allen Browne]

Can you provide some more info about this?

What version of Access?

What kind of field are you attempting to sort on? Is it:
- A field in your table? If so, what data type?
- A combo box? If so, is the visible column the bound column?
- A calculated field in a query? If so, post the SQL statement.
- A text box that has an expression in its Control Source?
If so, post the expression.

How are you performing the sort?
- Code you wrote?
- The right-click on the column heading?

Is the form in Continuous view or Datasheet view?

What is the data?
- Access tables in this same mdb file?
- Linked Access tables?
- Linked tables to another program? (SQL Server? Excel? ...?)

 

[Cadburys]

Hi Allen

thanks for the help

using version 2003

following query used for a form:

SELECT [qryJobs-10DeadlineDate].fldDeadlineDate,
[qryJobs-10DeadlineDate].fldDeadlineTime,
[qryJobs-10DeadlineDate].fldJobNumber, [qryJobs-10DeadlineDate].fldTypist,
[qryJobs-10DeadlineDate].fldDateDF, [qryJobs-10DeadlineDate].fldPriority,
[qryJobs-10DeadlineDate].fldTimeDF, [qryJobs-10DeadlineDate].fldSource,
[qryJobs-10DeadlineDate].fldProofreader, [qryJobs-10DeadlineDate].fldNameDF,
[qryJobs-10DeadlineDate].fldLengthDF,
[qryJobs-10DeadlineDate].fldClientStaffName,
[qryJobs-10DeadlineDate].fldClientStaffDept,
[qryJobs-10DeadlineDate].fldOffice, [qryJobs-10DeadlineDate].fldClient,
[qryJobs-10DeadlineDate].fldStatus, [qryJobs-10DeadlineDate].fldTimeEmailed,
[qryJobs-10DeadlineDate].fldReturnDate,
[qryJobs-10DeadlineDate].fldReturnTime, [qryJobs-10DeadlineDate].fldTeam,
[qryJobs-10DeadlineDate].DFLSecs, [qryJobs-10DeadlineDate].Status,
[qryJobs-10DeadlineDate].Client, [qryJobs-10DeadlineDate].Typist,
[qryJobs-10DeadlineDate].Team, [qryJobs-10DeadlineDate].StartDate,
[qryJobs-10DeadlineDate].EndDate, [qryJobs-10DeadlineDate].fldUploadDuration,
[qryJobs-10DeadlineDate].fldAdminPerson, Forms!frmStartNew!RDateStart AS
RDateStart, Forms!frmStartNew!RDateEnd AS RDateEnd,
[qryJobs-10DeadlineDate].fldCopyTyping,
[qryJobs-10DeadlineDate].fldCTNoOfPages,
[qryJobs-10DeadlineDate].fldCopyTypingDocName,
[qryJobs-10DeadlineDate].fldIncomplete,
[qryJobs-10DeadlineDate].fldCommentProofReader,
[qryJobs-10DeadlineDate].fldDateTyped,
[qryJobs-10DeadlineDate].fldDateProofed,
[qryJobs-10DeadlineDate].fldPMSLAAttained, [qryJobs-10DeadlineDate].fldCTTag,
[qryJobs-10DeadlineDate].fldSLATag,
[qryJobs-10DeadlineDate].fldCommentTypist,
[qryJobs-10DeadlineDate].fldCommentClient
FROM [qryJobs-10DeadlineDate]
WHERE ((([qryJobs-10DeadlineDate].fldStatus)<>[Forms]![frmStartNew]![txtRTC]
And ([qryJobs-10DeadlineDate].fldStatus)<>"cancelled") AND
(([Forms]![frmStartNew]![RDateStart])<=[fldReturnDate] Or
([Forms]![frmStartNew]![RDateStart]) Is Null) AND
(([Forms]![frmStartNew]![RDateEnd])>=[fldReturnDate] Or
([Forms]![frmStartNew]![RDateEnd]) Is Null))
ORDER BY [qryJobs-10DeadlineDate].fldDeadlineDate,
[qryJobs-10DeadlineDate].fldDeadlineTime;

at the bottom of the continuous form I have a text box with the following 3
calculations:

DFSum = Sum([DFLSecs]) 'This calculates the total of the DFLSecs column in
the form - DFLSecs is a number field in the underlying table
TotHMSDFLSum = [DFSum]\3600 & Format(([DFSum]\60) Mod 60,"\:00") &
Format([DFSum] Mod 60,"\:00") 'Formats DFSum figure to hh:mm:ss
TotJobs = Count([fldJobNumber] 'to calculate the number of records.

Sorting is done by clicking on a column heading (there are eight headings)

Code as follows:

Private Sub lblClient_Click()
Me.OrderBy = "fldClient"
Me.OrderByOn = True
End Sub

Private Sub lblClient_DblClick(Cancel As Integer)
Me.OrderBy = "fldClient DESC"
Me.OrderByOn = True
End Sub

Data is currently tables withn the database but will then be linked tables
to SQL with access front end.

As I said when the form is initially opened the calculations are fine but as
soon as a sort order is applied the calculations become Error#. The only way
to restore them is to go into the design view of the form and open up the
control source query - run it, close and return to design view. Using the
shortcut menus produces the same error.

I have tried removing the sorting done within the query but this did not
make a difference. Is a calculation affected by resorting?

Thank you for your help.

Nicky

 

[Allen Browne]

Suggestions:

1. In query design view, uncheck the Show box under these two fields:
RDateStart: Forms!frmStartNew!RDateStart
RDateEnd: Forms!frmStartNew!RDateEnd
This could avoid a circular dependency issue.

2. Set the Format property of your DFSum text box to:
General Number
so that Access knows this is a Number. This will help it understand the data
type (which it must know for TOTHMSDFLSum.)

3. Make sure there is nothing in the Format property of TotHMSDFLSum. (The
expression yields a string, not a number.)

4. If RDateStart and RDateEnd are unbound, set their Format property to:
General Date
so Access understands the data type.

5. In query design view, declare your 2 parameters.
Choose Parameters on the Query menu, and enter 2 rows in the dialog:
[Forms]![frmStartNew]![RDateStart] Date/Time
[Forms]![frmStartNew]![RDateEnd] Date/Time
This will ensure the query understands them correctly.

6. You could simplify TotJobs to:
=Count("*")
and set its Format to General Number.

7. In case the form is dirty, explicitly save before trying to apply the
sort, e.g.:
Private Sub lblClient_Click()
If Me.Dirty Then Me.Dirty = False
Me.OrderBy = "fldClient"
Me.OrderByOn = True
End Sub

8. If it still generates an error, try explicitly recalculating (after it
shows #Error) by opening the Immediate Window (Ctrl+G) and entering:
Forms!frmStartNew.Recalc

9. If it still misbehaves, try deleting TotHMSDFLSum and TotJobs, until you
get DFSum working. You can then add them back. (This is because Access will
quit trying to calculate once if finds a control that cannot be calculated,
so the fault can be other than where you see the #Error.)

10. You did not mention your verion or service pack. You may need a JET or
Office service pack (esp. if this is Access 2000.) Available from:
http://support.microsoft.com/sp/

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

thanks for the help

using version 2003

following query used for a form:

SELECT [qryJobs-10DeadlineDate].fldDeadlineDate,
[qryJobs-10DeadlineDate].fldDeadlineTime,
[qryJobs-10DeadlineDate].fldJobNumber, [qryJobs-10DeadlineDate].fldTypist,
[qryJobs-10DeadlineDate].fldDateDF, [qryJobs-10DeadlineDate].fldPriority,
[qryJobs-10DeadlineDate].fldTimeDF, [qryJobs-10DeadlineDate].fldSource,
[qryJobs-10DeadlineDate].fldProofreader,
[qryJobs-10DeadlineDate].fldNameDF,
[qryJobs-10DeadlineDate].fldLengthDF,
[qryJobs-10DeadlineDate].fldClientStaffName,
[qryJobs-10DeadlineDate].fldClientStaffDept,
[qryJobs-10DeadlineDate].fldOffice, [qryJobs-10DeadlineDate].fldClient,
[qryJobs-10DeadlineDate].fldStatus,
[qryJobs-10DeadlineDate].fldTimeEmailed,
[qryJobs-10DeadlineDate].fldReturnDate,
[qryJobs-10DeadlineDate].fldReturnTime, [qryJobs-10DeadlineDate].fldTeam,
[qryJobs-10DeadlineDate].DFLSecs, [qryJobs-10DeadlineDate].Status,
[qryJobs-10DeadlineDate].Client, [qryJobs-10DeadlineDate].Typist,
[qryJobs-10DeadlineDate].Team, [qryJobs-10DeadlineDate].StartDate,
[qryJobs-10DeadlineDate].EndDate,
[qryJobs-10DeadlineDate].fldUploadDuration,
[qryJobs-10DeadlineDate].fldAdminPerson, Forms!frmStartNew!RDateStart AS
RDateStart, Forms!frmStartNew!RDateEnd AS RDateEnd,
[qryJobs-10DeadlineDate].fldCopyTyping,
[qryJobs-10DeadlineDate].fldCTNoOfPages,
[qryJobs-10DeadlineDate].fldCopyTypingDocName,
[qryJobs-10DeadlineDate].fldIncomplete,
[qryJobs-10DeadlineDate].fldCommentProofReader,
[qryJobs-10DeadlineDate].fldDateTyped,
[qryJobs-10DeadlineDate].fldDateProofed,
[qryJobs-10DeadlineDate].fldPMSLAAttained,
[qryJobs-10DeadlineDate].fldCTTag,
[qryJobs-10DeadlineDate].fldSLATag,
[qryJobs-10DeadlineDate].fldCommentTypist,
[qryJobs-10DeadlineDate].fldCommentClient
FROM [qryJobs-10DeadlineDate]
WHERE
((([qryJobs-10DeadlineDate].fldStatus)<>[Forms]![frmStartNew]![txtRTC]
And ([qryJobs-10DeadlineDate].fldStatus)<>"cancelled") AND
(([Forms]![frmStartNew]![RDateStart])<=[fldReturnDate] Or
([Forms]![frmStartNew]![RDateStart]) Is Null) AND
(([Forms]![frmStartNew]![RDateEnd])>=[fldReturnDate] Or
([Forms]![frmStartNew]![RDateEnd]) Is Null))
ORDER BY [qryJobs-10DeadlineDate].fldDeadlineDate,
[qryJobs-10DeadlineDate].fldDeadlineTime;

at the bottom of the continuous form I have a text box with the following
3
calculations:

DFSum = Sum([DFLSecs]) 'This calculates the total of the DFLSecs column in
the form - DFLSecs is a number field in the underlying table
TotHMSDFLSum = [DFSum]\3600 & Format(([DFSum]\60) Mod 60,"\:00") &
Format([DFSum] Mod 60,"\:00") 'Formats DFSum figure to hh:mm:ss
TotJobs = Count([fldJobNumber] 'to calculate the number of records.

Sorting is done by clicking on a column heading (there are eight headings)

Code as follows:

Private Sub lblClient_Click()
Me.OrderBy = "fldClient"
Me.OrderByOn = True
End Sub

Private Sub lblClient_DblClick(Cancel As Integer)
Me.OrderBy = "fldClient DESC"
Me.OrderByOn = True
End Sub

Data is currently tables withn the database but will then be linked tables
to SQL with access front end.

As I said when the form is initially opened the calculations are fine but
as
soon as a sort order is applied the calculations become Error#. The only
way
to restore them is to go into the design view of the form and open up the
control source query - run it, close and return to design view. Using the
shortcut menus produces the same error.

I have tried removing the sorting done within the query but this did not
make a difference. Is a calculation affected by resorting?

Thank you for your help.

Nicky


--
Cheers

Can you provide some more info about this?

What version of Access?

What kind of field are you attempting to sort on? Is it:
- A field in your table? If so, what data type?
- A combo box? If so, is the visible column the bound column?
- A calculated field in a query? If so, post the SQL statement.
- A text box that has an expression in its Control Source?
If so, post the expression.

How are you performing the sort?
- Code you wrote?
- The right-click on the column heading?

Is the form in Continuous view or Datasheet view?

What is the data?
- Access tables in this same mdb file?
- Linked Access tables?
- Linked tables to another program? (SQL Server? Excel? ...?)