What's wrong in this formula

[Frank Situmorang]

Hello,

I do not what is wrong in this formula it says It contains invalid syntax
=IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

 

[John... Visio MVP]

Hello,

I do not what is wrong in this formula it says It contains invalid
syntax =IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

First, lose the quotes around [ChurchName_E],[ChurchName_L] if you want
the contents of those fields, not the literals.

Test the parts independently:
=IIf(True, [ChurchName_E],[ChurchName_L])
=IIf(False, [ChurchName_E],[ChurchName_L])
=DLookUp("[Language]","tblDefaults")="English"

see which gives an error.

Bob Quintal

PA is y I've altered my email address.

Losing the quotes as suggested should work and I would suspect also loing
them from around [Language] would complete the process.

John... Visio MVP

 

[Ken Snell \(MVP\)]

Hello,

I do not what is wrong in this formula it says It contains invalid
syntax =IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

First, lose the quotes around [ChurchName_E],[ChurchName_L] if you want
the contents of those fields, not the literals.

Test the parts independently:
=IIf(True, [ChurchName_E],[ChurchName_L])
=IIf(False, [ChurchName_E],[ChurchName_L])
=DLookUp("[Language]","tblDefaults")="English"

see which gives an error.

Bob Quintal

PA is y I've altered my email address.

Losing the quotes as suggested should work and I would suspect also loing
them from around [Language] would complete the process.

John... Visio MVP

No, those quotes around [Language] are needed as part of the DLookup
function's syntax. All three arguments for DLookup are string type.

 

[John... Visio MVP]

in
Hello,

I do not what is wrong in this formula it says It contains invalid
syntax =IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks


First, lose the quotes around [ChurchName_E],[ChurchName_L] if you want
the contents of those fields, not the literals.

Test the parts independently:
=IIf(True, [ChurchName_E],[ChurchName_L])
=IIf(False, [ChurchName_E],[ChurchName_L])
=DLookUp("[Language]","tblDefaults")="English"

see which gives an error.

Bob Quintal

PA is y I've altered my email address.

Losing the quotes as suggested should work and I would suspect also loing
them from around [Language] would complete the process.

John... Visio MVP

No, those quotes around [Language] are needed as part of the DLookup
function's syntax. All three arguments for DLookup are string type.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks Ken. It has been awhile since I actually used a DLookup.

John... Visio MVP

 

[Michael Gramelspacher]

Hello,

I do not what is wrong in this formula it says It contains invalid syntax
=IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

It works for me.

Immediate window:

?IIf(DLookUp("[Language]","tblDefaults")="English","[ChurchName_E]","[ChurchName_L]")

 

[Mike Painter]

Hello,

I do not what is wrong in this formula it says It contains invalid
syntax =IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

Lots of answer but the question that should be asked is where is this being
used?
In a query, form or report?

 

[Frank Situmorang]

Hi Mike Paniter:

It will be used in the form which is based on query. Probably I should use
Dlookup on query?. If it is in a report what would be a problem

Thanks
--
H. Frank Situmorang

Hello,

I do not what is wrong in this formula it says It contains invalid
syntax =IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

Lots of answer but the question that should be asked is where is this being
used?
In a query, form or report?

 

[Frank Situmorang]

Mike Painter:

For details error message is as follows:

You omittend an operand or operator, you entered an invalid character or
comma, or you enterred text without surrounding it in quotation marks

If you could help me, what is wrong on my form text control

Thanks in advance


--
H. Frank Situmorang

Hello,

I do not what is wrong in this formula it says It contains invalid
syntax =IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

Lots of answer but the question that should be asked is where is this being
used?
In a query, form or report?

 

[Mike Painter]

Try placing [ChurchName_E] and[ChurchName_L] on th form and then use
"Me!ChurchName_E","Me!ChurchName_L"
You can make then invisible.
I am assuming that those fields are in the query that the form uses.

I don't think the DLookup knows what "[ChurchName_E]","[ChurchName_L]" are.

