deagon
Posts: 31
|
| Posted: 02/20/2007, 10:53 AM |
|
Is there a way to do a CCDLookup with sum value and use null as a factor. Currently my code
looks like this.
Dim Conn
Dim Sum1
Dim Sum2
Dim Sum3
Dim Sum4
Set Conn = new clsDBjobstatus
conn.open
Sum1 = CCDLookup("SUM(orderValue)","Jobs","SalesRelease between '2007-01-01 00:00:00' and '2007-01-31 00:00:00'",Conn)
Sum2 = CCDLookup("SUM(ordervalue)","Jobs","ActualShipDateToCustomer between '2007-01-01 00:00:00' and '2007-01-31 00:00:00'",Conn)
sum3 = 3000000.00 ' Start Value of Backlog from last year
Sum4 = sum3 + sum2 'Current backlog for Jan including last year
EventCaller.value = Sum4 - Sum1
BackorderFWD = Sum4 - Sum1
Is there clean way to do a CCDLookup with Sum2 above and sort the data by the ActualShipDateToCustomer is a null value? I want only the shipdates that have no Ship dates.
Does this make Sense????
|
 |
 |
peterr
Posts: 5971
|
| Posted: 02/20/2007, 2:36 PM |
|
Most likely this is possible but I'm not sure if I fully understand your question. Are you trying to retrieve multiple records in specific order, or first record that matches certain condition?
It may also be worth taking a look at the CCDLookUp funtion in Common.asp, to understand that it's same as execution a single SQL statement:
SQL = "SELECT " & ColumnName & " FROM " & TableName & " WHERE " & Where
Connection.Execute(SQL)
So whatever can be retrieved from database via SQL can be done either via CCDLookUp or by executing similar SQL statements directly.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
deagon
Posts: 31
|
| Posted: 02/20/2007, 2:49 PM |
|
Instead of just getting the value of tableColumns between dates, I'm trying to get only the value of the Table Column when it has no value set. We have a date in a Column field that tells us when we have a Actual ship to Customers. My form needs to get the same data below in sum2 except the ActualShipDateToCustomer needs to be zero. No data in that field. Thats what we call part of our backlog distribution calculations. I'm currently using this lookup below but instead of straight data value I need the one field to be null or no value. Set to no date in that field. So what I'm trying to get is the same date except with a sort in the command that only returns the ordervalue of actualSHipDateTOCustomer that has not been specified. I hope this makes some sense.
Sum2 = CCDLookup("SUM(ordervalue)","Jobs","ActualShipDateToCustomer between '2007-01-01 00:00:00' and '2007-01-31 00:00:00'",Conn)
|
|
|
|
|
