Access, IF EXISTS, error 3129
Hello.
Have some trouble with an SQL line.
If a user exists it should update PhoneEndDate, else insert a new record.
This works fine in SQL Analyzer:
IF EXISTS (SELECT * FROM tblTable1 WHERE UserID = 555555)
UPDATE tblTable1 SET PhoneEndDate = '13-11-2010' WHERE UserID = 555555
ELSE INSERT INTO tblTable1 (UserID , PhoneType, PhoneStartDate) VALUES (555555,1,'14-11-2010')
But when I put this en Access, I get runtime-error 3129:
SQLSelectUser = "IF EXISTS (SELECT * FROM tblTable1 WHERE UserID = " & sUserId & ") UPDATE tblTable1 SET PhoneEndDate = '" & sUserDate & "') WHERE UserID = " & sUserId & " ELSE INSERT INTO tblTable1 (UserID, PhoneType, PhoneStartDate) VALUES (" & sUserId & "," & sPhoneType & ",'" & sPhoneStartDate & "')"
Any idea why?
Thanks..
Have some trouble with an SQL line.
If a user exists it should update PhoneEndDate, else insert a new record.
This works fine in SQL Analyzer:
IF EXISTS (SELECT * FROM tblTable1 WHERE UserID = 555555)
UPDATE tblTable1 SET PhoneEndDate = '13-11-2010' WHERE UserID = 555555
ELSE INSERT INTO tblTable1 (UserID , PhoneType, PhoneStartDate) VALUES (555555,1,'14-11-2010')
But when I put this en Access, I get runtime-error 3129:
SQLSelectUser = "IF EXISTS (SELECT * FROM tblTable1 WHERE UserID = " & sUserId & ") UPDATE tblTable1 SET PhoneEndDate = '" & sUserDate & "') WHERE UserID = " & sUserId & " ELSE INSERT INTO tblTable1 (UserID, PhoneType, PhoneStartDate) VALUES (" & sUserId & "," & sPhoneType & ",'" & sPhoneStartDate & "')"
Any idea why?
Thanks..
Last Comment
I dont think,you can use if exists.
ALTERNATE WAY IS RUN BOTH QUERY AS SHOWN BELOW.
UPDATE tblTable1 SET PhoneEndDate = '" & sUserDate & "') WHERE UserID = " & sUserId & "
INSERT INTO tblTable1 (UserID, PhoneType, PhoneStartDate)
SELECT " & sUserId & "," & sPhoneType & ",'" & sPhoneStartDate & "'
FROM tblTable1
WHERE UserID <> " & sUserId & "
IF THERE IS WAY THEN YOU WILL SURELY GET ANSWER,
ALTERNATE WAY IS RUN BOTH QUERY AS SHOWN BELOW.
UPDATE tblTable1 SET PhoneEndDate = '" & sUserDate & "') WHERE UserID = " & sUserId & "
INSERT INTO tblTable1 (UserID, PhoneType, PhoneStartDate)
SELECT " & sUserId & "," & sPhoneType & ",'" & sPhoneStartDate & "'
FROM tblTable1
WHERE UserID <> " & sUserId & "
IF THERE IS WAY THEN YOU WILL SURELY GET ANSWER,
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Note dates must be enclosed in # in Access. Also watch the format you pass dates into Access - it may well try and "Americanise" them
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
ASKER
Sorry for the long wait, but I've been away from work.
Thanks kelvinsparks.
A couple of issues with null though. But this is how it works now:
Thanks kelvinsparks.
A couple of issues with null though. But this is how it works now:
If DCount("*", "tblTable1", "[UserID]= '" & sUserId & "'") = 0 Then
SQLSelectUser = "INSERT INTO tblTable1 (UserID, PhoneType, PhoneStartDate) VALUES (" & sUserId & "," & sPhoneType & ",'" & sPhoneStartDate & "')"
Else
SQLSelectUser = "UPDATE tblTable1 SET PhoneEndDate='" & sUserDate & "' WHERE UserID= '" & sUserId & "'"
End If
DoCmd.RunSQL SQLSelectUser
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Kelvin