adress-labels (iff-function)

[bet]

Report-problem: who helps ?


-------------------------------
file = adress.mdb
field-LK = countrycode (value is BE or NL)
field-COUNTRY = value is "Belgium" or "Netherlands"
-----------------------------------------
Example for results (Adress-label for postings in a
post-office in Belgium):

Mr, ,
Frans Appelmans,
Kerkstraat, 295 ,
1000 Brussels (-> country-code "BE" is not printed, blank)
( ------> country is not printed, is not printed))

Mr ,
Jan Vierstra
Dijkstraat, 102 ,
NL-1248-Amsterdam (-----> landcode "NL-" is printed
NETHERLANDS (------> country is printed)

So , that means :
- if value in field-LK = BE....no printings "BE" (-> blank)
- if value in field-LK = NL....printing "NETHERLANDS"

When postings letters in a post-office outside Belgium, d
an Country is not blank but "Belgium"
--------------------------------------------------

I am an starting ms-acces-user.
I typed in menu "reports" on the label-properties:

1) First trial :
iff ADRESS.LK = "BE" then ADRESS.LAND(" ")
else ADRESS.LAND(ADRESS.LAND) endif

2) Second trial :.
case when ( [adress].[lk])="be" then " " else [adress].[country] end

Somebody knows exact use of points, comma's, hacks...etc. ?

Best Regards and New-Year Greetings
Bet

 

[Stefan Hoffmann]

hi Bet,

Mr, ,
Frans Appelmans,
Kerkstraat, 295 ,
1000 Brussels (-> country-code "BE" is not printed, blank)
( ------> country is not printed, is not printed))

Mr ,
Jan Vierstra
Dijkstraat, 102 ,
NL-1248-Amsterdam (-----> landcode "NL-" is printed
NETHERLANDS (------> country is printed)

So , that means :
- if value in field-LK = BE....no printings "BE" (-> blank)
- if value in field-LK = NL....printing "NETHERLANDS"

Where and how do you concatenate your strings?

Can you post you query SQL?

mfG
--> stefan <--

 

[John Spencer]

If you are trying to use the Immediate If (IIF) function then the expression
is:

IIF(ADRESS.LK = "BE",Null,ADRESS.LAND)

 

[bet]

-------------------------------

file = adress.mdb
field-LK = countrycode (value is BE or NL)
field-COUNTRY = value is "Belgium" or "Netherlands"
-----------------------------------------
Example for results (Adress-label for postings in a
post-office in Belgium):

Mr, ,
Frans Appelmans,
Kerkstraat, 295 ,
1000 Brussels (-> country-code "BE" is not printed, blank)
( ------> country is not printed, is not printed))

Mr ,
Jan Vierstra
Dijkstraat, 102 ,
NL-1248-Amsterdam (-----> landcode "NL-" is printed
NETHERLANDS (------> country is printed)

So , that means :
- if value in field-LK = BE....no printings "BE" (-> blank)
- if value in field-LK = NL....printing "NETHERLANDS"

When postings letters in a post-office outside Belgium, d
an Country is not blank but "Belgium"
--------------------------------------------------

I am an starting ms-acces-user.
I typed in menu "reports" on the label-properties:

1) First trial :
iff ADRESS.LK = "BE" then ADRESS.COUNTRY(" ")
else ADRESS.LAND(ADRESS.COUNTRY) endif

2) Second trial :.
case when ( [adress].[lk])="be" then " " else [adress].[country] end

-------------------------------------------------------------------------

IIF(ADRESS.LK = "BE",Null,ADRESS.COUNTRY)

Sorry, but this proposal does not work.
Acces message : "can't find the field IIF(ADRESS.LK =
"BE",Null,ADRESS.COUNTRY)
in the expression.
So, access does not indicate which field is not finding "lk" or
"country".....
Both fields are present in the report-field-list....
U tried also other combinations als "isnull" in steds of "null" or without
the notification "adresss"
or use with [], such as [adress]![lk]...but nothing works....
Also, i tried ";" in steds of "," because in europe some keyboards are
different (qwerty-azerty).
Thanks for your help.
Thanks for the goodwill,

 

[John Spencer]

Something to try.

IIF([LK] = "BE";Null;[LAND])

