Not returning a value

[DS]

This isn't returning a value...I'm sure it's my syntax.
I think it might be the CDDiscountWhere = 'A' part but then again.....
Any help appreciated.
Thanks
DS

Set rs = CurrentDb.OpenRecordset("SELECT
Sum(CDQuantity*CDPrice*(1-[CDDiscountPercent])) FROM " & _
"[MS Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblCheckDetails
" & _
"WHERE CDDiscountWhere = 'A' AND CDDiscountDP=2 And CDBizDay BETWEEN " &
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#"),
dbOpenSnapshot)

Set rs2 = CurrentDb.OpenRecordset("SELECT
Sum(CDQuantity*CDPrice*(1-[CDDiscountPercent])) FROM " & _
"tblCheckDetails " & _
"WHERE CDDiscountWhere = 'A' AND CDDiscountDP=2 And CDBizDay BETWEEN " &
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#"),
dbOpenSnapshot)

Me.TxtTaxableSales = Me.TxtSalesTotal = rs(0) + rs2(0)
rs.Close
rs2.Close

 

[Damon Heron]

Try Where = ""A"" AND.....

Damon

 

[Douglas J. Steele]

Me.TxtTaxableSales = Me.TxtSalesTotal = rs(0) + rs2(0)

is incorrect. That will return either 0 (False) or -1 (True), depending on
whether Me.TxtSalesTotal is equal to the sum of rs(0) and rs2(0).

As an aside, you've been asking a lot of questions recently where you're
running the same query against tables in two different databases. Are you
sure you have to use two different databases? What you're doing is not very
common!

 

[DS]

Thanks Douglas. No it's not very common
but it's the way I have this database set up so that it only contains
records for one year at a time. I wanted to keep the database as lean as
possible without having to store older records that will probably be
accessed only once in a blue moon. Non the less, these records have to be
kept and made accessable for a leat 7 years.
Thanks
DS

 

[DS]

Douglas,
I fixed this.....
Me.TxtTaxableSales = Me.TxtSalesTotal + rs(0) + rs2(0)
But I'm still not getting a Sum of Total. What can be done to make this
return a Sum total?
Thanks
DS

 

[Douglas J. Steele]

In case the sum is null in one (or both) of the recordsets, use

Me.TxtTaxableSales = Me.TxtSalesTotal + Nz(rs(0),0) + Nz(rs2(0),0)

 

[Douglas J. Steele]

Unless you're talking a large volume of records (say, 100,000/year or more),
I think you'd be far better off storing everything in a single database.

 

[DS]

300 to 500 records a day, everyday for 7 Years. With anywhere from 1 to 7
users on at one time.
Thanks
DS

 

[Douglas J. Steele]

500 * 366 * 7 is 1,281,000, which is still within the realm of doable for
Access.