new to sql please help

i use access 2000 as my front end. i had a form named main with a couple of unbound fields on it . i used to put in last name and first name and then in a query i would put "like [forms]![main]![text0] & "*"
and also "like [forms]![main]![text2] & "*"

the lookup patient button would open my patient form based on the query results of what i typed in on the main form . ex: i type in riley it would give me my patient form with all the riley's in it.

how do i get my access form to open from information i am looking for in a query now that my whole database is back on a sql server.

by the way i upsized to a adp file no longer using mdb. please if possible explain in detail how to do this as i am having trouble with the syntax of sql. i understand i cant use "*" as a wildcard now but % instead.

thanks in advance.Hoe are you connecting to SQL Sever?

You shouldn't have to change your code...did you create linked tables?|||im assuming they not linked tables since it is now a access project i dont see the little globe when i used to use link tables.

i think everything is useing passthorugh queries now all my tables are on the sql server. just the forms and macros are on the frontend now.

my code i had to change was something like

dim db as dao.database
dim rs as dao.recordset

i had to change that to
dim conn as adodb.connection
dim rs as new adodb.recordset

and quite a few other things

if i left them linked the queries should have stayed working but im trying to get away from passing things through the jet engine and using the full power of sql .|||this was my old code to make a chartnumber based on the first and last name fields. this is the one that didnt work .

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|||Well this is a TOTAL guess...

If you're doing passthrus, you'll need SQL Server syntax..

(Why not just call stored procedures if you're making such a radical change?)

anyway...

SQL = "SELECT (MAX(CONVERT(int,(RIGHT(chartnumber))))
FROM tblpatientinfo
WHERE UPPER(LEFT(chartnumber,5)) = " _
& "'" UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

my front end skills have gotten rusty...

You might want to also look to post here:

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3|||i did get that code to work but my main real problem is how do i get user sitting at my main form where he has to enter a patients lst name and first then when he hits the lookup patient button it opens my patient record with only the patient he typed in from the previous form . this was easy on access just added the expression into the query . but sql doesnt seem to support forms based queries. so what i really need is what does sql use instead of forms based queries to prompt a user for what they want to fill a record with.

if i use select lname from tblpatientinfo where lname="riley"

my form will only give me patients with last name riley

but how do i get it to ask what name we are looking for i guess would be the real question here. do i make a stored procedure to prompt for the information and base the form on the stored procedure or does my from on open have to request what im looking for. i may not be useing the correct wording so please be patient but sql is a whole new world to me.
i tried useing a filter on my form but if i put the stored procedure in the filter it doesnt recognize the stored procedure name only access query names it seems.

New query based on SOME fields returned in another query

I need to have two queries, the second query uses some information from the first query but the second query's results is not a subset of the first query.

this is my first query

select name, date, company, valid
from tbl1, tbl2, tbl3
where valid=0

Then I need to take the person's name to do another query, but I don't need to look in the first set of results to get what I need. It is a brand new query just with the name as the parameter.

select name
from tbl4
where name = @.name

Right now I have the second query getting it's parameter from the first query in RS. The two queries are two different data sets BUT my second query is limited by the results from the first query. How do I get the second query to be it's own query not based on the first but get a parameter from the first query.

I know this is all very confusing. I apologize. I am trying to explain fully.

Please help, I am new to RS and quite new in SQL

Well, I think I solved my problem. I needed to create a sub report.

new line in bcp

I am exporting sql server table data to excel using bcp... But if any field
contain carrige return , that fields break up into to next line. Is their
any solution to itOne other way might be to create an ODBC connection to Excel and export
through this data source
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Vikram" <aa@.aa> wrote in message
news:ew#myvQPGHA.1532@.TK2MSFTNGP12.phx.gbl...
> I am exporting sql server table data to excel using bcp... But if any
field
> contain carrige return , that fields break up into to next line. Is their
> any solution to it
>|||If you do not want the carriage returns in the data, create a view
that returns all the columns of the table but removes the carriage
returns from the particular column(s) using REPLACE(). Then BCP out
from the view, instead of from the table.
If you must preserve the carriage returns in the data, don't use BCP.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Mar 2006 14:11:29 +0530, "Vikram" <aa@.aa> wrote:

>I am exporting sql server table data to excel using bcp... But if any field
>contain carrige return , that fields break up into to next line. Is their
>any solution to it|||But I cant use views as I am calling stored procedure from bcp... And stored
procedure return data from temp tables...
Is there is no way by which bcp can preserve carrige return in the data.
Because when we use DTS, it export the data perfectly. I even cannot use DTS
as sp return data from temp table...
Any suggestion will be helpful?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:uubc02t9b70m4qtavu7cohq4vmuvi1oqlr@.
4ax.com...
> If you do not want the carriage returns in the data, create a view
> that returns all the columns of the table but removes the carriage
> returns from the particular column(s) using REPLACE(). Then BCP out
> from the view, instead of from the table.
> If you must preserve the carriage returns in the data, don't use BCP.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Mar 2006 14:11:29 +0530, "Vikram" <aa@.aa> wrote:
>
field|||Vikram (aa@.aa) writes:
> But I cant use views as I am calling stored procedure from bcp... And
> stored procedure return data from temp tables...
You using the queryout option? Anyway, if you are already using temp
tables, you have all possibilities to modify the data.

> Is there is no way by which bcp can preserve carrige return in the data.
I guess the question is not how get BCP to preserve the CR in the data -
it bulks out whatever that is, but how to a file should look like for
Excel to accept it with the newlines preserved. I don't know Excel well
enough to say how the file should look like.
Once you are equipped with that knowledge, you can address this by either
formatting the data when you select it, or use a format file, to have BCP
to do it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx