How to sort chronologically 1

[mongous]

I am trying to offer users a combo-box to choose a month for which to report data. I use this SQL:
[red]
SELECT DISTINCT [tblRepData].[Month] FROM tblRepData;
[/red]

This pulls the months, but it sorts them alphabetically in the list. I want them to display in order (Jan,Feb,Mar,etc)

Can anyone help me out? Thanks.

 

[mongous]

yes. it is [row_date]. that is the daily data collected. the [Month] field i created for the sole purpose of making pulling by month easier, so the user didn't have to type in a StartDate and EndDate, which is how it was...

 

[PaulBricker]

Copy this into the sql for the underlying Row Source.

SELECT Format([row_date],"mmmm yyyy&quot AS myMonth
FROM [tblRepData]
GROUP BY Format([row_date],"mmmm yyyy&quot, Month([row_date])
ORDER BY Month([row_date]);

Set the column count to 1 and the column width to 1"

See if that gives you what you need.

Paul

 

[mongous]

WoW! That a WHOLE lot closer! It displays the months correctly, but when I select one I get the error
[blue]
'The value you entered isn't valid for this field'
[/blue]

It says something like you may have entered text in a numeric field etc...

But alot closer! I need to study that SQL.

 

[mongous]

I just don't get it. WHAT field is it talking about? I never pushed a button, just update the box. Could it be talking about the table itself???

 

[PaulBricker]

Are you trying to do something like this

Private Sub Combo0_AfterUpdate()
Dim strSqL As String
Dim rst As DAO.Recordset
strSqL = "Select * From [tblRepData] Where Format([tblRepData].[row_date],'mmmm yyyy') = '" & Me.ComboName & "'"
Set Me.RecordSource = strSqL
End Sub
This would pull all records related to whatever mmmm yyyy you selected in your combo box.

Paul

 

[PaulBricker]

Forget the line
Dim rst as DAO.Recordset

It's just an extra that got inserted by accident.

Hang in there, we'll get it.

Paul

 

[mongous]

No, I don't even have an associated procedure yet! I mean I have one attached to a 'Preview Button', but I haven't altered it yet since I decided to go with the month combo box...

It used to just prompt for start and end dates (straight from the underlying query)

 

[PaulBricker]

What do you want to filter? A Form or Report?
The code above will filter the form to return records with dates that match out "mmmm yyyy" format. I didn't get any errors when I ran the code or updated the combo box. You'll need to let me know where to go from here.

Paul

 

[mongous]

Well the combo box itself is to filter a report. That combined with another combo box (used to filter by team), will be the parameters for the report. All this previous was just to get the data in the combo box to display correctly.

Follow?

 

[PaulBricker]

Do you have a field name in the Control Source for your Combo box? That may be causing the error. My combo is unbound.

Paul

 

[mongous]

Get this. No I don't! It is also unbound, the only thing there is the SQL in the Row Source property.

Is it possible maybe it's something to do with my [Month] field in the table? I checked the FieldSize property, and it is set to 50 - should be plenty.

I wonder if it has do do with the 'converting' to a numeric value, when the field is set to text. I am a little afraid to change it to number and lose data!?!?!?

I am not sure, but it has to be something with that, cuz you just probably created yours from scratch (didn't have to format imported dates...)?

 

[PaulBricker]

True, I just started from scratch. You could create a backup table tblRepDataBackup so you don't lose any data. Just copy and paste the tblRepData and save as tblRepDataBackup. Then it should bomb is you lose data because you can restore by changing the name of tblRepData to something else and change tblRepDataBackup to tblRepData.

Just a thought. I'll be around for another 15 minutes then I'm gone for the evening.

Paul

 

[mongous]

Well I tried, and it just deleted all the data in that field! Lucky I had a backup. Hmmm, well back to the drawing board, there has GOT to be a way....

Thanks again for all your help!

 

[PaulBricker]

Keep me posted. I'm intrigued by the whole thing.

Paul

 

[mongous]

A-HA!! Got it! All is was, was I somehow had the format property of the combo box itself set to standard. I just deleted that and - voila! it worked!

Now to just get the code to work for the report!

Thanks alot for all the help! It helped alot and is much appreciated! Kudos!