Use variable in a Union Query

[ConfusedMike]

Hi,



I am using a Union Query to join the contents of Table1 with a variable number of blank rows extracted from Table2 to create a table which will then be used in a fixed length report. I have used the following code which works by finding the number of rows in Table1 and then subtracting that value from 10 (the total number of rows I require):

SELECT Table1.AID, Table1.A, Table1.A1, Table1.A2 FROM Table1
UNION SELECT Table2.Row, Table2.B, Table2.B1, Table2.B2 FROM Table2 WHERE ((Table2.ARow)<=(10-(Select Count(*) As RowsInTable1 From Table1)));


To make it easier to read in the future I would like to use variables such as 'TotalNumberOfRows' instead of '10' and 'RowsInTable1' instead of 'Select Count(*) As RowsInTable1 From Table1'

All attempts to define the varialble within the query result in a syntax error and I am not sure how to define them outside the query and then reference them in the where statement.

Can anyone help me with this

[June7]

An Access query can call a function or refer to a control on a form or report or an input parameter prompt can get a value.

A query in VBA code can concatenate a variable.

[ConfusedMike]

Thanks for you help so far but I'm not there yet.

I created a function as follows:

Private Function Rows() as Integer
Rows = DCount("*","Table1")
End Function

I know that the function works as I can output the answer to a msgbox but when I substite (10-Rows()) into the where statement instead of (10-(Select Count...... I get the following error 'Undefined function 'Rows' in expression'

What am I doing wrong?

[June7]

Has to be a Public function in a general module.

[ConfusedMike]

Hi June7,

Thanks, it works now. Its so simple when you know how!!!!!!!