DCount + SQL

[aziz rasul]

I have a table like tblRates0104, tblRates0204, etc. where the first two numerical values represents month and the latter two digits represents year. I have a SQL statement which will select the correct tblRates table based on selecting the Month\Year values from combo boxes.

I want to use the DCount and include the SQL statement. Is this possible?

 

[Mile-O]

I have a table like tblRates0104, tblRates0204

Why?



I find it bizarre that you ask a question to ensure that your tables are properly normalised (here) and then mention two unnormalised tables.

 

[shay]

Hi

You could try the adapting the following code:

Dim TableName As String, x

TableName = "tblRates" & cmbMonth & cmbYear (cmbMonth and cmbYear refer to your two combo boxes)
x = DCount("*", TableName)
MsgBox x

But personally I would have just one table, tblRates, with a month field and a year field. Then it would be far easier to run your queries. It would also avoid you having to create a new table for each month.

To use the DCount function you would then do the following

x = DCount("*", "tblRates","[Mnth] = " & cmbMonth & " and [Yr] = " & cmbYear)
MsgBox x

hth

shay

 

[aziz rasul]

In the real world, you often don't have control to do as you wish.

The post on normalisation was based on soemthing I was doing.

In this instance, I've been asked to resolve a problem (in a very small space of time) on a db designed by someone else, hence the post. Given sufficient time I could go thru the whole database and normalise the tables and redo all associated queries. But this luxury is not available to me.

Shay, the problem is that the domain is a query that takes on more than one table. Here's the SQL statement that I copied from the SQL window: -

SELECT tblLO.LOPID FROM tblRates0104 RIGHT JOIN tblLO ON tblRates0104.LOPID = tblLO.LOPID WHERE (((tblRates0104.LOPID) Is Null));

I'm looking into making a Make Table query and using your suggestion of using the Tablename. I'll repost soon.

 

[aziz rasul]

Tried the MT approach and it works fine.