Trying To Convert Query From T-SQL To Access
May 9, 2008
First off, I apologise if this is classed as off topic as it's concerning access but I couldn't see another forum that was better suited for this question.
I'm trying to run this query in Access. I designed it in SQL Management Studio with a test database that I set up and it runs fine. However, when I tried to run it in access I get a syntax error on the subquery bit. Can anyone tell me what I'm doing wrong here?
UPDATE tblInvoice
SET tblInvoice.PeriodID =
(SELECT p.PeriodID FROM tblPeriod p
INNER JOIN
tblInvoice i
ON i.TransDate BETWEEN p.PeriodStartDate AND p.PeriodEndDate
WHERE i.InvoiceID = tblInvoice.InvoiceID)
Thanks in advance,
Andy.
View 3 Replies
ADVERTISEMENT
Aug 17, 2007
I am being asked to convert this access query into sql server 2000.
Access query
SELECT Left(Trim([Notes_Primary_Key]),InStr(Trim([Notes_Primary_Key])," ")) AS PcnPID, Trim([Notes_Secondary_Key]) AS PcnTicketNum
FROM tri_offnotes;
I'm new to SQL and am not familiar with which function replaces the InStr access function.
View 2 Replies
View Related
Apr 10, 2007
how to convert this access query to sql query
IIf([Total Bunches] > 0, Production * 1000 / [Total Bunches], 0) as Name2
SUM(IIf(BlockInYield = -1, [SIZE], 0)) as Name1
IIf(BlockInYield = TRUE, IIf(TC_M > 0, TC_M, TC_DENS *[SIZE]), 0) as Name
please......
View 3 Replies
View Related
Aug 21, 2013
I am trying to convert a code from access Db to sql code?
II(Left([dbo_ClaimLosstype].[dscr],4)="Liab","D_Liab","D_Property"),
View 4 Replies
View Related
Apr 2, 2008
Here is the access query
SELECT tbl1.evenID, Sum(IIf(IsNull([evpeDateCompleted]),0,1)) AS Completions, Count(tbl2.evpeID) AS Attendees, IIf(Count([evpeID])=0,"No Bookings",IIf(Count([evpeID])=Sum(IIf(IsNull([evpeDateCompleted]),0,1)),"Closed",IIf(Sum(IIf(IsNull([evpeDateCompleted]),0,1))>0,"Closing","In Progress"))) AS Status
FROM tbl1 LEFT JOIN tbl2 ON tbl1.evenID=tbl2.evpeEvenID
GROUP BY tbl1.evenID;
coding is my passion
View 2 Replies
View Related
Mar 18, 2008
I having trouble converting this access query to sql 2000, can anyone offer advice? The where clause is troubling me.
---Access Query---
SELECT DISTINCT Procedures.PatientNumber,
rocedures.TicketNumber,
Procedures.Counter, Procedures.DateOfService, Policies.PatientNumber, Policies.CarrierCode,
Policies.EffectiveDate, Policies.Employer, Policies.FirstName, Policies.GroupNumber, Policies.LastName,
Policies.MiddleInitial, Policies.PolicyHolderBirthdate, Policies.PolicyNumber,
Policies.PrimaryPolicyIndicator, Policies.TerminationDate, Procedures.ProcedureCode,
Procedures.PrimaryPolicyCarrierCode
FROM Procedures LEFT JOIN Policies ON (Procedures.Counter = Policies.Counter)
AND (Procedures.PrimaryPolicyCarrierCode = Policies.CarrierCode)
AND (Procedures.PatientNumber = Policies.PatientNumber)
WHERE (((Procedures.DateOfService)>=[effectivedate]
And (Procedures.DateOfService)
<=IIf(IsNull([terminationdate]),Now(),[terminationdate])))
OR (((IsNull([procedures].[primarypolicycarriercode]))<>False))
View 2 Replies
View Related
Jun 28, 2005
I have the following query created in MS Access that I would like to convert to MS SQL Server, but I can't get the IF logic to work properly, any help you can provide would be greatly appreciated.
Code:
SELECT Customer.Last_Name, Customer.First_Name, Customer.Middle_Name, Customer.Address, Customer.City, Customer.Region, Customer.Postal_Code, Customer.Country, Orders.Order_ID, Customer.Customer_ID, Employees.LastName, Employees.FirstName, Order_Line.Item_ID, Item.[Long Description], Order_Line.Units_Purchased, Order_Line.Price, Order_Line.Discount, CCur(Order_Line.Price*[Units_Purchased]*(1-[Discount])/100)*100 AS ExtendedPrice, Store.Store_Address, Store.Store_City, Store.Store_State, Store.Store_Zip_code, Store.Store_Phone_Number, Store.Store_Fax_Number, Item.Taxable_Nontaxable,
IIf(Item.Taxable_Nontaxable=Yes,([ExtendedPrice]*Tax_Table.Tax_Rate),([ExtendedPrice]*0)) AS Tax_Amt,
Tax_Table.Tax_Rate
View 3 Replies
View Related
Jan 30, 2008
I'm having trouble converting this access query into a sql 2000 query.
Your help would be appreciated:
IIf(nz([PTicketNum],"M999Z")="0","M999Z",IIf(Trim(nz([PTicketNum],"M999Z"))="","M999Z",nz([PTicketNum],"M999Z")))
Here is what I have, but I'm not confident it is correct:
CASE WHEN (PTicketNum = '0' OR PTicketNum IS NULL) THEN 'M999Z' else PTicketNum END AS Ticket
View 1 Replies
View Related
Jun 6, 2008
I'm trying to convert an Access database application to an ASP.NET application with SQL Server 2005 as the database backend. However, I'm having trouble converting some of the queries to SQL stored procedures. Here's an example Access query that I'm trying to convert into working SQL:
SELECT DISTINCTROW EXISTS (SELECT AccountCodeID
FROM AccountCodes
WHERE CodeID = 22 AND AccountCodes.AccountID = Accounts.AccountID) AS FullTimeInfo,
EXISTS (SELECT AccountCodeID
FROM AccountCodes
WHERE CodeID = 24 AND AccountCodes.AccountID = Accounts.AccountID) AS ShortTermInfo,
EXISTS (SELECT AccountCodeID
FROM AccountCodes
WHERE CodeID = 10 AND AccountCodes.AccountID = Accounts.AccountID) AS GeneralInfo,
Accounts.*
FROM Accounts
INNER JOIN AccountCodes
ON Accounts.AccountID = AccountCodes.AccountID
WHERE (((Accounts.SummitID)=@SummitID) AND ((AccountCodes.CodeID)=10 Or (AccountCodes.CodeID)=22 Or (AccountCodes.CodeID)=24))
ORDER BY Accounts.LastName, Accounts.FirstName
My understanding is that EXISTS can only be used in the WHERE clause in SQL. Any suggestions on how to properly rewrite this?
Jon
View 1 Replies
View Related
Aug 10, 2007
How do I write this query that was written in access to sql?
SELECT Max(tri_InsrTran.claimnum) AS MaxOfclaimnum, tri_InsrTran.trannum
FROM tri_InsrTran
GROUP BY tri_InsrTran.trannum;
View 2 Replies
View Related
May 17, 2006
SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,UCASE(Buildings.BuildingName) AS BuildingName,Buildings.MasterPlanCode, Buildings.UniformBuildingCode,Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") ASdBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") ASdCoveredUnenclosedGrossArea,IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,Buildings.YearConstructed, Format$([NumberLevels],"00") ASdNumberLevels, Format$([UnrelatedGrossArea],"0000000") ASdUnrelatedGrossArea, Buildings.YearLatestImprovement,UCASE(Buildings.Address) AS Address, Buildings.CityCode,CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,Format$([Mechanical],"0000000") AS dMechanical,Format$([Custodial],"0000000") AS dCustodialFROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =Buildings.CityCodeORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;Please if anyone can help me in Converting the above given Access Queryto Sql Server. I don't know which function to use for format$, IIF. Iwould really appreciate your suggestions.Thanks,
View 8 Replies
View Related
Jul 15, 2006
The following is my code for Access... can someone help me convert it to sql:
My Connectionstring is "server=(local);database=Database;trusted_connection=true"
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
Sub btnLogin_OnClick(Src As Object, E As EventArgs)
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim intUserCount As Integer
Dim strSQL As String
strSQL = "SELECT COUNT(*) FROM tblLoginInfo " _
& "WHERE username='" & Replace(txtUsername.Text, "'", "''") & "' " _
& "AND password='" & Replace(txtPassword.Text, "'", "''") & "';"
myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & Server.MapPath("login.mdb") & ";")
myCommand = New OleDbCommand(strSQL, myConnection)
myConnection.Open()
intUserCount = myCommand.ExecuteScalar()
myConnection.Close()
If intUserCount > 0 Then
lblInvalid.Text = ""
FormsAuthentication.SetAuthCookie(txtUsername.Text, True)
Response.Redirect("login_db-protected.aspx")
Else
lblInvalid.Text = "Sorry... try again..."
End If
End Sub
</script>
View 4 Replies
View Related
Nov 4, 2007
Dear All,
I would like to convert from Access To SQL DB undervisual Studio.Net 2005...
How can I do it easily, or if there any software to do this automaticlly, please your help..
Awaiting your valuable reply.
Many thanks in advance for your cooperation and continuous support....
View 6 Replies
View Related
Feb 16, 2005
I am currently in the process of writing an application to convert an Access database to SQL. Basically, I have created an odbc link in access and then I transfer the data from the access table to the linked table.
The problem is where I have to transfer the Identity keys. I can transfer all the data but the keys. They will auto increment. I tried using INSERT_IDENTITY tablename ON but it just returns an error mosty of the time. I have gotten it to transfer once or twice by stepping through.
Here is the basic code:
The code that causes everything to error is commented out.
Private Sub CopySQLRecordSet(ByVal stTableName As String, _
Optional ByVal blIdentity As Boolean = True, _
Optional ByVal stSearch As String = "")
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
Dim adoCommand As New ADODB.Command, stCommand As String
Dim adoSQLCommand As New ADODB.Command
Dim errorString As String
On Error GoTo ErrorCopying
PrgPart.Value = PrgPart.Value + 1
If blCancelPressed Then End '???fix later
cat.ActiveConnection = db1 'This doesn't seem to work with our normal settings for spectrumDbase
tbl.ParentCatalog = cat
tbl.Name = "dbo_" & stTableName
tbl.Properties("Temporary Table") = False
'possibly, this line will work for Oracle as well
tbl.Properties("Jet OLEDB:Link Provider String") = "odbc;DSN=
;DATABASE=database;"
tbl.Properties("Jet OLEDB:Remote Table Name") = stTableName
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Table Hidden In Access") = False
tbl.Properties("Jet OLEDB:Cache Link Name/Password") = False
cat.Tables.Append tbl
Suspend 1
adoCommand.CommandType = adCmdText
adoCommand.ActiveConnection = database
adoSQLCommand.CommandType = adCmdText
adoSQLCommand.ActiveConnection = rsDbase
'spectrumDBase.BeginTrans
'stCommand = "BEGIN TRANSACTION " & vbNewLine _
' & "go" & vbNewLine
'If there is an identity field in the table, it must be temporarily disabled
'to insert from an foreign DB.
'If blIdentity Then
' stCommand = stCommand & "SET IDENTITY_INSERT dbo_" & stTableName & " ON " & vbNewLine & "GO" & vbNewLine
'adoCommand.CommandText = stCommand
'adoCommand.Execute
'End If
'insert the records from the source table
stCommand = ""
stCommand = stCommand & "INSERT INTO dbo_" & stTableName _
& " SELECT * FROM " & stTableName & vbNewLine _
& "GO" & vbNewLine
'stCommand = stCommand & "COMMIT TRANSACTION"
adoCommand.CommandText = stCommand
adoCommand.Execute
'clean up
' stCommand = stCommand & vbNewLine & "DROP TABLE dbo_" & stTableName & vbNewLine & "GO"
' adoCommand.CommandText = stCommand
' adoCommand.Execute
'reinsert identity property
' If blIdentity Then stCommand = "SET IDENTITY_INSERT " & stTableName & " OFF" & vbNewLine
'Block statement, going to leave this out for now
'stCommand = stCommand & "go" & vbNewLine _
'& "COMMIT TRANSACTION"
'adoCommand.CommandText = stCommand
'adoCommand.Execute
Set adoCommand = Nothing
Set adoSQLCommand = Nothing
Exit Sub
ErrorCopying:
'need to save this to a string so it doesn't reset when Resume occurs
errorString = Err.Description
' MsgBox "Error copying the [" & stTableName & "] table." & vbNewLine _
' & "Error: " & errorstring
Resume errorCatch
errorCatch:
On Error Resume Next
'set a log
Dim fso As FileSystemObject, fStream As TextStream
Set fso = New FileSystemObject
Set fStream = fso.OpenTextFile(App.Path & "DBTransfer.log", ForAppending, True)
fStream.WriteLine "***Error copying the [" & stTableName & "] table."
fStream.WriteLine " Error: " & errorString
fStream.WriteLine " "
fStream.Close
Set fStream = Nothing
Set fso = Nothing
'clean up
adoCommand.CommandText = "DROP TABLE dbo_" & stTableName
adoCommand.Execute
If blIdentity Then
'reinsert identity property
adoSQLCommand.CommandText = "SET IDENTITY_INSERT " & stTableName & " OFF"
adoSQLCommand.Execute
End If
Set adoCommand = Nothing
Set adoSQLCommand = Nothing
End Sub
View 8 Replies
View Related
Jan 24, 2008
Who convert access dba to ms sql 2000 standard.
I try with access conversion, but some buttons commands and rules donīt work.
The dba contains tabs, forms, relīs, macros, modules and relations with tabs and examination images. Itīs possible convert everything without damage or transform this itens?
The dba contains critical information about identification and personal clinic story about patients, but the access capacity itīs out (2GB) and i need to expand this dba.
I have some urgency to this problem, itīs depends to buy a windows 2008 server and mssql server 2008.
I hope everyone help me.
Thanks,
View 1 Replies
View Related
Feb 9, 2004
hi all,
How to convert a database from MS Access to MS SQL, is there some tool like that?
View 1 Replies
View Related
Jun 24, 2008
Hello does anyone know how to convert this access code to sql. Also is there a program out there that will do it for you.
IIf([Ceridian]![UnionIndicator]="U" And [YearsService]>=3,[HealthBenefits]![medplan.Bi-WeeklyURBio-Rad]*26,[HealthBenefits]![medplan.Bi-WeeklyBio-Rad]*26) AS MedicalCostER, IIf([Ceridian]![UnionIndicator]="U" And [YearsService]>=3,[medplan]![Bi-WeeklyUREE]*26,[medplan]![Bi-WeeklyEE]*26) AS MEdicalCostEE
View 1 Replies
View Related
Jul 31, 2007
I have an Access database and would like to convert it to an MS SQL database?!
View 4 Replies
View Related
Oct 15, 2007
I have an Access database that used to produce a mass of Performance Indicators from Access tables. The data is now held on SQL Server and I run the Access queries from the SQL tables. I wouldlike to move all the queries over to SQL but not sure if I can do that. Here's an example of one of the queries (the SQL view)
SELECT tblCalls.*
FROM tblCalls
WHERE (((tblCalls.Call_date)>=[Forms]![ReportParams]![SDate] And (tblCalls.Call_date)<=[Forms]![ReportParams]![EDate]) AND ((tblCalls.NotAccepted)=False) AND ((tblCalls.Completed_time) Is Not Null) AND ((tblCalls.Category)="fly tipping"));
SELECT Month([Call_date]) AS Mnth, DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0) AS Qtr, Sum(Work_Days([Call_date],[Completed_time])) AS RespTime, Sum(1) AS Count, Sum(Work_Days([Call_date],[Completed_time]))/[Count] AS AvgTime
FROM qryFlyTippingStatsSummary1
GROUP BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0)
ORDER BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0);
View 2 Replies
View Related
Dec 16, 2007
what's the best way in sql 2005 to convert a access db?
View 1 Replies
View Related
Dec 28, 2006
Can I convert .MDB file to SQL Server database or I need to develop SQLserver database from begin, every table, every field?
View 2 Replies
View Related
Jul 20, 2005
I'm going crazy trying to convert an Access Function to SQL.From what I've read, it has to be done as a stored procedure.I'm trying to take a field that is "minutes.seconds" and convert it to minutes.This is what I have in Access:Function ConvertToTime (myAnswer As Variant)Dim myMinutesmyMinutes-(((((myAnswer * 100)Mod 100/100/0.6)+(CInt(myAnswer-0.4))))ConvertToTime =(myMinutes)End FunctionWhen I tried to modify it in SQL:CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTimeFunction ConvertToTime(myAnswer As Variant)Dim myMinutesmyMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))ConvertToTime=(myMinutes)EndI get an error after ConverToTime.
View 2 Replies
View Related
Oct 18, 2006
Hello to everybody,
I had just installed SQLserver Express. I'd like to know if tool to migrate an Access db to SQLexpress exists
Thank you
View 6 Replies
View Related
Apr 28, 2008
how To Convert .btr file in SQL OR ACCESS
please help me
tanks
View 4 Replies
View Related
Dec 15, 2006
Is there anyway to convert an SQL Express database to SQL server or Access format?
View 1 Replies
View Related
Oct 3, 2007
I have a sql server database and i need to convert to Access, how can i do this using sql server?
View 4 Replies
View Related
Dec 7, 2007
Sorry if this has been answered before. Could not find any answers.
OS Vista Business, SQL Server 2005 Express.
I have a CSV file which I imported to Access only 100 records with 8 fields. Then ran Tools >> Database Utilities >> Upsizing Wisard. The result was I exported the table field headers but not the data. Is there any method on how I can get the field headers and data into SQL Server Express?
View 2 Replies
View Related
Apr 7, 2004
I am trying to convert code I have working for access to work with SQL.
fldName, fldEmail, ID are the names in the database. recNum does have the value of the record that I want to edit. Here is the error I am getting.
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '?'.
And here is the stack trace (which I donât know how to read except for the line the error is on)
[SqlException: Line 1: Incorrect syntax near '?'.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
goodellweb.adm_contact.editNow_Click(Object sender, EventArgs e) in C:Inetpubwwwrootwebrootgoodellwebadmadm_contacts.aspx.vb:306
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1315
here is my code.
Dim editSQL As String = "Update tbEmail Set fldName=?, fldEmail=? Where ID=?"
Dim SqlConn As New SqlConnection(ConnStr)
Dim Cmd As New SqlCommand(editSQL, SqlConn)
Cmd.Parameters.Add(New SqlParameter("@fldName", nameEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@fldEmail", emailEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@recNum", recNum))
SqlConn.Open()
Try
Cmd.ExecuteNonQuery()
Finally
SqlConn.Close()
End Try
Response.Write("recNum " & recNum & " <br>")
Thanks
Michael
View 4 Replies
View Related
Dec 8, 1999
Hello sir,
I send you this message to told hwo can i do to convert one database which is in Access97 to SQL Server6.5.
I must create a interface in the web which is able to interrogate the database that the reason why i want to do that.
Thanks for any help in advance.
View 2 Replies
View Related
Jun 25, 2007
I have an Access application with various DB's linked together. One of the DB's contains a field, SOURCECODE, which was mistakenly entered in as an nvarchar(4000) field in MS Sql Server. When I link the table, Access converts it to a memo field.
There will never be more than 10 chars in that field. The company that created the DB says its will be too risky to change the data type. I need to link this field to another field that's a VARCHAR.
How can I do this? Access doesn't allow the CAST feature.
View 1 Replies
View Related
Nov 25, 2004
I have used the Upsizing Wizard in Access 2003 to convert a database to SQL. When I open it using SQL 2000 Desktop edition, I am finding a problem with the views and stored procedures.
When I open a query in design view, the tables are displayed in the upper pane without the details of the individual fields. All I get is a tick box next to *(All Columns)
If I create a new table, the same thing happens. If I create a new database, then make a table within it, any queries behave normally, with all the fields displayed.
I would be grateful for any ideas
View 3 Replies
View Related
Feb 7, 2007
is it possible to convert an Access database to SQL Server 2005 or 2000?
View 3 Replies
View Related
Jan 13, 2004
ive been having a lot of trouble converting this last line from my access database to sql server. i was hoping someone could help
order by Name, IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title
any suggestions?
View 5 Replies
View Related
