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

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.

0
unhinged
4/3/2007 8:53:02 AM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
787 Views

Similar Articles

[PageSpeed] 56

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

0
unhinged
4/3/2007 9:10:41 AM
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.

0
unhinged
4/3/2007 9:23:15 AM
Reply:

Similar Artilces:

change date format from dd/mm/yyyy to mm/yyyy
I have a column with dates in dd/mm/yyyy . I want to get rid of the dd. although converting the date format does the job.. but the dd/mm/yyyy value still remains on the formula bar. this then makes pivot table analysis difficult as I want to analyse by month. help please Add a column for the month =MONTH(A1) and copy down, and include that in the pivot. Maybe add a year and day column as well. -- HTH RP (remove nothere from the email address if mailing direct) "flow23" <flow23@discussions.microsoft.com> wrote in message news:8F878380-3053-4E29-93D5-538BA9B9EFB3@micros...

Customizing Activities\History LIst
Is there a way to add a date to Activities' History list? It would be nice to know when each activity happened. Thanks, Troy Troy, no way currently, although Microsoft has mentioned it might be an option in version 2.0 (nothing is guaranteed!) A few ISVs have custom products that do it... www.salentica.com www.c360.com Dave "Troy" <anonymous@discussions.microsoft.com> wrote in message news:af6b01c436bd$ce671f00$a001280a@phx.gbl... > Is there a way to add a date to Activities' History > list? It would be nice to know when each activity > happened. >...

Better to "nest" or use multi-level list?
I'm in the process of creating/defining a style(s) for a list that will be numbered in "level 1" and have indented bullets in "level 2." For such a list is it better to define a multi-level list style and linked paragraph styles? Or use one's already defined numbered list and use one of the Word's pre-defined bullet lists when needed for the second level? As part of this question, is is preferable not to have a style "nested" within another style or doesn't it matter? Thank you. -- Norm Hi Norm: I'm not sure why yo...

Distribution List Changes
Hi All, I have a mail enabled distribution list with four users. Only those users plus the Administrator have rights on this group. Yet a seperate user is able to add / edit or remove users to this group even though they do not belong to this group. The user does not even have admin rights and is able to make changes. Let me know of any options I can try. Thanks Waynear wrote: > Hi All, > > I have a mail enabled distribution list with four users. Only those > users plus the Administrator have rights on this group. > > Yet a seperate user is able to add / edit or remov...

action queries
hey! it's me again! let me rephrase my statement. we were taught of how to make a database but only the basic ones. i know how to make tables, forms, queries and reports. we were taught how to make action queries but not on how to apply them directly in the forms. i want to know how to add new records, delete records and update records while in the form, using the action queries. thank you so much. and again, i hope you could help me. Hi Maan, The easiest way (without useing VBA) would be to create a macro to call the action query, place a button on the form and assign the click ev...

Sound shows on custom Annimation list yet no sound heard
When the PP Presentation is saved as a show,some slides have sound and some do not. All sound came from the same folder. Also, when playing the slides while in PPPresentation, swome have sound and some do not despite all being indicated on custom annimation lisy and all startin "after previous." Thanks! Often when one sound plays but another doesn't, it's an issue of the length of the file path. What's the file path to your music? Is it really long? If so, the longer-named songs might just be hitting the length limit. -- Echo [MS PPT MVP] http://www.ec...

