forms based queries

please help i am new to sql and my old access 2000 program used forms based queries.

the user simply input the patients last name and first name and the form would open up every record that had a matching combo to edit and if the combo didnt exist the form was ready for new input.

im under the understanding that sql doesnt support forms based queries and no i would like to know how to open my access form but first prompt users for what last name and first name they are looking for then fill the recordset with just those patients.

i have upsized my access 2000. mdb file to an access project now adp.
so im not using linked tables anymore or this would not be a problem.

this is very urgent for me to fix . i have already fixed all my vb code to make my chart number automatically. now im stuck on getting a prompt for a user to enter info to fill a form.Try using a separe form that is Modal = yes. This forces them to close the form before opening the next form. Then on event Form.Close check to make sure variables (names) are set properly (not empty).

Forms authentication and URL access

Ive written a security extension based on microsoft samples, i can call the
SOAP API and get authenticated but i dont know what to do so i can use this
authentication method using URL access, i need that since i want to be able
to keep the toolbar.Rodrigo,
The typical integration scenario for Forms Autnentication and web-based URL
reporting is:
1. You have a web app which authenticates the user on the server side.
2. The web app calls LogonUser SOAP API.
3. The web app utilizes the Report Viewer or another approach (static
hyperlinks, client-side javascript, etc) to submit URL-based report
requests.
So, once the user is authenticated the we app just needs to submit report
requests on the client side of the application. As long as the
authentication ticket (cookie) is passed successfully, the reports should
render fine subject to aurhorization restrictions.
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Rodrigo Sánchez" <rodrigoss@.netshoreprogramming.com> wrote in message
news:%23p%23QEV5jEHA.704@.TK2MSFTNGP09.phx.gbl...
> Ive written a security extension based on microsoft samples, i can call
the
> SOAP API and get authenticated but i dont know what to do so i can use
this
> authentication method using URL access, i need that since i want to be
able
> to keep the toolbar.
>|||thanks, ill review my implementation and get back to you with more detail if
needed
"Teo Lachev" <teo.lachev@.nospam.prologika.com> escribió en el mensaje
news:%23rrIi15jEHA.592@.TK2MSFTNGP11.phx.gbl...
> Rodrigo,
> The typical integration scenario for Forms Autnentication and web-based
URL
> reporting is:
> 1. You have a web app which authenticates the user on the server side.
> 2. The web app calls LogonUser SOAP API.
> 3. The web app utilizes the Report Viewer or another approach (static
> hyperlinks, client-side javascript, etc) to submit URL-based report
> requests.
> So, once the user is authenticated the we app just needs to submit report
> requests on the client side of the application. As long as the
> authentication ticket (cookie) is passed successfully, the reports should
> render fine subject to aurhorization restrictions.
>
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Rodrigo Sánchez" <rodrigoss@.netshoreprogramming.com> wrote in message
> news:%23p%23QEV5jEHA.704@.TK2MSFTNGP09.phx.gbl...
> > Ive written a security extension based on microsoft samples, i can call
> the
> > SOAP API and get authenticated but i dont know what to do so i can use
> this
> > authentication method using URL access, i need that since i want to be
> able
> > to keep the toolbar.
> >
> >
>|||Teo - Is it a requirement of Forms Authentication that the users who are
authenticated to the ASP.NET web app have credentials defined in reporting
services? If not, how can you manage that user's rights?
thanks
"Teo Lachev" wrote:
> Rodrigo,
> The typical integration scenario for Forms Autnentication and web-based URL
> reporting is:
> 1. You have a web app which authenticates the user on the server side.
> 2. The web app calls LogonUser SOAP API.
> 3. The web app utilizes the Report Viewer or another approach (static
> hyperlinks, client-side javascript, etc) to submit URL-based report
> requests.
> So, once the user is authenticated the we app just needs to submit report
> requests on the client side of the application. As long as the
> authentication ticket (cookie) is passed successfully, the reports should
> render fine subject to aurhorization restrictions.
>
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Rodrigo Sánchez" <rodrigoss@.netshoreprogramming.com> wrote in message
> news:%23p%23QEV5jEHA.704@.TK2MSFTNGP09.phx.gbl...
> > Ive written a security extension based on microsoft samples, i can call
> the
> > SOAP API and get authenticated but i dont know what to do so i can use
> this
> > authentication method using URL access, i need that since i want to be
> able
> > to keep the toolbar.
> >
> >
>
>|||No credentials, just security policies in the report catalog. You would
typically use the Report Manager to set up your user security policy just
like you would normally do with Windows authentication.
Please note though that you don't have to register invividual users.
Instead, if it makes sense, you can group users into application-defined
roles outside of the Report Server. Then, in the Report Manager you will set
up security policies based the roles not users for easier maintainance.
Finally, in your CheckAccess overloads you need to take extra steps to find
out which role the user belongs to and authorize the user based on the user
role membership
Does this confuse even more :-)
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"ISGADMIN" <isgadmin@.newsgroup.nospam> wrote in message
news:9ABD45EE-390D-4272-A0AC-119114A0638E@.microsoft.com...
> Teo - Is it a requirement of Forms Authentication that the users who are
> authenticated to the ASP.NET web app have credentials defined in reporting
> services? If not, how can you manage that user's rights?
> thanks
> "Teo Lachev" wrote:
> > Rodrigo,
> >
> > The typical integration scenario for Forms Autnentication and web-based
URL
> > reporting is:
> > 1. You have a web app which authenticates the user on the server side.
> > 2. The web app calls LogonUser SOAP API.
> > 3. The web app utilizes the Report Viewer or another approach (static
> > hyperlinks, client-side javascript, etc) to submit URL-based report
> > requests.
> >
> > So, once the user is authenticated the we app just needs to submit
report
> > requests on the client side of the application. As long as the
> > authentication ticket (cookie) is passed successfully, the reports
should
> > render fine subject to aurhorization restrictions.
> >
> >
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "Rodrigo Sánchez" <rodrigoss@.netshoreprogramming.com> wrote in message
> > news:%23p%23QEV5jEHA.704@.TK2MSFTNGP09.phx.gbl...
> > > Ive written a security extension based on microsoft samples, i can
call
> > the
> > > SOAP API and get authenticated but i dont know what to do so i can use
> > this
> > > authentication method using URL access, i need that since i want to be
> > able
> > > to keep the toolbar.
> > >
> > >
> >
> >
> >