Make sure that your control is not named LK or LAND. I mistakenly thought
your table was named adress. When I reread your post, I saw that the
database was named Adress.

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

-------------------------------
file = adress.mdb
field-LK = countrycode (value is BE or NL)
field-COUNTRY = value is "Belgium" or "Netherlands"
-----------------------------------------
Example for results (Adress-label for postings in a
post-office in Belgium):

Mr, ,
Frans Appelmans,
Kerkstraat, 295 ,
1000 Brussels (-> country-code "BE" is not printed, blank)
( ------> country is not printed, is not printed))

Mr ,
Jan Vierstra
Dijkstraat, 102 ,
NL-1248-Amsterdam (-----> landcode "NL-" is printed
NETHERLANDS (------> country is printed)

So , that means :
- if value in field-LK = BE....no printings "BE" (-> blank)
- if value in field-LK = NL....printing "NETHERLANDS"

When postings letters in a post-office outside Belgium, d
an Country is not blank but "Belgium"
--------------------------------------------------

I am an starting ms-acces-user.
I typed in menu "reports" on the label-properties:

1) First trial :
iff ADRESS.LK = "BE" then ADRESS.COUNTRY(" ")
else ADRESS.LAND(ADRESS.COUNTRY) endif

2) Second trial :.
case when ( [adress].[lk])="be" then " " else [adress].[country] end

-------------------------------------------------------------------------

IIF(ADRESS.LK = "BE",Null,ADRESS.COUNTRY)

Sorry, but this proposal does not work.
Acces message : "can't find the field IIF(ADRESS.LK =
"BE",Null,ADRESS.COUNTRY)
in the expression.
So, access does not indicate which field is not finding "lk" or
"country".....
Both fields are present in the report-field-list....
U tried also other combinations als "isnull" in steds of "null" or without
the notification "adresss"
or use with [], such as [adress]![lk]...but nothing works....
Also, i tried ";" in steds of "," because in europe some keyboards are
different (qwerty-azerty).
Thanks for your help.
Thanks for the goodwill,

 

[bet]

Something to try.

IIF([LK] = "BE";Null;[LAND])

Dear John,

For better communication, i changed the fieldname LAND
in the fiekdname COUNTRY...

I did as you say like above but.....

I typed exactly :



IIF([LK] = "BE-";Null;[COUNTRY])



in the field COUNTRY at the report-panel into a label , in the field COUNTRY
, submenu rightclick "properties, line operationsource" and ms-access
returned

following message :



Syntaxproblem. In query-expression


[IIF([LK] = "BE-";Null;[COUNTRY])].

01..à...à...à...33



01............................... = [ ..(operator, I did not type it !!!!)

02-03-04..................... = IIF (expression-function)

05............................... = (......(operator)

06....................... ... = [......(operator)

07-08.......................... = LK..(field) , value is BE- or NL-

09................................ = ] ......(operator)

10................................ = = .....(operator)

11................................ = " ......(operator)

12-13-14......................= BE- (custom-criteria-value)

15................................ = " .....(operator)

16................................ = ; .....(operator)

17-18-19-20................ = Null(constant-value)

21................................ = ; .... (operator)

22................................ = [...... (operator)

23-24-25-26-27-28-29. .= COUNTRY (custom field)

30................................ = ] ......(operator)

31................................ = ) ......(operator)

32................................ = ] ......(operator , , I did not type it
!!!!)

33................................ = . ....(operator = dotpoint i did not
type it !!!!)



MS-access returned a message with a string of 33 characthers,

3 characters more than I have typped.

Ms-access called my expression a "querry-expression" but I did not

operate in the query-panel but in the report-panel on a label-field,

so I named it a report-querry to make communications better

 

[John Spencer]

Are you trying to do this in the query of the report or in a control on the
report?

Please copy and paste the SQL of the query. In query design view, select
View SQL and then copy and paste the text. Perhaps I can understand what is
happening then.

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

Something to try.

IIF([LK] = "BE";Null;[LAND])

Dear John,

For better communication, i changed the fieldname LAND
in the fiekdname COUNTRY...

I did as you say like above but.....

I typed exactly :



IIF([LK] = "BE-";Null;[COUNTRY])



