DSum syntax Problem (need experienced helper)

[osaffar]

Hi all Im new and am finally looking for some experienced help,

Ive been trying to fix this for a few days. My database has the typical structure Clients>Orders>OrderDetails>Products.....In tblOrders there is a DeliveryDate and TotalValue field. I want to create a DueAmount module that Sums all the [tblOrders]![TotalValues] for a particular client where the [tblOrders]!DeliveryDate is 3 months old (so [tblOrders]!DeliveryDate + 3months is >= Today)

Here is my DSum() code so far

Dim DueValue As Variant
DueValue = 0

DueValue = DSum("TotalValue", "tblOrders", "[ClientID] ='" & strClientID & "' AND DateAdd(m,3,#" & [tblOrders]!DeliveryDate & "#)>#" & Date & "#") <---What is wrong with this line. shows message box"Compiler Error: External name not defined"

Forms!Clients!DueAmount = DueValue
Forms!Clients!DueAmount.Requery

Thank you in advance...Help much appreciated

Oscar

 

[namliam]

You dont have any (proper) declaration for "[tblOrders]!DeliveryDate" if you want to use the orders table that your using in the DSum, I am not sure you can do it like this but ...
...DateAdd(m,3,[tblOrders]!DeliveryDate)>#...

Also, you need to make sure when your parcing dates like this that they are in the US format of MM/DD/YYYY to prevent any issues.

Next, ID usually is a number field possibly even a autonumber...
"[ClientID] ='" & strClientID & "'
should possibly be
"[ClientID] =" & strClientID & "

 

[osaffar]

Thanks for the quick response. No joy though. I just tried this and it didn't work

DueValue = DSum("TotalValue", "tblOrders", "[ClientID] ='" & strClientID & "' AND DateAdd(m,3,[tblOrders]!DeliveryDate)>#" & Date & "#")

Also

I check the date format by

Dim DeliveryDat As Date
DeliveryDat = DLookup("DeliveryDate", "tblOrders")
MsgBox DeliveryDat

Got a message box showing 07/10/2009 So im guessing this is US format

The DSum line iv used works without calling the DateAdd function in criteria so "[ClientID] ='" & strClientID & "'" is definitely working on its own

Any other ideas? perhaps a different approach altogether. maybe using a FOR loop. or another way of adding 3 months to a date??? and comparing with todays date

thank you

 

[namliam]

"' AND DateAdd(m,3,[tblOrders]!DeliveryDate)>#" & Date & "#")

I can only imagine:
"' AND DateAdd(m,3,DeliveryDate)>#" & Format(Date(), "MM/DD/YYYY") & "#")

Failing that you can use a 'proper' sql instead...

mySQL = ""
mySQL = mySQL  & " Select Sum([TotalValue]) SumOfTotalValue "
mySQL = mySQL  & " From tblOrders "
mySQL = mySQL  & " Where [ClientID] = '" & strClientID & "'"
mySQL = mySQL  & "   AND DateAdd(m,3,DeliveryDate)>Date() "

 

[osaffar]

First two dont work. something is still wrong with DateAdd(m,3,[tblOrders]!DeliveryDate) I know this because when i run This

DueDate = DateAdd(m, 3, [tblOrders]!DeliveryDate.ItemData(1))
MsgBox DueDate

I get "Compile Error: external name not defined" highlighting [tblOrders] can anybody help me? been stuck on this line for 3 days

 

[namliam]

This delivery date is from the table that your trying to sum, correct??

I didnt notice this before the it needs to be "M" not just M...
"' AND DateAdd(""m"",3,DeliveryDate)>#" & Format(Date(), "MM/DD/YYYY") & "#")

 

[osaffar]

The problem seems to revolve around [tblOrders]. I cant seem to use any table data when outside of a method. do i have to declare a table before i use it and how do i do it. what do you mean by 'proper' declaration?

Even this wont work!!!

MsgBox "Today is '" & [tblOrders]![DeliveryDate].ItemData(0) & "'"

Do i have to declare a recordset or something? never done those things. :|

Thank again

 

[DCrake]

The DateAdd Element needs DateAdd("m",3,[tblOrders]!DeliveryDate)>#" & Date & "#")

Single set of quotes around the m and not captial M

 

[osaffar]

Trust me guys thats not it. I keep getting "external name not defined" error no matter how i try to use [tblOrders]!DeliveryDate or anything in table orders for that matter
here is whole code

code:

Dim DueValue As Variant
DueValue = 0
'MsgBox "Today is '" & [tblOrders]![DeliveryDate].ItemData(0) & "'" < -----This doesnt work either ...why???

'Calculates the Total Due Amount Value if passed the Due Date and returns it to Forms!Clients.DueAmount
'Not getting an error anymore but TotalValue is given for before and after DueDate.So the Second criteria isnt having an effect
DueValue = DSum("TotalValue", "tblOrders", "[ClientID] ='" & strClientID & "' AND [DueDate]< #" & Date & "#")
Forms!Clients!DueAmount = DueValue
Forms!Clients!DueAmount.Requery
End Function

 

[DCrake]

What type of control is DeliveryDate?

 

[DCrake]

'MsgBox "Today is '" & [tblOrders]![DeliveryDate].ItemData(0) & "'" < -----This doesnt work either ...why???

you cannot use .ItemData(0) in this context. This property only relates to listboxes and combo boxes on forms.

 

[osaffar]

Ok noted about the msgbox problem. i see that you cannot use table data on a msgbox ambiguously.

Aside from that issue The main problem is below: Im Not getting an error anymore but TotalValue is summed up for all Orders. I want Totalvalue to be added only if the due date has passed. So the Second criteria isnt having any effect

'Calculates the Total Due Amount Value if passed the Due Date and returns it to Forms!Clients.DueAmount


DueValue = DSum("TotalValue", "tblOrders", "[ClientID] ='" & strClientID & "' AND [DueDate]< #" & Date & "#")

Endwith

 

[osaffar]

It works! Here is the correct syntax for adding all TotalValue for a particular client after a certain date. couldn't find an example of this anywhere. took far too long but its working now. thanks to all who helped

DueValue = DSum("TotalValue", "tblOrders", "[ClientID] ='" & strClientID & "' AND [DueDate]< #" & Date & "#")

Endwith