I do not what is wrong in this formula it says It contains invalid
syntax =IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

Lots of answer but the question that should be asked is where is
this being used?
In a query, form or report?

 

[Frank Situmorang]

Mike:

I have used this but still syntax error.
=IIf(DLookUp("[Language]","tblDefaults")="English",
"Me!ChurchName_E","Me!ChurchName_L")

Please help if there is still a way out. In fact my intention is if the
langguage in English, it will use English name, but if not , it is better it
uses Local Name of the church.

Thanks.

--
H. Frank Situmorang

Try placing [ChurchName_E] and[ChurchName_L] on th form and then use
"Me!ChurchName_E","Me!ChurchName_L"
You can make then invisible.
I am assuming that those fields are in the query that the form uses.

I don't think the DLookup knows what "[ChurchName_E]","[ChurchName_L]" are.

I do not what is wrong in this formula it says It contains invalid
syntax =IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

Lots of answer but the question that should be asked is where is
this being used?
In a query, form or report?

 

[Mike Painter]

I goofed with teh quote marks. it should be
Me!ChurchName_E,Me!ChurchName_L
but ther is nothing wrong with the formula and it should have returned a
string equal to "Me!ChurchName_E" or"Me!ChurchName_L"

My only other suggestion is to compile the database and see what happens.
Sometimes Access throws messages that have nothing to do with what the
message is attached to.

Mike:

I have used this but still syntax error.
=IIf(DLookUp("[Language]","tblDefaults")="English",
"Me!ChurchName_E","Me!ChurchName_L")

Please help if there is still a way out. In fact my intention is if
the langguage in English, it will use English name, but if not , it
is better it uses Local Name of the church.

Thanks.

Try placing [ChurchName_E] and[ChurchName_L] on th form and then use
"Me!ChurchName_E","Me!ChurchName_L"
You can make then invisible.
I am assuming that those fields are in the query that the form uses.

I don't think the DLookup knows what
"[ChurchName_E]","[ChurchName_L]" are.

I do not what is wrong in this formula it says It contains invalid
syntax =IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

Lots of answer but the question that should be asked is where is
this being used?
In a query, form or report?

 

[Douglas J. Steele]

The equal sign implies to me that it's being used as the ControlSource for a
text box. As such, you cannot include the Me!: just use

=IIf(DLookUp("[Language]","tblDefaults")="English", ChurchName_E,
ChurchName_L)

That assumes that it's a bound form, and that the form's Recordsource
includes ChurchName_E and ChurchName_L as fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I goofed with teh quote marks. it should be
Me!ChurchName_E,Me!ChurchName_L
but ther is nothing wrong with the formula and it should have returned a
string equal to "Me!ChurchName_E" or"Me!ChurchName_L"

My only other suggestion is to compile the database and see what happens.
Sometimes Access throws messages that have nothing to do with what the
message is attached to.

Mike:

I have used this but still syntax error.
=IIf(DLookUp("[Language]","tblDefaults")="English",
"Me!ChurchName_E","Me!ChurchName_L")

Please help if there is still a way out. In fact my intention is if
the langguage in English, it will use English name, but if not , it
is better it uses Local Name of the church.

Thanks.

Try placing [ChurchName_E] and[ChurchName_L] on th form and then use
"Me!ChurchName_E","Me!ChurchName_L"
You can make then invisible.
I am assuming that those fields are in the query that the form uses.

I don't think the DLookup knows what
"[ChurchName_E]","[ChurchName_L]" are.

Frank Situmorang wrote:
I do not what is wrong in this formula it says It contains
invalid
syntax =IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

Lots of answer but the question that should be asked is where is
this being used?
In a query, form or report?

 

[Frank Situmorang]

Dough:

