DLookup in Text box to show Currency (number field)

[S Himmelrich]

I'm currently using the following code in the control souce of a text
box on a form to retrieve data from a query:

Here is the code: =DLookUp([FY12 CAPEX Forecast.EIS - Ent IT Infra
BOH Svcs],[FY12 Forecast])

I get "#Name?" in the text box when viewing the form. Any ideas?

Thanks in advance for you help

 

[John W. Vinson]

I'm currently using the following code in the control souce of a text
box on a form to retrieve data from a query:

Here is the code: =DLookUp([FY12 CAPEX Forecast.EIS - Ent IT Infra
BOH Svcs],[FY12 Forecast])

I get "#Name?" in the text box when viewing the form. Any ideas?

Thanks in advance for you help

Read the help on DLookUp.

The three arguments to DLookUp must be TEXT STRINGS - the first is a text
string containing the name of the field that you want to look up; the second
is a text string containing the name of the Table or Query containing that
field; and the third is a text string containing the (optional) query criteria
identifying which record in the table/query to use.

Assuming that the FY12 Forecast query returns only one record, you should be
able to use

=DLookUp("[FY12 CAPEX Forecast.EIS - Ent IT InfraBOH Svcs]","[FY12 Forecast]")

If it contains more than one record, you'll need some sort of criterion
identifying WHICH record you want.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[S Himmelrich]

I'm currently using the following code in the control souce of a text
box on a form to retrieve data from a query:

Here is the code:  =DLookUp([FY12 CAPEX Forecast.EIS - Ent IT Infra
BOH Svcs],[FY12 Forecast])

I get "#Name?" in the text box when viewing the form.  Any ideas?

Thanks in advance for you help

Read the help on DLookUp.

The three arguments to DLookUp must be TEXT STRINGS - the first is a text
string containing the name of the field that you want to look up; the second
is a text string containing the name of the Table or Query containing that
field; and the third is a text string containing the (optional) query criteria
identifying which record in the table/query to use.

Assuming that the FY12 Forecast query returns only one record, you shouldbe
able to use

=DLookUp("[FY12 CAPEX Forecast.EIS - Ent IT InfraBOH Svcs]","[FY12 Forecast]")

If it contains more than one record, you'll need some sort of criterion
identifying WHICH record you want.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

this is still giving me the same error - is it an issue that the query
is returning a number/currency - do I need to reformat it in the query?

 

[S Himmelrich]

I'm currently using the following code in the control souce of a text
box on a form to retrieve data from a query:

Here is the code:  =DLookUp([FY12 CAPEX Forecast.EIS - Ent IT Infra
BOH Svcs],[FY12 Forecast])

I get "#Name?" in the text box when viewing the form.  Any ideas?

Thanks in advance for you help

Read the help on DLookUp.

The three arguments to DLookUp must be TEXT STRINGS - the first is a text
string containing the name of the field that you want to look up; the second
is a text string containing the name of the Table or Query containing that
field; and the third is a text string containing the (optional) query criteria
identifying which record in the table/query to use.

Assuming that the FY12 Forecast query returns only one record, you shouldbe
able to use

=DLookUp("[FY12 CAPEX Forecast.EIS - Ent IT InfraBOH Svcs]","[FY12 Forecast]")

If it contains more than one record, you'll need some sort of criterion
identifying WHICH record you want.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Actually I found the issue, but now I get a blinking #error which is
odd, I know the syntax is correct as I tested this with another table
selecting a number value and it worked. Is there a setting that I'm
missing - this is a query that is build on a cross tab query...any
thoughts?

Thanks, Scott

 

[John W. Vinson]

this is still giving me the same error - is it an issue that the query
is returning a number/currency - do I need to reformat it in the query?

Sorry, my crystal ball is a bit foggy - I can't see your current expression or
your query from here. Could you please post the total SQL of the query?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[S Himmelrich]

Sorry, my crystal ball is a bit foggy - I can't see your current expression or
your query from here. Could you please post the total SQL of the query?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

SELECT [FY12 CAPEX Forecast].Status, [FY12 CAPEX Forecast].[EIS - App
and Infra Architecture], [FY12 OPEX Forecast].[EIS - App and Infra
Architecture], [FY12 CAPEX Forecast].[EIS - Cmn Svcs Splier Mgmt],
[FY12 OPEX Forecast].[EIS - Cmn Svcs Splier Mgmt], [FY12 CAPEX
Forecast].[EIS - Ent IT Client Svc and TRM], [FY12 OPEX Forecast].[EIS
- Ent IT Client Svc and TRM], [FY12 OPEX Forecast].[EIS - Ent IT Infra
BOH Svcs], [FY12 CAPEX Forecast].[EIS - Program and Service Mgmt],
[FY12 OPEX Forecast].[EIS - Program and Service Mgmt], [FY12 CAPEX
Forecast].[EIS - Ent IT Infra BOH Svcs]
FROM [FY12 CAPEX Forecast] INNER JOIN [FY12 OPEX Forecast] ON [FY12
CAPEX Forecast].Status = [FY12 OPEX Forecast].Status
WHERE ((([FY12 CAPEX Forecast].Status)="Approved"));

 

