Expression error in Sorting and Grouping

[LARiot]

I have a report where it needs to be sorted by a text field which has numbers:

1
101
102
2
201

Should read as:

1
2
101
102
201

The problem is that the name of the field is Ep # and when I type Cdbl([Ep #]) in the expression field of Sorting and Grouping I get:

Extra ) in query expression '[Cdbl([Ep #])]'

Any idea how to fix this?

By the way I'm using Cdbl instead of CInt because some episode numbers have decimals.

Thanks

 

[dhookom]

I expect your issue is that you didn't prefix your expression with "=". Try:
=Cdbl([Ep #])
or
=Val([Ep #])
If that doesn't work, create another column in your report's record source
SortEP: Cdbl([Ep #])

Then find and consistently use a naming convention that doesn't allow symbols, punctuation, or spaces

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LARiot]

Thanks Duane,

It worked after I added an nz() function as well.

=Val(nz([Ep #]))

 

[dhookom]

I guess I didn't see the null values in your samples ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LARiot]

dhookom, It's been almost a month your last comment still makes me smile.