Can I look up a value using DLookUp() from an input/paramater query?

[kegz]

Everything is okay until I put a parameter in my query and all my textboxes bound to a DLookUp() function are displaying #Error..

Please Help...

 

[Galaxiom]

We will need more information. What are the RecordSource query and the DLookUp expression?

BTW Are you sure you cannot retreive the values directly in the query or in a subform?. Populating multiple controls with DLookUps is not usually a very efficient strategy.

 

[kegz]

A have a query named qryBudget with fields ContriDate, ContriCategory(criteria 1 Or 2 Or 5 Or 6), Contribution. Then I created a new query named qryOperation and I use the qryBudget to get the sum of all contributions. All the fields of qryOperation are all expressions to get a certain budget. Ex. light and water budget.. In my report I used the DLookUp() to retrieve the value of light and water and it's working. But when I put a criteria on the ContriDate in the qryBudget, the textbox bound to the DLookUP() will display error...

 

[kegz]

BTW the DLookUp() expression is DLookUp("[Light and Water]", "qryOperation")

 

[kegz]

aaree with above?

 

[vbaInet]

aaree with above?

Probably an Internet troll.

What is the DLookup() statement that was showing #Error?

 

[kegz]

DLookUp("[Light and Water]", "qryOperation") this one

 

[vbaInet]

1. What is the Record Source of the report?
2. Did you say that you are using qryBudget in qryOperation?
3. Does qryBudget or qryOperation have (or will they have) pop-up parameters?

 

[kegz]

I'm in trouble...
1. The record source of the report is qryExpensesLightandWater
2. Ok lets put it this way.. When i created the qryOperation I chose the qryBudget boz i want to get the sum of the budget for just 4 categories..
3. Do you mean criteria?

 

[vbaInet]

Paste the SQL statements of qryBudget and qryOperation here. Enclose them in code tags too, here's how:

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

 

[kegz]

1.Ok I will tell you the details of my qryBudget.... I chose the tblContribution as my reference table/query then I chose the following fields contriDate, ContriCategory with the criteria of 1 or 2 or 5 or 6, Contribution..

2.The Details of my qryOpearation.. I chose the qryBudget as my reference table/query..
The fields are all expressions. Total Contribution: Sum([Contribution]), Light and Water: [Operation]*.20 and so on.

3. The details od my report. The report is based on my expenses query. What you will see in my report is the ExpensesDate, ExpensesItem, Amount, a textbox with the sum of the expenses, a textbox bound to DLookUp("[Light and Water]", "[qryOperation]"), and a textbox of the remaining budget(the value of the DLookUp - sum of expenses)..

with this everything is working smoothly. But when I put a criteria on the ContriDate in the qrybudget it will produce error to the textbox bound to the DLookUp()..


I hope it make things more clearer...

 

[vbaInet]

I don't need to know about the details. I would like to see the SQL statement instead.

 

[kegz]

sql statement for the qryBudget

SELECT tblContribution.ContriDate, tblContribution.Contribution, tblContribution.ContriCategoryID
FROM tblContribution
WHERE (((tblContribution.ContriDate) Between [Budget Start Date] And [Budget End Date]) AND ((tblContribution.ContriCategoryID)=1 Or (tblContribution.ContriCategoryID)=2 Or (tblContribution.ContriCategoryID)=5 Or (tblContribution.ContriCategoryID)=6));

sql statement for qryOperation

SELECT Sum([Contribution]) AS [Total Budget Contribution], [Total Budget Contribution]*0.22 AS [Percent], [Total Budget Contribution]-[Percent] AS Balance, [Balance]*0.75 AS Operation, [Operation]*0.4 AS Pastor, [Operation]*0.1 AS [Pastor Allowance], [Operation]*0.03 AS SSS, [Operation]*0.02 AS PhilHealth, [Operation]*0.05 AS [Office Supplies], [Operation]*0.2 AS [Light and Water], [Operation]*0.05 AS [Building Fund], [Operation]*0.05 AS [Sunday Worship], [Operation]*0.02 AS [Bible Study], [Operation]*0.03 AS Venebolence, [Operation]*0.02 AS Transportation, [Operation]*0.03 AS [Travelling Allowance]
FROM qryBudget;

 

[vbaInet]

WHERE (((tblContribution.ContriDate) Between [COLOR=Red][Budget Start Date][/COLOR] And [COLOR=Red][Budget End Date][/COLOR])

This is what I meant by number 3 in post #9. Those are pop-up parameters.

You can't use DLookup with those.

 

[kegz]

But I need that that parameter to track down my budget for a certain week, month or year..
Any suggestion you can give?

 

[vbaInet]

It needs to point to textboxes on a form for it to work. Those parameters can't be plugged in via a DLookup() function. So if you follow my advice, it will look like this:

WHERE (((tblContribution.ContriDate) Between [Forms]![[COLOR=Red]FormName[/COLOR]]![[COLOR=Red]txtStartDate[/COLOR]] And [Forms]![[COLOR=Red]FormName[/COLOR]]![[COLOR=Red]txtEndDate[/COLOR]])

Where FormName is the name of your form and txtStartDate and txtEndDate are the names of your textboxes.

 

[kegz]

So I'll create a new form?
A blank form?

 

[vbaInet]

New form/blank form... same thing really. It's up to you where you want those textboxes to be.

Just bear in mind that the form must be open before you open the report and it must remain open.

 

[kegz]

Thanks I will follow your advice... It'll be a lot of work.. Thanks a lot

 

[vbaInet]

It won't be too bad. It's only going to take a few minutes.

Let us know how you get on.