List box (and queries) not sorting dates or showing correct headers

[unhinged]

G'day all,

I have inherited a system with a search function screen that is acting
weird. There are actually two search screens, both constructed in a
similar fashion, but the first does what it is supposed to do and the
second is misbehaving.

The form has a bunch of unbound controls allowing the user to enter
search criteria. The only mandatory criteria is the sort order, which
is set from a combo box. The data source for the combo box is a table,
sys_GAPSSortBy, which contains a list of query names and a user-
friendly label for use by the combo box. When the user clicks the
"Search" button, the rowsource for the list box is updated with the
query name from sys_GAPSSortBy, and then a requery is performed on the
list box.

The queries show the same data but have different sort orders - why
there isn't just one query that gets modified in code, I don't know.
There are two queries which sort on a formatted date field (ascending
and descending) and they sort the date as though it was text. With the
working search screen, I included the unmodified date field and sorted
on that without displaying it and that now works. Not so with the GAPS
search form, even though I have modified the queries in exactly the
same fashion.

I also do not get the correct headers for these queries in the list
box, and checking has revealed that they are also incorrect in
datasheet view (and so is the sort order... weird!).

Does anybody have any clue what the problem is here? If it is
corruption, is there an automated method for recreating the faulty
objects?

Ta,

Daniel.

 

[unhinged]

OK,

It turned out that the sorting issue was due to me leaving the sort
criteria on the formatted query fields - D'OH!

However, I still need to overcome this issue with the header for a
column not being the same as that of the query field. Any ideas?

Just in case it throws light on the subject, here is a bunch of SQL
from the queries that are in use:

qry_GaPSInquirySearch_SortByDateDESCEND:

SELECT qry_GaPSInquirySearch.intInquiryId,
qry_GaPSInquirySearch.ShowDate, qry_GaPSInquirySearch.ShowTime,
qry_GaPSInquirySearch.MinuteName,
qry_GaPSInquirySearch.ContractsIssueType,
qry_GaPSInquirySearch.memIssue, qry_GaPSInquirySearch.txtName,
qry_GaPSInquirySearch.txtPhone, qry_GaPSInquirySearch.txtCompany,
qry_GaPSInquirySearch.txtEmail, qry_GaPSInquirySearch.OfficerName,
qry_GaPSInquirySearch.[ATM Number], qry_GaPSInquirySearch.UserType,
qry_GaPSInquirySearch.IssueType
FROM qry_GaPSInquirySearch
ORDER BY qry_GaPSInquirySearch.dtmDate DESC ,
qry_GaPSInquirySearch.dtmTime DESC;

qry_GaPSInquirySearch:

SELECT qry_GaPSInquiry.intInquiryId,
Format(qry_GaPSInquiry.dtmDate,"dd/mm/yyyy") AS ShowDate,
Format(qry_GaPSInquiry.dtmTime,"Short Time") AS ShowTime,
qry_GaPSInquiry.MinuteName, qry_GaPSInquiry.ContractsIssueType,
qry_GaPSInquiry.memIssue, qry_GaPSInquiry.txtName,
qry_GaPSInquiry.txtPhone, qry_GaPSInquiry.txtCompany,
qry_GaPSInquiry.txtEmail, qry_GaPSInquiry.OfficerName,
qry_GaPSInquiry.IssueType, [txtRftNumber] & "" AS [ATM Number],
ct_UserType.UserTypeName AS UserType, qry_GaPSInquiry.dtmDate,
qry_GaPSInquiry.dtmTime
FROM (qry_GaPSdfRTFNumber INNER JOIN (qry_GaPScmbUserType INNER JOIN
(qry_GaPStxtInquirerName INNER JOIN (qry_GaPScmbOfficer INNER JOIN
(qry_GaPStxtInquiryDateFrom INNER JOIN (qry_GaPStxtInquiryDateTo INNER
JOIN (qry_GaPScmbOrganisation INNER JOIN (qry_GaPStxtIssueDescription
INNER JOIN (qry_GaPScmbIssueType INNER JOIN (qry_GaPSInquiryID INNER
JOIN qry_GaPSInquiry ON qry_GaPSInquiryID.intInquiryId =
qry_GaPSInquiry.intInquiryId) ON qry_GaPScmbIssueType.intInquiryId =
qry_GaPSInquiry.intInquiryId) ON
qry_GaPStxtIssueDescription.intInquiryId =
qry_GaPSInquiry.intInquiryId) ON qry_GaPScmbOrganisation.intInquiryId
= qry_GaPSInquiry.intInquiryId) ON
qry_GaPStxtInquiryDateTo.intInquiryId = qry_GaPSInquiry.intInquiryId)
ON qry_GaPStxtInquiryDateFrom.intInquiryId =
qry_GaPSInquiry.intInquiryId) ON qry_GaPScmbOfficer.intInquiryId =
qry_GaPSInquiry.intInquiryId) ON qry_GaPStxtInquirerName.intInquiryId
= qry_GaPSInquiry.intInquiryId) ON qry_GaPScmbUserType.intInquiryId =
qry_GaPSInquiry.intInquiryId) ON qry_GaPSdfRTFNumber.intInquiryId =
qry_GaPSInquiry.intInquiryId) INNER JOIN ct_UserType ON
qry_GaPSInquiry.intUserType = ct_UserType.ID;

' Note that all of these sub-queries are basically just grabbing
information from the unbound controls on the search screen.
' Also, the UserType label is appearing correctly, but the
ContractsIssueType is showing as GaPS Issue Type (with spaces)



qry_GaPSInquiry:

SELECT tblInquiry.intInquiryId, tblInquiry.dtmDate,
tblInquiry.dtmTime, ct_MinuteName.MinuteName, tblInquiry.txtName,
tblInquiry.txtPhone, tblInquiry.txtCompany, tblInquiry.memIssue,
ct_IssueType.IssueType, tblInquiry.txtEmail, tblInquiry.txtReferredTo,
ct_Officer.OfficerName, tblInquiry.hypSPRFilePath,
tblInquiry.txtHelpDesk, ct_GaPSIssueType.GaPSIssueType AS
ContractsIssueType, tblInquiry.txtRftNumber, tblInquiry.intUserType
FROM (((tblInquiry LEFT JOIN ct_Officer ON tblInquiry.txtOfficer =
ct_Officer.ID) LEFT JOIN ct_IssueType ON tblInquiry.txtIssue =
ct_IssueType.ID) LEFT JOIN ct_MinuteName ON tblInquiry.txtTimeTaken =
ct_MinuteName.ID) LEFT JOIN ct_GaPSIssueType ON
tblInquiry.txtGaPSIssue = ct_GaPSIssueType.ID
WHERE (((tblInquiry.txtHelpDesk)=2));

' This is taking data from the source table and it is here that I am
trying to "rename" the GaPSIssueType field (no spaces!)
' as ContractsIssueType. No joy when viewed in datasheet mode.


Thanks for reading this far,

Daniel

 

[unhinged]

Found the problem!!!!!

The header was being overridden by the caption value in the underlying
table ct_GaPSIssueType. This has to be one of the weirdest issues I
have ever come across, but no doubt there are others in wait for me...

Many thanks to my colleague Jon Bosker who helped talk me through this.