Union query with a where between statement

[BrettH]

Hi everyone,

I have been reading the helpful posts here and now have one of my own.

Could anyone tell me (nicely as I am new, just as you once were) what I am doing wrong in the following code;

SELECT Maint_PM.[Machine ID]
From Maint_PM
Where (Between (([Forms]![Date Range]![StartDate]) And ([Forms]![Date Range]![EndDate])))
UNION ALL Select Maint_UM.[Machine ID]
From Maint_UM
Where (Between (([Forms]![Date Range]![StartDate]) And ([Forms]![Date Range]![EndDate])))

It keeps telling me there is a missing operator in my between statement, but from what I have read I thought I had it correct.

I am trying to give a list of machines based on a start date and end date from a form called Date Range. Any help will be greatly appreciated.

Brett

 

[boblarson]

Hi everyone,

I have been reading the helpful posts here and now have one of my own.

Could anyone tell me (nicely as I am new, just as you once were) what I am doing wrong in the following code;

SELECT Maint_PM.[Machine ID]
From Maint_PM
Where (Between (([Forms]![Date Range]![StartDate]) And ([Forms]![Date Range]![EndDate])))
UNION ALL Select Maint_UM.[Machine ID]
From Maint_UM;
Where (Between (([Forms]![Date Range]![StartDate]) And ([Forms]![Date Range]![EndDate])))

It keeps telling me there are characters after the SQL statement, but from what I have read I thought I had it correct.

I am trying to give a list of machines based on a start date and end date from a form called Date Range. Any help will be greatly appreciated.

Brett

Two things -

1. You have a semi colon before your last where clause that shouldn't be there.

2. You have two where clauses which don't have the field properly designated.

3. You don't need all of the parens.

It should be this (replace YourFieldNameHere with the actual name of the field that the criteria is on):

SELECT Maint_PM.[Machine ID]
From Maint_PM
Where [COLOR="Red"][YourFieldNameHere][/COLOR] Between [Forms]![Date Range]![StartDate] And [Forms]![Date Range]![EndDate]
UNION ALL 
Select Maint_UM.[Machine ID]
From Maint_UM
Where [COLOR="red"][YourFieldNameHere][/COLOR] Between [Forms]![Date Range]![StartDate] And [Forms]![Date Range]![EndDate]

 

[BrettH]

Bob!!! You are a deadset legend mate!

Any idea though why it doesn't prompt the Date range form and instead throws up that little Access request for the start and end dates?

How can I make it call up the date range form?

Sorry to push the new friendship.

Brett

 

[boblarson]

Bob!!! You are a deadset legend mate!

Any idea though why it doesn't prompt the Date range form and instead throws up that little Access request for the start and end dates?

How can I make it call up the date range form?

You don't call the form from the query, you call the query from the form. Open the form for input and provide a button to open the query from there. The form must be open for the query to be able to use the inputs.