Help! join with dates

[erwin007]

After a lot of searching and trying I put my question here:

I have 2 tables (data1108 & data1208)
both tables have the same fields: date(text), amount(number) and type(number)

Now I want to have all records with the following criteria:

Date must be between date1 and date2, amount must be 5 and type must be 3.

The SQL for 1 table looks like this:
"SELECT * FROM data1108 WHERE amount = 5 AND type = 3 AND CLng(cdate(date)) BETWEEN CLng(CDate(date1)) AND CLng(CDate(date2))"

This works nice but now I want this from the 2 tables...which I can not figure out.

Help would be highly appreciated!

[manavo11]

"SELECT * FROM data1108 WHERE amount = 5 AND type = 3 AND CLng(cdate(date)) BETWEEN CLng(CDate(date1)) AND CLng(CDate(date2)) UNION SELECT * FROM data1208 WHERE amount = 5 AND type = 3 AND CLng(cdate(date)) BETWEEN CLng(CDate(date1)) AND CLng(CDate(date2))"

This should return all the values from both tables. Might I ask though, why you have more than one table with the same columns? And why the numbers in the table name after "data" look like dates?

Also moved to Database Development forum.

[Grimfort]

This would be a database question, but anyway, what your after is a union query.

Heres a link: http://www.w3schools.com/sql/sql_union.asp

[GaryMazzone]

Why are you converting a date to a Long?