[John W. Vinson]

Sorry, my crystal ball is a bit foggy - I can't see your current expression or
your query from here. Could you please post the total SQL of the query?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

SELECT [FY12 CAPEX Forecast].Status, [FY12 CAPEX Forecast].[EIS - App
and Infra Architecture], [FY12 OPEX Forecast].[EIS - App and Infra
Architecture], [FY12 CAPEX Forecast].[EIS - Cmn Svcs Splier Mgmt],
[FY12 OPEX Forecast].[EIS - Cmn Svcs Splier Mgmt], [FY12 CAPEX
Forecast].[EIS - Ent IT Client Svc and TRM], [FY12 OPEX Forecast].[EIS
- Ent IT Client Svc and TRM], [FY12 OPEX Forecast].[EIS - Ent IT Infra
BOH Svcs], [FY12 CAPEX Forecast].[EIS - Program and Service Mgmt],
[FY12 OPEX Forecast].[EIS - Program and Service Mgmt], [FY12 CAPEX
Forecast].[EIS - Ent IT Infra BOH Svcs]
FROM [FY12 CAPEX Forecast] INNER JOIN [FY12 OPEX Forecast] ON [FY12
CAPEX Forecast].Status = [FY12 OPEX Forecast].Status
WHERE ((([FY12 CAPEX Forecast].Status)="Approved"));

Again:

YOU can see your database.

I cannot.

This query does not include the DLookUp, and you have not said what field is
giving you an error.

I'd love to be able to help, but I cannot solve problems that you don't show
me.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[S Himmelrich]

SELECT [FY12 CAPEX Forecast].Status, [FY12 CAPEX Forecast].[EIS - App
and Infra Architecture], [FY12 OPEX Forecast].[EIS - App and Infra
Architecture], [FY12 CAPEX Forecast].[EIS - Cmn Svcs Splier Mgmt],
[FY12 OPEX Forecast].[EIS - Cmn Svcs Splier Mgmt], [FY12 CAPEX
Forecast].[EIS - Ent IT Client Svc and TRM], [FY12 OPEX Forecast].[EIS
- Ent IT Client Svc and TRM], [FY12 OPEX Forecast].[EIS - Ent IT Infra
BOH Svcs], [FY12 CAPEX Forecast].[EIS - Program and Service Mgmt],
[FY12 OPEX Forecast].[EIS - Program and Service Mgmt], [FY12 CAPEX
Forecast].[EIS - Ent IT Infra BOH Svcs]
FROM [FY12 CAPEX Forecast] INNER JOIN [FY12 OPEX Forecast] ON [FY12
CAPEX Forecast].Status = [FY12 OPEX Forecast].Status
WHERE ((([FY12 CAPEX Forecast].Status)="Approved"));

Again:

YOU can see your database.

I cannot.

This query does not include the DLookUp, and you have not said what fieldis
giving you an error.

I'd love to be able to help, but I cannot solve problems that you don't show
me.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

I understand you point - I thought the past several statements made my
thoughts clear - sorry about that.

Recap:

