Turn of Violation error message in Update Query

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

Is there any way I can turn Off the Violation error message in an update
query.

I know that on occasions the query will attempt to enter data which is not
allowed. For instance it could try to enter 30/02/03 in a date field. I
want the query to continue to run anyway as when I accept Run Anyway option
I get the result which I need.

There are other reasons that I dont want to change the field format to a
Text field for instance as the dates are later checked and need to remain in
the Date Format.

If I cant do this is there a way I can store the data in Text format and get
an "IF" statment in a criteria to read it as a date without getting a Data
Type Missmatch?

Thanks

Les.
 
To handle the last option, check the IsDate, CDate, and DateSerial
functions. Also, the date delimiter is "#", so to make Access realize that
something is a date you sometimes have to wrap it in # signs. Try it without
the #s first, if it doesn't work, try it with them and see what happens.

#3 Mar 2003 7:00:00 AM#

For the first option, I tried this on a number field. The first option
generates an error, the second doesn't.

Public Sub aa()
Dim strSQL As String
strSQL = "UPDATE Table2 SET Table2.Field1 = 'a';"
CurrentDb.Execute strSQL, dbFailOnError
End Sub


Public Sub aa()
Dim strSQL As String
strSQL = "UPDATE Table2 SET Table2.Field1 = 'a';"
CurrentDb.Execute strSQL
End Sub
 
Wayne thanks for the quick responce but I not sure how to impliment your
suggestions.

My update query writes to the table "tblTemp_Diary" the field name is
"dat30th" so it's this field which I try to write to with 30/02/03. I tried
changing the to text and including the "#" at each end but I just get a Date
Data missmatch error.

I would be greatfull if you could help further.

Thanks

Les
 
Are you running the update query manually or through code? Can you post the
SQL of the query and the code if you're using any?
 
I am going to run this query after closing a form.

The date that is updated is collected from a number of sources and is
concatinated to form the date. This is why I get a date of 30/02/03 because
it assumes 31 days in each month. This is the only way I can complile the
date.

There are a few more date fields which are exactly the same but they dont
suffer from this problem I have taken them out to cut down the code a bit.

Hope this is of use.

Les

UPDATE (tblAssociate_Details LEFT JOIN (tblAssociate_Diary LEFT JOIN
tblTemp_Diary ON tblAssociate_Diary.id = tblTemp_Diary.id) ON
tblAssociate_Details.id = tblAssociate_Diary.key) LEFT JOIN
tblProject_Record ON tblAssociate_Details.id = tblProject_Record.id SET
tblTemp_Diary.id = tblAssociate_Details!id, tblTemp_Diary.txtMonth =
tblAssociate_Diary!txtMonth, tblTemp_Diary.dat30th = "30" & "/" &
tblAssociate_Diary!numMonth_Number & "/" &
Forms!frmAssociate_Details![Project Records].Form!dteYearLeft,
tblTemp_Diary.dat31st = "31" & "/" & tblAssociate_Diary!numMonth_Number &
"/" & Forms!frmAssociate_Details![Project Records].Form!dteYearLeft
WHERE (((tblAssociate_Details.id)=[Forms]![frmAssociate_Details]![id]) AND
((tblAssociate_Diary.txtMonth)<>"Month") AND
((tblProject_Record.txtProject_Title)=[Forms]![frmAssociate_Details]![Projec
t Records].[Form]![txtProject_Title]));
 
Ok, it appears that you want to manually run the query. Getting rid of the
prompts through code is fairly simple, but if you manually run the query
then you'll need to modify the query to change the bad dates into something
else.What do you want in the field if the date is not a valid date?

Try:
tblAssociate_Diary!txtMonth, tblTemp_Diary.dat30th = IIf(IsDate("30" & "/" &
tblAssociate_Diary!numMonth_Number & "/" &
Forms!frmAssociate_Details![Project Records].Form!dteYearLeft), "30" & "/" &
tblAssociate_Diary!numMonth_Number & "/" &
Forms!frmAssociate_Details![Project Records].Form!dteYearLeft, Null)

--
Wayne Morgan
MS Access MVP


Les said:
I am going to run this query after closing a form.

The date that is updated is collected from a number of sources and is
concatinated to form the date. This is why I get a date of 30/02/03 because
it assumes 31 days in each month. This is the only way I can complile the
date.

There are a few more date fields which are exactly the same but they dont
suffer from this problem I have taken them out to cut down the code a bit.

