mlcampeau
Last Activity: Sep 29 '10, 08:39 PM
Joined: Jul 16 '07
Location: BC, Canada
-
From what I understood from your previous post was that you tried =sum([txtBeginner]) in your footer. Did you try the running sum invisible box in the details section and then just have a text box that says =[sumBeginner]? Because I know Access won't allow the aggregate functions on a calculated expression in a different section, but it has let me just reference a calculated textbox from a different section.... -
From my experience, you can't Sum an expression that is in a different section in the report. What I've done in the past to overcome this is create the =IIF([SkillLevel]="Beginner", 1, 0) textbox in the details section and name it txtBeginner. Then also in the details section, create another textbox that says =[txtBeginner] and go to the properties of the textbox and change it to Running Sum Over Group. Change Visible=No. Name it appropriately,...Leave a comment:
-
I'm glad you got it to work! Thanks for posting your final solution!...Leave a comment:
-
Glad it worked for you!...Leave a comment:
-
The only thing that I can see right now is that in your Score calculation, you have quotes around all your numbers. Numbers don't need quotes around them so remove them and see what happens. (I don't know if that will make any difference but it's worth a shot)...Leave a comment:
-
Count doesn't let you have a condition, but DCount does.
(Note that the syntax in the condition portion may be wrong...I'm still learning when to use quotes and such in this function)Code:=DCount("[FieldName]","TableName","[FieldName]=True")
You could also try something like this:
Code:=Sum(IIf([FieldName]=True,1,0))
Leave a comment:
-
Why don't you create a separate query to get the sum that you want, and then you can use DLookup()? Again, I must point out that I am still learning Access and there may be an easier/more efficient way of doing this...I just don't know of one!...Leave a comment:
-
Glad you got it figured out. Thanks for posting your solution!...Leave a comment:
-
To get the results you want you need to make sure that 15 fields match?? I've only used DSum with a maximum of 3 or 4 fields, so unfortunately, I don't know a way to get around this (I'm still learning a lot when it comes to Access). I also don't know what the 2048 limit is, but I'm assuming it's just not liking having 15 fields. Can you not sum the fields in your query rather than using DSum?...Leave a comment:
-
The trickiest part to this is knowing how/when to use single/double quotes. I'm still learning so I'm going to take a stab at this:
(I'm assuming Program Description is a string)...Code:=DSum("[Trial Balance Amount]","DDRS Data", "[Acct] = " & [Acct] & " AND [Program Description]= '" & [Program Description] & "'")Leave a comment:
-
I'm no expert on the syntax for DSum but yours definitely doesn't look right.
Try something like:
=DSum("[Trial Balance Amount]","DDRS Data", "[Acct] = "&[Acct])
If Acct isn't a number then try:
=DSum("[Trial Balance Amount]","DDRS Data", "[Acct] = '" & [Acct] &"'")...Leave a comment:
-
Not sure of a way to do it with one query, but if you make one query such as:
[CODE=sql]SELECT FName, Count(FName) AS CountOfFName
FROM TableName
GROUP BY FName;[/CODE]
Create a second query such as:
[CODE=sql]SELECT TableName.FName , TableName.Marke t
FROM QueryName RIGHT JOIN TableName ON QueryName.FName = TableName.FName
GROUP BY TableName.FName , TableName.Marke t, QueryName.Count OfFName...Leave a comment:
-
Not a problem. Good luck!Leave a comment:
-
Instead of using VB to do this, why don't you just create a textbox on your subform with =Left([First name], 1) as the expression?...Leave a comment:
-
Your welcome. I'm glad it worked for you.Leave a comment:
-
Instead of 2 tables, you could always just have the one, with an extra field named Active with a datatype of Yes/No. Then when there is an address change, you can add the new address to the table and just make the new one active, and the old one inactive.Leave a comment:
-
Okay so I just did a test and this is how I had to get it to work. I made 3 queries.
The first sums the PurchasedQty:
[CODE=sql]SELECT Product.Product ID, Sum(Purchased.P urchasedQty) AS SumOfPurchasedQ ty
FROM Product LEFT JOIN Purchased ON Product.Product ID = Purchased.Produ ctID
GROUP BY Product.Product ID;[/CODE]
The second sums the SalesQty:
[CODE=sql]SELECT Product.Product ID, Sum(Sales.Sales Qty)...Leave a comment:
-
Well, if it is asking you to enter the ProductId, then you must not have it named correctly in your query. Try going to the Query Design view and select productid from the drop down field list. Also, in your From clause, your tables are not joined at all. In your query design view, make sure that the tables are joined by productid....Leave a comment:
-
The DateDiff() function will help you out here. Something like this shouuld work:
Between (DateDiff("m", -12,Date()) AND DateDiff("m",-9,Date())) AND Is Not Null...Leave a comment:
-
You could try something like this:
[CODE=sql]SELECT [ProductID.Produ ctID], (Sum([Purchase.QtyPur chased])-Sum([Sales.QtySold])) AS ClosingStock
FROM [your 3 tables][/CODE]
If it doesn't work, please explain how it's not working, and let us know what you have tried.Leave a comment:
No activity results to display
Leave a comment: