combine query in ASP

mateenmohd · July 17th, 2003, 12:33 AM

Hi,

How can use combine query in asp
what is the syntax error of following query?
problem in date (data type) how can use date query in asp to retrieve the records.

Date_recd and Date_revd is the date data type.

ssql = "select drawings.#date_recd#, drawings.#date_revd#, drawings.type, drawings.location, sectors.sect_area from drawings, sectors
where drawings.name = sectors.name and drawings.type in ('" & plt & "','" & util & "') " and sectors.sect_area in ('" & sa1 & "','" & sa2 & "') "
order by sectors.sect_area, drawings.type"

regards.

Mateen Martin
[email protected]

David Cameron · July 17th, 2003, 12:45 AM

You haven't actually told us what the syntax error message is.

However the voices in my head they told me that the # around the column names is a problem. Try removing them.

regards
David Cameron

owain · July 17th, 2003, 03:17 AM

The #'s should only be used when you are testing for a date, not selecting it from the table. Try this SQL statement:

Code:

ssql = "SELECT drawings.date_recd, drawings.date_revd, drawings.type, " & _
"drawings.location, sectors.sect_area " & _
"FROM drawings, sectors " & _
"WHERE drawings.name = sectors.name " & _
"AND drawings.type IN ('" & plt & "', '" & util & "') " & _
"AND sectors.sect_area in ('" & sa1 & "', '" & sa2 & "') " & _
"ORDER BY sectors.sect_area, drawings.type"

If you wanted to add a condition to only select rows where the column drawings.date_recd was after 1st January 2000 then you could include " AND drawings.date_recd > #2000-01-01#" to the query.

Regards
Owain Williams

mateenmohd · July 20th, 2003, 02:42 PM

Thanks for your response.
this query display all records.
I want that if I select sect_area only,
what query will write in asp, I have to retrieve
records from two tables.

regards.

Mateen

Quote:

quote:Originally posted by owain
The #'s should only be used when you are testing for a date, not selecting it from the table. Try this SQL statement:

Code:

ssql = "SELECT drawings.date_recd, drawings.date_revd, drawings.type, " & _
"drawings.location, sectors.sect_area " & _
"FROM drawings, sectors " & _
"WHERE drawings.name = sectors.name " & _
"AND drawings.type IN ('" & plt & "', '" & util & "') " & _
"AND sectors.sect_area in ('" & sa1 & "', '" & sa2 & "') " & _
"ORDER BY sectors.sect_area, drawings.type"

If you wanted to add a condition to only select rows where the column drawings.date_recd was after 1st January 2000 then you could include " AND drawings.date_recd > #2000-01-01#" to the query.

Regards
Owain Williams