On my Main Switchboard form I have the picuture of the church and the name
of the church. The form is based on this Query:
SELECT [Switchboard Items].SwitchboardID, [Switchboard Items].ItemNumber,
[Switchboard Items].Language, [Switchboard Items].ItemText, [Switchboard
Items].Command, [Switchboard Items].Argument, tblChurches.ChurchName_E,
tblChurches.ChurchName_L, tblChurches.CAddress1, tblChurches.CAddress2,
tblChurches.CpostalCode, tblChurches.CemailAddress, tblChurches.Cregion,
tblChurches.CCountry, tblChurches.Ccity, tblChurches.CPhone, tblChurches.CFax
FROM tblChurches INNER JOIN ([Switchboard Items] INNER JOIN tblDefaults ON
[Switchboard Items].Language = tblDefaults.Language) ON tblChurches.ChurchID
= tblDefaults.Church;

I have tried your suggestion, but still shows error message/syntax error

Please help.



--
H. Frank Situmorang

The equal sign implies to me that it's being used as the ControlSource for a
text box. As such, you cannot include the Me!: just use

=IIf(DLookUp("[Language]","tblDefaults")="English", ChurchName_E,
ChurchName_L)

That assumes that it's a bound form, and that the form's Recordsource
includes ChurchName_E and ChurchName_L as fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I goofed with teh quote marks. it should be
Me!ChurchName_E,Me!ChurchName_L
but ther is nothing wrong with the formula and it should have returned a
string equal to "Me!ChurchName_E" or"Me!ChurchName_L"

My only other suggestion is to compile the database and see what happens.
Sometimes Access throws messages that have nothing to do with what the
message is attached to.

Mike:

I have used this but still syntax error.
=IIf(DLookUp("[Language]","tblDefaults")="English",
"Me!ChurchName_E","Me!ChurchName_L")

Please help if there is still a way out. In fact my intention is if
the langguage in English, it will use English name, but if not , it
is better it uses Local Name of the church.

Thanks.


Try placing [ChurchName_E] and[ChurchName_L] on th form and then use
"Me!ChurchName_E","Me!ChurchName_L"
You can make then invisible.
I am assuming that those fields are in the query that the form uses.

I don't think the DLookup knows what
"[ChurchName_E]","[ChurchName_L]" are.

Frank Situmorang wrote:
I do not what is wrong in this formula it says It contains
invalid
syntax =IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

Lots of answer but the question that should be asked is where is
this being used?
In a query, form or report?

 

[Douglas J. Steele]

Are you sure that the DLookup is returning a string?

Go to the Imnmediate Window (Ctrl-G), type

?DLookUp("[Language]","tblDefaults")

(including the question mark) and hit Enter. What appears on the next line?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dough:

On my Main Switchboard form I have the picuture of the church and the name
of the church. The form is based on this Query:
SELECT [Switchboard Items].SwitchboardID, [Switchboard Items].ItemNumber,
[Switchboard Items].Language, [Switchboard Items].ItemText, [Switchboard
Items].Command, [Switchboard Items].Argument, tblChurches.ChurchName_E,
tblChurches.ChurchName_L, tblChurches.CAddress1, tblChurches.CAddress2,
tblChurches.CpostalCode, tblChurches.CemailAddress, tblChurches.Cregion,
tblChurches.CCountry, tblChurches.Ccity, tblChurches.CPhone,
tblChurches.CFax
FROM tblChurches INNER JOIN ([Switchboard Items] INNER JOIN tblDefaults ON
[Switchboard Items].Language = tblDefaults.Language) ON
tblChurches.ChurchID
= tblDefaults.Church;

I have tried your suggestion, but still shows error message/syntax error

Please help.



--
H. Frank Situmorang

The equal sign implies to me that it's being used as the ControlSource
for a
text box. As such, you cannot include the Me!: just use

=IIf(DLookUp("[Language]","tblDefaults")="English", ChurchName_E,
ChurchName_L)

That assumes that it's a bound form, and that the form's Recordsource
includes ChurchName_E and ChurchName_L as fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I goofed with teh quote marks. it should be
Me!ChurchName_E,Me!ChurchName_L
but ther is nothing wrong with the formula and it should have returned
a
string equal to "Me!ChurchName_E" or"Me!ChurchName_L"

