Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 23rd, 2008, 03:10 AM
Authorized User
 
Join Date: Nov 2005
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date Parameter in a Union Query

I have created a Union Query of Three Tables A,B,C. Now My problem is that I would like the Result to display only Records from the three tables falling in the date Criteria that I specify. The Statement I used looks like this "TABLE[stock_track] UNION ALL TABLE[tblktzstocks]UNION ALL Select * from tblExpenseDetails where companycode=can;
This Union displays all Records. But I only want data between the dates I specify
Iam Stuck


Bmulenga
__________________
Bmulenga
 
Old January 23rd, 2008, 07:41 AM
Friend of Wrox
 
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

select * from tab1 where date='???'
union
select * from tab2 where date='???'
union
select * from tab3 where date='???'



urt
 
Old January 23rd, 2008, 04:26 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Which table has the date field, and how are you passing this? Are you using a parameter entry in the Criteria line of the date field like:

Between #[Enter a Start Date:]# And #[Enter an End Date:]#

Or are you using some combo boxes on a form? With combos you can either do this in the Criteria line of the Date field:

Between #[Forms]![frmMyForm].[cboStartDate]# And #[Forms]![frmMyForm].[cboEndDate]#

Or you can pass it in code like:

Dim dtStart As Date
Dim dtEnd As Date
Dim sLink As String

dtStart = Me.cboStartdate
dtEnd = Me.cboEndDate

sLink = "[DateField] Between #" & dtStart & "# And #" & dtEnd & "#"

DoCmd.OpenReport sDocName, acPreview, , sLink

Did any of that help?


mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old January 24th, 2008, 03:35 AM
Authorized User
 
Join Date: Nov 2005
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you mmcdonal. All the three tables have a date field. They all have the same fields. What it is, is that data is being entered independently for different sites, and I am using a union to have a consolidated list. However, I want to view the data in the Union query based on the date criteria I specify. The Tables in the Union statement below are the ones I have used.
"I have created a Union Query of Three Tables A,B,C. Now My problem is that I would like the Result to display only Records from the three tables falling in the date Criteria that I specify. The Statement I used looks like this "TABLE[stock_track] UNION ALL TABLE[tblktzstocks]UNION ALL Select * from tblExpenseDetails where companycode=can;"This Union displays all Records. But I only want data between the dates I specify
Iam Stuck


Bmulenga
 
Old January 24th, 2008, 05:33 AM
Authorized User
 
Join Date: Nov 2005
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you Urt for your response
I am failing to use the statements below as you sent them. Could you elaborate more on how I can use them. Everytime I tried them with the table names, the system was giving a syntax error!
select * from tab1 where date='???'
union
select * from tab2 where date='???'
union
select * from tab3 where date='???'


Bmulenga
 
Old January 24th, 2008, 12:49 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I used urtivedi's solution and came up with this query string:

select * from table1 where date=#1/23/2008# union all select * from table2 where date=#1/23/2008# UNION ALL select * from table3 where date=#1/23/2008#;

This worked to show me all the records from 3 tables where the date matched the criteria. Column names are an issue. I would be inclined to add aliases since it gave me the PK from table1 for all three tables in one column called Table1ID, for example. You can add additional criteria of course.

How do you want to pass the date criteria?



mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old January 25th, 2008, 03:30 AM
Authorized User
 
Join Date: Nov 2005
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks mmcdonal.
So far the query statement is executing without a syntax error. However, How do I make the query to pick records between two date ranges i.e [Beginning Date]and[Ending Date]

Bmulenga
 
Old January 25th, 2008, 04:33 AM
Authorized User
 
Join Date: Nov 2005
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear mmcdonal
I used the statement below to execute the query, However, I noticed that it only managed to pick records from the first Table 'Stock_track'in the statement and left out records from the other two tables where the date matched the criteria.
select * from Stock_track where date=#1/1/2008# union all select * from tblKTZstocks where date=#1/1/2008#union all select * from tblExpenseDetails where date=#1/1/2008#;

Going by this type of statement, I see that any time one wants to change the date criteria they have to go to the statement to change. Is there another way that could be used to enter the date criteria

Bmulenga
 
Old January 25th, 2008, 08:13 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

That's why I asked how you were submitting the date criteria. You can do this if you are using a form to select a date:

select * from Stock_track where date=#[Forms]![frmMyForm].[cboDateCombo]# union all select * from tblKTZstocks where date=#[Forms]![frmMyForm].[cboDateCombo]# union all select * from tblExpenseDetails where date=#[Forms]![frmMyForm].[cboDateCombo]#;

I was able to get records from each table using this. The problem was that all the date from Table2 and Table3 comes in under the column name from table1, that's why I suggested using an alias on some columns.

I think a better way to do this would be to create a table, then empty the table each time you want to use it, then dump the data from each other table one at a time as you run this, and then display data from the table, not the query.


mmcdonal

Look it up at: http://wrox.books24x7.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Parameter in a Union Query bright_mulenga Access VBA 0 January 17th, 2008 03:52 AM
problem with the parameter date in a mdx query olmouy SQL Language 0 May 30th, 2007 05:32 AM
UNION QUERY Help Corey Access 1 October 27th, 2006 05:29 PM
UNION query. rupen Access 3 April 28th, 2006 02:49 AM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.