in the field COUNTRY at the report-panel into a label , in the field
COUNTRY , submenu rightclick "properties, line operationsource" and
ms-access returned

following message :



Syntaxproblem. In query-expression


[IIF([LK] = "BE-";Null;[COUNTRY])].

01..à...à...à...33



01............................... = [ ..(operator, I did not type it
!!!!)

02-03-04..................... = IIF (expression-function)

05............................... = (......(operator)

06....................... ... = [......(operator)

07-08.......................... = LK..(field) , value is BE- or NL-

09................................ = ] ......(operator)

10................................ = = .....(operator)

11................................ = " ......(operator)

12-13-14......................= BE- (custom-criteria-value)

15................................ = " .....(operator)

16................................ = ; .....(operator)

17-18-19-20................ = Null(constant-value)

21................................ = ; .... (operator)

22................................ = [...... (operator)

23-24-25-26-27-28-29. .= COUNTRY (custom field)

30................................ = ] ......(operator)

31................................ = ) ......(operator)

32................................ = ] ......(operator , , I did not type
it !!!!)

33................................ = . ....(operator = dotpoint i did not
type it !!!!)



MS-access returned a message with a string of 33 characthers,

3 characters more than I have typped.

Ms-access called my expression a "querry-expression" but I did not

operate in the query-panel but in the report-panel on a label-field,

so I named it a report-querry to make communications better

 

[bet]

Are you trying to do this in the query of the report or in a control on
the report?

--------> I was doing as follows :

I am not working in the query-panel, so I cannot send the
results shown in the SQL-querry making a lable. Lables always, in
my knowledge only can be maked in a report-panel.
In the report-panel, there is no SQL-information available.

1) Open adress.mdb
2) 2x click in report panel
3) click design-panel
4) working with label-wizard and save work.
5) labels are printing fine and ok
6) then...changing in 1 field (COUNTRY-field) the expression
7) looking the preview...but does not appear !
8) result is a message-box , displayed as shown in other message,
numbered 1 to 33 characters.

The report is based on a query ant that query is based on a tabele.
All, table, query, report... are working perfect as long as the
field COUNTRY is working without filter-conditions.

This is the SQL of the query-panel :
-------------------------------------
SELECT DISTINCTROW adress.[send-title], adress.[send-prename], adress.LK,
adress.[send-name], adress.STRAAT, adress.GSM, adress.POST, adress.WOONPL,
adres.TELEFOON, adress.COUNTRY, adress.type

FROM adress

WHERE (((adress.WOONPL) Like "*xx") AND ((adress.type) Like "*zz*"))

ORDER BY adress.[send-name];


If you wish, I send you a attach in an email of the labelview.
It is not allowed te make attaches in newsgroups.
Best greetings,
Bet

Please copy and paste the SQL of the query. In query design view, select
View SQL and then copy and paste the text. Perhaps I can understand what
is happening then.

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

Something to try.

IIF([LK] = "BE";Null;[LAND])

Dear John,

For better communication, i changed the fieldname LAND
in the fiekdname COUNTRY...

I did as you say like above but.....

I typed exactly :



IIF([LK] = "BE-";Null;[COUNTRY])



in the field COUNTRY at the report-panel into a label , in the field
COUNTRY , submenu rightclick "properties, line operationsource" and
ms-access returned

following message :



Syntaxproblem. In query-expression


[IIF([LK] = "BE-";Null;[COUNTRY])].

01..à...à...à...33



01............................... = [ ..(operator, I did not type it
!!!!)

02-03-04..................... = IIF (expression-function)

05............................... = (......(operator)

06....................... ... = [......(operator)

07-08.......................... = LK..(field) , value is BE- or NL-

09................................ = ] ......(operator)

10................................ = = .....(operator)

11................................ = " ......(operator)

12-13-14......................= BE- (custom-criteria-value)

15................................ = " .....(operator)

16................................ = ; .....(operator)

17-18-19-20................ = Null(constant-value)

21................................ = ; .... (operator)

22................................ = [...... (operator)

23-24-25-26-27-28-29. .= COUNTRY (custom field)

30................................ = ] ......(operator)

31................................ = ) ......(operator)

32................................ = ] ......(operator , , I did not type
it !!!!)

33................................ = . ....(operator = dotpoint i did
not type it !!!!)