My only other suggestion is to compile the database and see what
happens.
Sometimes Access throws messages that have nothing to do with what the
message is attached to.






Frank Situmorang wrote:
Mike:

I have used this but still syntax error.
=IIf(DLookUp("[Language]","tblDefaults")="English",
"Me!ChurchName_E","Me!ChurchName_L")

Please help if there is still a way out. In fact my intention is if
the langguage in English, it will use English name, but if not , it
is better it uses Local Name of the church.

Thanks.


Try placing [ChurchName_E] and[ChurchName_L] on th form and then use
"Me!ChurchName_E","Me!ChurchName_L"
You can make then invisible.
I am assuming that those fields are in the query that the form uses.

I don't think the DLookup knows what
"[ChurchName_E]","[ChurchName_L]" are.

Frank Situmorang wrote:
I do not what is wrong in this formula it says It contains
invalid
syntax =IIf(DLookUp("[Language]","tblDefaults")="English",
"[ChurchName_E]","[ChurchName_L]")
Thanks

Lots of answer but the question that should be asked is where is
this being used?
In a query, form or report?

 

[Michael Gramelspacher]

Dough:

On my Main Switchboard form I have the picuture of the church and the name
of the church. The form is based on this Query:
SELECT [Switchboard Items].SwitchboardID, [Switchboard Items].ItemNumber,
[Switchboard Items].Language, [Switchboard Items].ItemText, [Switchboard
Items].Command, [Switchboard Items].Argument, tblChurches.ChurchName_E,
tblChurches.ChurchName_L, tblChurches.CAddress1, tblChurches.CAddress2,
tblChurches.CpostalCode, tblChurches.CemailAddress, tblChurches.Cregion,
tblChurches.CCountry, tblChurches.Ccity, tblChurches.CPhone, tblChurches.CFax
FROM tblChurches INNER JOIN ([Switchboard Items] INNER JOIN tblDefaults ON
[Switchboard Items].Language = tblDefaults.Language) ON tblChurches.ChurchID
= tblDefaults.Church;

I have tried your suggestion, but still shows error message/syntax error

Please help.

Maybe what you need is a calculated field in the query.

IIF(tblDefaults.Language = "English" , tblchurches.ChurchName_E, tblChurches.ChurchName_L) AS
ChurchName

The control source on the form would then be ChurchName. If the default is English, ChurchName
will contain the churchname in English, otherwise it will contain the church name in the local
language.

 

[Mike Painter]

Maybe what you need is a calculated field in the query.

IIF(tblDefaults.Language = "English" , tblchurches.ChurchName_E,
tblChurches.ChurchName_L) AS ChurchName

The control source on the form would then be ChurchName. If the
default is English, ChurchName will contain the churchname in
English, otherwise it will contain the church name in the local
language.

Good point, and a much better way to go.

 

[Mike Painter]

The equal sign implies to me that it's being used as the
ControlSource for a text box. As such, you cannot include the Me!:
just use
=IIf(DLookUp("[Language]","tblDefaults")="English", ChurchName_E,
ChurchName_L)

Oops. My dog wrote the post. Bad Sam, bad dog.

 

[Frank Situmorang]

Mike:

Thanks very much. Your suggestion now is the one that makes it work for me .

With many thanks
--
H. Frank Situmorang

Dough:

On my Main Switchboard form I have the picuture of the church and the name
of the church. The form is based on this Query:
SELECT [Switchboard Items].SwitchboardID, [Switchboard Items].ItemNumber,
[Switchboard Items].Language, [Switchboard Items].ItemText, [Switchboard
Items].Command, [Switchboard Items].Argument, tblChurches.ChurchName_E,
tblChurches.ChurchName_L, tblChurches.CAddress1, tblChurches.CAddress2,
tblChurches.CpostalCode, tblChurches.CemailAddress, tblChurches.Cregion,
tblChurches.CCountry, tblChurches.Ccity, tblChurches.CPhone, tblChurches.CFax
FROM tblChurches INNER JOIN ([Switchboard Items] INNER JOIN tblDefaults ON
[Switchboard Items].Language = tblDefaults.Language) ON tblChurches.ChurchID
= tblDefaults.Church;