Drop Down List and Check Box
Hi...I was search for this topic but don`t found the solve...Anyway I have a drop down list like this [image: http://www.geocities.com/augurtrade/excel_checkbox.txt] and this [image: http://www.geocities.com/augurtrade/excel_checkbox2.txt] ok my questions is what is (Blanks) and (NonBlanks)...? Is it possible for me to see only all the check box that was checked or vice versa...??? How to make a total below the checkbox i mean when someone check the check box, a total number appear below the last check box [image: http://www.geocities.com/augurtrade/excel_checkbox3.txt] [image: http://www.g...

Data Sort with header row
How can I set the default so that the "with header row" indicator is flagged as opposed to the "without header row"? Hi Ian, Don't think you can. You can help Excel make a guess by making the formatting of the header row differ from the data below it, by making the header Text, and Bold; which helps but is far from being reliable. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ian" <ian@ckf....

how reliable is the "Who has you in their contact list" list?
Because someone who I think has recently (within the last week) deleted me is still on this list (I don't think she blocked me), but when I check who deleted me on blockstatus.com, he shows up. could it be it's not updated? which of the two would be more reliable? -- maykk Greetings, Do not use these websites, they will steal your identity and sell it. You should change your password immediately. That said, in general, the "who has..." list within Messenger is accurate. There have been instances over the last six-months or so where the list (and m...

Outlook Business Contact Manager anniversaries showing up in "OutLook Today" #2
Hi, I just imported about a 1000 customer records into a Business Contact Manager database so I could start sending emails to my clients (as well as perform other tasks). I was appalled to see all the anniversary dates being displayed in my Outlook Today page. Is there anyway to prevent BCE birthday and anniversary dates from being displayed in Outlook Today? I only want my normal contact info to appear here. Thanks! Emile ...

Check Box to display records with completed status only
Hi, I have a query whereby a record is lablelled complete if the "Completed date" field has a date in it, otherwise it is blank. I want to check box on a form when ticked, to display a report with completed records only. Can anyone help? Thanks, B/ On your form, you have a command button to open the report, and a check box named (say) chkCompleteOnly. If the check box is checked, you want to open the report to show only the records that have a date in the [Completed date] field. The event procedure for the command button's Click event would look like this: Priv...

Selecting from contacts list
I have all my contacts in the Contacts List and I want to delete all those with a common extension i.e. all those who have email addresses @hotmail. I am using Outlook 2007 Can you do a search or advanced search which will show the contact you want to delete? You should be able to highlight & delete them from the results. "GrahamC" <GrahamC@discussions.microsoft.com> wrote in message news:6AC1880D-9632-4ED9-BAE2-7083690B9FA7@microsoft.com... > I have all my contacts in the Contacts List and I want to delete all those > with a common extension i.e. all t...

Disable Auto date
How to disable Auto date (The balue that gets updated to today's date ). I want to preserve the last opened date on the sheet. Thank you. How do you put the date there? If you type it, you could type the date (or hit the ctrl-semicolon shortcut combo). HItesh Zinzuwadia wrote: > > How to disable Auto date (The balue that gets updated to > today's date ). I want to preserve the last opened date on > the sheet. > Thank you. -- Dave Peterson ec35720@msn.com ...

Script to list access right
Hi I am trying to find any script which would generate list of login users with certain access level (select, write etc.) at one particular database. Is there something like that? Thanks. Justin ...

Filters, Buttons, Queries, oh my!
i have a main form with a subform. the main form and the subform are unrelated. the subform is based on a query that lists a load of stuff and does some calcs that use numbers on the main form with numbers from the subform. this all works great. i have some buttons that allow me to cause a filter to be applied to the subform based on the code that i put in the buttons on click event. there are 6 buttons that turn on a specific filter and 6 other buttons that turn off the filter. i know i probably should have used toggle buttons, but as i am still learning i wanted to make it easy on my...

a list of lists
currently i use CArray to keep track of a list of objects, but I don't know MFC classes well enough to keep track of a list of lists. I'd appreciate some suggestions "wanwan" <ericwan78@yahoo.com> ha scritto nel messaggio news:1187535523.924671.143350@j4g2000prf.googlegroups.com... > currently i use CArray to keep track of a list of objects, but I don't > know MFC classes well enough to keep track of a list of lists. I'd > appreciate some suggestions You might want to embedd your list based on CArray into a class (e.g. CListOfSomething), and then ...

Smart List export to Excel #2
Hi, We are using Dynamics 9.0 and Office 2003. When we try to do a Smart List export to Excel, we keep getting an "Exception_Class_Object_Exception" that references varying object errors, such as 'Cells' or 'Value'; the object errors will change each time we try an export. As a workaround, I've found that closing Excel before doing the export allows the export to complete successfully. However, that solution isn't acceptable, as our Dynamics users would like to be able to work in Excel, doing other tasks, while the export is processing. Does anyone...

List of different values in data area
Hi excel specialists, How Can I automatically get the list of different values from the dat area and to find out their frequence? INDIVIDUALLY MEASURED VALUES: 3,5 4 3,5 4 3 3,5 4 4 3 3,5 4 3 3,5 3 3 3,5 4 4 Thanks for your help in advance. Balcovja -- balcovj ----------------------------------------------------------------------- balcovja's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2623 View this thread: http://www.excelforum.com/showthread.php?threadid=39572 Take a look at FREQUENCY in Hep -- HTH RP (remove nothere from the email address if m...

Global Address List and Handhelds
We use Ipaqs throughout the company, and I was wondering if there is anyway to have exchange add the GAL to the outlook contacts on the workstation, so that when they sync up, they will retrieve the Global Address List? In news:%23TkYtT0$GHA.3560@TK2MSFTNGP04.phx.gbl, Flip <phil.atkinson@beltek.com> typed: > We use Ipaqs throughout the company, and I was wondering if there is > anyway to have exchange add the GAL to the outlook contacts on the > workstation, so that when they sync up, they will retrieve the Global > Address List? No (and this wouldn't really be an ...

Make table query with calculated date fields
I have a make table query that calculates requirement dates based on a [StartDate]. All of the fields (80 of them) are set as Short Date in properties. But when the table is created all of the fields are text. I would like to avoid using the FORMAT command in the query and change the field properties of the table manually. Does anyone have a suggestion? Thank you in advance for your help. Share the SQL view of you make table query. -- Duane Hookom Microsoft Access MVP "Harry" wrote: > I have a make table query that calculates requirement dates based on a > [StartDat...

Can data in one text box automatically appear in another?
I am trying to design a form in Publisher, and would like to link text boxes so that data entered in one will automatically appear in another on the same page. Is this feasible? How will this form be used? "G.O.G." <G.O.G.@discussions.microsoft.com> wrote in message = news:E4C951DF-7D4F-4FC1-8F6B-2C0B4EB8882A@microsoft.com... > I am trying to design a form in Publisher, and would like to link text = boxes=20 > so that data entered in one will automatically appear in another on = the same=20 > page. Is this feasible? Maybe with some programming... -- Mary Saue...

Text block query
I wish to create a block of several lines of text inside a border on a worksheet. What terms do I look for to describe this operation? Just wasted an hour looking in vain. I know I will kick myself when someone points me in the right direction. Thanks Brian Tozer Brian Tozer wrote: > I wish to create a block of several lines of text inside a border on a > worksheet. > What terms do I look for to describe this operation? > Just wasted an hour looking in vain. > I know I will kick myself when someone points me in the right > direction. Cancel panic. Problem solved. As usua...

Fields in items list-- add/delete
Hai, I am using RMS version 1.2. In Stores Operations Manager it displays list of items when click on Database-->Items. It displays only five columns. Now i need to add / delete some fields in this list. Is it possible to add or delete the fields? if yes how can i add the fields? Thanks in advance, Nagendra On Wed, 27 Apr 2005 01:22:02 -0700, "Nagendra" <Nagendra@discussions.microsoft.com> wrote: >Is it possible to add or delete the fields? if yes how can i add the fields? I don't think so antonio ...

remove small list from large list
I have two mail lists on excel. The first has 16,000 names, the second has about 6,000. I need to remove the 6,000 from the 16,000 as they all appear in the big list as well. How do I extract those 6,000 entries from the larger list? My expected result is a mailing list with 10,000 names. Chip Pearson has some techniques at: http://www.cpearson.com/excel/duplicat.htm Howie J. wrote: > > I have two mail lists on excel. The first has 16,000 names, the second has > about 6,000. I need to remove the 6,000 from the 16,000 as they all appear > in the big list as well. How do ...

Jpeg's appearing as black boxes...
Version: 2008 I am new to using Word for Mac - having recently transferred from a PC. I am finding that some (not all) some documents are displaying the jpeg's on them as black boxes. Instead of the image the entire are is just black and prints this way also. The image displays correctly in the finder, but once the document is full size, the image is a black box. <br> Help please! How do I get my images back? <br><br>Thanks <br><br>Amber Hi Britamy: Chances are they are CMYK JPEGs. Word (or any other Microsoft Office application) can't handle CM...