MS-access returned a message with a string of 33 characthers,

3 characters more than I have typped.

Ms-access called my expression a "querry-expression" but I did not

operate in the query-panel but in the report-panel on a label-field,

so I named it a report-query to make communications better

 

[John Spencer]

Perhaps you can fix this all in the query - the source for the report is a
query, even if it is not stored in the queries that you can see.

SELECT DISTINCTROW adress.[send-title]
, adress.[send-prename]
, IIF(adress.LK="BE",Null,address.LK) as LK
, adress.[send-name]
, adress.STRAAT
, adress.GSM, adress.POST
, adress.WOONPL
, adres.TELEFOON
, IIF(Adress.LL = "BE",Null,adress.COUNTRY) as Country, adress.type

FROM adress

WHERE (((adress.WOONPL) Like "*xx") AND ((adress.type) Like "*zz*"))

ORDER BY adress.[send-name];

As long as you only need to suppress ONE country code and country name,
there should be little problem with creating the query this way. You MIGHT
want to use "" in place of the Null in the two IIF functions.

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

Are you trying to do this in the query of the report or in a control on
the report?

--------> I was doing as follows :

I am not working in the query-panel, so I cannot send the
results shown in the SQL-querry making a lable. Lables always, in
my knowledge only can be maked in a report-panel.
In the report-panel, there is no SQL-information available.

1) Open adress.mdb
2) 2x click in report panel
3) click design-panel
4) working with label-wizard and save work.
5) labels are printing fine and ok
6) then...changing in 1 field (COUNTRY-field) the expression
7) looking the preview...but does not appear !
8) result is a message-box , displayed as shown in other message,
numbered 1 to 33 characters.

The report is based on a query ant that query is based on a tabele.
All, table, query, report... are working perfect as long as the
field COUNTRY is working without filter-conditions.

This is the SQL of the query-panel :
-------------------------------------
SELECT DISTINCTROW adress.[send-title], adress.[send-prename], adress.LK,
adress.[send-name], adress.STRAAT, adress.GSM, adress.POST,
adress.WOONPL, adres.TELEFOON, adress.COUNTRY, adress.type

FROM adress

WHERE (((adress.WOONPL) Like "*xx") AND ((adress.type) Like "*zz*"))

ORDER BY adress.[send-name];


If you wish, I send you a attach in an email of the labelview.
It is not allowed te make attaches in newsgroups.
Best greetings,
Bet

Please copy and paste the SQL of the query. In query design view, select
View SQL and then copy and paste the text. Perhaps I can understand what
is happening then.

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

"John Spencer" <[email protected]> schreef in bericht
Something to try.

IIF([LK] = "BE";Null;[LAND])


Dear John,

For better communication, i changed the fieldname LAND
in the fiekdname COUNTRY...

I did as you say like above but.....

I typed exactly :



IIF([LK] = "BE-";Null;[COUNTRY])



in the field COUNTRY at the report-panel into a label , in the field
COUNTRY , submenu rightclick "properties, line operationsource" and
ms-access returned

following message :



Syntaxproblem. In query-expression


[IIF([LK] = "BE-";Null;[COUNTRY])].

01..à...à...à...33



01............................... = [ ..(operator, I did not type it
!!!!)

02-03-04..................... = IIF (expression-function)

05............................... = (......(operator)

06....................... ... = [......(operator)

07-08.......................... = LK..(field) , value is BE- or NL-

09................................ = ] ......(operator)

10................................ = = .....(operator)

11................................ = " ......(operator)

12-13-14......................= BE- (custom-criteria-value)

15................................ = " .....(operator)

16................................ = ; .....(operator)

17-18-19-20................ = Null(constant-value)

21................................ = ; .... (operator)

22................................ = [...... (operator)

23-24-25-26-27-28-29. .= COUNTRY (custom field)

30................................ = ] ......(operator)

31................................ = ) ......(operator)

32................................ = ] ......(operator , , I did not
type it !!!!)

33................................ = . ....(operator = dotpoint i did
not type it !!!!)



MS-access returned a message with a string of 33 characthers,

3 characters more than I have typped.

Ms-access called my expression a "querry-expression" but I did not

operate in the query-panel but in the report-panel on a label-field,

so I named it a report-query to make communications better