Sorting Access report based on linked Excel table

[GSCaupling]

Office 2007; Windows 7

I have what should be a simple task that is driving me crazy.

In Access I have one table linked to an Excel table. I have one query based on that table and one report based on that query.

The report fields: Name (last, first mi concatenated from three fields); Account Number, Statement Date; Amount, Percent, Commission.

I want the report sorted in the order presented above: Name, Acct. Number, Statement date, etc. I'm using the sorting/grouping functions in the Access report, which may not be necessary if the query already does it.

The date is the problem. They appear almost random - I cannot detect any pattern to their order.

The dates are in fact dates in the original excel worksheet (formatted mm/dd/yyyy). The report's underlying query has, from left to right: Last Name (ascending), First Name (ascending), MI (ascending) and Statement Date (ascending).

I have trimmed all the fields in Excel to ensure there are no spaces and I am at a loss as to how I can get this Access report to sort properly.

Any help is appreciated!

GS

[Green]******^*******
[small]I[/small] [small]Hate[/small] [♥] [small]Ambiguity.[/small][/green]

 

[SkipVought]

hi,

The dates are in fact dates in the original excel worksheet

How did you verify that?

Very simply, is you FORMAT any cell that your think contains a REAL DATE in Excel and CHANGE the format to GENERAL, your Number Format should change. If there is no change, you do NOT have a REAL DATE.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GSCaupling]

Skip,

All 863 dates changed when tested under General format (e.g., 10/23/2010 became 40474).

I've tried setting the dates to ascending in the query only, the report only, and in both at the same time. It has made no difference.

Thanks for any additional suggestions.

GS

[Green]******^*******
[small]I[/small] [small]Hate[/small] [♥] [small]Ambiguity.[/small][/green]

 

[dhookom]

Don't waste your time attempting to sort in the query. Always use the sorting and grouping. If your report doesn't seem to be sorting correctly on the date field, you can try force the field to date with as sorting and grouping expression of:

=DateValue([Your Date Field])

You could also create a column like this in your record source if you find that easier.

Duane
Hook'D on Access
MS Access MVP

 

[GSCaupling]

Thanks, Duane.

I deleted all sorting instructions from the query and used the datevalue function as suggested. All sorting instruction now come from Access' sorting/grouping.

The report still does not sort correctly.

I didn't yet try a new datevalue column in the Excel source doc. I would *really* like an Access solution so I can avoid re-tinkering with the source every month.

Thanks for the input, especially about not using the query to sort.

GS



[Green]******^*******
[small]I[/small] [small]Hate[/small] [♥] [small]Ambiguity.[/small][/green]

 

[dhookom]

Did you actually create a field named "Name"? Can you provide the actual expressions used in your sorting and grouping control sources?

Duane
Hook'D on Access
MS Access MVP

 

[GSCaupling]

Duane,

I just stumbled across the solution moments ago. BTW - none of the fields were "name" or any other reserved word.

The grouping was LastName (A-Z), FirstName (A-Z), PolicyNum (smallest to largest), and StmtDate (oldest to newest).

The problem was the interval setting for the date grouping. This setting is not visible unless you click on "More" and somehow it was set to a Quarterly interval. When I changed it to "By Day" the problem was solved.

I still don't know how it got set to quarterly to begin with and feel sort of silly for having bothered you.

On the bright side, maybe this will help somebody.

Thanks,
GS



[Green]******^*******
[small]I[/small] [small]Hate[/small] [♥] [small]Ambiguity.[/small][/green]