sorting the subform

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

I have a form of employee data and a subform of quarterly
data (one-to-many relationship).

I created a subform that works perfectly, except.... the
user wants the quarterly data to sort by year, then
quarter even though the 7-character column contains data
such as "Q1 2002", "Q2 2002", "Q1 2003", "Q2 2003", etc.

So I used the RIGHT and LEFT substring functions to break
out the year and quarter in order to sort by year first.
The subform works fine all by itself.

However.... the subform ceases to work within the main
form and gives an error that seems to point to the
function (it doesn't like a parenthesis).

Does anyone know if there are restrictions to using
substring functions in the data source of a subform??

Any ideas for a workaround??

By the way, this is Access 2000 in a project (adp) file.

Many Thanks !!
Tony
 
However.... the subform ceases to work within the main
form and gives an error that seems to point to the
function (it doesn't like a parenthesis).

Does anyone know if there are restrictions to using
substring functions in the data source of a subform??
Click to expand...

No. Please post your code; it's probably some other syntax error.

You might want to instead base the Subform on a sorted Query, sorting
on the raw unformatted date/time field to get a chronological sort.
 
Here is the Record Source SQL code underneath the subform:

SELECT * FROM tbl_empdata ORDER BY personid, RIGHT
(comboqtr,4), comboqtr

Again, comboqtr has values such as "Q1 2002", "Q2
2002", "Q1 2003", "Q2 2003"

So by using RIGHT(comboqtr,4) I intend to pull out the
year for a more reasonable sort. The subform works fine
as a standalone form.

When I attempt to open the main form, I get a warning
message that says: Line 1: Incorrect syntax near ')'.

Then the main form opens without the attached subform (by
the way, I have checked the parent-child link fields and
they are correct).

Does this help you to help me??
Hope so!
Thanks,
Tony
 
Here is the Record Source SQL code underneath the subform:

SELECT * FROM tbl_empdata ORDER BY personid, RIGHT
(comboqtr,4), comboqtr

Again, comboqtr has values such as "Q1 2002", "Q2
2002", "Q1 2003", "Q2 2003"
Click to expand...

Try changing this to

SELECT <just the fields you need, not all of them>
FROM tbl_empdata
ORDER BY PersonID, datefield;

assuming that comboqtr is a field derived from a date. If it's not,
your order by should be OK, though it won't use indexes and will
therefore be slower.
When I attempt to open the main form, I get a warning
message that says: Line 1: Incorrect syntax near ')'.
Click to expand...

This message does not appear to have anything to do with this Query.
Try opening the VBA editor by typing Ctrl-G and selecting Debug...
Compile <your database> and fix any VBA code!
 
John,
Thanks for all your help! Just so you know, the comboqtr
field is not a datefield (unfortunately it is a
nvarchar). I did do a specific select and still get the
same problem (and, yes, all of my VBA compiles without an
error). I wonder if this is an anomaly with the SQL
Server backend??
Regards,
Tony
 
John,
Thanks for all your help! Just so you know, the comboqtr
field is not a datefield (unfortunately it is a
nvarchar). I did do a specific select and still get the
same problem (and, yes, all of my VBA compiles without an
error). I wonder if this is an anomaly with the SQL
Server backend??
Regards,
Tony
Click to expand...

It might be (though I don't see why). I'd suggest that you repost
(mentioning the fact that it's SQL Server, which you hadn't mentioned
before!) and see if someone else can pick up on it.
 
Back
Top