Query Issue

[firoz.raj]

The following inner querey return 9/9/2012 7:45:21 AM .
so simple i want all the records from OracleTradingSummaryDetail .which is matching with
SELECT Max(TradingSummaryStatus.UPDATEDTIME

but when i running the following query it is asking UpdatedTime as a parameter .why ? it needs to show all the matching rows .

SELECT *
FROM OracleTradingSummaryDetail
WHERE UPDATEDTIME=
(SELECT Max(TradingSummaryStatus.UPDATEDTIME) as LastSalesDate
FROM TradingSummaryStatus
WHERE Status<>'C');

[FunkyDexter]

I'm guessing this is Access. If so then Access doesn't recognise the field UpdatedTime on the OracleTradingSummaryDetail table so it think's you're referenceing a parameter. Check the spelling of all the field and table names you've used in the query.

[firoz.raj]

speeling is correct .I Modified a bit . because i need WeekNo,Status ... of Trading summary Status table .
but it does not show anything .which status is not c .means not close.

SELECT TradingSummaryStatus.YEAR, TradingSummaryStatus.WEEKNO, TradingSummaryStatus.UPDATEDTIME, TradingSummaryStatus.STATUS, *
FROM OracleTradingSummaryDetail INNER JOIN TradingSummaryStatus ON OracleTradingSummaryDetail.ID = TradingSummaryStatus.ID
WHERE (((TradingSummaryStatus.UPDATEDTIME)=(SELECT Max(TradingSummaryStatus.UPDATEDTIME) as LastSalesDate FROM TradingSummaryStatus WHERE Status<>'C')));

[FunkyDexter]

Is it still asking for UpdatedTime as a parameter or is it just not returning data?
And can you confirm that this is an Access database?

Also, it helps us see what's going on if you lay your sql out in a nice freindly way. The way you laid it out in the first post was perfect.

[firoz.raj]

And can you confirm that this is an Access database?

Yes it is again asking updated Time . oh yes i am using Ms Access Database .

[FunkyDexter]

Access assumes anything it doesn't recognise is a paramater. If it's asking for UpdatedTime then one of the references to UpdatedTime in your query is incorrect. In the query you posted last you reference UpdatedTime 3 times and every time it's from the TradingSummaryStatus table. That leaves 3 possibilities:-
1. There is no UpdatedTime field on the TradingSummaryStatus table
2. The field name is miss-spelt
3. You're not running the sql you've posted here.

[firoz.raj]

i want to insert the saleslevel1 as it is in a tradingsummary staus table for csaleslevel . as it is string datatype . but i am still getting error at red line .let me know please as sales level is string so i put in inverted comma. but still i am getting error . let me know please .

con.Execute "insert into tradingsummarystatus(BRANDID,BRANDDETAIL,GROUP_NO,WEEKNO,years,CSALESLEVEL) select brandid,brandDetail,groupno," & Val(CmbWeekNo.Text) & ",2012, "Saleslevel1" from brandmaster"

[naveed217]

It is better if you use variables for better readability!
The Correct syntax is:
dim strsql as string
strsql="insert into tradingsummarystatus(BRANDID,BRANDDETAIL,GROUP_NO,WEEKNO,years,CSALESLEVEL) Values(brandid,brandDetail,groupno," & Val(CmbWeekNo.Text) & ",2012, '" & salesLevel1 & "'"
con.Execute strsql

You cannot get saleslevel1 from brandmaster table while executing this command. Instead get it before in the string saleslevel1 and then execute it.
You are assuming WEEKNO and YEARS as numeric fields, isn't it?
I am in the process of uploading my supermarket project at download.cnet.com I will confirm that as soon as I do it.