Nz in a DLookup control ?

[CW]

I have a form control for which this is the source:
=DLookUp("[SumOfTravelCost]","[qrySummarySpendFY08]")
If the underlying query has returned nothing for that particular cost, the
control shows a blank.
I would like it to show a zero.
I have tried setting the default value in the control to 0, but that didn't
work.
I have fiddled around with Nz in combination with the DLookUp expression but
again no good.
How can I achieve this, please?
Many thanks
CW

 

[Jeff Boyce]

Try embedding the DLookup() function within the Nz() function, something
like:

=Nz(DLookup(),0)

By the way, your expression leaves out the syntax element dealing with
"criterion". When you do that, the following excerpt from the VBA HELP
system applies ... is this what you intend?

The DLookup function returns a single field value based on the information
specified in criteria. Although criteria is an optional argument, if you
don't supply a value for criteria, the DLookup function returns a random
value in the domain.



Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[CW]

Perfect, many thanks Jeff. I had been omitting the ,0 at the end!
Thanks for your point re the missing criterion which I understand, but it's
OK in this instance as I am merely showing summary figures rather than
anything record-specific.
I appreciate your quick response - I can get rid of those nasty empty
controls on my form now!
Many thanks
CW

Try embedding the DLookup() function within the Nz() function, something
like:

=Nz(DLookup(),0)

By the way, your expression leaves out the syntax element dealing with
"criterion". When you do that, the following excerpt from the VBA HELP
system applies ... is this what you intend?

The DLookup function returns a single field value based on the information
specified in criteria. Although criteria is an optional argument, if you
don't supply a value for criteria, the DLookup function returns a random
value in the domain.



Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form control for which this is the source:
=DLookUp("[SumOfTravelCost]","[qrySummarySpendFY08]")
If the underlying query has returned nothing for that particular cost, the
control shows a blank.
I would like it to show a zero.
I have tried setting the default value in the control to 0, but that
didn't
work.
I have fiddled around with Nz in combination with the DLookUp expression
but
again no good.
How can I achieve this, please?
Many thanks
CW

 

[Jeff Boyce]

?"summary figures? -- do you need to be using the DSum() function instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Perfect, many thanks Jeff. I had been omitting the ,0 at the end!
Thanks for your point re the missing criterion which I understand, but
it's
OK in this instance as I am merely showing summary figures rather than
anything record-specific.
I appreciate your quick response - I can get rid of those nasty empty
controls on my form now!
Many thanks
CW

Try embedding the DLookup() function within the Nz() function, something
like:

=Nz(DLookup(),0)

By the way, your expression leaves out the syntax element dealing with
"criterion". When you do that, the following excerpt from the VBA HELP
system applies ... is this what you intend?

The DLookup function returns a single field value based on the
information
specified in criteria. Although criteria is an optional argument, if you
don't supply a value for criteria, the DLookup function returns a random
value in the domain.



Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form control for which this is the source:
=DLookUp("[SumOfTravelCost]","[qrySummarySpendFY08]")
If the underlying query has returned nothing for that particular cost,
the
control shows a blank.
I would like it to show a zero.
I have tried setting the default value in the control to 0, but that
didn't
work.
I have fiddled around with Nz in combination with the DLookUp
expression
but
again no good.
How can I achieve this, please?
Many thanks
CW

 

[CW]

Well it would certainly save me hours writing tens of individual queries from
which to pull using DLookUp, but I couldn't get DSum to work when I tried
earlier.
The source data is in a few queries each of which contain these fields:
[Transport] [NominalGroup] [Amount]
Within those columns there are 3 different types of Transport (Air, Road,
Sea) and 3 different NominalGroups (Rev, Ins, Stg).
I have to analyse all the revenue summarising it by permutations of each
Transport Type and within that, each NominalGroup.
I have been laboriously writing individual queries to group and sum each
combination and then using DLookUp to pull each result into a control on a
summary form. There are over 150 of them and I am not halfway yet!!
If I could skip that step and go straight to each calculation using DSum
that would be great but I could not see how to do it...you would be my hero!!!
Many thanks
CW

