#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0

    Access SQL ORDER BY putting NULL values last in query


    Hello,

    NOTE: Im working in MS Access 2003, only results in this SQL/database (not mysyl, MSSQL etc)

    I want to be able to ORDER an SQL query and put the NULL values last.

    I have a basic databse:
    Table name: PeopleTable
    Field names: TableID, PersonField, PersonID

    The aim is to order by PersonID and put the NULL values last

    I use the orderby SQL statement (SELECT * FROM PeopleTable ORDER BY PersonID and it shows the results ordered by the

    PersonID however the NULL values are first.

    The desired result is to have similar results to the above query but ensure the NULL values are displaed last?

    How can i achieve this is MS Access 2003? Can someone provide me with the SQL using the said example?

    Thanks,

    Jac
  2. #2
  3. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    May 2009
    Location
    Somerset, UK
    Posts
    131
    Rep Power
    91
    Create a new calculated column called 'Sort' (or whatever) and use that. Something like this:

    SELECT * FROM PeopleTable ORDER BY IIf(IsNull([PersonID]),1,0), PeopleTable.PersonID;
    Regards

    JD

    Software matters - Bespoke Access Database Design and Development
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0
    Hello,

    Topic: Auditing results in a datasheet (which i have learnt is more similar to a FORM than a table)

    So I now use the AfterUpdate method/function/sub and it triggers when a field is changed.
    The question i have is how to determine exactly what value was changed, if there is 100 records and i change the 10th one, it triggers when i leave the field so can i extract the value (in the field) that was changed (ie, the new updated value, not what is previously was)

    The method/function/sub used:

    Private Sub PersonID_AfterUpdate()

    End sub

    Thanks,

    Jac
  6. #4
  7. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0
    Ok there has been some confusion.
    My question is:
    I have a subform datasheet (imagine a raw table opened up in Access) on a Main form that displays data.
    If i change a field, i want to trigger the AfterUpdate method and get the value changed in the datasheet, if it was a textbox on a normal form it would be nameoftextbox.value
    what is the code to get a value from a field in a subfrom datasheet?
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0
    Ok, so I dont seem to be getting anywhere.
    Imagine you have a datasheet that has 7 columns (fields) with about 100 rows of data in it.
    If a user happens to change the 3rd coloum in row 68, then how can i dtermine programitcally that this column was changed?
    The afterupdate event triggers, yes, correct, but the trigger isn't the issue, its getting the column/field changed and value that was changed.
    (as i said if it was textbox on a form, it would be textboxname.value)

    Now the datasheet is a subform, after reading the posts, i thought i will just access the subform is code: (something like)

    Forms!NameOfSubform.subformvalue (that was changed),

    However this relies on you needing to know the value that was changed (hardcoded), so what i need is when the trigger for afterupdate is done, it basically gives me the data i need, so effectivly gives me the subform name and field and value. I NEED THE CODE TO DO IT not the principal.
  10. #6
  11. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    Maybe if you can explain to us why you need to know this data, it may help us figure out a way to help?? in which object are you putting your afterupdate event? If your datasheet has 7 columns, then you could use afterupdate events for each of the seven columns. After that column is updated the appropriate code will run, and always know exactly which field was updated (itself).
    For example: If you want to display the value was changed in a field called IDNumber, the in the afterupdate event of the IDNumber field in the subform you could use:
    msgbox me.IDNumber

    This would display the changed value of the IDNumber field, but only if that value was updated. You would do this for each field you want, with whatever code it is that you want to do what ever is you want it to do.

    btw,
    It's useless trying to determine the column and row of access tables as these positions could change at any time and have no real relationship to the data stored in them.

Similar Threads

  1. Including null values in query?
    By benign in forum Microsoft Access Help
    Replies: 0
    Last Post: April 17th, 2009, 09:47 AM
  2. Crosstab query - Null Values
    By Sully in forum Microsoft Access Help
    Replies: 0
    Last Post: January 11th, 2006, 04:07 PM
  3. Relationships and Null values in Query
    By Gainzim in forum Microsoft Access Help
    Replies: 0
    Last Post: September 13th, 2004, 08:53 AM
  4. Query null values
    By sjbeeny in forum Microsoft Access Help
    Replies: 7
    Last Post: June 7th, 2004, 07:31 AM

IMN logo majestic logo threadwatch logo seochat tools logo