Error when sorting a column in forms record source

Hi Experts,

I have a SQL view that is being linked to my Access app.
That view has a column named SNVNum, defined as follows
CAST(REPLACE(dbo.Skilled_Nursing_Visit_Note.SNV_ID, 'snv', '') AS int)

Open in new window

Now when I open the view and sort by that column it works perfectly fine in db container.
it also works fine when running the following in SSMS.
Select * from vw_SkilledVisitNotesFilterFrm Order by SNVNum

Open in new window

However when I assign the form the following SQL as record source.
        Me.RecordSource = "Select * from vw_SkilledVisitNotesFilterFrm  Order by SNVNum"

Open in new window

it gives me the error attached.
Untitled.png
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Does refreshing the linked table from access help?

Does the view definition (or any views used by that view) contain join hints?
bfuchsAuthor Commented:
Hi,
Does refreshing the linked table from access help?
No.
Does the view definition (or any views used by that view) contain join hints?
It contains joins, not sure what join hints means?
Thanks,
Ben
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Your SSMS statement order by a different field than your Access statement
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

Dale FyeOwner, Dev-Soln LLCCommented:
I'm sure this has to do with the conversion of the SNV_ID field to SNVNum

is there any chance that your SNV_ID column might contain some text other than "snv"?

Where does this "SNV" text appear in that field, beginning or end?  Have you tried using Left or Right in SQL to strip out the "snv" text from that field? and then use Cast as you are doing above?

Another option would be to simply return the SNV_ID to Access and then use:

snvnum = VAL(SNV_ID)

to do your conversion.
Doug BishopDatabase DeveloperCommented:
Doesn't seem to make sense. SNV_ID is being CAST to an INT in the view code after replacing 'snv' with '' and aliased to SVNNum. If there were extraneous characters in SNV_ID, running a query against the entire table, as he states he is doing, should produce an error in SSMS.
bfuchsAuthor Commented:
Your SSMS statement order by a different field than your Access statement
Not sure what do you mean, are you referring to what I OP? they are the same.
Is there any chance that your SNV_ID column might contain some text other than "snv"?
This seems to have been the problem as I sorted by that in SQL and realized two records only containing "s" and after removing I'm getting a slightly different error message, see attached.
Where does this "SNV" text appear in that field, beginning or end?
Always at the beginning.

I ran the below and all sterted with 1 at the first column, which means there are no more records left w/o SNV.
select CHARINDEX ( 'snv' , SNV_ID  )  ,*  from dbo.Skilled_Nursing_Visit_Note
order by  CHARINDEX ( 'snv' , SNV_ID  ) 

Open in new window


Another option would be to simply return the SNV_ID to Access and then use:..to do your conversion.
Would prefer SQL does it as it would perform better.

Thanks,
Ben
Untitled.png
bfuchsAuthor Commented:
I don't see your attached error message?
I just attached, refresh the page.
Thanks,
Ben
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
As the error message states, that value is too long to fit in an int. You could cast to bigint (but that is only supported in later versions of Access), and might require the use of some of the later drivers as well, though I am not 100% sure on the driver issue.
bfuchsAuthor Commented:
You could cast to bigint (but that is only supported in later versions of Access),
Access is not the problem as we use O365, the SQL version is the problem, we have SQL 2008, see attached error when trying cast as bigint in SSMS.

Thanks,
Ben
Untitled.png
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.