UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sorting A Field In A Table, Access 2016    
 
   
techexpressinc
post Jul 16 2019, 02:26 PM
Post#1



Posts: 421
Joined: 6-October 08
From: indiana, usa


Why does this not work?
CODE
DoCmd.OpenTable STRTABLENAME2
DoCmd.SelectObject acTable, STRTABLENAME2
DoCmd.SetOrderBy "PlanNbrCurrentYR ASC"
DoCmd.Close acTable, STRTABLENAME2, acSaveYes


Any ideas?

Thank you
Russ

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
theDBguy
post Jul 16 2019, 02:27 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,000
Joined: 19-June 07
From: SunnySandyEggo


Hi Russ. Rather than try to figure out how to make this work, wouldn't it be simpler to use a query instead?

PS. Are you trying to rearrange how the data is stored in the table? If so, you may be fighting an uphill battle.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
techexpressinc
post Jul 16 2019, 03:10 PM
Post#3



Posts: 421
Joined: 6-October 08
From: indiana, usa


Hmm - uphill battle vba SQL built a set tables and preparing to send them to Excel. The order is needing to be by PlanNbr.
Russ

Attached File(s)
 

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
theDBguy
post Jul 16 2019, 03:13 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,000
Joined: 19-June 07
From: SunnySandyEggo


Hi Russ. So, like I was saying, if the goal is to export the table's data into Excel in a particular order, you might use a query instead of a table in your export process.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Jul 16 2019, 03:57 PM
Post#5


UA Admin
Posts: 35,506
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

Tables have no inherent sort order, but as you see, you CAN apply one for display purposes when looking directly at the table in datasheet view.

However, to USE that data in some way, such as export in a filtered, sorted recordset to Excel, the appropriate tool is a QUERY which includes an ORDER BY clause to override the inherent lack of sorting in the table and a WHERE clause to apply a filter.




--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
strive4peace
post Jul 16 2019, 04:40 PM
Post#6


strive4peace
Posts: 20,445
Joined: 10-January 04



hi Russ,

I agree with DbGuy and GPG. A query can be sorted how you desire, have criteria, specify column order, and so on.

"send them to Excel"

how is that being done? TransferSpreadsheet, for instance, can use a select query as well as a table.

Tables are for storing information. When you want to change or use information from those tables, best to define how you want it to be ~


This post has been edited by strive4peace: Jul 16 2019, 05:07 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
techexpressinc
post Jul 16 2019, 04:42 PM
Post#7



Posts: 421
Joined: 6-October 08
From: indiana, usa


Yes the transfer tables to Excel process is being used.

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
strive4peace
post Jul 16 2019, 04:45 PM
Post#8


strive4peace
Posts: 20,445
Joined: 10-January 04



hi Russ,

great! What is that code? Post it and let's change it to use a query instead ... and then you can explicitely set how you want things to be sorted without trying to do that by modifying properties of a table object ... tables are opened in no particular order


This post has been edited by strive4peace: Jul 16 2019, 04:57 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
theDBguy
post Jul 16 2019, 04:49 PM
Post#9


Access Wiki and Forums Moderator
Posts: 76,000
Joined: 19-June 07
From: SunnySandyEggo


Hi Crystal. Good to see you again! smile.gif

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
techexpressinc
post Jul 16 2019, 06:09 PM
Post#10



Posts: 421
Joined: 6-October 08
From: indiana, usa


The code is attached in the 9:10pm post#3, of this thread.
Russ
This post has been edited by techexpressinc: Jul 16 2019, 06:10 PM

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
GroverParkGeorge
post Jul 16 2019, 06:15 PM
Post#11


UA Admin
Posts: 35,506
Joined: 20-June 02
From: Newcastle, WA


As we've said a few times, create queries based on your tables. In those queries apply the ORDER BY Clause you want. Change lines like this one:

Call DoCmd.TransferSpreadsheet(TransferType:=acExport, TableName:=STRTABLENAME1, FileName:=STRFILENAMEAll)

to lines like this one:

Call DoCmd.TransferSpreadsheet(TransferType:=acExport, TableName:=QryBasedonSTRTABLENAME1withOrderByInIt, FileName:=STRFILENAMEAll)

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
techexpressinc
post Jul 16 2019, 06:28 PM
Post#12



Posts: 421
Joined: 6-October 08
From: indiana, usa


Looks like a simple fix, I will try it and let you know the results.
Thanks
Russ

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
techexpressinc
post Jul 22 2019, 10:37 AM
Post#13



Posts: 421
Joined: 6-October 08
From: indiana, usa


Fixed, Thanks everyone for your input. I was headed down the path of an ugly fix, opening and running Excel from the DB doing the sort within Excel.

The query sort is much cleaner. Ended up with crappy query names but that is ok. The query name ends up being the tab name on the Excel, so even know the Transfer command states TableName= that refers to a query instead.


CODE
'
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, TableName:="OnDesk", FileName:=STRFILENAMEAll)
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, TableName:="InSus", FileName:=STRFILENAMEAll)
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, TableName:="InAudit", FileName:=STRFILENAMEAll)
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, TableName:="OutAuditNtMaild", FileName:=STRFILENAMEAll)
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, TableName:="Draft", FileName:=STRFILENAMEAll)
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, TableName:="Publishd", FileName:=STRFILENAMEAll)
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, TableName:="5558", FileName:=STRFILENAMEAll)
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, TableName:="SSA", FileName:=STRFILENAMEAll)
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, TableName:="NotBilld", FileName:=STRFILENAMEAll)
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, TableName:="Compltd", FileName:=STRFILENAMEAll)


Cheers to all!

Russ

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
theDBguy
post Jul 22 2019, 10:41 AM
Post#14


Access Wiki and Forums Moderator
Posts: 76,000
Joined: 19-June 07
From: SunnySandyEggo


Hi Russ. Congratulations! Glad to hear you got it sorted out. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
strive4peace
post Jul 22 2019, 12:19 PM
Post#15


strive4peace
Posts: 20,445
Joined: 10-January 04



hi Russ,

awesome! Nice it is working ~ you can make it better later wink.gif By the way, it is a good idea to start query names with "q" so you can differentiate tables and queries. Then you won't have a query name starting with a number either ~

{ thanks, DbGuy ~ happy to see you too }


This post has been edited by strive4peace: Jul 22 2019, 12:21 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
WildBird
post Jul 22 2019, 05:55 PM
Post#16


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


So you are exporting to a Excel workbook that already exists? And this workbook has a number of worksheets?

You could do this via a loop, as long as you have consistent naming convention.

E.g. you could have all your queries named
qryOnDesk
qryInSus

And have worksheets named
OnDesk
inSus

Code could check for the existence of the sheet and automatically export. If sort field is consistent, could also do code to not even need queries.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th August 2019 - 10:22 AM