More text box control problems 3

[jpl458]

I am building a form that will display a lot of text boxes, each of which should display the result of a different query.

Here is example:

I have a query that calculates the sales from yesterday. It runs perfectly in the QBE grid but won't run from the text box. Here is the SQL;

SELECT Sum(dbo_Transaction_Table.ArAmt) AS SumOfArAmt
FROM dbo_Transaction_Table
WHERE (((dbo_Transaction_Table.Sku) Like "17*") AND ((dbo_Transaction_Table.Date_of_Transaction)=Date()-1));

In the Txt box control source I open the expression builder and build the following:

=[Form YTD Yestereday]![SumOfArAmt]

Form YTD Yesterday is the query name and SumOfAramnt is the result field.

I have tried adding " in various places but nothing works.

Help would be greatly appreciated.

Thanks in advance.

jpl

 

[randy700]

How about a dlookup?

=dlookup("SumOfArAmt","queryname")

Randy

 

[jpl458]

That didn't work and it seem like it should. Here are the variations I tried:

1. =DLookUp("[SumOfArAmt]","Form YTD Yesterday")

2 =DLookUp("SumOfArAmt","Form YTD Yesterday")

3. =DLookUp("[SumOfArAmt]","Form_YTD_Yesterday")

None of which worked.

Stumped

jpl

 

[TheAceMan1]

How are ya jpl458 . . .

[ol][li][blue]=[Form YTD Yestereday]![SumOfArAmt][/blue] is simply [red]wrong[/red] fo opening a query.[/li]
[li]The [blue]DLookup[/blue] aggregate function however does work (I've simulated the DLookup ... no problemo).[/li][/ol]

As a test ... open a new form, add an unbound textbox and set the control source to [blue]=DLookUp("[SumOfArAmt]","Form YTD Yesterday")[/blue]. Open the form to see the results and let us know ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

What about this ?
=DSum("ArAmt", "dbo_Transaction_Table", "Sku Like '17*' AND Date_of_Transaction=Date()-1")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

Is "Form YTD Yesterday" the name of you query?

Randy

 

[dhookom]

None of which worked.
Stumped

You are going to be stumped longer if you don't tell us what didn't work and why you think it didn't work. None of us can see your results.

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

Thanks all for the responses.

AceMan1 I followed your instructions and copied the expression into the control source of a new textbox on a new form. The expression is =DLookUp("[SumOfArAmt]","Form YTD Yesterday"). And the term #Error appears in the textbox when I open the form.

Next, I copied PHV's expression into the control source and it worked. That expression is:

=DSum("ArAmt", "dbo_Transaction_Table", "Sku Like '17*' AND Date_of_Transaction=Date()-1")

But it seems that AceMan1's expression should work. I double checked the spelling and punctuation and all seems ok except that it returns an error. I went back and re copied Aces expression into the form, but his time I got a message stating the the jet could not find the table or query named YTD Yesterday, which is the queries name. But it is there and it does run.
(I am running with linked tables to SQL server, and have some small tables in Access, but never had a problem with that before). Still, I would like to be able to reference a query with DLookup from a form - seems like that would be handy.

Using DSum as the advantage of not needing a query, thus keeping the query page less cluttered

Appreciate all your time and help. You guys are great.

jpl

 

[TheAceMan1]

[blue]I got a message stating the the jet could not find the table or query named [purple]YTD Yesterday[/purple], which is the queries name.[/blue]

Well well well ...

[blue][purple]Form YTD Yesterday[/purple] is the query name ...[/blue]

This is the query name you apprised us to use! ... Not [purple]YTD Yesterday[/purple] ... I'll be DLookup will work just fine if you use [purple]YTD Yesterday[/purple]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[randy700]

And, because you have spaces in the query name (a bad practice IMO), you should put brackets around it...
[YTD Yesterday]

Randy

 

[jpl458]

I got things to work by starting over and following the rules. I see that having spaces in names is a bad practice, but I'm sorry that i can't give a definitive reason why the stuff didn't work. Probably novice error. I have created a bunch of Dsums dLookups Davg,,etc as well as getting a field from a query. Practice makes you better, perfect practice makes perfect.

Thanks again all, and I trust I will return with more questions.

jpl