invalid parsing

[richaluft]

Hi:
Can anyone identify why the following is not functioning:
IIf(isnull(DLookUp("[ICD1]","EntryICDs","[EntryNum] = 2926"),"",
Replace(DLookUp("[ICD1]","EntryICDs","[EntryNum] = 2926"),"."," "))?

I'm trying to use it as a query fieldname, and I keep getting errmsg
of
"Compile Error: Expected list separator or )" when I try to debug in
immediate window
Richard

 

[John W. Vinson]

Hi:
Can anyone identify why the following is not functioning:
IIf(isnull(DLookUp("[ICD1]","EntryICDs","[EntryNum] = 2926"),"",
Replace(DLookUp("[ICD1]","EntryICDs","[EntryNum] = 2926"),"."," "))?

I'm trying to use it as a query fieldname, and I keep getting errmsg
of
"Compile Error: Expected list separator or )" when I try to debug in
immediate window
Richard

Replace is a function which returns a value. It doesn't change the values in
any of its arguments.

Just what are you trying to ACCOMPLISH here? What is the content of ICD1?
Would an Update query perhaps be better?

UPDATE EntryICDs
SET ICD1 = Replace([ICD1], ".", ",")
WHERE EntryNum = 2926
AND ICD1 LIKE "*.*";

This will replace all periods in the field ICD1 with a comma in those records
where EntryNum is equal to 2926 and where there is a period to replace.

John W. Vinson [MVP]

 

[richaluft]

Hi:
Can anyone identify why the following is not functioning:
IIf(isnull(DLookUp("[ICD1]","EntryICDs","[EntryNum] = 2926"),"",
Replace(DLookUp("[ICD1]","EntryICDs","[EntryNum] = 2926"),"."," "))?

I'm trying to use it as a query fieldname, and I keep getting errmsg
of
"Compile Error: Expected list separator or )" when I try to debug in
immediate window
Richard

Replace is a function which returns a value. It doesn't change the values in
any of its arguments.

Just what are you trying to ACCOMPLISH here? What is the content of ICD1?
Would an Update query perhaps be better?

UPDATE EntryICDs
SET ICD1 = Replace([ICD1], ".", ",")
WHERE EntryNum = 2926
AND ICD1 LIKE "*.*";

This will replace all periods in the field ICD1 with a comma in those records
where EntryNum is equal to 2926 and where there is a period to replace.

John W. Vinson [MVP]

John:

The Field ICD1 might be null, or might contain a string such as
550.90. If null, then field should remain null: if contains a string,
then string should appear WITHOUT the decimal.
Richard

 

[John W. Vinson]

The Field ICD1 might be null, or might contain a string such as
550.90. If null, then field should remain null: if contains a string,
then string should appear WITHOUT the decimal.
Richard

In that case the Replace syntax would be

REPLACE([ICD1], ".", "")

to convert 550.90 to 55090.

What was the point of the criterion on EntryNum? Do you want to correct the
period only for that entrynum or for all entrynums?

John W. Vinson [MVP]

 

[richaluft]

The Field ICD1 might be null, or might contain a string such as
550.90. If null, then field should remain null: if contains a string,
then string should appear WITHOUT the decimal.
Richard

In that case the Replace syntax would be

REPLACE([ICD1], ".", "")

to convert 550.90 to 55090.

What was the point of the criterion on EntryNum? Do you want to correct the
period only for that entrynum or for all entrynums?

John W. Vinson [MVP]

John:
If ICD1 is null, then using only the Replace function gives errmsg of
"data type mismatch in criteria expression". I need the IIf function
( or some similar alternative) in order to cover instances where it
is null value. When I know that ICD1 is not null, the Replace
function parses fine.
Richard

 

[richaluft]

The Field ICD1 might be null, or might contain a string such as
550.90. If null, then field should remain null: if contains a string,
then string should appear WITHOUT the decimal.
Richard

In that case the Replace syntax would be

REPLACE([ICD1], ".", "")

to convert 550.90 to 55090.

What was the point of the criterion on EntryNum? Do you want to correct the
period only for that entrynum or for all entrynums?

John W. Vinson [MVP]

John:
Never mind. Simply needed to use Chr(34) instead of quote marks to
delimit "replace" fn in the IIf clause.