?"summary figures? -- do you need to be using the DSum() function instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Perfect, many thanks Jeff. I had been omitting the ,0 at the end!
Thanks for your point re the missing criterion which I understand, but
it's
OK in this instance as I am merely showing summary figures rather than
anything record-specific.
I appreciate your quick response - I can get rid of those nasty empty
controls on my form now!
Many thanks
CW

Try embedding the DLookup() function within the Nz() function, something
like:

=Nz(DLookup(),0)

By the way, your expression leaves out the syntax element dealing with
"criterion". When you do that, the following excerpt from the VBA HELP
system applies ... is this what you intend?

The DLookup function returns a single field value based on the
information
specified in criteria. Although criteria is an optional argument, if you
don't supply a value for criteria, the DLookup function returns a random
value in the domain.



Regards

Jeff Boyce
Microsoft Office/Access MVP



I have a form control for which this is the source:
=DLookUp("[SumOfTravelCost]","[qrySummarySpendFY08]")
If the underlying query has returned nothing for that particular cost,
the
control shows a blank.
I would like it to show a zero.
I have tried setting the default value in the control to 0, but that
didn't
work.
I have fiddled around with Nz in combination with the DLookUp
expression
but
again no good.
How can I achieve this, please?
Many thanks
CW

 

[Jeff Boyce]

"How" depends on "what"...

I can't tell from your description how your tables/fields are structured.
There's a chance, if you have experience using a spreadsheet, that the
tables got set up like spreadsheets. Access is a relational database, and
you won't get easy use of the features/functions Access offers if you feed
it 'sheet data.

For example, the DSum() function expects the values to be summed to be in a
single field (tall, not wide).

Please post a description of the table/field structure. Here's a much
simplified example:

tblPerson
PersonID
LastName
FirstName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate


Regards

Jeff Boyce
Microsoft Office/Access MVP

Well it would certainly save me hours writing tens of individual queries
from
which to pull using DLookUp, but I couldn't get DSum to work when I tried
earlier.
The source data is in a few queries each of which contain these fields:
[Transport] [NominalGroup] [Amount]
Within those columns there are 3 different types of Transport (Air, Road,
Sea) and 3 different NominalGroups (Rev, Ins, Stg).
I have to analyse all the revenue summarising it by permutations of each
Transport Type and within that, each NominalGroup.
I have been laboriously writing individual queries to group and sum each
combination and then using DLookUp to pull each result into a control on a
summary form. There are over 150 of them and I am not halfway yet!!
If I could skip that step and go straight to each calculation using DSum
that would be great but I could not see how to do it...you would be my
hero!!!
Many thanks
CW

?"summary figures? -- do you need to be using the DSum() function
instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Perfect, many thanks Jeff. I had been omitting the ,0 at the end!
Thanks for your point re the missing criterion which I understand, but
it's
OK in this instance as I am merely showing summary figures rather than
anything record-specific.
I appreciate your quick response - I can get rid of those nasty empty
controls on my form now!
Many thanks
CW

:

Try embedding the DLookup() function within the Nz() function,
something
like:

=Nz(DLookup(),0)

By the way, your expression leaves out the syntax element dealing with
"criterion". When you do that, the following excerpt from the VBA
HELP
system applies ... is this what you intend?

The DLookup function returns a single field value based on the
information
specified in criteria. Although criteria is an optional argument, if
you
don't supply a value for criteria, the DLookup function returns a
random
value in the domain.



Regards

Jeff Boyce
Microsoft Office/Access MVP



I have a form control for which this is the source:
=DLookUp("[SumOfTravelCost]","[qrySummarySpendFY08]")
If the underlying query has returned nothing for that particular
cost,
the
control shows a blank.
I would like it to show a zero.
I have tried setting the default value in the control to 0, but that
didn't
work.
I have fiddled around with Nz in combination with the DLookUp
expression
but
again no good.
How can I achieve this, please?
Many thanks
CW

 

[CW]

