I Know it's Xmas BUT....... DLookup problem

[Tony Williams]

I know it's sad posting on Xmas day but I'm on a cruise ship in the middle of
the Indian Ocean and there's not much else to do!!!

I have a report based on a qry "qrySalesV2", in the header is a Date/time
control "txtqtr2" based on a date/ time field txtmonthlabel and I'm
calculating the same date 1 year previous in a control txtqtr2 using this
code
=DateAdd("m",-12,[txtqtr1])
In the detail section is a txt control "Sum of txtDomfactot" and I'm
attempting to find the the same value of this control for "txtqtr2" using
=DLookup("[Sum of txtDomfactot]","qrySalesV2","[txtmonthlabel]=[txtqtr2]")
I've also tried "[txtmonthlabel]=#" & [txtqtr2] & "#"
If I run my report with either then the report runs OK and all the records
for all the table are found.
However if I try to run the report by entering a prompt in the query for the
field "txtmonthlabel" and asking the user to input a specific date to find
the data for one date only, I get the "txtDomfactot" and "txtqtr2" values but
not the Dlookup values, I get #Error instead. All the date fields and
controls are formatted mmmm yyyy
Why should asking for a prompt in the query criteria stop the report from
giving me the calculated controls? Running only the query with the prompt
also works.
Anyone who can spare the time to help over Xmas would be appreciated!
Thanks and Merry Xmas
Tony Williams

 

[Ken Snell [MVP]]

Not sure that I'm following exactly what you're trying to do here. I know
I'm getting confused over which names go with the report and which go with
the report's RecordSource query.

Perhaps you can post the qrySalesV2 SQL statement and let us start from
there.

 

[Tony Williams]

Thanks Ken as i mentoned on a ship and difficult to copy from my laptop to
the ship's internet connection screens But txtdomfactor and txtmonthlabel are
included in the query as fields from my table and are also controls on my
report. All the other controls I mentioned are calculated controls on the
report. What I'm trying to do is to take the value of txtdomfactot at a date,
which is txtmonthlabel and chow what the value of txtdomfactot is at a poitn
in time 12 months previous to the date txtmonthlabel. Does that help, sorry
but as I said can't copy to the ships computer for security reasons.
Thanks
Tony Williams

Not sure that I'm following exactly what you're trying to do here. I know
I'm getting confused over which names go with the report and which go with
the report's RecordSource query.

Perhaps you can post the qrySalesV2 SQL statement and let us start from
there.

--

Ken Snell
<MS ACCESS MVP>

I know it's sad posting on Xmas day but I'm on a cruise ship in the middle
of
the Indian Ocean and there's not much else to do!!!

I have a report based on a qry "qrySalesV2", in the header is a Date/time
control "txtqtr2" based on a date/ time field txtmonthlabel and I'm
calculating the same date 1 year previous in a control txtqtr2 using this
code
=DateAdd("m",-12,[txtqtr1])
In the detail section is a txt control "Sum of txtDomfactot" and I'm
attempting to find the the same value of this control for "txtqtr2" using
=DLookup("[Sum of txtDomfactot]","qrySalesV2","[txtmonthlabel]=[txtqtr2]")
I've also tried "[txtmonthlabel]=#" & [txtqtr2] & "#"
If I run my report with either then the report runs OK and all the records
for all the table are found.
However if I try to run the report by entering a prompt in the query for
the
field "txtmonthlabel" and asking the user to input a specific date to find
the data for one date only, I get the "txtDomfactot" and "txtqtr2" values
but
not the Dlookup values, I get #Error instead. All the date fields and
controls are formatted mmmm yyyy
Why should asking for a prompt in the query criteria stop the report from
giving me the calculated controls? Running only the query with the prompt
also works.
Anyone who can spare the time to help over Xmas would be appreciated!
Thanks and Merry Xmas
Tony Williams

 

[Tony Williams]

Ken,
Managed to get the SQL here it is
SELECT DISTINCTROW tblmaintabs.txtmonthlabel, Sum(tblmaintabs.txtdomfactot)
AS [Sum of txtDomfactot]
FROM tblmaintabs
GROUP BY tblmaintabs.txtmonthlabel

This runs my report OK If I put [Enter Month] in criteria for txtmonthlabel
in query none of the calculated controls in the report I listed in my first
post work I just get error

Any help?
Thanks
Tony Williams

Not sure that I'm following exactly what you're trying to do here. I know
I'm getting confused over which names go with the report and which go with
the report's RecordSource query.

Perhaps you can post the qrySalesV2 SQL statement and let us start from
there.

--

