Dlookup

[Guest]

In a query, I have the following expression:
CYP: ("Net-Period End " & DLookUp("[CYP]","CutoffDataQuery"))

This will result in the field labeled CYP having the value:
Net-Period End 08/31/2005

Rather than returning the value listed above, I want the CYP field to return
the data in a field named: Net-Period End 08/31/2005

I tried to simply put [ and ] around the expression listed above but it did
not work. Is there some way to return let access know that the result of the
expression above should be the data within a field named by the expression?
Net-Period End 08/31/2005

 

[Jeff Boyce]

Carlo

You may have another, more serious issue. If you have

a field named: Net-Period End 08/31/2005

you've embedded data as a field name. While this might be necessary when
using a spreadsheet, you'll find that Access handles this better if your
table doesn't have repeating fields (I'm only guessing, since you didn't
say, but might you have one field for each "Net-Period End"...?).

Regards

Jeff Boyce
<MS Office/Access MVP>

In a query, I have the following expression:
CYP: ("Net-Period End " & DLookUp("[CYP]","CutoffDataQuery"))

This will result in the field labeled CYP having the value:
Net-Period End 08/31/2005

Rather than returning the value listed above, I want the CYP field to
return
the data in a field named: Net-Period End 08/31/2005

I tried to simply put [ and ] around the expression listed above but it
did
not work. Is there some way to return let access know that the result of
the
expression above should be the data within a field named by the
expression?
Net-Period End 08/31/2005

 

[Guest]

Jeff,

Thanks for the quick response.

You are correct. I have a table that contains 36 fields, each with a
Net-Period End date (i.e., Net-Period End 08/31/05, Net-Period End 09/30/05,
etc.) . This table was created as a result of an import of data from
Peachtree accounting software. In each date field exists financial results
data that I am using in financial reports. Rather than having the reports
refer to a field named by a specific date (that would have to be changed in
each report every month), I am having the reports refer to a more generic
field name (i.e., Current Month). In an underlying query, I would manually
change Current Month to the appropriate Net-Period End.. date. Rather than
manually doing this every month in the query, I was hoping the query can
lookup the date value in another table and then provide me the corresponding
financial data for the date.

I can lookup the date value (as mentioned in my initial e-mail), however, I
do not want that value to be the resulting data in the query but rather the
correpsing Net-Period End date.

For example, for August 2005 results, I would manually change the query to:
Current Month: [Net-Period End 08/31/05]

This would provide me financial records for August. I would like to
accomplish this same task but via a dlookup expression as initially
suggested. When I use
Current Month: ("Net-Period End " & DLookUp("[CYP]","CutoffDataQuery")

it returns Net-Period End 08/31/05 for each record rather than the financial
data. I was wondering if there is some way to tell access that the dlookup
expression is the field name to be used to provide the data, not the actual
default data to be returned.

Sorry for being long winded.... I hope this clarifies things...

Carlo

You may have another, more serious issue. If you have

a field named: Net-Period End 08/31/2005

you've embedded data as a field name. While this might be necessary when
using a spreadsheet, you'll find that Access handles this better if your
table doesn't have repeating fields (I'm only guessing, since you didn't
say, but might you have one field for each "Net-Period End"...?).

Regards

Jeff Boyce
<MS Office/Access MVP>

In a query, I have the following expression:
CYP: ("Net-Period End " & DLookUp("[CYP]","CutoffDataQuery"))

This will result in the field labeled CYP having the value:
Net-Period End 08/31/2005

Rather than returning the value listed above, I want the CYP field to
return
the data in a field named: Net-Period End 08/31/2005

I tried to simply put [ and ] around the expression listed above but it
did
not work. Is there some way to return let access know that the result of
the
expression above should be the data within a field named by the
expression?
Net-Period End 08/31/2005

 

[Jeff Boyce]

Carlo

Consider doing the following:

Given that you have a date value you can use, create a query that prompts
you for the month (???and year, a second prompt) (e.g., [Enter month number:
1-12]).

In your query, find all records with values in the entered month (e.g.,
Where Month(YourDate)=[Enter month number: 1-12]).

If necessary, calculate the sum of all such records.

This approach means you'll NEVER have to alter column names, add columns,
....

If you'll post the structure of the data you are querying, you'll probably
get more ideas about how to handle this.

Regards

