asked on

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

Anders Ebro (Microsoft MVP)

Does refreshing the linked table from access help?

Does the view definition (or any views used by that view) contain join hints?

bfuchs

ASKER

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)

Your SSMS statement order by a different field than your Access statement

Dale Fye

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.

D B

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.

bfuchs

ASKER

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

bfuchs

ASKER

I don't see your attached error message?

I just attached, refresh the page.
Thanks,
Ben

Anders Ebro (Microsoft MVP)

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.

bfuchs

ASKER

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

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.