Ken Snell
<MS ACCESS MVP>

I know it's sad posting on Xmas day but I'm on a cruise ship in the middle
of
the Indian Ocean and there's not much else to do!!!

I have a report based on a qry "qrySalesV2", in the header is a Date/time
control "txtqtr2" based on a date/ time field txtmonthlabel and I'm
calculating the same date 1 year previous in a control txtqtr2 using this
code
=DateAdd("m",-12,[txtqtr1])
In the detail section is a txt control "Sum of txtDomfactot" and I'm
attempting to find the the same value of this control for "txtqtr2" using
=DLookup("[Sum of txtDomfactot]","qrySalesV2","[txtmonthlabel]=[txtqtr2]")
I've also tried "[txtmonthlabel]=#" & [txtqtr2] & "#"
If I run my report with either then the report runs OK and all the records
for all the table are found.
However if I try to run the report by entering a prompt in the query for
the
field "txtmonthlabel" and asking the user to input a specific date to find
the data for one date only, I get the "txtDomfactot" and "txtqtr2" values
but
not the Dlookup values, I get #Error instead. All the date fields and
controls are formatted mmmm yyyy
Why should asking for a prompt in the query criteria stop the report from
giving me the calculated controls? Running only the query with the prompt
also works.
Anyone who can spare the time to help over Xmas would be appreciated!
Thanks and Merry Xmas
Tony Williams

 

[Ken Snell [MVP]]

In the table's design view for tblmaintabs, what is the data type for
txtmonthlabel? What type of data does it contain?

I think what's happening is that you're not matching the data from your form
with the correct format needed by the query for this field.

If you can post that info, then I think we can help.

--

Ken Snell
<MS ACCESS MVP>

Ken,
Managed to get the SQL here it is
SELECT DISTINCTROW tblmaintabs.txtmonthlabel,
Sum(tblmaintabs.txtdomfactot)
AS [Sum of txtDomfactot]
FROM tblmaintabs
GROUP BY tblmaintabs.txtmonthlabel

This runs my report OK If I put [Enter Month] in criteria for
txtmonthlabel
in query none of the calculated controls in the report I listed in my
first
post work I just get error

Any help?
Thanks
Tony Williams

Not sure that I'm following exactly what you're trying to do here. I know
I'm getting confused over which names go with the report and which go
with
the report's RecordSource query.

Perhaps you can post the qrySalesV2 SQL statement and let us start from
there.

--

Ken Snell
<MS ACCESS MVP>

I know it's sad posting on Xmas day but I'm on a cruise ship in the
middle
of
the Indian Ocean and there's not much else to do!!!

I have a report based on a qry "qrySalesV2", in the header is a
Date/time
control "txtqtr2" based on a date/ time field txtmonthlabel and I'm
calculating the same date 1 year previous in a control txtqtr2 using
this
code
=DateAdd("m",-12,[txtqtr1])
In the detail section is a txt control "Sum of txtDomfactot" and I'm
attempting to find the the same value of this control for "txtqtr2"
using
=DLookup("[Sum of
txtDomfactot]","qrySalesV2","[txtmonthlabel]=[txtqtr2]")
I've also tried "[txtmonthlabel]=#" & [txtqtr2] & "#"
If I run my report with either then the report runs OK and all the
records
for all the table are found.
However if I try to run the report by entering a prompt in the query
for
the
field "txtmonthlabel" and asking the user to input a specific date to
find
the data for one date only, I get the "txtDomfactot" and "txtqtr2"
values
but
not the Dlookup values, I get #Error instead. All the date fields and
controls are formatted mmmm yyyy
Why should asking for a prompt in the query criteria stop the report
from
giving me the calculated controls? Running only the query with the
prompt
also works.
Anyone who can spare the time to help over Xmas would be appreciated!
Thanks and Merry Xmas
Tony Williams

 

[Tony Williams]

Thanks Ken that was it! One of the fields didn't match the data type on the
form. Why is it that I always overlook the simple things like that?
Thanks again and have a great New Year, just leaving Zanzibar heading for
the Seycelles.
Tony

In the table's design view for tblmaintabs, what is the data type for
txtmonthlabel? What type of data does it contain?

I think what's happening is that you're not matching the data from your form
with the correct format needed by the query for this field.

If you can post that info, then I think we can help.

--

Ken Snell
<MS ACCESS MVP>

Ken,
Managed to get the SQL here it is
SELECT DISTINCTROW tblmaintabs.txtmonthlabel,
Sum(tblmaintabs.txtdomfactot)
AS [Sum of txtDomfactot]
FROM tblmaintabs
GROUP BY tblmaintabs.txtmonthlabel

