I am using some code from an access db that works how do I convert it in SQL?
Below is one line of the code. Thanks as always for your help.
[Loading Time In]-[order time in] AS [percent of load]
Anthony97
Last Activity: Mar 26 '10, 01:28 PM
Joined: Aug 17 '09
Location:
-
What do I use in Sql to replace a subtraction symbol
-
I changed the & to + between [reason codes1] + [reason codes2] I'm still getting invalid columns starting with xpd flag and it continues to the next flag -
invalid column name
This code works fine in access but i'm moving this query to sql it compiles fine but then when i try to execute it i get numerous errors of invalid columns. I've attached a sample of the code. As always your help is greatly appreciated.
Before this is the code from a access 2007 query
...Code:SELECT tships.[Load ], tships.[Ship Id], Trim([XPD flag] & " " & [ACDT flag] & " -
Anthony97 started a topic data type nvarchar and varchar are incopatible in the boolean and operatorin SQL Serverdata type nvarchar and varchar are incopatible in the boolean and operator
I'm trying to convert a query from access to sql server
UPDATE [Shipments]
SET [Shipments].[TCM Route Id] = LTrim([sfpostal/zip code]) & 'to' & LTrim([st postal/zip code])
FROM [Shipments]
WHERE ((([Shipments].[Route Id]) Is Null Or ([Shipments].[Route Id]) Like Null));
this code works fine in access but will not run in sql server. I'm at a loss. -
The multi-part identifier "Loads.STREET Name" could not be bound.
I'm getting this code to complie fine, the problem begins win I try and execute it? I'm stuck.
UPDATE [Loads]
SET [tblMaster_Loads _44].[Destination Plant] = [tblPlants].[Plant]
FROM [Loads]
INNER JOIN [tblPlants]
ON [Loads].[STREET Name] = [tblPlants].[STREET Name]
INNER JOIN [tblMaster_Loads _44]
ON [Loads].[Master Load Number] = [tblMaster_Loads _44].[Master... -
I'm trying to run a package in integration services
I'm trying to run a package in integration services and I'm getting the following errors:
Error: An OLE DB error has occurred. Error Code: 0x80040E37
Error:"componen t "OLE DB Destination"[284]" failed validation and returned validation status "VS_ISBROKE N".
I've made sure that the package exist on the server, and I have run it in visual studio and it works, any suggestions on what... -
Delerna,
Yes I would like to delete the extra records from the table, that's exactly what I'm looking to do. Here's the code I tried to use but it removes too much, I found it while browsing the internet, it was borken down into 4 parts and does not do what I really want it to do.
--part 1
select * from tblChargeAmts
--part2
with temptable as
(...Leave a comment:
-
removing duplicate records
I'm working on removing duplicate records from a table in sql.
I've tried using select distinct, and I've grouped by but I'm still running into some issues. Attached is a sample of the output table.... -
having trouble with joins
I can't get this code to run on SQL server without the following error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'JOIN'.
UPDATE [tblAcsChargeAmt s]
JOIN [T Accessorials_Es timated]
ON [tblAcsChargeAmt s].[Shipment Id] = [T Accessorials_Es timated].[Shipment Id]
AND [tblAcsChargeAmt s].[Load Id] = [T Accessorials_Es timated].[Load Id]
... -
trying to get a date in access query without hardcoding
I'm trying to get a date that's is 7 days earlier than the current date, so when I run the query I will always have records that came in within the last week.
Here's the section of the code I'm trying to get to work.
Code:from inventory where (status = 'SHIPPED') and (rec_rcv_date) > [currentdate]- 7
-
Combining Multiple Projects
I got started in SQL server for less than 5 months and I am curious if anyone has ever combined multiple individual projects into one solution? If yes do you have any recommendations on accomplishing this?
As always your suggestions are greatly appreciated. -
I asked did anyone know of a way to substitute something else for
Set fs=Application. FileSearch
This functionality existed in Access 2003 and now doesn't exist in Access 2007.. That was the question as stated earlier in the post thread.Leave a comment:
-
I've did additional research and a lot of the suggestions I've found online don't work. I am not well based in VBA, but I would think there has to be a way to substitute something else for Set fs=Application. FileSearch, the problem is finding an example that is close to what I'm trying to do.
What the form is supposed to do and did in Access 2003 is:
1) you enter a specific file which is in a csv format
2) click a...Leave a comment:
-
was that functionality available in access 2003? I think this might have been copied over from Access 2003Leave a comment:
-
I'm getting error message on a form that I've inherited
I get the following error message
Run-time error '2455' you entered invalid reference to the property FileSearch
'************** *************** *************** *************** *************** *************
...Code:Set fs = Application.FileSearch With fs ' Set search directory and file type to search for .LookIn = strFileLocation .FileName = strFileName ' Loop -
UPDATE tblShipments
SET tblShipments.[Order Type] = IIf([EDI TP Id] Like "web*","WEB ",
IIf([FrctStndRte 1/0]=1,"EDI",IIf([EDI TP Id] Is Null,"MAN","EDI ")));Leave a comment:
-
converting access 2007 update query to sql server 2005 update query
I ran this code on my access 07 db and it updates a number of records 61,425, I try and run the query in SQL Server 2005 modifying the IIf to CASE WHEN and I get a number of sytax errors.
Original code
UPDATE tblShipments
SET tblShipments.[Order Type] = IIf([EDI TP Id] Like "web*","WEB ",
IIf([FrctStndRte 1/0]=1,"EDI",IIf([EDI TP Id] Is Null,"MAN","EDI ")));... -
Thanks I'll check it out, will this work with the queries as well, that's my major area of concern.Leave a comment:
-
is there any tool that quickly converts access 07 to sql server 05?Leave a comment:
-
I modified the query and I'm getting the following errors at the end. Is there another way to represent a blank value in T SQL ?
SELECT
[Load Id],
[Shipment Id],
[End Date],
[SH Arv D Loc Date],
[End TS],
[SH Arv D Loc TS],
[SH Arv D Loc TS]-[End TS] AS [Variance],
[Conf Mode],
[Transport Means Grp],...Leave a comment:
No activity results to display
Leave a comment: