Full Version: Dates not sorting
UtterAccess Forums > Microsoft® Access > Access Forms
Berty
Hi, I've got a table with with a period field (datatype is Date\time) and formatted mmm-yy.
I have a crosstab query (very simple) that sums values and has the period as column headers. (Jan-06, Feb-06 etc)
Ocan not get the periods to be in calendar order - either asc or desc. - It is only allowing sorting by alpha - Feb-06, Jan-06, Mar-06
I guess this is a formatting issue - The field name in the Crosstab query is: Period: Format([Period],"mmm-yy")
Any suggestions?
I've attached a dummy of my problem
Cheers
khaos
In the query build a hidden field with this as the sort.
ormat([Period],"mm")
HTH
Ken
AQM_UK
As you have year there, what I do is Format([Period],"yyyy") and sort ascending by this and then do one for month and sort acsending by that.
Is this is really a text field, just using month will bring months of future years together.
Jim
Berty
Hi Ken
must be missing something here - have tried your sugestion and no go.
Looking at a standard query - I can sort the dates - but the crosstab won't let me do it -- aarrrgghh
Could I ask a favour? - please could you look at my attachment and do a tweak so that I can see what you mean (sorry if this sounds presumptious)..
Many thanks Ken
John
Berty
Hi Jim
Thanks - my problem is that I can't get the periods to be calendar sequential - only alphabetical
John
(pulling my remaining few strands of hair out)
jmcwk
Berty,
Just an FYI and do not know if others have had any problems but was unable to download your Attachment it must be zipped and no larger than 500000kb
Berty
Aha!
The joys of Microsoft file compression (My Win Zip is on my old lap-top which I replaced last week)
Will sort this tomorrow.
If in the meantime any suggestions to help fix my issue - would be greatly received
Cheers John
jmcwk
Berty,
o As I do not lead you astray I have since found out that the Administrator has now placed a firewall in place that disallows any downloads so IF in fact you did have it zipped yourDbname.zip it was not your fault that I could not download. Sorry for the cofusion!
schroep
See if this earlier discussion helps.
schroep
John -
o complicate matters further, looks like the UA admins are working on the upload/download functionality of this site for the next week or so.
jmcwk
Peter,
Can you tell me or do you know If it is on the UA end or is it on my end? The Firewall was an assumption on my part as I could not even get a download from the Archive.
Thank You
schroep
John -
just tried a few from the archive and got the same UA message:
So, that looks to me to be a site issue, not on your end (assuming you aren't getting something different).
jmcwk
Peter,
ppreciate that I had received the same message however because I had a simular message in the past (appearance wise) I failed to read it thoroughly and immediately closed the window. DAH!
khaos
I really didn't get anywhere with the crosstab. I never use that query type as it is nearly worthless to me. If I want to pivot a view I write my own. I always need more groups than the crosstan allows.
en
Berty
Hi Ken
It sounds like I should try and write my own as well. - I can just about do some sql in vba for a normal query - but wouldn't know where to start for a crosstab style bit of sql - any pointers?
Much appreciated
Cheers
John
khaos
I use a line like so to get new columns
um(IIf([DATA].[StateCol]=1,IIf([DATA].[ParaNet]=True,[IncurAmtNet2],[IncurAmt2]
),0))*[CombFactorUsed] AS IA2_SW,
In this specific case there is a matching column where stateCol=2 and use a similar name for the column. I use group by first etc as needed to get other columns to 1 per group. The entire query this belongs to is large and esoteric. If you really want to see it I can post it as well.
HTH
Ken
Berty
Thanks Ken
ou've given me some ideas - very many thanks
John
khaos
You're welcome, glad to help.
AQM_UK
John, I did not make myself clear here.
o a "yyyy" for year and then a "mm" for month.
This gives you 2006 , 2007, 2008 for the year and you sort ascending on this.
The "mm" for month gives you 01, 02, 03, 04 etc for the months.
Sort ascending on this and then you have the data in year and month order.
The field that you DISPLAY is the one formatted "mmmm yyyy"
HTH
Jim
Berty
Hi Jim
can do what you say in a normal select query - but can't in a crosstab - am I missing something obvious here (it wouldn't be the first tim)
Cheers Jim
John
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.