I have tried your suggestion, but still shows error message/syntax error

Please help.

Maybe what you need is a calculated field in the query.

IIF(tblDefaults.Language = "English" , tblchurches.ChurchName_E, tblChurches.ChurchName_L) AS
ChurchName

The control source on the form would then be ChurchName. If the default is English, ChurchName
will contain the churchname in English, otherwise it will contain the church name in the local
language.

 

[Mike Painter]

If the calculated field was the solution, it was not my idea.

Mike:

Thanks very much. Your suggestion now is the one that makes it work
for me .

With many thanks

Dough:

On my Main Switchboard form I have the picuture of the church and
the name of the church. The form is based on this Query:
SELECT [Switchboard Items].SwitchboardID, [Switchboard
Items].ItemNumber, [Switchboard Items].Language, [Switchboard
Items].ItemText, [Switchboard Items].Command, [Switchboard
Items].Argument, tblChurches.ChurchName_E,
tblChurches.ChurchName_L, tblChurches.CAddress1,
tblChurches.CAddress2, tblChurches.CpostalCode,
tblChurches.CemailAddress, tblChurches.Cregion,
tblChurches.CCountry, tblChurches.Ccity, tblChurches.CPhone,
tblChurches.CFax FROM tblChurches INNER JOIN ([Switchboard Items]
INNER JOIN tblDefaults ON [Switchboard Items].Language =
tblDefaults.Language) ON tblChurches.ChurchID = tblDefaults.Church;

I have tried your suggestion, but still shows error message/syntax
error

Please help.

Maybe what you need is a calculated field in the query.

IIF(tblDefaults.Language = "English" , tblchurches.ChurchName_E,
tblChurches.ChurchName_L) AS ChurchName

The control source on the form would then be ChurchName. If the
default is English, ChurchName will contain the churchname in
English, otherwise it will contain the church name in the local
language.

 

[Frank Situmorang]

Mike:

I should have replied to you instead of Mike Painter. Thanks very much Mike
for your suggestion. I already get tired after checking and checking nothing
wrong with the comma, etc. but when I put your suggestion it works now.

There are 2 things left that I have to think about on my churchdata base.

1. Making a dynamic report. the purpose of this is that we just need one
report to produce many fileter by member type, status, gender, churchname,
etc. Currently I have each report with each query ( filter in design query).
this method requires me to have many text labels to have to translated into
many languages, so I choose not to use this method.
2. Making append in the form menu. You have sent me the sample. I am just
studying it.

Thanks very much for all your helps.
--
H. Frank Situmorang

Dough:

On my Main Switchboard form I have the picuture of the church and the name
of the church. The form is based on this Query:
SELECT [Switchboard Items].SwitchboardID, [Switchboard Items].ItemNumber,
[Switchboard Items].Language, [Switchboard Items].ItemText, [Switchboard
Items].Command, [Switchboard Items].Argument, tblChurches.ChurchName_E,
tblChurches.ChurchName_L, tblChurches.CAddress1, tblChurches.CAddress2,
tblChurches.CpostalCode, tblChurches.CemailAddress, tblChurches.Cregion,
tblChurches.CCountry, tblChurches.Ccity, tblChurches.CPhone, tblChurches.CFax
FROM tblChurches INNER JOIN ([Switchboard Items] INNER JOIN tblDefaults ON
[Switchboard Items].Language = tblDefaults.Language) ON tblChurches.ChurchID
= tblDefaults.Church;

I have tried your suggestion, but still shows error message/syntax error

Please help.

Maybe what you need is a calculated field in the query.

IIF(tblDefaults.Language = "English" , tblchurches.ChurchName_E, tblChurches.ChurchName_L) AS
ChurchName

The control source on the form would then be ChurchName. If the default is English, ChurchName
will contain the churchname in English, otherwise it will contain the church name in the local
language.