Sorting as CInt rather than string (1 Viewer)

[RCheesley]

Hi all,

I'm having trouble with a query when in pivot chart view, as it is sorting the dates wrongly:

yyyy ww

2009 1
2009 10
2009 11

when I want it to display as:

2009 1
2009 2
2009 3

and so forth.

I've got as far as identifying that I may need it to sort as "CInt" rather than a string (which someone told me) but that might as well be double dutch to me .... I've tried searching but am not coming up with anything useful.

The SQL behind the query is as follows (I've put some line breaks in for ease of reading):

SELECT DISTINCTROW Format$([tblSpecimen].[SpecimenDate],'yyyy ww') 

AS [SpecimenDate By Week], tblOrganism.OrganismAbbreviation, tblSpecimen.SpecimenLocation, tblHospital.HospitalCode, Count(*) AS [Count Of tblSpecimen], Location(Nz([SpecimenDate],0),Nz([SpecimenCurrentAdmission],0)) AS Loc, tblPatientDetails.PatientDetailsGP

FROM (tblPatientDetails INNER JOIN (tblHospital INNER JOIN tblSpecimen ON tblHospital.HospitalID = tblSpecimen.SpecimenLocation) ON tblPatientDetails.PatientDetailsID = tblSpecimen.PatientDetailsID) INNER JOIN tblOrganism ON tblSpecimen.SpecimenOrganism = tblOrganism.OrganismID

GROUP BY Format$([tblSpecimen].[SpecimenDate],'yyyy ww'), tblOrganism.OrganismAbbreviation, tblSpecimen.SpecimenLocation, tblHospital.HospitalCode, Location(Nz([SpecimenDate],0),Nz([SpecimenCurrentAdmission],0)), tblPatientDetails.PatientDetailsGP, tblSpecimen.SpecimenOrganism, Year(tblSpecimen.SpecimenDate)*12+DatePart('m',tblSpecimen.SpecimenDate)-1

HAVING (((tblPatientDetails.PatientDetailsGP)<69));

Anyone able to point me in the right direction?

 

[MStef]

Look at "DemoSortingCIntA2000.mdb" (attachment, zip).
Look at Table, query1 (column 3).
run query1.

 

[MStef]

The attachment.

 

[RCheesley]

Thanks for this example, an explanation of the syntax would be useful as I can't find this anywhere online that explains it in "Normal" speak.

Using this example

Format(CInt([f2]),"00")

I tried the following but it's coming up with "The expression you entered has a function containing the wrong number of arguments"

SpecimenDate By Week: Format(Cint(tblSpecimen.SpecimenDate,'yyyy ww'))

Was originally (works aside from the number sorting order):

SpecimenDate By Week: Format$(tblSpecimen.SpecimenDate,'yyyy ww')

 

[DCrake]

To make this as simple as possible you need to break up the field you want to sort into two parts

SpecYr:Format([SpecimenDate],"yyyy")
SpecMt:Format(Format([SpecimenDate,"ww"),"00")

Then perform you sort by SpecYr By SpecMt


You can still show your orignal field and hide these 2 new columns or you could concat them in a third column

Period:SpecYr & " " & SpecMt

Alternatively sort by specimen date but don't show it.

It all depends on whether you want to display the period in the correct format or not.


David