Please Help Just Upsized Access To Sql And Now Code Doesnt Work

here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.

Public Function chartlookup()
Dim db As DAO.Database <--believe to be the problem
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String

SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

Set db = CurrentDb() <-- -problem
Set rs = db.OpenRecordset(SQL) <--problem

If IsNull([Forms]![fpatient]![chartnumber]) = False Then GoTo 400

If rs.EOF = False Then
If IsNull(rs!RecNum) = False Then
NewNum = rs!RecNum + 1
Else
NewNum = 1
End If
End If
'If NewNum = 1 Then

[Forms]![fpatient]![chartnumber] = UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & Format(NewNum, "000000")
'End If

400 End Function

basically if you typw in john smith this code would put smijo00001 into chartnumber field now i get a run-time error 91.What error are you receiving?

Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.

You have a few access specific casts there that are going to cause trouble for you.|||Change you dao to ado - using recordset and/or connection objects.|||Originally posted by Teddy
What error are you receiving?

Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.

You have a few access specific casts there that are going to cause trouble for you.

im receiving a run-time error 91 in the set rs= db.recordset(sql) statement but i know it coming from previous dim db as dao.database and dim rs as dao.recordset. i dont know how to convert this vb code to work with sql. maybe dim rs as sql.recordset and dim db as sql.database . please forgive my ignorance but i am new to sql and i dont know the syntax for sql. by the way thank you for the advice with ucase and cint not working . i tried a previous suggestion and turned dim rs as ado.recordset but the only option i get is adobe.recordset am i missing a reference .|||You need to use the ms ado 2.x library reference. Using adodb.recordset and adodb.connection - however, in your case you only need adodb.recordset.|||Hey
Who one saying that ODBC Connections not possible by DAO?

[QUOTE][SIZE=1]Originally posted by opcbriley
here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.

Public Function chartlookup()
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String

SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("costing", dbDriverNoPrompt)
.........

You may also open connection by using this code
remaining code will remain same
plz use it n reply me
thx|||You can use odbc - but don't - you are much better off using oledb (or use tds in .net). Better performance, resource usage and flexibility/functionality.|||yeah i know that OLEDB performance much better that dao but person asked abt DAO libraray n no one was there to reply by DAO libraray so I hv ginven reply by DAO library|||i appreciate all your help i was able to switch to ado connection i finally learned the syntax last night after about hour of reading. that code is working perfect now.

now on to find all my other problems that i have to change to be compatible with sql. i sure hope sql is worth all this trouble.

placing norms on a bar chart

Maybe someone can help. I'd like to do one of two things, depending on which is actually possible:

Place one chart on top of another so that, in addition to having a simple bar chart, I can also have a way of tracking comparison data on the same chart. (My preference is a line chart with diamond markers on top of a bar chart.)Within one chart, have one of the series be a different chart type. Once series could be a bar and the next could be a line with diamond markers. The catch is the diamonds would have to be placed right on top of their corresponding bars.

These are both things that I've been able to do in Excel, but am finding out that I've taken for granted just how versatile Excel is.

Thanks in advance!

Krdjis

Did you look at the "plot data as line" option (in Report Designer, go to the Chart Properties dialog. Click on the Data tab, select the data point you want to make a line, click 'Edit..'. In the chart value dialog, click on the Appearance tab and select 'Plot Data as Line'.)?

You may also want to check out this MSDN article on RS charts: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MoreSSRSCharts.asp

-- Robert

placing norms on a bar chart

Maybe someone can help. I'd like to do one of two things, depending on which is actually possible:

Place one chart on top of another so that, in addition to having a simple bar chart, I can also have a way of tracking comparison data on the same chart. (My preference is a line chart with diamond markers on top of a bar chart.)Within one chart, have one of the series be a different chart type. Once series could be a bar and the next could be a line with diamond markers. The catch is the diamonds would have to be placed right on top of their corresponding bars.

These are both things that I've been able to do in Excel, but am finding out that I've taken for granted just how versatile Excel is.

Thanks in advance!

Krdjis

Did you look at the "plot data as line" option (in Report Designer, go to the Chart Properties dialog. Click on the Data tab, select the data point you want to make a line, click 'Edit..'. In the chart value dialog, click on the Appearance tab and select 'Plot Data as Line'.)?

You may also want to check out this MSDN article on RS charts: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MoreSSRSCharts.asp

-- Robert

Placing labels on line chart

Hi,
Some of my charts have many data series on them and the colours can be
difficult to differentiate. I would like to include a label on the
actual line chart as the legend is not helpful - the colours are too
similar. Is this easy to do?
For an example see here:
http://tinyurl.com/3kkou
Thanks.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.htmlFollowing the provided link shows an error on the server side. Not sure if
this is exactly what you are looking for, but you may be interested in the
thread "bar chart help" and the example attached at the bottom of the
posting.
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=7f5be65f-e515-4993-9526-e9e85480c67a&sloc=en-us
For the line charts you may however want to have the labels e.g. only on the
first datapoint of the line and would need to use the First() aggregate
function to determine it.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eGPmtFZ7EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Some of my charts have many data series on them and the colours can be
> difficult to differentiate. I would like to include a label on the
> actual line chart as the legend is not helpful - the colours are too
> similar. Is this easy to do?
> For an example see here:
> http://tinyurl.com/3kkou
> Thanks.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html

Place one Chart on top of another one

I am trying to place one chart on top of another chart in report designer.
Everything looks go. So, I publish the report to Reporting Servers and for
some reason the graphs are placed next to one another instead of being on top
of each other. How can I fix this problem?
The reason why I am placing one chart on top of another chart is because I
can not figure out how to pass a value to CROSS AT property in a Bubble
chart. Does anyone know how to do this as well?
Thanks,
PeterHTML renderer does not support overlapping objects. You can try and render
it PDF or Jpeg and it will be overlapped, but not in HTML.
"Peter Revere" <PeterRevere@.discussions.microsoft.com> wrote in message
news:B9CBFE61-900D-4CD5-8712-CF3A8A72F6CA@.microsoft.com...
> I am trying to place one chart on top of another chart in report designer.
> Everything looks go. So, I publish the report to Reporting Servers and
for
> some reason the graphs are placed next to one another instead of being on
top
> of each other. How can I fix this problem?
> The reason why I am placing one chart on top of another chart is because I
> can not figure out how to pass a value to CROSS AT property in a Bubble
> chart. Does anyone know how to do this as well?
> Thanks,
> Peter|||Alexandre,
Thanks for the response. When I export the report to pdf is does work.
However, when I publish the report to Reporting Services the default viewer
is HTML. Is there away to set the default view in Reporting Services to PDF?
Peter
"Alexandre Mineev" wrote:
> HTML renderer does not support overlapping objects. You can try and render
> it PDF or Jpeg and it will be overlapped, but not in HTML.
> "Peter Revere" <PeterRevere@.discussions.microsoft.com> wrote in message
> news:B9CBFE61-900D-4CD5-8712-CF3A8A72F6CA@.microsoft.com...
> > I am trying to place one chart on top of another chart in report designer.
> > Everything looks go. So, I publish the report to Reporting Servers and
> for
> > some reason the graphs are placed next to one another instead of being on
> top
> > of each other. How can I fix this problem?
> >
> > The reason why I am placing one chart on top of another chart is because I
> > can not figure out how to pass a value to CROSS AT property in a Bubble
> > chart. Does anyone know how to do this as well?
> >
> > Thanks,
> > Peter
>
>|||If you are using URL access, add "&rs:Format=PDF" to the URL. You can read
about this in BOL in "Specifying a Rendering Format on a URL" underneath "URL
Access."
Ted
"Peter" wrote:
> Alexandre,
> Thanks for the response. When I export the report to pdf is does work.
> However, when I publish the report to Reporting Services the default viewer
> is HTML. Is there away to set the default view in Reporting Services to PDF?
>
> Peter|||Ted,
That worked thanks. I almost have it. This particular report has parameter
prompts. Is there away for the user to have the ability to pick there own
parameter values from the prompts or am I stuck hard coding in the values for
them?
"Ted K" wrote:
> If you are using URL access, add "&rs:Format=PDF" to the URL. You can read
> about this in BOL in "Specifying a Rendering Format on a URL" underneath "URL
> Access."
> Ted
> "Peter" wrote:
> > Alexandre,
> >
> > Thanks for the response. When I export the report to pdf is does work.
> > However, when I publish the report to Reporting Services the default viewer
> > is HTML. Is there away to set the default view in Reporting Services to PDF?
> >
> >
> > Peter
>|||Peter,
If you create a report parameter and don't specify any values, then the user
will get a text box where they can type anything. I think that's what you're
asking about -- usually people ask the opposite, how to create drop down
lists or how to fill those lists from a query. This is all well documented
in BOL, see the article "Using Parameters in a Report."
Ted
"Peter" wrote:
> Ted,
> That worked thanks. I almost have it. This particular report has parameter
> prompts. Is there away for the user to have the ability to pick there own
> parameter values from the prompts or am I stuck hard coding in the values for
> them?
> "Ted K" wrote:
> > If you are using URL access, add "&rs:Format=PDF" to the URL. You can read
> > about this in BOL in "Specifying a Rendering Format on a URL" underneath "URL
> > Access."
> >
> > Ted
> >
> > "Peter" wrote:
> >
> > > Alexandre,
> > >
> > > Thanks for the response. When I export the report to pdf is does work.
> > > However, when I publish the report to Reporting Services the default viewer
> > > is HTML. Is there away to set the default view in Reporting Services to PDF?
> > >
> > >
> > > Peter
> >