Jeff Boyce
<MS Office/Access MVP>

Jeff,

Thanks for the quick response.

You are correct. I have a table that contains 36 fields, each with a
Net-Period End date (i.e., Net-Period End 08/31/05, Net-Period End
09/30/05,
etc.) . This table was created as a result of an import of data from
Peachtree accounting software. In each date field exists financial
results
data that I am using in financial reports. Rather than having the reports
refer to a field named by a specific date (that would have to be changed
in
each report every month), I am having the reports refer to a more generic
field name (i.e., Current Month). In an underlying query, I would
manually
change Current Month to the appropriate Net-Period End.. date. Rather
than
manually doing this every month in the query, I was hoping the query can
lookup the date value in another table and then provide me the
corresponding
financial data for the date.

I can lookup the date value (as mentioned in my initial e-mail), however,
I
do not want that value to be the resulting data in the query but rather
the
correpsing Net-Period End date.

For example, for August 2005 results, I would manually change the query
to:
Current Month: [Net-Period End 08/31/05]

This would provide me financial records for August. I would like to
accomplish this same task but via a dlookup expression as initially
suggested. When I use
Current Month: ("Net-Period End " & DLookUp("[CYP]","CutoffDataQuery")

it returns Net-Period End 08/31/05 for each record rather than the
financial
data. I was wondering if there is some way to tell access that the
dlookup
expression is the field name to be used to provide the data, not the
actual
default data to be returned.

Sorry for being long winded.... I hope this clarifies things...

Carlo

You may have another, more serious issue. If you have

a field named: Net-Period End 08/31/2005

you've embedded data as a field name. While this might be necessary when
using a spreadsheet, you'll find that Access handles this better if your
table doesn't have repeating fields (I'm only guessing, since you didn't
say, but might you have one field for each "Net-Period End"...?).

Regards

Jeff Boyce
<MS Office/Access MVP>

In a query, I have the following expression:
CYP: ("Net-Period End " & DLookUp("[CYP]","CutoffDataQuery"))

This will result in the field labeled CYP having the value:
Net-Period End 08/31/2005

Rather than returning the value listed above, I want the CYP field to
return
the data in a field named: Net-Period End 08/31/2005

I tried to simply put [ and ] around the expression listed above but it
did
not work. Is there some way to return let access know that the result
of
the
expression above should be the data within a field named by the
expression?
Net-Period End 08/31/2005

 

[Guest]

Is there not then a way to have the result of the Dlookup be treated as a
field name reference rather than a data value?

Carlo

Consider doing the following:

Given that you have a date value you can use, create a query that prompts
you for the month (???and year, a second prompt) (e.g., [Enter month number:
1-12]).

In your query, find all records with values in the entered month (e.g.,
Where Month(YourDate)=[Enter month number: 1-12]).

If necessary, calculate the sum of all such records.

This approach means you'll NEVER have to alter column names, add columns,
....

If you'll post the structure of the data you are querying, you'll probably
get more ideas about how to handle this.

Regards

Jeff Boyce
<MS Office/Access MVP>

Jeff,

Thanks for the quick response.

You are correct. I have a table that contains 36 fields, each with a
Net-Period End date (i.e., Net-Period End 08/31/05, Net-Period End
09/30/05,
etc.) . This table was created as a result of an import of data from
Peachtree accounting software. In each date field exists financial
results
data that I am using in financial reports. Rather than having the reports
refer to a field named by a specific date (that would have to be changed
in
each report every month), I am having the reports refer to a more generic
field name (i.e., Current Month). In an underlying query, I would
manually
change Current Month to the appropriate Net-Period End.. date. Rather
than
manually doing this every month in the query, I was hoping the query can
lookup the date value in another table and then provide me the
corresponding
financial data for the date.

I can lookup the date value (as mentioned in my initial e-mail), however,
I
do not want that value to be the resulting data in the query but rather
the
correpsing Net-Period End date.

For example, for August 2005 results, I would manually change the query
to:
Current Month: [Net-Period End 08/31/05]

