Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Hello, yet again, Colleagues All,

    I did not think that after the recent little drama, i would be back again so soon with another demand on your invaluable advice.

    I attach a PDF with some text and screen grabs which illustrate my problem.

    The first page shows a form with a number of combo boxes which allow the operator to select a list of functions (parties, not VBA!) according to one criterion or another. I recently added a new one (by Booking Date) by copying the adjacent one (by Function Date) and modifying the properties and the query. Pages two and three show what the relevant properties look like like for the highlighted combo box. Page four presents the text of the underlying queries.

    Now here comes the interesting (and aggravating) bit. Pages five and six show what happens when you run the program and click on each of the combo boxes. The selection by Function Date works fine, the selection by Booking Date cannot find any records (though it is clever enough to find the column headings). I abstracted the query from the Booking Date combo box and copied into a Query, where it ran perfectly. The output is on Page 7.

    I did delete the combo box and substituted another one built from scratch and it worked - but then all of a sudden, it went off again.

    Has anyone seen anything like this, or can make a suggestion as to where I might direct future research ?
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I dont know if this will make a difference but your sql statement:
    SELECT Function.FunctionKey, Format([BookingDate],"dd/mm/yy ddd") AS Book_Date,
    IIf([ClientTypeKey]=9,[ClientFullName],[OrganisationName]) AS Client_Name, Function.ClientKey AS Client_Key,
    Format([FunctionDate],"dd/mm/yy ddd") AS Func_Date, Function.FunctionRef AS Func_Ref, Function.FunctionTypeCode AS
    Func_Type
    FROM Function
    WHERE (((Function.FactoryKey)=[Forms]![frmMainSwitch]![cmbFactoryKey]) AND ((IsNull([BookingDate]))=False))
    ORDER BY Function.FunctionDate DESC;

    I would change to:
    SELECT Function.FunctionKey, Format([BookingDate],"dd/mm/yy ddd") AS Book_Date,
    IIf([ClientTypeKey]=9,[ClientFullName],[OrganisationName]) AS Client_Name, Function.ClientKey AS Client_Key,
    Format([FunctionDate],"dd/mm/yy ddd") AS Func_Date, Function.FunctionRef AS Func_Ref, Function.FunctionTypeCode AS
    Func_Type
    FROM Function
    WHERE Function.FactoryKey)=[Forms]![frmMainSwitch]![cmbFactoryKey] AND [BookingDate] is not Null
    ORDER BY Function.FunctionDate DESC;

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Are you using Access 2003 SP3?
    If so, have you installed the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007?

  4. #4
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788039' date='08-Aug-2009 08:36']Are you using Access 2003 SP3?
    If so, have you installed the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007?[/quote]
    No, Hans. I am still stuck on SP 2. Every time I look for updates, MS pushes SP3, but every time I try to download it, it falls over. I suspect I have not got enough space on my C drive (this was a second-hand computer already set up with partitions and I did not like the idea of reinstalling all of my many bits of software, wome of which i am not sure I even have the media for). Everything that I can move has been reinstalled on another drive, but my free space on C varies between 300 and 900 Mbytes.

    I will try your suggestion, Patt. The formulation that I had used is simply what comes out of the query editor in the control. However, I reiterate the point that the query works fine when run on its own. Very peculiar.

    Regards,

    Jim

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think this causes the problem, but shouldn't the row source for the booking date combo box be sorted on booking date?

    ...
    ORDER BY Function.BookingDate DESC

  6. #6
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788045' date='08-Aug-2009 09:42']I don't think this causes the problem, but shouldn't the row source for the booking date combo box be sorted on booking date?

    ...
    ORDER BY Function.BookingDate DESC[/quote]

    Doh, yes, Hans, you are quite right. I have tried every possible permutation from using the wizard to writing the code in SQL mode to cutting and pasting from other combo boxes. In its most recent incarnation, I had obviously overlooked the change of sort order while editing.

    Regards,

    Jim

  7. #7
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    I have a fix of sorts, which I will report on to round out this thread. I would not call it a solution, because I cannot say with confidence why it works (though I can speculate).

    What I did was to create a new combo box and edit each property to bring it into line with the other sibling combo boxes. The main difference with previous attempts was that I copied and pasted the rowsource SQL from the FunctionDate-based combo box and edited it in the actual property line. In other words, I did not use the wizard.

    Bearing in mind that the same SQL has always worked in an MS Query, I theorise that the wizard, which checks text as it is entered and modifies it to suit its own rules (square brackets around field names and so forth) is being a bit too clever somewhere. The Jet engine is possibly a bit more simplistic and so long as the SQL makes sense and is not ambiguous, it will shrug its shoulders and process it. So far as entering the SQL directly in the rowsource property line, no fancy checking is going on and no behind the scenes assertions are being made, so it will be OK if it is acceptable to the Jet engine.

    Thanks again to all of you for your input.

    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •