Add a word to the beginning of a field? **

[Guest]

Okay, the circular reference error went away after I specified the

within the function.

Jarrod

I entered this:

PageNum: Iif(IsNull([PageNum]), "", Iif(IsNumeric([PageNum]), "Catalog Page
", "Supplement Page ") & [PageNum]

And I got this error:
"Circular reference caused by alias 'PageNum' in query definition's SELECT
list."

What does that mean?

Thanks for the continuing assistance.

Jarrod

Some of the records do not have any data in this particular field (Some
products are not in any printed catalogs). For those records, the field
needs to remain NULL.

So nest the IIF's:

Iif(IsNull([PageNum], "",
Iif(IsNumeric([PageNum]), "Catalog Page ", "Supplement Page ") &
[PageNum]


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Okay, things are looking better, but they're still not working 100%.

Maybe as as a refresher, here's my steps:

1 - Bring pagenum data to my Access db from a MAS90 database.
2 - I delete all data in the field for records (products) that aren't
catalogued (not on any pages). For some reason, MAS90 will have something
like "000" or something random here, even when the items just isn't on any
pages at all.
3 - I delete all data in the field for records (products) that have been
discontinued.
4 - At this point, I'm ready to export a query which compiles a variety of
product info from different tables (including pagenum).

The function I'm using in this query is this (character-for-character):

PageNum: IIf(IsNull([ShopCartSmall -
terms].[PageNum]),"",IIf(IsNumeric([ShopCartSmall -
terms].[PageNum]),"Catalog Page ","Supplement Page ") & [ShopCartSmall -
terms].[PageNum])

What's happening, is that "Supplement Page" is getting stuck in front of all
the page numbers, even the ones that should say "Catalog Page".

Thoughts?
Again, thanks for the continued assistance.

 

[John Vinson]

The function I'm using in this query is this (character-for-character):

PageNum: IIf(IsNull([ShopCartSmall -
terms].[PageNum]),"",IIf(IsNumeric([ShopCartSmall -
terms].[PageNum]),"Catalog Page ","Supplement Page ") & [ShopCartSmall -
terms].[PageNum])

What's happening, is that "Supplement Page" is getting stuck in front of all
the page numbers, even the ones that should say "Catalog Page".

Thoughts?

It sounds like [ShopCartSmall - terms] is being treated as non-numeric
in all cases. What exactly does this field contain for records which
should say "Catalog Page"? Are there leading blanks, special
characters, anything other than numeric digits?

John W. Vinson[MVP]

 

[Guest]

"It sounds like [ShopCartSmall - terms] is being treated as non-numeric in
all cases."

Yep, I realized this was the case, THEN, realized that I had forgotten that
the last two chracters of these entries are always letters. I need the IIfs
to only test the first three characters of each cell. I know I can use a
Left () function to do this. But I don't know where/how to use it in the
expression. I know it the first open paranthesis should probably go after
"IsNumeric". After that I'm not sure.

Jarrod

 

[Guest]

Actually, it seems like this should work, but it doesn't:

PageNum: IIf(IsNull([ShopCartSmall -
terms].[PageNum]),"",IIf(Left(IsNumeric([ShopCartSmall -
terms].[PageNum]),3),"Catalog Page ","Supplement Page ") & [ShopCartSmall -
terms].[PageNum])

"It sounds like [ShopCartSmall - terms] is being treated as non-numeric in
all cases."

Yep, I realized this was the case, THEN, realized that I had forgotten that
the last two chracters of these entries are always letters. I need the IIfs
to only test the first three characters of each cell. I know I can use a
Left () function to do this. But I don't know where/how to use it in the
expression. I know it the first open paranthesis should probably go after
"IsNumeric". After that I'm not sure.

Jarrod

It sounds like [ShopCartSmall - terms] is being treated as non-numeric
in all cases. What exactly does this field contain for records which
should say "Catalog Page"? Are there leading blanks, special
characters, anything other than numeric digits?

John W. Vinson[MVP]

 

[Guest]

Okay, THIS seems like it should work, but it doesn't:

PageNum: IIf(IsNull([ShopCartSmall -
terms].[PageNum]),"",IIf(IsNumeric(Left([ShopCartSmall - terms].[PageNum]),
3),"Catalog

Page ","Supplement Page ") & [ShopCartSmall - terms].[PageNum])

Does this function only work with "text"?

Jarrod

Actually, it seems like this should work, but it doesn't:

PageNum: IIf(IsNull([ShopCartSmall -
terms].[PageNum]),"",IIf(Left(IsNumeric([ShopCartSmall -
terms].[PageNum]),3),"Catalog Page ","Supplement Page ") & [ShopCartSmall -
terms].[PageNum])

"It sounds like [ShopCartSmall - terms] is being treated as non-numeric in
all cases."

Yep, I realized this was the case, THEN, realized that I had forgotten that
the last two chracters of these entries are always letters. I need the IIfs
to only test the first three characters of each cell. I know I can use a
Left () function to do this. But I don't know where/how to use it in the
expression. I know it the first open paranthesis should probably go after
"IsNumeric". After that I'm not sure.

Jarrod

It sounds like [ShopCartSmall - terms] is being treated as non-numeric
in all cases. What exactly does this field contain for records which
should say "Catalog Page"? Are there leading blanks, special
characters, anything other than numeric digits?

John W. Vinson[MVP]

 

[John Vinson]

Okay, THIS seems like it should work, but it doesn't:

PageNum: IIf(IsNull([ShopCartSmall -
terms].[PageNum]),"",IIf(IsNumeric(Left([ShopCartSmall - terms].[PageNum]),
3),"Catalog

Page ","Supplement Page ") & [ShopCartSmall - terms].[PageNum])

Does this function only work with "text"?

Let's break it down:

IIf(IsNull([ShopCartSmall - terms].[PageNum]), <logical expr
"", <true branch
IIf(
IsNumeric(Left([ShopCartSmall - terms].[PageNum]), 3)), <logical
"Catalog Page", <true
"Supplement Page ") <false

A misplaced parenthesis it seems: try

IIf(IsNull([ShopCartSmall - terms].[PageNum]), "",
IIf(IsNumeric(Left([ShopCartSmall - terms].[PageNum]), 3)), "Catalog
Page", "Supplement Page ")


John W. Vinson[MVP]