CODEWITHSUNDEEP
ms-accessunion
user3159652
user3159652

Reputation: 1

when using union that uses values from a form it creates a error?

I have this union statement when I try to take parameters from a form and pass it to a union select statement it says too many parameters. This is using MS ACCESS.

SELECT Statement FROM table 1 where Date = Between [Forms]![DateIN]![StartDate]
UNION
SELECT Statement FROM table 2 where Date = Between [Forms]![DateIN]![StartDate]

This is the first time I am using windows DB applications to do Database apps. I am Linux type of person and always use MySQL for my projects but for this one have to use MS Access.

Is there anther way to pass parameters to UNION Statement because this method of defining values in a form can work on Single SELECT statements. But I don't know why this problem exist.

Upvotes: 0

Views: 93

Answers (2)

user3159652
user3159652

Reputation: 1

Still getting problems when using this method of calling the values or dates from the form to be used on the UNION statement. Here is the actual query that I am trying to use.

I don't want to recreate the wheel but I was thinking that if the Date() can be used with between Date() and Date()-6 to represent a 7 days range then I might have to right a module that takes the values from the for and then returns the values that way I can do something like Sdate() and Edate() then this can be used with Between Sdate() and Edate().

I have not tried this yet but this can be my last option I don't even know if it will work but it is worth a try. But before i do that i want to try all the resources that Access can help me make my life easy such as its OO Stuff it has for helping DB programmers.

SELECT  
    "Expenditure" as [TransactionType], *  
FROM 
    Expenditures 
WHERE 
   (((Expenditures.DateofExpe) Between [Forms]!Form1![Text0] and  [Forms]![Form1]![Text11]))

UNION 

SELECT 
    "Income" as [TransactionType], *  
FROM 
    Income 
WHERE 
    (((Income.DateofIncom)  Between [Forms]!Form1![Text0] and [Forms]![Form1]![Text11]  ));

Access VBA has great power but I don't want to use it as of yet as it will be hard to modify changes for a user that does not know how to program. trying to keep this DB app simple as possible for a dumb user to fully operate.

Any comments is much appreciated.

Upvotes: 0

HansUp
HansUp

Reputation: 97131

Between "Determines whether the value of an expression falls within a specified range of values" like this ...

expr [Not] Between value1 And value2

But your query only gives it one value ... Between [Forms]![DateIN]![StartDate]

So you need to add And plus another date value ...

Between [Forms]![DateIN]![StartDate] And some_other_date

Also Date is a reserved word. If you're using it as a field name, enclose it in brackets to avoid confusing the db engine: [Date]

If practical, rename the field to avoid similar problems in the future.

And as Gord pointed out, you must also bracket table names which include a space. The same applies to field names.

Upvotes: 1

Related Questions