Pivot tables, Charts, Pivot chart

Hi all,

Can someone help me with some learning resources of Pivot Tables, Charts and Pivot Charts.

Thanx

Search for Pivot Table in Excel help you'll get some topics that explain these concepts and how to get started with them.

Here's one that came up that is online if you don't have Excel Help handy at the moment:

http://office.microsoft.com/training/training.aspx?AssetID=RC010136191033

Here are some more results to browse as well:

http://office.microsoft.com/en-us/results.aspx?Scope=DC%2CEM%2CES%2CFX%2CHA%2CHP%2CQZ%2CRC%2CTC%2CXT&Query=Pivot+Table

Hope that helps.

|||

Hello,

I am having issue with Performance of the Pivot Table connecting to OLAP Cubes. I am wondering if any of you have links to how to improve Pivot Table performance? Basically, I have done the backend optimization on the schema, but there should tricks on the Excel such as Connection Properties that I could use that could relate to MDX or Threshold Level that could speed up the Pivot Table performance. Below is some initial thoughts...

Non Empty Threshold=1

Any help is appreciated!

-Lawrence

Pivot tables, Charts, Pivot chart

Hi all,

Can someone help me with some learning resources of Pivot Tables, Charts and Pivot Charts.

Thanx

Search for Pivot Table in Excel help you'll get some topics that explain these concepts and how to get started with them.

Here's one that came up that is online if you don't have Excel Help handy at the moment:

http://office.microsoft.com/training/training.aspx?AssetID=RC010136191033

Here are some more results to browse as well:

http://office.microsoft.com/en-us/results.aspx?Scope=DC%2CEM%2CES%2CFX%2CHA%2CHP%2CQZ%2CRC%2CTC%2CXT&Query=Pivot+Table

Hope that helps.

|||

Hello,

I am having issue with Performance of the Pivot Table connecting to OLAP Cubes. I am wondering if any of you have links to how to improve Pivot Table performance? Basically, I have done the backend optimization on the schema, but there should tricks on the Excel such as Connection Properties that I could use that could relate to MDX or Threshold Level that could speed up the Pivot Table performance. Below is some initial thoughts...

Non Empty Threshold=1

Any help is appreciated!

-Lawrence