the DLookup is in a text box in a form: =DLookUp("[FY12 CAPEX
Forecast.EIS - Ent IT InfraBOH Svcs]","[FY12 Forecast]")
(where I'm getting #Error)

the sql of the query I'm referencing :
SELECT [FY12 CAPEX Forecast].Status, [FY12 CAPEX Forecast].[EIS - App
and Infra Architecture], [FY12 OPEX Forecast].[EIS - App and Infra
Architecture], [FY12 CAPEX Forecast].[EIS - Cmn Svcs Splier Mgmt],
[FY12 OPEX Forecast].[EIS - Cmn Svcs Splier Mgmt], [FY12 CAPEX
Forecast].[EIS - Ent IT Client Svc and TRM], [FY12 OPEX Forecast].
[EIS
- Ent IT Client Svc and TRM], [FY12 OPEX Forecast].[EIS - Ent IT
Infra
BOH Svcs], [FY12 CAPEX Forecast].[EIS - Program and Service Mgmt],
[FY12 OPEX Forecast].[EIS - Program and Service Mgmt], [FY12 CAPEX
Forecast].[EIS - Ent IT Infra BOH Svcs]
FROM [FY12 CAPEX Forecast] INNER JOIN [FY12 OPEX Forecast] ON [FY12
CAPEX Forecast].Status = [FY12 OPEX Forecast].Status
WHERE ((([FY12 CAPEX Forecast].Status)="Approved"));

I appreciate your help.

 

[John W. Vinson]

I understand you point - I thought the past several statements made my
thoughts clear - sorry about that.

Recap:

the DLookup is in a text box in a form: =DLookUp("[FY12 CAPEX
Forecast.EIS - Ent IT InfraBOH Svcs]","[FY12 Forecast]")
(where I'm getting #Error)

the sql of the query I'm referencing :
SELECT [FY12 CAPEX Forecast].Status, [FY12 CAPEX Forecast].[EIS - App
and Infra Architecture], [FY12 OPEX Forecast].[EIS - App and Infra
Architecture], [FY12 CAPEX Forecast].[EIS - Cmn Svcs Splier Mgmt],
[FY12 OPEX Forecast].[EIS - Cmn Svcs Splier Mgmt], [FY12 CAPEX
Forecast].[EIS - Ent IT Client Svc and TRM], [FY12 OPEX Forecast].
[EIS
- Ent IT Client Svc and TRM], [FY12 OPEX Forecast].[EIS - Ent IT
Infra
BOH Svcs], [FY12 CAPEX Forecast].[EIS - Program and Service Mgmt],
[FY12 OPEX Forecast].[EIS - Program and Service Mgmt], [FY12 CAPEX
Forecast].[EIS - Ent IT Infra BOH Svcs]
FROM [FY12 CAPEX Forecast] INNER JOIN [FY12 OPEX Forecast] ON [FY12
CAPEX Forecast].Status = [FY12 OPEX Forecast].Status
WHERE ((([FY12 CAPEX Forecast].Status)="Approved"));

I appreciate your help.

Is this query saved under the name [FY12 Forecast]? If so, there is no FIELD
in that query named [FY12 CAPEX Forecast.EIS - Ent IT InfraBOH Svcs]. There IS
a field named [EIS - Ent IT Infra BOH Svcs] though; try using that fieldname.

Note that the fieldname in the query is qualified by the tablename. However,
the tablename is NOT part of the fieldname, and DLookUp is looking for just
the fieldname.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[S Himmelrich]

I understand you point - I thought the past several statements made my
thoughts clear - sorry about that.

Recap:

the DLookup is in a text box in a form: =DLookUp("[FY12 CAPEX
Forecast.EIS - Ent IT InfraBOH Svcs]","[FY12 Forecast]")
(where I'm getting #Error)

the sql of the query I'm referencing :
SELECT [FY12 CAPEX Forecast].Status, [FY12 CAPEX Forecast].[EIS - App
and Infra Architecture], [FY12 OPEX Forecast].[EIS - App and Infra
Architecture], [FY12 CAPEX Forecast].[EIS - Cmn Svcs Splier Mgmt],
[FY12 OPEX Forecast].[EIS - Cmn Svcs Splier Mgmt], [FY12 CAPEX
Forecast].[EIS - Ent IT Client Svc and TRM], [FY12 OPEX Forecast].
[EIS
- Ent IT Client Svc and TRM], [FY12 OPEX Forecast].[EIS - Ent IT
Infra
BOH Svcs], [FY12 CAPEX Forecast].[EIS - Program and Service Mgmt],
[FY12 OPEX Forecast].[EIS - Program and Service Mgmt], [FY12 CAPEX
Forecast].[EIS - Ent IT Infra BOH Svcs]
FROM [FY12 CAPEX Forecast] INNER JOIN [FY12 OPEX Forecast] ON [FY12
CAPEX Forecast].Status = [FY12 OPEX Forecast].Status
WHERE ((([FY12 CAPEX Forecast].Status)="Approved"));

I appreciate your help.

Is this query saved under the name [FY12 Forecast]? If so, there is no FIELD
in that query named [FY12 CAPEX Forecast.EIS - Ent IT InfraBOH Svcs]. There IS
a field named [EIS - Ent IT Infra BOH Svcs] though; try using that fieldname.

Note that the fieldname in the query is qualified by the tablename. However,
the tablename is NOT part of the fieldname, and DLookUp is looking for just
the fieldname.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

In lieu of this situation making us both feel like we are going in
circles (at least me). I’ve turned the crosstab queries into make
table queries and simplified everything. Thank you for your daily
help on this…..it was a learning experience.