Hope this is of use.

Les

UPDATE (tblAssociate_Details LEFT JOIN (tblAssociate_Diary LEFT JOIN
tblTemp_Diary ON tblAssociate_Diary.id = tblTemp_Diary.id) ON
tblAssociate_Details.id = tblAssociate_Diary.key) LEFT JOIN
tblProject_Record ON tblAssociate_Details.id = tblProject_Record.id SET
tblTemp_Diary.id = tblAssociate_Details!id, tblTemp_Diary.txtMonth =
tblAssociate_Diary!txtMonth, tblTemp_Diary.dat30th = "30" & "/" &
tblAssociate_Diary!numMonth_Number & "/" &
Forms!frmAssociate_Details![Project Records].Form!dteYearLeft,
tblTemp_Diary.dat31st = "31" & "/" & tblAssociate_Diary!numMonth_Number &
"/" & Forms!frmAssociate_Details![Project Records].Form!dteYearLeft
WHERE (((tblAssociate_Details.id)=[Forms]![frmAssociate_Details]![id]) AND
((tblAssociate_Diary.txtMonth)<>"Month") AND
((tblProject_Record.txtProject_Title)=[Forms]![frmAssociate_Details]![Projec
t Records].[Form]![txtProject_Title]));








Are you running the update query manually or through code? Can you post the
SQL of the query and the code if you're using any?

--
Wayne Morgan
MS Access MVP


a
Date
Click to expand...
Click to expand...
 
Thanks Wayne that works fine now.

Les

Wayne Morgan said:
Ok, it appears that you want to manually run the query. Getting rid of the
prompts through code is fairly simple, but if you manually run the query
then you'll need to modify the query to change the bad dates into something
else.What do you want in the field if the date is not a valid date?

Try:
tblAssociate_Diary!txtMonth, tblTemp_Diary.dat30th = IIf(IsDate("30" & "/" &
tblAssociate_Diary!numMonth_Number & "/" &
Forms!frmAssociate_Details![Project Records].Form!dteYearLeft), "30" & "/" &
tblAssociate_Diary!numMonth_Number & "/" &
Forms!frmAssociate_Details![Project Records].Form!dteYearLeft, Null)

--
Wayne Morgan
MS Access MVP


Les said:
I am going to run this query after closing a form.

The date that is updated is collected from a number of sources and is
concatinated to form the date. This is why I get a date of 30/02/03 because
it assumes 31 days in each month. This is the only way I can complile the
date.

There are a few more date fields which are exactly the same but they dont
suffer from this problem I have taken them out to cut down the code a bit.

Hope this is of use.

Les

UPDATE (tblAssociate_Details LEFT JOIN (tblAssociate_Diary LEFT JOIN
tblTemp_Diary ON tblAssociate_Diary.id = tblTemp_Diary.id) ON
tblAssociate_Details.id = tblAssociate_Diary.key) LEFT JOIN
tblProject_Record ON tblAssociate_Details.id = tblProject_Record.id SET
tblTemp_Diary.id = tblAssociate_Details!id, tblTemp_Diary.txtMonth =
tblAssociate_Diary!txtMonth, tblTemp_Diary.dat30th = "30" & "/" &
tblAssociate_Diary!numMonth_Number & "/" &
Forms!frmAssociate_Details![Project Records].Form!dteYearLeft,
tblTemp_Diary.dat31st = "31" & "/" & tblAssociate_Diary!numMonth_Number &
"/" & Forms!frmAssociate_Details![Project Records].Form!dteYearLeft
WHERE (((tblAssociate_Details.id)=[Forms]![frmAssociate_Details]![id]) AND
((tblAssociate_Diary.txtMonth)<>"Month") AND
Click to expand...
((tblProject_Record.txtProject_Title)=[Forms]![frmAssociate_Details]![Projec
t Records].[Form]![txtProject_Title]));








Are you running the update query manually or through code? Can you
Click to expand...
post
the
SQL of the query and the code if you're using any?

--
Wayne Morgan
MS Access MVP


Wayne thanks for the quick responce but I not sure how to impliment your
suggestions.

My update query writes to the table "tblTemp_Diary" the field name is
"dat30th" so it's this field which I try to write to with 30/02/03. I
tried
changing the to text and including the "#" at each end but I just
Click to expand...
Click to expand...
get
a
Click to expand...
Click to expand...
 
Back
Top