That's exactly the problem, Jeff - the original source was a spreadsheet of
data dumped from our corporate system and yes, it is certainly wide.
As a result, unfortunately I am not working with a proper related table
structure.
I guess that seals the fate of DSum and I'll just have to plug away creating
all these mini-queries.
Which leads me to a final question - is there a limit on the number of
queries in Access 2003?
Thanks again
CW

"How" depends on "what"...

I can't tell from your description how your tables/fields are structured.
There's a chance, if you have experience using a spreadsheet, that the
tables got set up like spreadsheets. Access is a relational database, and
you won't get easy use of the features/functions Access offers if you feed
it 'sheet data.

For example, the DSum() function expects the values to be summed to be in a
single field (tall, not wide).

Please post a description of the table/field structure. Here's a much
simplified example:

tblPerson
PersonID
LastName
FirstName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate


Regards

Jeff Boyce
Microsoft Office/Access MVP

Well it would certainly save me hours writing tens of individual queries
from
which to pull using DLookUp, but I couldn't get DSum to work when I tried
earlier.
The source data is in a few queries each of which contain these fields:
[Transport] [NominalGroup] [Amount]
Within those columns there are 3 different types of Transport (Air, Road,
Sea) and 3 different NominalGroups (Rev, Ins, Stg).
I have to analyse all the revenue summarising it by permutations of each
Transport Type and within that, each NominalGroup.
I have been laboriously writing individual queries to group and sum each
combination and then using DLookUp to pull each result into a control on a
summary form. There are over 150 of them and I am not halfway yet!!
If I could skip that step and go straight to each calculation using DSum
that would be great but I could not see how to do it...you would be my
hero!!!
Many thanks
CW

?"summary figures? -- do you need to be using the DSum() function
instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Perfect, many thanks Jeff. I had been omitting the ,0 at the end!
Thanks for your point re the missing criterion which I understand, but
it's
OK in this instance as I am merely showing summary figures rather than
anything record-specific.
I appreciate your quick response - I can get rid of those nasty empty
controls on my form now!
Many thanks
CW

:

Try embedding the DLookup() function within the Nz() function,
something
like:

=Nz(DLookup(),0)

By the way, your expression leaves out the syntax element dealing with
"criterion". When you do that, the following excerpt from the VBA
HELP
system applies ... is this what you intend?

The DLookup function returns a single field value based on the
information
specified in criteria. Although criteria is an optional argument, if
you
don't supply a value for criteria, the DLookup function returns a
random
value in the domain.



Regards

Jeff Boyce
Microsoft Office/Access MVP



I have a form control for which this is the source:
=DLookUp("[SumOfTravelCost]","[qrySummarySpendFY08]")
If the underlying query has returned nothing for that particular
cost,
the
control shows a blank.
I would like it to show a zero.
I have tried setting the default value in the control to 0, but that
didn't
work.
I have fiddled around with Nz in combination with the DLookUp
expression
but
again no good.
How can I achieve this, please?
Many thanks
CW

 

[John W. Vinson]

Well it would certainly save me hours writing tens of individual queries from
which to pull using DLookUp, but I couldn't get DSum to work when I tried
earlier.
The source data is in a few queries each of which contain these fields:
[Transport] [NominalGroup] [Amount]
Within those columns there are 3 different types of Transport (Air, Road,
Sea) and 3 different NominalGroups (Rev, Ins, Stg).
I have to analyse all the revenue summarising it by permutations of each
Transport Type and within that, each NominalGroup.
I have been laboriously writing individual queries to group and sum each
combination and then using DLookUp to pull each result into a control on a
summary form. There are over 150 of them and I am not halfway yet!!
If I could skip that step and go straight to each calculation using DSum
that would be great but I could not see how to do it...you would be my hero!!!
Many thanks
CW

Yipe! You certainly DON'T need a separate query, much less a DLookup on a
separate query, for each permutation; can't you do a Totals query grouping by
Transport and NominalGroup to get (in this example) your nine subtotals? This
Query could be used as the Recordsource for a report, and there you could use
the Sorting and Grouping to get higher level subtotals.

 

[Jeff Boyce]