This runs my report OK If I put [Enter Month] in criteria for
txtmonthlabel
in query none of the calculated controls in the report I listed in my
first
post work I just get error

Any help?
Thanks
Tony Williams

Not sure that I'm following exactly what you're trying to do here. I know
I'm getting confused over which names go with the report and which go
with
the report's RecordSource query.

Perhaps you can post the qrySalesV2 SQL statement and let us start from
there.

--

Ken Snell
<MS ACCESS MVP>


I know it's sad posting on Xmas day but I'm on a cruise ship in the
middle
of
the Indian Ocean and there's not much else to do!!!

I have a report based on a qry "qrySalesV2", in the header is a
Date/time
control "txtqtr2" based on a date/ time field txtmonthlabel and I'm
calculating the same date 1 year previous in a control txtqtr2 using
this
code
=DateAdd("m",-12,[txtqtr1])
In the detail section is a txt control "Sum of txtDomfactot" and I'm
attempting to find the the same value of this control for "txtqtr2"
using
=DLookup("[Sum of
txtDomfactot]","qrySalesV2","[txtmonthlabel]=[txtqtr2]")
I've also tried "[txtmonthlabel]=#" & [txtqtr2] & "#"
If I run my report with either then the report runs OK and all the
records
for all the table are found.
However if I try to run the report by entering a prompt in the query
for
the
field "txtmonthlabel" and asking the user to input a specific date to
find
the data for one date only, I get the "txtDomfactot" and "txtqtr2"
values
but
not the Dlookup values, I get #Error instead. All the date fields and
controls are formatted mmmm yyyy
Why should asking for a prompt in the query criteria stop the report
from
giving me the calculated controls? Running only the query with the
prompt
also works.
Anyone who can spare the time to help over Xmas would be appreciated!
Thanks and Merry Xmas
Tony Williams

 

[Ken Snell [MVP]]

Enjoy your trip!

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken that was it! One of the fields didn't match the data type on
the
form. Why is it that I always overlook the simple things like that?
Thanks again and have a great New Year, just leaving Zanzibar heading for
the Seycelles.
Tony

In the table's design view for tblmaintabs, what is the data type for
txtmonthlabel? What type of data does it contain?

I think what's happening is that you're not matching the data from your
form
with the correct format needed by the query for this field.

If you can post that info, then I think we can help.

--

Ken Snell
<MS ACCESS MVP>

Ken,
Managed to get the SQL here it is
SELECT DISTINCTROW tblmaintabs.txtmonthlabel,
Sum(tblmaintabs.txtdomfactot)
AS [Sum of txtDomfactot]
FROM tblmaintabs
GROUP BY tblmaintabs.txtmonthlabel

This runs my report OK If I put [Enter Month] in criteria for
txtmonthlabel
in query none of the calculated controls in the report I listed in my
first
post work I just get error

Any help?
Thanks
Tony Williams

:

Not sure that I'm following exactly what you're trying to do here. I
know
I'm getting confused over which names go with the report and which go
with
the report's RecordSource query.

Perhaps you can post the qrySalesV2 SQL statement and let us start
from
there.

--

Ken Snell
<MS ACCESS MVP>


message
I know it's sad posting on Xmas day but I'm on a cruise ship in the
middle
of
the Indian Ocean and there's not much else to do!!!

I have a report based on a qry "qrySalesV2", in the header is a
Date/time
control "txtqtr2" based on a date/ time field txtmonthlabel and I'm
calculating the same date 1 year previous in a control txtqtr2
using
this
code
=DateAdd("m",-12,[txtqtr1])
In the detail section is a txt control "Sum of txtDomfactot" and I'm
attempting to find the the same value of this control for "txtqtr2"
using
=DLookup("[Sum of
txtDomfactot]","qrySalesV2","[txtmonthlabel]=[txtqtr2]")
I've also tried "[txtmonthlabel]=#" & [txtqtr2] & "#"
If I run my report with either then the report runs OK and all the
records
for all the table are found.
However if I try to run the report by entering a prompt in the query
for
the
field "txtmonthlabel" and asking the user to input a specific date
to
find
the data for one date only, I get the "txtDomfactot" and "txtqtr2"
values
but
not the Dlookup values, I get #Error instead. All the date fields
and
controls are formatted mmmm yyyy
Why should asking for a prompt in the query criteria stop the report
from
giving me the calculated controls? Running only the query with the
prompt
also works.
Anyone who can spare the time to help over Xmas would be
appreciated!
Thanks and Merry Xmas
Tony Williams