Urgent Help---Syntax Error in Query 1

[siebel2002]

Please Help..
I am getting a syntax error in date in query expression. Run time error 3075. the offending sql statement is as follows.

SELECT tblPortfolio.PortfolioName, tblPortfolio.Portfolio, tblTradeComments.*, tblSecurities.AssetID, tblSecurities.SecurityName, tblSecurities.TICKER FROM (tblTradeComments INNER JOIN tblSecurities ON tblTradeComments.AssetID = tblSecurities.AssetID) INNER JOIN tblPortfolio ON tblTradeComments.Portfolio = tblPortfolio.Portfolio Where tblPortfolio.PortfolioName = 'International ADR' And tblTradeComments.TradeDate Between #4/01/02# And #6/31/02#


I cant figure out where the heck is the error..

Thanks gurus!

rv

 

[BlackKnight]

Hi,
I assume Tradedate is a datetime field? Have you tried chasnging the format of the date like th following?

SELECT tblPortfolio.PortfolioName, tblPortfolio.Portfolio, tblTradeComments.*, tblSecurities.AssetID, tblSecurities.SecurityName, tblSecurities.Ticker
FROM (tblTradeComments INNER JOIN tblSecurities ON tblTradeComments.AssetID = tblSecurities.AssetID) INNER JOIN tblPortfolio ON tblTradeComments.Portfolio = tblPortfolio.Portfolio
WHERE (((tblPortfolio.PortfolioName)='International ADR') AND ((tblTradeComments.TradeDate) Between #4/1/2002# And #6/31/2002#));

Have a good one!
BK

 

[siebel2002]

Hi BK..

I am still getting the same error.. sorry

 

[BlackKnight]

Hi,

Hmmm..it worked correctly for me. Is TradeDate a date/time field?

Have a good one!
BK

 

[siebel2002]

It is a puzzle to me..

yes it is Date/Time data type

 

[LittleSmudge]

Okay - so forget the detail of the SQL code and go into a methodical debug mode.

Delete the entire WHERE clause and run it.
If it runs you've narrowed down where the problem is.

If it still faulters remove one of the linked table relationships and run it again.

Keep on cutting bits off until it runs then add LITTLE bits back on carefully until you find the problem.
Fix the problem then you can put all the rest back and test again.


'ope-that-'elps.

G LS

 

[archery]

It is NOT the query that is wrong but your "hard-coded June" date. June does NOT have 31 days in it.

 

[LittleSmudge]

WELL spotted Archery.

That's the sort of silly little error that the structures debug approach will find quickly but sitting staring at the code will take ages to pick up.

Have a star for knowing your calendar so well !


G LS

 

[siebel2002]

hi archery:

I am afraid that is not it.,. I get the same error ven after changing the date to any other date in June!

??

Have a nice day!