December 19th, 2012, 09:59 PM
Access SQL ORDER BY putting NULL values last in query
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?
December 20th, 2012, 05:17 AM
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;
December 20th, 2012, 11:13 PM
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()
December 21st, 2012, 07:44 AM
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?
December 23rd, 2012, 08:43 PM
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.
December 24th, 2012, 08:15 AM
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:
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.
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.