|
Is that valid for MYSQL? I thought that was SQL Server syntax?
|
|
|
|
|
Yes it is SQL Server syntax.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
dgreat1 wrote: Any ideas?
Use parameters, let ADO.NET figure it out.
|
|
|
|
|
Good Afternoon,
I am fairly new to access coding. I am trying to write a SQL in access that will get make a packing slip based upon the return shipping address, but if the return shipping address is null, then the shipping would be the order address.
I started with this, but it is not working
=IIf(IsNull([Returns]![Address]),[ Orders]![Addr1], [Returns]![Address])
& IIf(IsNull([Returns]![Address]),[ Orders]![Addr2],[Returns]![Address])
& IIf(IsNull([Returns]![Address]),[ Orders]![Addr3], [Returns]![Address])
& IIf(IsNull([Returns]![City]),[ Orders]![City],[Returns]![City])
& IIf(IsNull([Returns]![State]),[ Orders]![State],[Returns]![State])
& IIf(IsNull([Returns]![Zip]),[ Orders]![Zip],[Returns]![Zip])
& IIf(IsNull([Returns]![Country]),[ Orders]![Country],[Returns]![Country])
& IIf(IsNull([Returns]![Phone]),[ Orders]![Phone],[Returns]![Phone])
So, what I wanted was the person wanted to return a product, and his return address is different from his order address, So I wanted some kind of jet SQL script to say if this person put in a return address then the packaging slip would use that but if that person did not put in a return address, then it would use the order address where that return address is null.
I appreciate anyones help on this, thank you
|
|
|
|
|
IIRC you can create functions within Access where you can write the code to return 1 address (decision is made in the function) and then use the function in your select statement.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can I get an example of this?
|
|
|
|
|
Nope, I have not used Access in 10 years and that is the way I like to keep it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can anyone tell me how to change time for a DB in SQL Server 2005? I need to correct the time for a database, thanks.
|
|
|
|
|
You set the time of the windows server.
|
|
|
|
|
so you mean the database will use the time of windows server. my issue is for a specific database, say DB002, I wanna change its current time setting (set its current time 01-01-2006), can I do that?
|
|
|
|
|
SQL Server uses windows clock which then again uses clock from BIOS. So you can have only one time at the server which is the time of all databases.
I don't know what happens if you set up a virtual PC. Since virtual PC has emulated bios it could be possible to have "several times" on one physical server. But... this is just a guess.
|
|
|
|
|
maybe we got into a wrong direction. let me clarify this issue. in one of my databases, I have some tables having timestamp columns, now I just wanna change displayed time for those columns. Can I just use some scripts like: update TABLE ** set **=getdate()
|
|
|
|
|
Sorry, I misunderstood you.
Is the column's data type timestamp? Timestamp-typed columns cannot be modified via DML. Moreover they are not timestamps, but only unique identifiers that database engine update.
|
|
|
|
|
data type for the column is 'datetime', column name could be anything.
|
|
|
|
|
If it's datetime then you can simply set a desired date to the column:
UPDATE TableName SET DateTimeColumn = CONVERT(datetime, '01/01/2006', 101) ...
which will use midnight for time portion
or you can use current date and subtract desired amount of days like:
UPDATE TableName SET DateTimeColumn = GETDATE() - 975 ...
which will today end up to 01/01/2006, but using current time
FYI, if the column name would be timestamp, it is hardcoded to many libraries that is must have timestamp as data type. This will lead to problems in some cases (typically in cursor handling) if the actual datatype isn't timestamp.
|
|
|
|
|
|
|
i created a trigger.. but its not taking effect.. help me plz
CREATE TRIGGER Trig1
ON DB_TEST.tbl_table1
AFTER INSERT, UPDATE
AS
declare @date1 datetime
declare @stat numeric
select @date1=(SELECT datestarted from inserted)
select @stat=(SELECT status_value from inserted)
if (@stat=2 and @date1<GETDATE())
UPDATE tbl_table1 set column1=(datediff(day,GETDATE(), @date1)) from tbl_table1 where status_value=2
else if (@stat=2 and @date1>GETDATE())
UPDATE tbl_table1 set column1='date1 greater than current date' from tbl_table1 where status_value=2
(here the column1 is varchar datatype)
|
|
|
|
|
Why double post?
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
hey... where man its reposted????? just check welll.... if u know the answer kindly help me
|
|
|
|
|
What do you mean it doesn't take effect?
Few points:
- if @stat <> 2 => no update executed
- if @date1 = GETDATE => no update executed
is this the problem?
|
|
|
|
|
The column1 doesnt take any effect.....
is there any errors in my coding?
|
|
|
|
|
Are you meaning that the update isn't updating column1?
Try to add some information to the trigger:
CREATE TRIGGER Trig1
ON DB_TEST.tbl_table1
AFTER INSERT, UPDATE
AS
declare @date1 datetime
declare @stat numeric
select @date1=(SELECT datestarted from inserted)
PRINT 'date1 = ' + convert (varchar(50), @date1)
select @stat=(SELECT status_value from inserted)
PRINT 'stat = ' + convert (varchar(50), @stat)
if (@stat=2 and @date1<GETDATE()) BEGIN
PRINT 'Executing update 1'
UPDATE tbl_table1 set column1=(datediff(day,GETDATE(), @date1)) from tbl_table1 where status_value=2
PRINT 'Rows affected = ' + convert (varchar(50), @@ROWCOUNT)
END
ELSE BEGIN
PRINT 'Condition 1 not met'
END
if (@stat=2 and @date1>GETDATE()) BEGIN
PRINT 'Executing update 2'
UPDATE tbl_table1 set column1='date1 greater than current date' from tbl_table1 where status_value=2
PRINT 'Rows affected = ' + convert (varchar(50), @@ROWCOUNT)
END
ELSE BEGIN
PRINT 'Condition 2 not met'
END
|
|
|
|
|
i m getting Invalid object name 'tbl_table1'. error when i execute the below update command.. help me. i m trying to implement the below in trigger and also in normal update statement
UPDATE tbl_table1 set column1=((datediff(day,column2,column3))+1) from tbl_table1 where column4=1 or column5=7
thanks in advance.....
|
|
|
|
|
Check this[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you then save my time by voting my post.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
