SQL Union Query with dual parameters

[CEnd99]

Hi,
I am trying to create a SQL Query accessing a SQL database combining two tables together. I was able to successfully create the Union Query however, now I want to add a parameter that is the same in both queries. I can get it in the first part but in the second half, I get an error message "The multi-part identifier "MRI_Journal.Period" could not be bound. The query I have used is below:
SELECT
MRI_GHIS.ENTITYID,
MRI_GHIS.ACCTNUM,
MRI_GHIS.PERIOD,
MRI_GHIS.REF,
MRI_GHIS.SOURCE,
MRI_GHIS.AMT,
MRI_GHIS.DESCRPN
FROM MRIData.dbo.MRI_GHIS MRI_GHIS
WHERE (MRI_GHIS.ENTITYID='C01400') AND (MRI_GHIS.ACCTNUM Like '%CP1810344%') AND (MRI_GHIS.PERIOD>=?) and (MRI_GHIS.period <=?)
UNION
SELECT
MRI_JOURNAL.ENTITYID,
MRI_JOURNAL.ACCTNUM,
MRI_JOURNAL.PERIOD,
MRI_JOURNAL.REF,
MRI_JOURNAL.SOURCE,
MRI_JOURNAL.AMT,
MRI_JOURNAL.DESCRPN
FROM MRIData.dbo.MRI_JOURNAL MRI_JOURNAL
WHERE (MRI_JOURNAL.ENTITYID='C01400') AND (MRI_JOURNAL.ACCTNUM Like '%CP1810344%') and (MRI_JOURNAL.PERIOD >=?)

 

[xenou]

Is this in MSAccess?
You seem to have two parameters, not one.

Note: would also recommend you get it working with the parameter in the first query only, then proceed to see if it works in a union query.

 

[CEnd99]

Sorry, I did not realize the second parameter was still there in the first half of the query. I can get it to work with either one or two in the first half but the minute I put a parameter after the Union statement, I get the unbound error. I want the first parameter to be the same as the parameter in the second half of the query.
SELECT
MRI_GHIS.ENTITYID,
MRI_GHIS.ACCTNUM,
MRI_GHIS.PERIOD,
MRI_GHIS.REF,
MRI_GHIS.SOURCE,
MRI_GHIS.AMT,
MRI_GHIS.DESCRPN
FROM MRIData.dbo.MRI_GHIS MRI_GHIS
WHERE (MRI_GHIS.ENTITYID='C01400') AND (MRI_GHIS.ACCTNUM Like '%CP1810344%') AND (MRI_GHIS.PERIOD>=?)
UNION
SELECT
MRI_JOURNAL.ENTITYID,
MRI_JOURNAL.ACCTNUM,
MRI_JOURNAL.PERIOD,
MRI_JOURNAL.REF,
MRI_JOURNAL.SOURCE,
MRI_JOURNAL.AMT,
MRI_JOURNAL.DESCRPN
FROM MRIData.dbo.MRI_JOURNAL MRI_JOURNAL
WHERE (MRI_JOURNAL.ENTITYID='C01400') AND (MRI_JOURNAL.ACCTNUM Like '%CP1810344%') and (MRI_JOURNAL.PERIOD >=?)

 

[Joe4]

Is this in MSAccess?

It was originally posted in the Excel forum.
I moved it to the Access forum, because SQL is closer to Access than Excel!

 

[xenou]

Does the union query work without the parameters (for instance if you hard code in the dates)? Can you think of another way to provide the parameters?

 

[CEnd99]

Does the union query work without the parameters (for instance if you hard code in the dates)? Can you think of another way to provide the parameters?

yes, if I hard code the dates it works perfectly. However, I am wanting to roll this query out to a large number of staff members that do not have coding knowledge and I don't want them to have to go in and manually change the dates on a periodic basis.

 

[xenou]

You'll probably have to use a different strategy for this. It just doesn't work with that type of parameterization.