Update Query problem

[Regi]

I'm trying to run the following Update Query for Table CMF3. When I run it it
looks OK and tells me it is about to update the correct amount of rows. Then
I get an error for type conversion failure. (My goal is to get the Fee Pct
amount into the spaces where it is 0.)
I realized from looking at the error that problem are rows where there is a
letter in the field for either sub task or Job# (1188-01A) Both the Sub Task
field and Job# are Text fields. What do I need to do to get this to work?

UPDATE [A028 CMF3] SET [A028 CMF3].[Fee Pct] = DMax("[Fee Pct]","A028
CMF3","[Job#] =" & [A028 CMF3]![Job#])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null) and "[A028
CMF3.SUB TASK] <>'000'";

CMF3 Table
Task Sub Task Job# Fee Pct
1188 000 1188-000 13
1188 001 1188-001 0
1188 01A 1188-01A 0

Thanks, Regi

 

[KARL DEWEY]

You have quotes where they do not belong. Try this --
UPDATE [A028 CMF3] SET [A028 CMF3].[Fee Pct] = DMax("[Fee Pct]","A028
CMF3","[Job#] =" & [A028 CMF3]![Job#])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null) AND ([A028
CMF3.SUB TASK] <>"000");

 

[John Spencer]

TheSQL of a query would look like the following. The Chr(34) will add quote
marks around the Job# to identify it as a string value

UPDATE [A028 CMF3]
SET [A028 CMF3].[Fee Pct] =
DMax("[Fee Pct]","[A028 CMF3]","[Job#] =" & Chr(34) & [A028 CMF3]![Job#]
& Chr(34))
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null)
AND [A028 CMF3.SUB TASK] <>"000"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Regi]

Karl,

I'll give this a try.

Are there rules for quote use?

Thanks, Regi

You have quotes where they do not belong. Try this --
UPDATE [A028 CMF3] SET [A028 CMF3].[Fee Pct] = DMax("[Fee Pct]","A028
CMF3","[Job#] =" & [A028 CMF3]![Job#])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null) AND ([A028
CMF3.SUB TASK] <>"000");

--
KARL DEWEY
Build a little - Test a little

I'm trying to run the following Update Query for Table CMF3. When I run it it
looks OK and tells me it is about to update the correct amount of rows. Then
I get an error for type conversion failure. (My goal is to get the Fee Pct
amount into the spaces where it is 0.)
I realized from looking at the error that problem are rows where there is a
letter in the field for either sub task or Job# (1188-01A) Both the Sub Task
field and Job# are Text fields. What do I need to do to get this to work?

UPDATE [A028 CMF3] SET [A028 CMF3].[Fee Pct] = DMax("[Fee Pct]","A028
CMF3","[Job#] =" & [A028 CMF3]![Job#])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null) and "[A028
CMF3.SUB TASK] <>'000'";

CMF3 Table
Task Sub Task Job# Fee Pct
1188 000 1188-000 13
1188 001 1188-001 0
1188 01A 1188-01A 0

Thanks, Regi

 

[Regi]

Karl,

This didn't work for me. It wanted to change less rows that I think it
should. I'm not sure what the number represents. I also got the same type
conversion error.

Ideas?

Thanks, Regi

You have quotes where they do not belong. Try this --
UPDATE [A028 CMF3] SET [A028 CMF3].[Fee Pct] = DMax("[Fee Pct]","A028
CMF3","[Job#] =" & [A028 CMF3]![Job#])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null) AND ([A028
CMF3.SUB TASK] <>"000");

--
KARL DEWEY
Build a little - Test a little

I'm trying to run the following Update Query for Table CMF3. When I run it it
looks OK and tells me it is about to update the correct amount of rows. Then
I get an error for type conversion failure. (My goal is to get the Fee Pct
amount into the spaces where it is 0.)
I realized from looking at the error that problem are rows where there is a
letter in the field for either sub task or Job# (1188-01A) Both the Sub Task
field and Job# are Text fields. What do I need to do to get this to work?

UPDATE [A028 CMF3] SET [A028 CMF3].[Fee Pct] = DMax("[Fee Pct]","A028
CMF3","[Job#] =" & [A028 CMF3]![Job#])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null) and "[A028
CMF3.SUB TASK] <>'000'";

CMF3 Table
Task Sub Task Job# Fee Pct
1188 000 1188-000 13
1188 001 1188-001 0
1188 01A 1188-01A 0

Thanks, Regi

 

[KARL DEWEY]

Try to skin the cat another way by using a totals query instead of the DMax
and join it in you select query you run to test it. If it is ok then change
to update and run.
--
KARL DEWEY
Build a little - Test a little

Karl,

This didn't work for me. It wanted to change less rows that I think it
should. I'm not sure what the number represents. I also got the same type
conversion error.

Ideas?

Thanks, Regi

You have quotes where they do not belong. Try this --
UPDATE [A028 CMF3] SET [A028 CMF3].[Fee Pct] = DMax("[Fee Pct]","A028
CMF3","[Job#] =" & [A028 CMF3]![Job#])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null) AND ([A028
CMF3.SUB TASK] <>"000");

--
KARL DEWEY
Build a little - Test a little

I'm trying to run the following Update Query for Table CMF3. When I run it it
looks OK and tells me it is about to update the correct amount of rows. Then
I get an error for type conversion failure. (My goal is to get the Fee Pct
amount into the spaces where it is 0.)
I realized from looking at the error that problem are rows where there is a
letter in the field for either sub task or Job# (1188-01A) Both the Sub Task
field and Job# are Text fields. What do I need to do to get this to work?

UPDATE [A028 CMF3] SET [A028 CMF3].[Fee Pct] = DMax("[Fee Pct]","A028
CMF3","[Job#] =" & [A028 CMF3]![Job#])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null) and "[A028
CMF3.SUB TASK] <>'000'";

CMF3 Table
Task Sub Task Job# Fee Pct
1188 000 1188-000 13
1188 001 1188-001 0
1188 01A 1188-01A 0

Thanks, Regi

 

[Ken Sheridan]

The error is probably due to the fact that Job # is of text data type, but
the value is not being wrapped in quotes in the criterion of ther DMax
function call.

There might also be a problem if the DMax function returns a Null for some
values of Job #. In most cases its best to give a numeric column a default
value of zero and prohibit Nulls. A Null has no meaning its an unknown, and
is semantically ambiguous so can cause problems. These might be due to an
expression returning Null rather than a value as Nulls propagate (e.g. Null +
anything = Null); there could be problems of comparison in that comparing any
value with Null returns Null, not True or False (even Null = Null evaluates
to Null); or there could be problems of interpretation as to the meaning of a
Null (e.g. does a Null credit limit mean a customer has unlimited credit,
zero credit, or what? There is no way of knowing). I'd suggest first
replacing any Nulls with zeros with:

UPDATE [A028 CMF3]
SET [Fee Pct] = 0
WHERE [Fee Pct] IS NULL;

Then amend the table design, setting the [Fee Pct] column's Required
property to True, and its DefaultValue property to 0.

Then try:

UPDATE [A028 CMF3] SET [Fee Pct] =
DMAX("[Fee Pct]","[A028 CMF3]","[Job#] ='" & [Job#] & "'")
WHERE [Fee Pct] = 0 AND [ SUB TASK] <>"000";

What this will do is update the Fee Pct value in rows where it is currently
zero and the Sub Task value is not "000", to the highest value in the Fee Pct
column for those rows with the same Job # as the current row's Job #. Is
that what's wanted?

Ken Sheridan
Stafford, England

Karl,

This didn't work for me. It wanted to change less rows that I think it
should. I'm not sure what the number represents. I also got the same type
conversion error.

Ideas?

Thanks, Regi

You have quotes where they do not belong. Try this --
UPDATE [A028 CMF3] SET [A028 CMF3].[Fee Pct] = DMax("[Fee Pct]","A028
CMF3","[Job#] =" & [A028 CMF3]![Job#])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null) AND ([A028
CMF3.SUB TASK] <>"000");

--
KARL DEWEY
Build a little - Test a little

I'm trying to run the following Update Query for Table CMF3. When I run it it
looks OK and tells me it is about to update the correct amount of rows. Then
I get an error for type conversion failure. (My goal is to get the Fee Pct
amount into the spaces where it is 0.)
I realized from looking at the error that problem are rows where there is a
letter in the field for either sub task or Job# (1188-01A) Both the Sub Task
field and Job# are Text fields. What do I need to do to get this to work?

UPDATE [A028 CMF3] SET [A028 CMF3].[Fee Pct] = DMax("[Fee Pct]","A028
CMF3","[Job#] =" & [A028 CMF3]![Job#])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null) and "[A028
CMF3.SUB TASK] <>'000'";

CMF3 Table
Task Sub Task Job# Fee Pct
1188 000 1188-000 13
1188 001 1188-001 0
1188 01A 1188-01A 0

Thanks, Regi

 

[Regi]

Thank you all for your suggestions/ideas it's apreciated. I'll work on them
today. Regi

TheSQL of a query would look like the following. The Chr(34) will add quote
marks around the Job# to identify it as a string value

UPDATE [A028 CMF3]
SET [A028 CMF3].[Fee Pct] =
DMax("[Fee Pct]","[A028 CMF3]","[Job#] =" & Chr(34) & [A028 CMF3]![Job#]
& Chr(34))
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null)
AND [A028 CMF3.SUB TASK] <>"000"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'm trying to run the following Update Query for Table CMF3. When I run it
it
looks OK and tells me it is about to update the correct amount of rows.
Then
I get an error for type conversion failure. (My goal is to get the Fee Pct
amount into the spaces where it is 0.)
I realized from looking at the error that problem are rows where there is
a
letter in the field for either sub task or Job# (1188-01A) Both the Sub
Task
field and Job# are Text fields. What do I need to do to get this to work?

UPDATE [A028 CMF3] SET [A028 CMF3].[Fee Pct] = DMax("[Fee Pct]","A028
CMF3","[Job#] =" & [A028 CMF3]![Job#])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null) and
"[A028
CMF3.SUB TASK] <>'000'";

CMF3 Table
Task Sub Task Job# Fee Pct
1188 000 1188-000 13
1188 001 1188-001 0
1188 01A 1188-01A 0

Thanks, Regi