Forms authentication and report publishing from Visual Studio

Hello,
I am using forms authentication to access RS. I can publish reports from the
RS interface, but I somehow can not publish reports from within the VS2000.
It asks me for a password and username and I supply those, but without an
error message it asks me again and again, with no success.
Any ideas on how to publish from VS?
ThanksDid you solve this or anyone else for that matter? I have the sam
problem. :

Forms Authentication

I've successfully set up RS to use Forms Authentication. However, here's
something strange. The first time I access Report Manager (say after a
reboot) and enter the admin credentials, RS churns and churns, then I end up
back at the login screen. If I enter the same credentials again, I get
logged in successfully.
Any one else experience this issue?
ThxAre you using the same server name to access the report server in your
browser as is set in the config files? If not, the cookie won't be getting
through due to cross-(web)domain security.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"FNDS" <absolutelynospam@.nodomain_.com> wrote in message
news:ezRzXl%239EHA.1396@.tk2msftngp13.phx.gbl...
> I've successfully set up RS to use Forms Authentication. However, here's
> something strange. The first time I access Report Manager (say after a
> reboot) and enter the admin credentials, RS churns and churns, then I end
> up back at the login screen. If I enter the same credentials again, I get
> logged in successfully.
> Any one else experience this issue?
> Thx
>|||"Jeff A. Stucker" <jeff@.mobilize.net> wrote in
news:ugC03WC#EHA.3708@.TK2MSFTNGP14.phx.gbl:
> Are you using the same server name to access the report server in your
> browser as is set in the config files? If not, the cookie won't be
> getting through due to cross-(web)domain security.
>
So, how do you address this issue in a hardware load balanced, web farm?
We have a single URL (domain) (ex. http://reporting.xxxxxxx.com/Reports)
but each machine naturally maintains a unique IP that I'm assuming is being
used by each machine when setting the cookie. The web.config files have
matching <machinekey> values to allow for Forms Auth in a web farm but I
don't know how to control the domain of the cookie.

Formatting syntax

Hi,

I am trying to convert an MS Access query to SQL script.The query builds a 16 digit field by combining 3 different columns.

access query

Select

"SP"& Format([CusNum],"000")& " "& Format$([InVoiceNum],"00000000") & "-" & Format$(,"00") AS RefNo,

From PurchaseOrder

RefNo

SP053 00000001-00

SP053 00000010-00

SP05314556895-00

SQL query to replace access query

Select

'SP'+Isnull(Cast(CusNumasvarchar(3)),'000')+''+Isnull(Cast(InVoiceNumasvarchar(8)),'00000000')+'-'+Isnull(Cast(PIasvarchar(2)),'00')

From dbo.PurchaseOrder

SP0531-0

SP05310-0

SP05314556895-00

I need some help in putting together a query that will give me the same results as produced by the access query.If InvoiceNum is less than 8 digits long I don’t know how to pad the field with 0000 to get it to be desired length.

Thanks for your help.

Nats

Nats:

Maybe something like this:

declare @.aNumber integer set @.aNumber = 715

select @.aNumber as [Input Number],
right ('0000000' + convert(varchar(8), @.aNumber), 8) as [formatted Number]

Input Number formatted Number
-
715 00000715

( Bemoaning my rustiness at Access; I wrote part of Access 97 Unleashed 2nd Ed, but I haven't used Access really since the previous millenium -- SHEESH! )

|||

Try the example below.

Chris

SELECT 'SP'

+ RIGHT('000' + ISNULL(CAST([CusNum] AS VARCHAR(3)), ''), 3)

+ ' '

+ RIGHT('00000000' + ISNULL(CAST([InVoiceNum] AS VARCHAR(8)), ''), 8)

+ '-'

+ RIGHT('00' + ISNULL(CAST(PI AS VARCHAR(2)), ''), 2)

FROM dbo.PurchaseOrder

--Note that PI should be in square brackets, however for some reason when I do this in the forum I end up with a piece of pizza, like so:

|||

Awesome works great...

Thanks a bunch!

Formatting problem

Hi there,

new to the forum, I have a question for you, hoping for your kind help.
We are using CR to porting a lot of Access report under .Net (VS 2003). We have encountered a lot of problem, and guessed we cannot simply copy-and-paste.

In order to manage developing phase better, and to obtain a cross-application use of that report, we have choosen to late-bind the report on the datasource, that is: the report is designed having just a .ttx (fields definition only) as database. Later at runtime we will provide a strong-typed dataset as datasource. This chain works better, but we are suffering some problems formatting Date, which came from Oracle as DateTime (this is the .Net native class too).

Altrough we have specified a DATE or DATETIME in .ttx file, setting the desidered format in the .rpt, data take the default format which include time (that we wish to hide). Thought is possible to size the field in appropriate manner, so the time ciphers will be hide, it is unclear what is happening under the hood and why format specification seems to be ignored.

Whe have tried to workaround the issue formatting server-side the value, and even assuring the value reach the report as String: in this case it is obvious the format issue is skipped, but we cannot group-by nor order-by date (in fact it is not a Date, it is a String and is ordered and grouped in its proper fashion).
Our desired format is dd/mm/yyyy.

Any suggestion will be greatly appreciated, really gratefully.

Best Reguards,
MarcoA little update,

it seems that value declared in the .rpt as DateTime will be formatted accordingly with format specification;
and values declared in the .rpt as Date will be formatted accordingly to thread default (which include time, and needs to include it).

Pleaser remark the type declaration in .ttx is the same (DATE). Do anyone know a better valid definition? Do anyone know how to force the report-level type, or force the fomat on the simple Date value?

I can add that seems (but is not demonstrated) that report entirelly created with CR.Net has Date as report-lever-type, where report originally created with CR 9 and opened and changed with CT.Net has DateTime as report-level-type. Odd, isn't it?

A word of clarity is strongly rewarded, for the good of my mind :)
Please, do not leave me alone with this trouble, if you can help soon.

Again best Reguards,

Marco|||I found the solution of my own,

it is once again the case who can lead to bad mistakes. If you have an "old" ttx, your DateTime field is marked as "Date" or "DATE" or "DATETIME", making no difference.
With the .Net version if you have a field marked as "Date" or "DATE" or "DATETIME" it will be recognized as a Date Field.
If you have a field marked as DateTime instead, it will be recognized as DateTime which is my desiderd type.

This DateTime field will match the type in the DataSet field, so the format will be applyied correctly, but if you have declared a Date field and at runtime it become DateTime, it will take the default format, because you have specified a Date format, not a DateTime one.

Formatting numbers in SQL

Hi
I need to format the output of a numeric value from SQL-server with a
chosen thousand-separator and decimal-separator.
The Access equivalent would be something like:
Select format(myNumber, "#.###,##") as myFormattedNumber from myTable;
I have searched the net for a solution, and found something about
FORMAT_STRING, but that doesn't seem to be usable in SQL-Server...
Can anyone help me with this?
Regards,
Johnny Nordtvedt, NorwayIn an N-Tier environment it's better to leave presentational features
out of the database and do them in your client/middle tier. Some users
might prefer to configure a different numeric format so it's probably
not something you ought to fix in a SELECT statement.
TSQL does have a CONVERT function, which provides some limited
formatting capability but only if you convert your numerics to a
string. Also, AFAIK the CONVERT function only supports comma as a
thousands separator by default. In reality you should find it much
easier to do the formatting in the client control or form that displays
the data.
David Portas
SQL Server MVP
--|||My problem is that these values is displayed in a Word-document, and the
program that collects these values and inserts them into word, is not
available for formatting, and will not ever be available. So basically, I
am stuck with formatting this in SQL-server.
Regards,
Johnny Nordtvedt, Oslo, Norway|||Is it possible to make a user defined function to do this? And how would I
go by to do this?
Still hope that someone can help me with this. I am certain someone has
done this before?
Regards,
Johnny Nordtvedt, Oslo, Norway|||DECLARE @.numeric NUMERIC(10,2)
SET @.numeric = 123456.78
SELECT
REPLACE(REPLACE(REPLACE(
CONVERT(VARCHAR(10),CAST(@.numeric AS MONEY),1)
,',','~') ,'.',',') ,'~','.')
David Portas
SQL Server MVP
--|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
There are formatting functions on Merge fields in Word. Read the Word
Help file (in English version):
Field Types and Switches
Field Reference
Switches
How to: Highlight merge field, hit Alt-9. Add switches at end of
field:
/# will give numeric format.
/#.#,## I believe will format the number 99999.00 as 99.999,00
Also, read the Word Help articles on Merge Fields.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQlWjTIechKqOuFEgEQLidACfWKC4/ST6Pc1f6yJC3xaIoOO4dC8AoKLM
k5KfIvuctrzjBfvZduWw4W0t
=lpsE
--END PGP SIGNATURE--
Johnny, Norway wrote:
> My problem is that these values is displayed in a Word-document, and the
> program that collects these values and inserts them into word, is not
> available for formatting, and will not ever be available. So basically, I
> am stuck with formatting this in SQL-server.

formatting numbers

I am using a MS Access ADP connected to SQL Server Data

In a view I have the following formula:

dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

this calculates cost + markup

I cannot get it to format in Currency

Ex:

Cost is $1.75
Markup is 2.00 (2%)
Total shows - 1.785000

I want $1.78

Also - I am using this formula to calculate the Quoted price for the Qty Entered

dbo.tblQuoteItem.Qty * dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

Using a Qty of 2 for above, I get 3.535000

I want $3.53

Any help is appreciated - ABSQL Server does not format output. That is the job of your interface (Access ADP, in this case).

Set the format of your form control or report field to display as currency.|||you could something like SELECT CAST(3.5553 as decimal(10,2)) but this rounds up (3.56). I would handle this in your VBA with a formatcurrency('your variable here',2).

This might round too. Or it might just cut off your percision. I forget. Test.

Formatting number in sql

How do you convert the following Access query to SQL Server ?
Format([tblA].[PRICE],"0.0000") AS Price
Thanks.SELECT Price = CONVERT(DECIMAL(10,4), tblA.Price)
..
Or, you could let the client application format for you.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23hU7bAp%23FHA.2420@.TK2MSFTNGP12.phx.gbl...
> How do you convert the following Access query to SQL Server ?
> Format([tblA].[PRICE],"0.0000") AS Price
> Thanks.
>|||fniles wrote:

> How do you convert the following Access query to SQL Server ?
> Format([tblA].[PRICE],"0.0000") AS Price
> Thanks.
Access is an application development environment as well as a database.
SQL Server isn't. The client application is what controls how your
numeric values are formatted, not SQL Server. You need to consult the
documentation for whatever client environment you are running.
David Portas
SQL Server MVP
--

Formatting Date /Time in SQL 2000

I used to have an Access 2k query that formatted this date field:
Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate
That gave me the date as this:
2006/03/30 00:00:01
Recently, we moved the data to a SQL 2000 table, so now I need to
create a view that gives me this date in that same format like I was
able to get it with that access query.
The actual date stored in the SQL table is 03/30/20006.
We use (convert(varchar,getdate(),101)) to get only the actual date
without the time.
Can anyone help me figure out how to get the same date format in SQL
Server 2000?
Thanks.SELECT CONVERT(CHAR(10), GETDATE(), 111);
http://www.aspfaq.com/2464
<ILCSP@.NETZERO.NET> wrote in message
news:1143819658.697747.320460@.j33g2000cwa.googlegroups.com...
>I used to have an Access 2k query that formatted this date field:
> Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate
> That gave me the date as this:
> 2006/03/30 00:00:01
> Recently, we moved the data to a SQL 2000 table, so now I need to
> create a view that gives me this date in that same format like I was
> able to get it with that access query.
> The actual date stored in the SQL table is 03/30/20006.
> We use (convert(varchar,getdate(),101)) to get only the actual date
> without the time.
> Can anyone help me figure out how to get the same date format in SQL
> Server 2000?
> Thanks.
>|||First off, if you've changed to a SQL server 2000 back end I'd hope
you've moved to a sensible front end (like .NET) in which case this is
where you should be doing the formatting (.NET has a very easy and
powerful set of format strings).
otherwise, to my knowledge you can only use the set formats that
convert gives you, otherwise you'll have to build up your string using
CAST(DatePart(YEAR,mydate) as char(4)) + '/' + CAST(DATEPART(MONTH,
mydate) as char(2)) + '/' etc...
NOTE: it's important to cast them as strings, otherwise sql my
interpret this as an arithmetic expression and start dividing the year
by the month etc.
Cheers
Will|||SELECT replace(convert(varchar, getdate(), 120),'-','/') as todays_date|||Hi guys, I did use Aaron's date and then I added a string showing the
seconds and that worked out well..
Thanks for your suggestions.
By the way, yes, we're moving to .NET in the coming months.

Formating

how to format an integer in sql 2005

for ex: 1.09876 i want to format to 1.1

in access i can use : format(field,'0.0') as field1

thanks

Here You can't format directly.. You can use the following code...

Code Snippet

Select Cast(1.050009 as Numeric(5,1))

--or

Select Convert(Numeric(5,1),1.050009)

|||

Another alternative if you are interested in converting to character data is the STR function:

select str(1.09876,5,1) as Format

/*
Format
1.1
*/

Format a number to be a date format

I have a number field that looks like this:
8 characters long yyyymmdd
ie: 20051201
I can not change its type to a date field. In Access and Crystal I was able
to manipulate the number to make it look like a date ie: 12/01/2005
Can anyone help me figure out how to format the field like this?
thanks so much
--
Jeanne Conde, MCPWell, i guess it can be achieved by code snipet, pass value as argument
in code block and through VB syntax manipulate string in ne form.
Hope it helps|||The VB.NET function Data.Parse will be the way to go.|||Right click the field, click on properties.
In the 'format' section, choose cuistom and enter dd/mm/yyyy
That should do the trick
"Jeanne Conde" wrote:
> I have a number field that looks like this:
> 8 characters long yyyymmdd
> ie: 20051201
> I can not change its type to a date field. In Access and Crystal I was able
> to manipulate the number to make it look like a date ie: 12/01/2005
> Can anyone help me figure out how to format the field like this?
> thanks so much
> --
> Jeanne Conde, MCP

Form that creates a temporal table

Hey,
I have a kind-of-newbie question.
I need to create a form that will eventually produce a report in html /
asp.
while in access that would not be a problem, I'm learning my ways
through this project in the asp world.
So, I created an access database that should hold the information
entered by the form and then the report will be just retieval of
information from it.
The thing is that the form is built in the way that you select a few
options and then click "submit" and that's one line in the database,
after you click "produce" it should take all that information you
loaded into the database and make it a report.
How will the report's query look like? how will it know what
information was enterd by that specific user at that time? is there a
way to make a temporal table that starts with the first "submit" and
ends when you click "produce" ?
Thanks,
Tomer.The answer is: primary key.
The procedure that inserts the data should return the primary key of the
newly inserted row after the insert, then you can use the primary key to
retrieve the row(s) that you want to display in the report.
In SQL 2005 this is even simpler - use the OUTPUT statement.
ML