3 Replies - 953 Views - Last Post: 19 November 2009 - 08:41 AM Rate Topic: -----

#1 HodgesHTI  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 11-November 09

SQL Query Problem

Post icon  Posted 11 November 2009 - 08:44 AM

SELECT dbo_passengers.intF_pass_id AS PASS_ID, dbo_passengers.strF_first_name AS FIRST_NAME, dbo_passengers.strF_last_name AS LAST_NAME, dbo_passengers.dteF_birthdate, dbo_BillingCodes.Name

FROM (dbo_passengers LEFT JOIN dbo_Pass_Elig ON dbo_passengers.intF_pass_id = dbo_Pass_Elig.Passenger_ID) LEFT JOIN (dbo_BillingCodes RIGHT JOIN dbo_BillCodeElig ON dbo_BillingCodes.BillCode_ID = dbo_BillCodeElig.BillCode_ID) ON dbo_Pass_Eligibility_ID = dbo_BillCodeElig.Eligibility_ID

WHERE (((dbo_passengers.dteF_birthdate)<#10/1/1944#) AND ((dbo_BillingCodes.Name)="MAR"));


I am getting an invalid objects error. I think it has something to do with the birthday in
WHERE (((dbo_passengers.dteF_birthdate)<#10/1/1944#) AND ((dbo_BillingCodes.Name)="MAR"));
Any ideas?

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Query Problem

#2 HodgesHTI  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 11-November 09

Re: SQL Query Problem

Posted 11 November 2009 - 12:12 PM

So I think the big problem is that this query is actually taken from an access database output into SQL code. the DB used to run off of an Access DB and now it is MSSQL. Could it be that the formatting of the birthdate field has changed on the SQL side. Unfortunately, the client is pulling these queries from a local DB on somebodies desktop and they are trying to actually query the live DB. (Obviously whatever modifications to the query are made so that is is pulling from Live DB.)

Any thoughts?
Was This Post Helpful? 0
  • +
  • -

#3 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: SQL Query Problem

Posted 11 November 2009 - 12:57 PM

Try changing this line:
WHERE (((dbo_passengers.dteF_birthdate)<#10/1/1944#) AND ((dbo_BillingCodes.Name)="MAR"));



To this:
WHERE (((dbo_passengers.dteF_birthdate)<'10/1/1944') AND ((dbo_BillingCodes.Name)='MAR'));



Hopethat helps :)
Was This Post Helpful? 0
  • +
  • -

#4 HodgesHTI  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 11-November 09

Re: SQL Query Problem

Posted 19 November 2009 - 08:41 AM

View PostPsychoCoder, on 11 Nov, 2009 - 11:57 AM, said:

Try changing this line:
WHERE (((dbo_passengers.dteF_birthdate)<#10/1/1944#) AND ((dbo_BillingCodes.Name)="MAR"));



To this:
WHERE (((dbo_passengers.dteF_birthdate)<'10/1/1944') AND ((dbo_BillingCodes.Name)='MAR'));



Hopethat helps :)



I was thinking the same type of conversion figuring that it was just the Access-SQL change. Now I receive an invalid syntax error at the semicolon.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1