This would provide me financial records for August. I would like to
accomplish this same task but via a dlookup expression as initially
suggested. When I use
Current Month: ("Net-Period End " & DLookUp("[CYP]","CutoffDataQuery")

it returns Net-Period End 08/31/05 for each record rather than the
financial
data. I was wondering if there is some way to tell access that the
dlookup
expression is the field name to be used to provide the data, not the
actual
default data to be returned.

Sorry for being long winded.... I hope this clarifies things...

Carlo

You may have another, more serious issue. If you have

a field named: Net-Period End 08/31/2005

you've embedded data as a field name. While this might be necessary when
using a spreadsheet, you'll find that Access handles this better if your
table doesn't have repeating fields (I'm only guessing, since you didn't
say, but might you have one field for each "Net-Period End"...?).

Regards

Jeff Boyce
<MS Office/Access MVP>


In a query, I have the following expression:
CYP: ("Net-Period End " & DLookUp("[CYP]","CutoffDataQuery"))

This will result in the field labeled CYP having the value:
Net-Period End 08/31/2005

Rather than returning the value listed above, I want the CYP field to
return
the data in a field named: Net-Period End 08/31/2005

I tried to simply put [ and ] around the expression listed above but it
did
not work. Is there some way to return let access know that the result
of
the
expression above should be the data within a field named by the
expression?
Net-Period End 08/31/2005

 

[Jeff Boyce]

Carlo

I'm not familiar with doing that, but perhaps? Have you reviewed the syntax
available through using Access HELP?

Regards

Jeff Boyce
<MS Office/Access MVP>

Is there not then a way to have the result of the Dlookup be treated as a
field name reference rather than a data value?

Carlo

Consider doing the following:

Given that you have a date value you can use, create a query that prompts
you for the month (???and year, a second prompt) (e.g., [Enter month
number:
1-12]).

In your query, find all records with values in the entered month (e.g.,
Where Month(YourDate)=[Enter month number: 1-12]).

If necessary, calculate the sum of all such records.

This approach means you'll NEVER have to alter column names, add columns,
....

If you'll post the structure of the data you are querying, you'll
probably
get more ideas about how to handle this.

Regards

Jeff Boyce
<MS Office/Access MVP>

Jeff,

Thanks for the quick response.

You are correct. I have a table that contains 36 fields, each with a
Net-Period End date (i.e., Net-Period End 08/31/05, Net-Period End
09/30/05,
etc.) . This table was created as a result of an import of data from
Peachtree accounting software. In each date field exists financial
results
data that I am using in financial reports. Rather than having the
reports
refer to a field named by a specific date (that would have to be
changed
in
each report every month), I am having the reports refer to a more
generic
field name (i.e., Current Month). In an underlying query, I would
manually
change Current Month to the appropriate Net-Period End.. date. Rather
than
manually doing this every month in the query, I was hoping the query
can
lookup the date value in another table and then provide me the
corresponding
financial data for the date.

I can lookup the date value (as mentioned in my initial e-mail),
however,
I
do not want that value to be the resulting data in the query but rather
the
correpsing Net-Period End date.

For example, for August 2005 results, I would manually change the query
to:
Current Month: [Net-Period End 08/31/05]

This would provide me financial records for August. I would like to
accomplish this same task but via a dlookup expression as initially
suggested. When I use
Current Month: ("Net-Period End " & DLookUp("[CYP]","CutoffDataQuery")

it returns Net-Period End 08/31/05 for each record rather than the
financial
data. I was wondering if there is some way to tell access that the
dlookup
expression is the field name to be used to provide the data, not the
actual
default data to be returned.

Sorry for being long winded.... I hope this clarifies things...

:

Carlo

You may have another, more serious issue. If you have

a field named: Net-Period End 08/31/2005

you've embedded data as a field name. While this might be necessary
when
using a spreadsheet, you'll find that Access handles this better if
your
table doesn't have repeating fields (I'm only guessing, since you
didn't
say, but might you have one field for each "Net-Period End"...?).

Regards

Jeff Boyce
<MS Office/Access MVP>


In a query, I have the following expression:
CYP: ("Net-Period End " & DLookUp("[CYP]","CutoffDataQuery"))

This will result in the field labeled CYP having the value:
Net-Period End 08/31/2005

Rather than returning the value listed above, I want the CYP field
to
return
the data in a field named: Net-Period End 08/31/2005

I tried to simply put [ and ] around the expression listed above but
it
did
not work. Is there some way to return let access know that the
result
of
the
expression above should be the data within a field named by the
expression?
Net-Period End 08/31/2005