Rather than stress yourself (and Access), what about the idea of normalizing
the data? Just because it started in a spreadsheet doesn't mean you have to
store it that way in Access.

(and if you normalized the data structure, Access' relationally-oriented
'totals' queries could do your work for you!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

That's exactly the problem, Jeff - the original source was a spreadsheet
of
data dumped from our corporate system and yes, it is certainly wide.
As a result, unfortunately I am not working with a proper related table
structure.
I guess that seals the fate of DSum and I'll just have to plug away
creating
all these mini-queries.
Which leads me to a final question - is there a limit on the number of
queries in Access 2003?
Thanks again
CW

"How" depends on "what"...

I can't tell from your description how your tables/fields are structured.
There's a chance, if you have experience using a spreadsheet, that the
tables got set up like spreadsheets. Access is a relational database,
and
you won't get easy use of the features/functions Access offers if you
feed
it 'sheet data.

For example, the DSum() function expects the values to be summed to be in
a
single field (tall, not wide).

Please post a description of the table/field structure. Here's a much
simplified example:

tblPerson
PersonID
LastName
FirstName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate


Regards

Jeff Boyce
Microsoft Office/Access MVP

Well it would certainly save me hours writing tens of individual
queries
from
which to pull using DLookUp, but I couldn't get DSum to work when I
tried
earlier.
The source data is in a few queries each of which contain these fields:
[Transport] [NominalGroup] [Amount]
Within those columns there are 3 different types of Transport (Air,
Road,
Sea) and 3 different NominalGroups (Rev, Ins, Stg).
I have to analyse all the revenue summarising it by permutations of
each
Transport Type and within that, each NominalGroup.
I have been laboriously writing individual queries to group and sum
each
combination and then using DLookUp to pull each result into a control
on a
summary form. There are over 150 of them and I am not halfway yet!!
If I could skip that step and go straight to each calculation using
DSum
that would be great but I could not see how to do it...you would be my
hero!!!
Many thanks
CW

:

?"summary figures? -- do you need to be using the DSum() function
instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Perfect, many thanks Jeff. I had been omitting the ,0 at the end!
Thanks for your point re the missing criterion which I understand,
but
it's
OK in this instance as I am merely showing summary figures rather
than
anything record-specific.
I appreciate your quick response - I can get rid of those nasty
empty
controls on my form now!
Many thanks
CW

:

Try embedding the DLookup() function within the Nz() function,
something
like:

=Nz(DLookup(),0)

By the way, your expression leaves out the syntax element dealing
with
"criterion". When you do that, the following excerpt from the VBA
HELP
system applies ... is this what you intend?

The DLookup function returns a single field value based on the
information
specified in criteria. Although criteria is an optional argument,
if
you
don't supply a value for criteria, the DLookup function returns a
random
value in the domain.



Regards

Jeff Boyce
Microsoft Office/Access MVP



I have a form control for which this is the source:
=DLookUp("[SumOfTravelCost]","[qrySummarySpendFY08]")
If the underlying query has returned nothing for that particular
cost,
the
control shows a blank.
I would like it to show a zero.
I have tried setting the default value in the control to 0, but
that
didn't
work.
I have fiddled around with Nz in combination with the DLookUp
expression
but
again no good.
How can I achieve this, please?
Many thanks
CW

 

[CW]

Yes I was wondering if it would make sense to do that. It would be a bit of
work but could pay off in the long run, as we are expected to produce this
analysis each month and no doubt there will be some changes in the
requirements which would play havoc with all my queries.
Is there a way to tell Access what fields in the spreadsheet are to go into
which table, or shall I have to do this manually?
Many thanks
CW

Rather than stress yourself (and Access), what about the idea of normalizing
the data? Just because it started in a spreadsheet doesn't mean you have to
store it that way in Access.

(and if you normalized the data structure, Access' relationally-oriented
'totals' queries could do your work for you!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

That's exactly the problem, Jeff - the original source was a spreadsheet
of
data dumped from our corporate system and yes, it is certainly wide.
As a result, unfortunately I am not working with a proper related table
structure.
I guess that seals the fate of DSum and I'll just have to plug away
creating
all these mini-queries.
Which leads me to a final question - is there a limit on the number of
queries in Access 2003?
Thanks again
CW

"How" depends on "what"...

I can't tell from your description how your tables/fields are structured.
There's a chance, if you have experience using a spreadsheet, that the
tables got set up like spreadsheets. Access is a relational database,
and
you won't get easy use of the features/functions Access offers if you
feed
it 'sheet data.

For example, the DSum() function expects the values to be summed to be in
a
single field (tall, not wide).

Please post a description of the table/field structure. Here's a much
simplified example:

tblPerson
PersonID
LastName
FirstName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate


Regards

Jeff Boyce
Microsoft Office/Access MVP

Well it would certainly save me hours writing tens of individual
queries
from
which to pull using DLookUp, but I couldn't get DSum to work when I
tried
earlier.
The source data is in a few queries each of which contain these fields:
[Transport] [NominalGroup] [Amount]
Within those columns there are 3 different types of Transport (Air,
Road,
Sea) and 3 different NominalGroups (Rev, Ins, Stg).
I have to analyse all the revenue summarising it by permutations of
each
Transport Type and within that, each NominalGroup.
I have been laboriously writing individual queries to group and sum
each
combination and then using DLookUp to pull each result into a control
on a
summary form. There are over 150 of them and I am not halfway yet!!
If I could skip that step and go straight to each calculation using
DSum
that would be great but I could not see how to do it...you would be my
hero!!!
Many thanks
CW

:

?"summary figures? -- do you need to be using the DSum() function
instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Perfect, many thanks Jeff. I had been omitting the ,0 at the end!
Thanks for your point re the missing criterion which I understand,
but
it's
OK in this instance as I am merely showing summary figures rather
than
anything record-specific.
I appreciate your quick response - I can get rid of those nasty
empty
controls on my form now!
Many thanks
CW

:

Try embedding the DLookup() function within the Nz() function,
something
like:

=Nz(DLookup(),0)

By the way, your expression leaves out the syntax element dealing
with
"criterion". When you do that, the following excerpt from the VBA
HELP
system applies ... is this what you intend?

The DLookup function returns a single field value based on the
information
specified in criteria. Although criteria is an optional argument,
if
you
don't supply a value for criteria, the DLookup function returns a
random
value in the domain.



Regards

Jeff Boyce
Microsoft Office/Access MVP



I have a form control for which this is the source:
=DLookUp("[SumOfTravelCost]","[qrySummarySpendFY08]")
If the underlying query has returned nothing for that particular
cost,
the
control shows a blank.
I would like it to show a zero.
I have tried setting the default value in the control to 0, but
that
didn't
work.
I have fiddled around with Nz in combination with the DLookUp
expression
but
again no good.
How can I achieve this, please?
Many thanks
CW

 

[CW]

Yipe is just the start of it. You should hear the language here. Or perhaps
better not.
I like your suggestion. I did use reports in a previous phase of this
analysis but they were not liked by the people that want them (appearance and
layout issues, so I suppose that was my fault rather than Access).
I'll try it again. I'll try anything that will save these scores of queries!
Many thanks
CW

Well it would certainly save me hours writing tens of individual queries from
which to pull using DLookUp, but I couldn't get DSum to work when I tried
earlier.
The source data is in a few queries each of which contain these fields:
[Transport] [NominalGroup] [Amount]
Within those columns there are 3 different types of Transport (Air, Road,
Sea) and 3 different NominalGroups (Rev, Ins, Stg).
I have to analyse all the revenue summarising it by permutations of each
Transport Type and within that, each NominalGroup.
I have been laboriously writing individual queries to group and sum each
combination and then using DLookUp to pull each result into a control on a
summary form. There are over 150 of them and I am not halfway yet!!
If I could skip that step and go straight to each calculation using DSum
that would be great but I could not see how to do it...you would be my hero!!!
Many thanks
CW

Yipe! You certainly DON'T need a separate query, much less a DLookup on a
separate query, for each permutation; can't you do a Totals query grouping by
Transport and NominalGroup to get (in this example) your nine subtotals? This
Query could be used as the Recordsource for a report, and there you could use
the Sorting and Grouping to get higher level subtotals.

 

[Jeff Boyce]

A common approach (your situation is not unique) is to import the data
'raw', then use a series of queries to "parse" the data into more permanent
(and well-normalized) tables. It is those well-normalized tables that you
would then use to create your 'report' queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes I was wondering if it would make sense to do that. It would be a bit
of
work but could pay off in the long run, as we are expected to produce this
analysis each month and no doubt there will be some changes in the
requirements which would play havoc with all my queries.
Is there a way to tell Access what fields in the spreadsheet are to go
into
which table, or shall I have to do this manually?
Many thanks
CW

Rather than stress yourself (and Access), what about the idea of
normalizing
the data? Just because it started in a spreadsheet doesn't mean you have
to
store it that way in Access.

(and if you normalized the data structure, Access' relationally-oriented
'totals' queries could do your work for you!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

That's exactly the problem, Jeff - the original source was a
spreadsheet
of
data dumped from our corporate system and yes, it is certainly wide.
As a result, unfortunately I am not working with a proper related table
structure.
I guess that seals the fate of DSum and I'll just have to plug away
creating
all these mini-queries.
Which leads me to a final question - is there a limit on the number of
queries in Access 2003?
Thanks again
CW

:

"How" depends on "what"...

I can't tell from your description how your tables/fields are
structured.
There's a chance, if you have experience using a spreadsheet, that the
tables got set up like spreadsheets. Access is a relational database,
and
you won't get easy use of the features/functions Access offers if you
feed
it 'sheet data.

For example, the DSum() function expects the values to be summed to be
in
a
single field (tall, not wide).

Please post a description of the table/field structure. Here's a much
simplified example:

tblPerson
PersonID
LastName
FirstName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate


Regards

Jeff Boyce
Microsoft Office/Access MVP

Well it would certainly save me hours writing tens of individual
queries
from
which to pull using DLookUp, but I couldn't get DSum to work when I
tried
earlier.
The source data is in a few queries each of which contain these
fields:
[Transport] [NominalGroup] [Amount]
Within those columns there are 3 different types of Transport (Air,
Road,
Sea) and 3 different NominalGroups (Rev, Ins, Stg).
I have to analyse all the revenue summarising it by permutations of
each
Transport Type and within that, each NominalGroup.
I have been laboriously writing individual queries to group and sum
each
combination and then using DLookUp to pull each result into a
control
on a
summary form. There are over 150 of them and I am not halfway yet!!
If I could skip that step and go straight to each calculation using
DSum
that would be great but I could not see how to do it...you would be
my
hero!!!
Many thanks
CW

:

?"summary figures? -- do you need to be using the DSum() function
instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Perfect, many thanks Jeff. I had been omitting the ,0 at the end!
Thanks for your point re the missing criterion which I
understand,
but
it's
OK in this instance as I am merely showing summary figures rather
than
anything record-specific.
I appreciate your quick response - I can get rid of those nasty
empty
controls on my form now!
Many thanks
CW

:

Try embedding the DLookup() function within the Nz() function,
something
like:

=Nz(DLookup(),0)

By the way, your expression leaves out the syntax element
dealing
with
"criterion". When you do that, the following excerpt from the
VBA
HELP
system applies ... is this what you intend?

The DLookup function returns a single field value based on the
information
specified in criteria. Although criteria is an optional
argument,
if
you
don't supply a value for criteria, the DLookup function returns
a
random
value in the domain.



Regards

Jeff Boyce
Microsoft Office/Access MVP



I have a form control for which this is the source:
=DLookUp("[SumOfTravelCost]","[qrySummarySpendFY08]")
If the underlying query has returned nothing for that
particular
cost,
the
control shows a blank.
I would like it to show a zero.
I have tried setting the default value in the control to 0,
but
that
didn't
work.
I have fiddled around with Nz in combination with the DLookUp
expression
but
again no good.
How can I achieve this, please?
Many thanks
CW

 

[CW]

OK, got it. I'll close the thread here. Many thanks for all your advice, Jeff.
CW

A common approach (your situation is not unique) is to import the data
'raw', then use a series of queries to "parse" the data into more permanent
(and well-normalized) tables. It is those well-normalized tables that you
would then use to create your 'report' queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Yes I was wondering if it would make sense to do that. It would be a bit
of
work but could pay off in the long run, as we are expected to produce this
analysis each month and no doubt there will be some changes in the
requirements which would play havoc with all my queries.
Is there a way to tell Access what fields in the spreadsheet are to go
into
which table, or shall I have to do this manually?
Many thanks
CW

Rather than stress yourself (and Access), what about the idea of
normalizing
the data? Just because it started in a spreadsheet doesn't mean you have
to
store it that way in Access.

(and if you normalized the data structure, Access' relationally-oriented
'totals' queries could do your work for you!)

Regards

Jeff Boyce
Microsoft Office/Access MVP


That's exactly the problem, Jeff - the original source was a
spreadsheet
of
data dumped from our corporate system and yes, it is certainly wide.
As a result, unfortunately I am not working with a proper related table
structure.
I guess that seals the fate of DSum and I'll just have to plug away
creating
all these mini-queries.
Which leads me to a final question - is there a limit on the number of
queries in Access 2003?
Thanks again
CW

:

"How" depends on "what"...

I can't tell from your description how your tables/fields are
structured.
There's a chance, if you have experience using a spreadsheet, that the
tables got set up like spreadsheets. Access is a relational database,
and
you won't get easy use of the features/functions Access offers if you
feed
it 'sheet data.

For example, the DSum() function expects the values to be summed to be
in
a
single field (tall, not wide).

Please post a description of the table/field structure. Here's a much
simplified example:

tblPerson
PersonID
LastName
FirstName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate


Regards

Jeff Boyce
Microsoft Office/Access MVP

Well it would certainly save me hours writing tens of individual
queries
from
which to pull using DLookUp, but I couldn't get DSum to work when I
tried
earlier.
The source data is in a few queries each of which contain these
fields:
[Transport] [NominalGroup] [Amount]
Within those columns there are 3 different types of Transport (Air,
Road,
Sea) and 3 different NominalGroups (Rev, Ins, Stg).
I have to analyse all the revenue summarising it by permutations of
each
Transport Type and within that, each NominalGroup.
I have been laboriously writing individual queries to group and sum
each
combination and then using DLookUp to pull each result into a
control
on a
summary form. There are over 150 of them and I am not halfway yet!!
If I could skip that step and go straight to each calculation using
DSum
that would be great but I could not see how to do it...you would be
my
hero!!!
Many thanks
CW

:

?"summary figures? -- do you need to be using the DSum() function
instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Perfect, many thanks Jeff. I had been omitting the ,0 at the end!
Thanks for your point re the missing criterion which I
understand,
but
it's
OK in this instance as I am merely showing summary figures rather
than
anything record-specific.
I appreciate your quick response - I can get rid of those nasty
empty
controls on my form now!
Many thanks
CW

:

Try embedding the DLookup() function within the Nz() function,
something
like:

=Nz(DLookup(),0)

By the way, your expression leaves out the syntax element
dealing
with
"criterion". When you do that, the following excerpt from the
VBA
HELP
system applies ... is this what you intend?

The DLookup function returns a single field value based on the
information
specified in criteria. Although criteria is an optional
argument,
if
you
don't supply a value for criteria, the DLookup function returns
a
random
value in the domain.



Regards

Jeff Boyce
Microsoft Office/Access MVP



I have a form control for which this is the source:
=DLookUp("[SumOfTravelCost]","[qrySummarySpendFY08]")
If the underlying query has returned nothing for that
particular
cost,
the
control shows a blank.
I would like it to show a zero.
I have tried setting the default value in the control to 0,
but
that
didn't
work.
I have fiddled around with Nz in combination with the DLookUp
expression
but
again no good.
How can I achieve this, please?
Many thanks
CW