How to direct Dsum() to sum dbs2.Tbl1 not dbs1.Tbl1 data

[EagleOne@microsoftdiscussiongroups]

2003

Want to calculate a Sum of a field in dbs2.Tbl1 dbs1.Tbl1 has a similar
field name.
The DSum VBA statement which follows works fine but not on the desired dbs2:

Reg7Count = IIf(IsNull(DCount("[AMT]", "ChooseData", "[Reg_Numb]=
'07'")), _
0, DCount("[AMT]", "ChooseData", "[Reg_Numb]= '07'"))

Reg7Count number is from dbs1.Tbl1 not dbs2.Tbl1 (dbs1 is Currentdb.name)

How do I direct DSum to dbs2?

Or how do I make dbs2 the currentdb with dbs1 still in the background?

TIA

EagleOne

 

[Douglas J. Steele]

The simplest way, of course, would be to have a linked table in dbs1 that
points to dbs2.

Failing that, you can use a recordset:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

Set dbCurr = OpenDatabase("C:\Folder\dbs2.mdb")
strSQL = "SELECT COUNT(*) AS RegCount" & _
"FROM ChooseData " & _
"WHERE [Reg_Numb]= '07'"
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Reg7Count = rsCurr!RegCount
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

(Note that you can create your own function that accepts the table name,
condition and database location as parameters, making it more generic)

Incidentally, there's no need for the IIf and IsNull in your example. DCount
will always return 0 if nothing matches the criteria. And even if it didn't,
the following would likely be better:

Reg7Count = Nz(DCount("[AMT]", "ChooseData", "[Reg_Numb]='07'"),0)

since it avoids having to do the count twice.

Also, I don't know whether it's relevant for what you're trying to do, but
putting a variable name in the DCount (your [AMT]) means that the count will
only return the number of rows matching the criteria where [AMT] is not
null. To return a count of all rows matching the criteria, use "*" instead
of the field name.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"EagleOne@microsoftdiscussiongroups"

 

[EagleOne@microsoftdiscussiongroups]

It is amazing and greatly appreciated how well you provide solutions!

How does one create a link (with VBA/SQL) in dbs1 to dbs2 table "CHOOSEData"?

HAGD!

The simplest way, of course, would be to have a linked table in dbs1 that
points to dbs2.

Failing that, you can use a recordset:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

Set dbCurr = OpenDatabase("C:\Folder\dbs2.mdb")
strSQL = "SELECT COUNT(*) AS RegCount" & _
"FROM ChooseData " & _
"WHERE [Reg_Numb]= '07'"
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Reg7Count = rsCurr!RegCount
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

(Note that you can create your own function that accepts the table name,
condition and database location as parameters, making it more generic)

Incidentally, there's no need for the IIf and IsNull in your example. DCount
will always return 0 if nothing matches the criteria. And even if it didn't,
the following would likely be better:

Reg7Count = Nz(DCount("[AMT]", "ChooseData", "[Reg_Numb]='07'"),0)

since it avoids having to do the count twice.

Also, I don't know whether it's relevant for what you're trying to do, but
putting a variable name in the DCount (your [AMT]) means that the count will
only return the number of rows matching the criteria where [AMT] is not
null. To return a count of all rows matching the criteria, use "*" instead
of the field name.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"EagleOne@microsoftdiscussiongroups"

2003

Want to calculate a Sum of a field in dbs2.Tbl1 dbs1.Tbl1 has a similar
field name.
The DSum VBA statement which follows works fine but not on the desired
dbs2:

Reg7Count = IIf(IsNull(DCount("[AMT]", "ChooseData", "[Reg_Numb]=
'07'")), _
0, DCount("[AMT]", "ChooseData", "[Reg_Numb]= '07'"))

Reg7Count number is from dbs1.Tbl1 not dbs2.Tbl1 (dbs1 is
Currentdb.name)

How do I direct DSum to dbs2?

Or how do I make dbs2 the currentdb with dbs1 still in the background?

TIA

EagleOne

 

[louisjohnphillips]

It is amazing and greatly appreciated how well you provide solutions!

How does one create a link (with VBA/SQL) in dbs1 to dbs2 table "CHOOSEData"?

HAGD!

The simplest way, of course, would be to have a linked table in dbs1 that
points to dbs2.

Failing that, you can use a recordset:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

  Set dbCurr = OpenDatabase("C:\Folder\dbs2.mdb")
  strSQL = "SELECT COUNT(*) AS RegCount" & _
    "FROM ChooseData " & _
    "WHERE [Reg_Numb]= '07'"
  Set rsCurr = dbCurr.OpenRecordset(strSQL)
  Reg7Count = rsCurr!RegCount
  rsCurr.Close
  Set rsCurr = Nothing
  Set dbCurr = Nothing

(Note that you can create your own function that accepts the table name,
condition and database location as parameters, making it more generic)

Incidentally, there's no need for the IIf and IsNull in your example. DCount
will always return 0 if nothing matches the criteria. And even if it didn't,
the following would likely be better:

  Reg7Count = Nz(DCount("[AMT]", "ChooseData", "[Reg_Numb]='07'"),0)

since it avoids having to do the count twice.

Also, I don't know whether it's relevant for what you're trying to do, but
putting a variable name in the DCount (your [AMT]) means that the countwill
only return the number of rows matching the criteria where [AMT] is not
null. To return a count of all rows matching the criteria, use "*" instead
of the field name.

"EagleOne@microsoftdiscussiongroups"

2003
Want to calculate a Sum of a field in dbs2.Tbl1  dbs1.Tbl1 has a similar
field name.
The DSum VBA statement which follows works fine but not on the desired
dbs2:
     Reg7Count = IIf(IsNull(DCount("[AMT]", "ChooseData", "[Reg_Numb]=
'07'")), _
       0, DCount("[AMT]", "ChooseData", "[Reg_Numb]= '07'"))
Reg7Count number is from dbs1.Tbl1 not dbs2.Tbl1    (dbs1 is
Currentdb.name)
How do I direct DSum to dbs2?
Or how do I make dbs2 the currentdb with dbs1 still in the background?
TIA
EagleOne- Hide quoted text -

- Show quoted text -

If you must use VBA, look at the DoCmd.Transferdatabase method. Try
something like this:

DoCmd.TransferDatabase , acLink, "db2", acTable, "ChooseData",
"db2_ChooseData"

However, using the File ==>> GetExternalData menu option is a simplier
method.

 

[EagleOne@microsoftdiscussiongroups]

Thank you!!

It is amazing and greatly appreciated how well you provide solutions!

How does one create a link (with VBA/SQL) in dbs1 to dbs2 table "CHOOSEData"?

HAGD!

The simplest way, of course, would be to have a linked table in dbs1 that
points to dbs2.

Failing that, you can use a recordset:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

Set dbCurr = OpenDatabase("C:\Folder\dbs2.mdb")
strSQL = "SELECT COUNT(*) AS RegCount" & _
"FROM ChooseData " & _
"WHERE [Reg_Numb]= '07'"
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Reg7Count = rsCurr!RegCount
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

(Note that you can create your own function that accepts the table name,
condition and database location as parameters, making it more generic)

Incidentally, there's no need for the IIf and IsNull in your example. DCount
will always return 0 if nothing matches the criteria. And even if it didn't,
the following would likely be better:

Reg7Count = Nz(DCount("[AMT]", "ChooseData", "[Reg_Numb]='07'"),0)

since it avoids having to do the count twice.

Also, I don't know whether it's relevant for what you're trying to do, but
putting a variable name in the DCount (your [AMT]) means that the count will
only return the number of rows matching the criteria where [AMT] is not
null. To return a count of all rows matching the criteria, use "*" instead
of the field name.

"EagleOne@microsoftdiscussiongroups"
message2003

Want to calculate a Sum of a field in dbs2.Tbl1 dbs1.Tbl1 has a similar
field name.
The DSum VBA statement which follows works fine but not on the desired
dbs2:

Reg7Count = IIf(IsNull(DCount("[AMT]", "ChooseData", "[Reg_Numb]=
'07'")), _
0, DCount("[AMT]", "ChooseData", "[Reg_Numb]= '07'"))

Reg7Count number is from dbs1.Tbl1 not dbs2.Tbl1 (dbs1 is
Currentdb.name)

How do I direct DSum to dbs2?

Or how do I make dbs2 the currentdb with dbs1 still in the background?

EagleOne- Hide quoted text -

- Show quoted text -

If you must use VBA, look at the DoCmd.Transferdatabase method. Try
something like this:

DoCmd.TransferDatabase , acLink, "db2", acTable, "ChooseData",
"db2_ChooseData"

However, using the File ==>> GetExternalData menu option is a simplier
method.

 

[David W. Fenton]

The simplest way, of course, would be to have a linked table in
dbs1 that points to dbs2.

Failing that, you can use a recordset:

[snip]

Or, use Trevor Best's domain lookup replacement functions, which
have an optional argument for the database:

http://www.mvps.org/access/modules/mdl0012.htm