update query

  • Thread starter Thread starter Yousoft
  • Start date Start date
Y

Yousoft

Please any one can help, I'm trying to run update query but always message
appear like ((query name) didn’t update 4 fields, due to type conversation
failure 0 records, due to key violations 0 records.. etc
How I can prevent this message from appearing always when I run the update
query?
Thanks
 
Post the SQL of your query. To do so, open the query in design mode, display
it in SQL view, copy the entire text to the body of your message.
Also post a description of the table you are updating - name and type of
each field.
-TedMi
 
Impossible to diagnose without having the database and the query.

Type conversion failure usually means that you are trying to stuff one type of
data into a field that expects something else. For instance, trying to put
"A" into a number field.

Since we don't know what your field types are and what you are trying to put
into the fields, you will need to trouble shoot this problem.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Table.

Allowance :text
Class :text
EmpNo :text
Name :text
TtlAllowance :text
Worked Num
Amount Num
Status :text
RjDate : Date



Update Query
UPDATE AllowData SET AllowData.Worked =
IIf(dayname(Forms!AllowData!To)="Thursday",(Forms!AllowData!To-Forms!AllowData!From)+3,(Forms!AllowData!To-Forms!AllowData!From)+1),
AllowData.Amount = ([TtlAllowance]/30)*[Worked], AllowData.RejDate =
Forms!AllowData!To
WHERE (((AllowData.EmpNo)=Forms!AllowData!EmpNo) And
((AllowData.Status)="Rejoined"));
 
this message still appearing, when I, run the update query for vacation it
is showing below.


UPDATE AllowData SET AllowData.RejDate = "", AllowData.Status =
Forms!AllowData!catg, AllowData.Amount = "", AllowData.Worked = ""
WHERE (((AllowData.EmpNo)=[Forms]![AllowData]![EmpNo]));




MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Since you're assigning the value of Forms!AllowData!To to RejDate I'll
assume that it is a Date data type. Therefore, the assignment of the
calculation:

(Forms!AllowData!To-Forms!AllowData!From)+3
or (Forms!AllowData!To-Forms!AllowData!From)+1

to the column AllowData.Worked, a Numeric data type, is causing the
error. I.e., you are assigning a Date to a Numeric column (field) - the
result of the above calculations will be a Date.

If you want the number of days worked use the DateDiff() function:

Worked = DateDiff("d", Forms!AllowData!From, Forms!AllowData!To) +
IIf(WeekDay(Forms!AllowData!To)=5,3,1)

(5 = vbThursday)

Also, it is a good idea to declare the data type in the query of
references to dates on forms. E.g.:

PARAMETERS Forms!AllowData!From Date, Forms!AllowData!To Date;
UPDATE AllowData
SET Worked = DateDiff("d", Forms!AllowData!From, Forms!AllowData!To) +
IIf(WeekDay(Forms!AllowData!To)=5,3,1) ,
.... etc. ...

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSrJteoechKqOuFEgEQLApQCePej1nnLPuIp/1WFH1Jh0MosJ6VgAoILF
jfEnmw17LZbixW8KdYO2HUVD
=llCe
-----END PGP SIGNATURE-----

Table.

Allowance :text
Class :text
EmpNo :text
Name :text
TtlAllowance :text
Worked Num
Amount Num
Status :text
RjDate : Date



Update Query
UPDATE AllowData SET AllowData.Worked =
IIf(dayname(Forms!AllowData!To)="Thursday",(Forms!AllowData!To-Forms!AllowData!From)+3,(Forms!AllowData!To-Forms!AllowData!From)+1),
AllowData.Amount = ([TtlAllowance]/30)*[Worked], AllowData.RejDate =
Forms!AllowData!To
WHERE (((AllowData.EmpNo)=Forms!AllowData!EmpNo) And
((AllowData.Status)="Rejoined"));


TedMi said:
Post the SQL of your query. To do so, open the query in design mode, display
it in SQL view, copy the entire text to the body of your message.
Also post a description of the table you are updating - name and type of
each field.
-TedMi

Please any one can help, I'm trying to run update query but always message
appear like ((query name) didn't update 4 fields, due to type
conversation
failure 0 records, due to key violations 0 records.. etc
How I can prevent this message from appearing always when I run the update
query?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Yousoft,

What are the data types of your fields? Are any of those required?
Example, I would think EmpNo can't be left blank... Also, only your form is
EmpNo a combo box and if yes, what is the Column Count and which one is the
bound column?

You might also want to take a look at...
http://www.regina-whipp.com/index_files/ActionQueryErrors.htm

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Yousoft said:
this message still appearing, when I, run the update query for vacation
it
is showing below.


UPDATE AllowData SET AllowData.RejDate = "", AllowData.Status =
Forms!AllowData!catg, AllowData.Amount = "", AllowData.Worked = ""
WHERE (((AllowData.EmpNo)=[Forms]![AllowData]![EmpNo]));




MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Since you're assigning the value of Forms!AllowData!To to RejDate I'll
assume that it is a Date data type. Therefore, the assignment of the
calculation:

(Forms!AllowData!To-Forms!AllowData!From)+3
or (Forms!AllowData!To-Forms!AllowData!From)+1

to the column AllowData.Worked, a Numeric data type, is causing the
error. I.e., you are assigning a Date to a Numeric column (field) - the
result of the above calculations will be a Date.

If you want the number of days worked use the DateDiff() function:

Worked = DateDiff("d", Forms!AllowData!From, Forms!AllowData!To) +
IIf(WeekDay(Forms!AllowData!To)=5,3,1)

(5 = vbThursday)

Also, it is a good idea to declare the data type in the query of
references to dates on forms. E.g.:

PARAMETERS Forms!AllowData!From Date, Forms!AllowData!To Date;
UPDATE AllowData
SET Worked = DateDiff("d", Forms!AllowData!From, Forms!AllowData!To) +
IIf(WeekDay(Forms!AllowData!To)=5,3,1) ,
.... etc. ...

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSrJteoechKqOuFEgEQLApQCePej1nnLPuIp/1WFH1Jh0MosJ6VgAoILF
jfEnmw17LZbixW8KdYO2HUVD
=llCe
-----END PGP SIGNATURE-----

Table.

Allowance :text
Class :text
EmpNo :text
Name :text
TtlAllowance :text
Worked Num
Amount Num
Status :text
RjDate : Date



Update Query
UPDATE AllowData SET AllowData.Worked =
IIf(dayname(Forms!AllowData!To)="Thursday",(Forms!AllowData!To-Forms!AllowData!From)+3,(Forms!AllowData!To-Forms!AllowData!From)+1),
AllowData.Amount = ([TtlAllowance]/30)*[Worked], AllowData.RejDate =
Forms!AllowData!To
WHERE (((AllowData.EmpNo)=Forms!AllowData!EmpNo) And
((AllowData.Status)="Rejoined"));


:

Post the SQL of your query. To do so, open the query in design mode,
display
it in SQL view, copy the entire text to the body of your message.
Also post a description of the table you are updating - name and type
of
each field.
-TedMi

Please any one can help, I'm trying to run update query but always
message
appear like ((query name) didn't update 4 fields, due to type
conversation
failure 0 records, due to key violations 0 records.. etc
How I can prevent this message from appearing always when I run the
update
query?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Assuming that RejDate is a date time field and Amount and Worked is a number
field then you cannot set those to a zero-length string. You either need to
set them to NULL or a default value (Date() and 0).

Status seems to be failing also. The field type for status must be something
other than the value in Forms!AllowData!catg. It could be that Status is a
lookup field and the value you see is not the value that is stored. You might
see text (e.g., Complete) and the actual value stored is a number (2). I avoid
lookup fields in tables because they hide the actual data and you can
accomplish what they do by using forms for data entry and setting up a
combobox (or a listbox) to get the same result.

Try the following and see if it works.
UPDATE AllowData
SET AllowData.RejDate = Null
, AllowData.Status = "XXX"
, AllowData.Amount = Null
, AllowData.Worked = Null
WHERE AllowData.EmpNo=[Forms]![AllowData]![EmpNo]

If that works then check and see what value is being passed by the control
Forms!AllowData!catg
Unless that is a zero-length string the update should work.

UPDATE AllowData
SET AllowData.RejDate = Null
, AllowData.Status = Forms!AllowData!catg
, AllowData.Amount = Null
, AllowData.Worked = Null
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
this message still appearing, when I, run the update query for vacation it
is showing below.


UPDATE AllowData SET AllowData.RejDate = "", AllowData.Status =
Forms!AllowData!catg, AllowData.Amount = "", AllowData.Worked = ""
WHERE (((AllowData.EmpNo)=[Forms]![AllowData]![EmpNo]));
Click to expand...
 
Only one problem left, that when I run the update query for selected Emp No.
it is pickup also the first record in my database, so how I can solve this
problem?

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Since you're assigning the value of Forms!AllowData!To to RejDate I'll
assume that it is a Date data type. Therefore, the assignment of the
calculation:

(Forms!AllowData!To-Forms!AllowData!From)+3
or (Forms!AllowData!To-Forms!AllowData!From)+1

to the column AllowData.Worked, a Numeric data type, is causing the
error. I.e., you are assigning a Date to a Numeric column (field) - the
result of the above calculations will be a Date.

If you want the number of days worked use the DateDiff() function:

Worked = DateDiff("d", Forms!AllowData!From, Forms!AllowData!To) +
IIf(WeekDay(Forms!AllowData!To)=5,3,1)

(5 = vbThursday)

Also, it is a good idea to declare the data type in the query of
references to dates on forms. E.g.:

PARAMETERS Forms!AllowData!From Date, Forms!AllowData!To Date;
UPDATE AllowData
SET Worked = DateDiff("d", Forms!AllowData!From, Forms!AllowData!To) +
IIf(WeekDay(Forms!AllowData!To)=5,3,1) ,
.... etc. ...

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSrJteoechKqOuFEgEQLApQCePej1nnLPuIp/1WFH1Jh0MosJ6VgAoILF
jfEnmw17LZbixW8KdYO2HUVD
=llCe
-----END PGP SIGNATURE-----

Table.

Allowance :text
Class :text
EmpNo :text
Name :text
TtlAllowance :text
Worked Num
Amount Num
Status :text
RjDate : Date



Update Query
UPDATE AllowData SET AllowData.Worked =
IIf(dayname(Forms!AllowData!To)="Thursday",(Forms!AllowData!To-Forms!AllowData!From)+3,(Forms!AllowData!To-Forms!AllowData!From)+1),
AllowData.Amount = ([TtlAllowance]/30)*[Worked], AllowData.RejDate =
Forms!AllowData!To
WHERE (((AllowData.EmpNo)=Forms!AllowData!EmpNo) And
((AllowData.Status)="Rejoined"));


TedMi said:
Post the SQL of your query. To do so, open the query in design mode, display
it in SQL view, copy the entire text to the body of your message.
Also post a description of the table you are updating - name and type of
each field.
-TedMi

Please any one can help, I'm trying to run update query but always message
appear like ((query name) didn't update 4 fields, due to type
conversation
failure 0 records, due to key violations 0 records.. etc
How I can prevent this message from appearing always when I run the update
query?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Only one problem left, that when I run the update query for selected Emp No.
it is pickup also the first record in my database, so how I can solve this
problem?

John Spencer said:
Assuming that RejDate is a date time field and Amount and Worked is a number
field then you cannot set those to a zero-length string. You either need to
set them to NULL or a default value (Date() and 0).

Status seems to be failing also. The field type for status must be something
other than the value in Forms!AllowData!catg. It could be that Status is a
lookup field and the value you see is not the value that is stored. You might
see text (e.g., Complete) and the actual value stored is a number (2). I avoid
lookup fields in tables because they hide the actual data and you can
accomplish what they do by using forms for data entry and setting up a
combobox (or a listbox) to get the same result.

Try the following and see if it works.
UPDATE AllowData
SET AllowData.RejDate = Null
, AllowData.Status = "XXX"
, AllowData.Amount = Null
, AllowData.Worked = Null
WHERE AllowData.EmpNo=[Forms]![AllowData]![EmpNo]

If that works then check and see what value is being passed by the control
Forms!AllowData!catg
Unless that is a zero-length string the update should work.

UPDATE AllowData
SET AllowData.RejDate = Null
, AllowData.Status = Forms!AllowData!catg
, AllowData.Amount = Null
, AllowData.Worked = Null
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
this message still appearing, when I, run the update query for vacation it
is showing below.


UPDATE AllowData SET AllowData.RejDate = "", AllowData.Status =
Forms!AllowData!catg, AllowData.Amount = "", AllowData.Worked = ""
WHERE (((AllowData.EmpNo)=[Forms]![AllowData]![EmpNo]));
Yousoft wrote:
Table.

Allowance :text
Class :text
EmpNo :text
Name :text
TtlAllowance :text
Worked Num
Amount Num
Status :text
RjDate : Date
Click to expand...
Click to expand...
Click to expand...
 
I can't see how that would happen. This should only update records where
EmpNo field in AllowData table is equal to the value of the EmpNo control.

Can you post the exact SQL string of the query you are using?

What is the value of EmpNo in the record(s) that are being updated?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
UPDATE AllowData SET AllowData.Worked =
DateDiff("d",Forms!AllowData!From,Forms!AllowData!To)+IIf(Weekday(Forms!AllowData!To)=5,3,1),
AllowData.Amount = ([TtlAllowance]*[Worked])/30, AllowData.RejDate =
Forms!AllowData!To
WHERE (((AllowData.EmpNo)=[Forms]![AllowData]![EmpNo]) AND
((AllowData.Status)="Rejoined"));

yusuf
 
Looking at the query, I am stumped on how it could be updating records other
then the ones that meet the criteria.

If you run this query, do you get unexpected records?
SELECT EmpNo, Status, Worked, Amount, RejDate
,[Forms]![AllowData]![EmpNo] as EmployeeCriteria
,
DateDiff("d",Forms!AllowData!From,Forms!AllowData!To)+IIf(Weekday(Forms!AllowData!To)=5,3,1)
as WorkedCalc
, ([TtlAllowance]*[Worked])/30 as AmountCalc
FROM AllowData

WHERE (((AllowData.EmpNo)=[Forms]![AllowData]![EmpNo]) AND
((AllowData.Status)="Rejoined"));

There is a problem Since you are updating worked and then trying to use the
new value of worked in the amount calculation. I think you are going to be
using the value of worked prior to the update. If the WorkedCalc value is
working, you will need to replace WORKED in the Amount calculation with the
expression you are using to calculate Worked.

Good Luck.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
UPDATE AllowData SET AllowData.Worked =
DateDiff("d",Forms!AllowData!From,Forms!AllowData!To)+IIf(Weekday(Forms!AllowData!To)=5,3,1),
AllowData.Amount = ([TtlAllowance]*[Worked])/30, AllowData.RejDate =
Forms!AllowData!To
WHERE (((AllowData.EmpNo)=[Forms]![AllowData]![EmpNo]) AND
((AllowData.Status)="Rejoined"));

yusuf


John Spencer said:
I can't see how that would happen. This should only update records where
EmpNo field in AllowData table is equal to the value of the EmpNo control.

Can you post the exact SQL string of the query you are using?

What is the value of EmpNo in the record(s) that are being updated?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
Back
Top