MS Access - What is wrong with this query?

try this and see the result :
Select tr_id, tr_routename, tr_from ,tr_to, tr_departtime, tr_status from travel where tr_id = 241514

Regards,

Bm Keshav

That query returns one row. The data is there for record 241514, i can guarentee you that.

Your data table structure is not clear, is tr_id is in each row ?

thanks
bm Keshav

tr_routename is a Memo field
tr_departtime is a DateTime field
tr_id is a Numeric field

Each row has a unique tr_id

The tr_id 241514 DOES exist in the table, and there IS a valid date value for every single row in the table. Some tr_routename fields will be null.

Isnt that all the information you should require?

in plain english, what do you want to see?

at least one row returned - not an error message

did you try the query posted by Bm Keshav?

did you get any result?

Yes, as i said; "That query returns one row. The data is there for record 241514, i can guarentee you that."

so, your problem is solved?

*sigh*

No, my problem is not resolved. No offence mate, but read the posts carefully because im just repeating whats already been established in this post;

My original question still stands. What BMKESHAV asked me was whether the subquery was returning any rows, which it was. So that does not answer my question, that just eliminates one of the reasons why it could be failing.

The problem is syntax, because the data is all there. There is a syntactical problem with my query. Whether its because im not handling the data types correctly, or im missing brackets or other characters is the question.

the problem is you are using this part in your where clause

{ where tr_routename = (Select tr_routename from travel where tr_id = 241514) and datevalue(tr_departtime) = (Select datevalue(tr_departtime) from travel where tr_id = 241514) }

you don't need sub-query to limit the result of the query

this the correct syntax

Select tr_id, tr_routename, tr_from ,tr_to, tr_departtime, tr_status from travel  where tr_id = 241514)

oops, sorry

this the correct syntax

Select tr_id, tr_routename, tr_from ,tr_to, tr_departtime, tr_status from travel  where tr_id = 241514

But that syntax is exactly the same is what i have already... Can you please post the entire query including the subqueries?

If you look at my original query closely - i am not just wanting to return the row for record 241514.

What i want is to return all rows where the tr_routename field is equal the the tr_routename field of id 241514 AND the datevalue of tr_departime is equal the the datevalue of record 241514

post sample data and the expected result..

That is not nessassary, i have already explained how the table is laid out and i have already explained what my expected result is.

I dont have time for this. The question is simple. Read the posts carefully because all of the required information has been given by me.

if this query is returning only one record


Select tr_id, tr_routename, tr_from ,tr_to, tr_departtime, tr_status from travel  where tr_id = 241514


then i can not help you any longer... good luck

You dont seem to understand. It returns one row, and i use the routename and departtime fields from that result in order to find other rows. The tr_id field is unique, so of course its only going to return one row. But, using the routename and date of that row, i can find other rows.

Read this very carefully and look at my original query;

What i want is to return all rows where the tr_routename field is equal the the tr_routename field of id 241514 AND the datevalue(tr_departime) is equal the the datevalue(tr_departime) of record 241514

So if the tr_routename of is 241514 is 'TestRoute' and the datevalue of tr_departtime for that row is '12/May/2009'

Then the query would effectively be "give me all rows where the tr_routename is 'TestRoute' and the datevalue(tr_departtime) is '12/May/2009'

<I dont have time for this>  

me too.

First of all try to put the value of tr_routename and tr_departtime in query and see the result
Select tr_id, tr_routename, tr_from ,tr_to, tr_departtime, tr_status from travel where tr_routename = "xyz" and tr_departtime ="valid deptime"
if its giving correct result then create a seperate query like this

Select tr_id, tr_routename, tr_departtime from travel where tr_id=241514
save this query as query1
now create a final query query2 like this
Select tr_id, tr_routename, tr_from ,tr_to, tr_departtime, tr_status from travel inner join query1 on query1.tr_id=travel.tr_id
save this as query2 and run this query and see the result

Bm Keshav

The only piece of information i was reluctant to give was sample data, and that was only because i had already given the table structure and it was clear to me that he didnt even understand what the query was trying to achieve and so i figured that despite the fact that i appreciated his help maybe he wasnt the right expert for my question. I knew that someone else would come along and understand what im trying to do and what the problem was based on the information i already gave, so i would have rather waited for that. I was running out of time i had already spent too much on the question. I admit i could have been a bit nicer, but questions were being asked that were already answered and i was running out of time and i just got frustrated. Im sorry if i caused offense.

The reason that the fields are memo fields and not text is because the mdb database was imported from sql server.