Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Location
    Baltimore, Maryland.. It's a big place you won't be able to find me!
    Posts
    15

    Unanswered: Data type Mismatch

    Hello All,

    Perhaps you will remember me from the "passing criteria" thread.

    now I have a new problem.

    I can the get the parameters to the query but I get a data type mismatch.

    The parameters are passed to the query as "parmameterx".

    Should I not include the quotes when I string the sql statement together is vba?

    Any help would be appreciated!

    Thanks agian!

    Joe

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    It would definately help to see the SQL statement but in any case, a string should be enclosed in single or double quotes:

    Single Quotes:
    "SELECT * FROM myTableName WHERE [myFieldName]='" & VariableOrMe.Control & "'"

    OR

    "SELECT * FROM myTableName WHERE [myFieldName]='My Data'"


    Double Quotes:
    "SELECT * FROM myTableName WHERE [myFieldName]=""" & VariableOrMe.Control & """

    OR

    "SELECT * FROM myTableName WHERE [myFieldName]="""MyData"""
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Mar 2006
    Location
    Baltimore, Maryland.. It's a big place you won't be able to find me!
    Posts
    15
    Here is the sql for the query I am attemping to run..

    SELECT Community_tbl.Community_Code, Community_tbl.Community_Name, Trade_Partner_Products_Tbl.Vendor_Id, Trade_Partners_Tbl.Vendor_Name, Trade_Partner_Products_Tbl.Phase, Options_Tbl.Option_Description, Trade_Partner_Products_Tbl.Price, [price]*[quanity] AS Total, House_Type_tbl.House_Name, Take_Offs_Tbl.House_Code, Take_Offs_Tbl.Cost_Code, Take_Offs_Tbl.Option_Number, Take_Offs_Tbl.Product, Take_Offs_Tbl.Quanity
    FROM Community_tbl, Options_Tbl INNER JOIN ((House_Type_tbl INNER JOIN Take_Offs_Tbl ON House_Type_tbl.House_Code = Take_Offs_Tbl.House_Code) INNER JOIN (Trade_Partners_Tbl INNER JOIN (Phases_Tbl INNER JOIN Trade_Partner_Products_Tbl ON Phases_Tbl.Phase = Trade_Partner_Products_Tbl.Phase) ON Trade_Partners_Tbl.Vendor_ID = Trade_Partner_Products_Tbl.Vendor_Id) ON Take_Offs_Tbl.Product = Trade_Partner_Products_Tbl.Product) ON Options_Tbl.Option_Number = Take_Offs_Tbl.Option_Number
    WHERE (((Community_tbl.Community_Code)="BLK") AND ((Trade_Partner_Products_Tbl.Phase)="363"))
    ORDER BY Take_Offs_Tbl.House_Code, Take_Offs_Tbl.Option_Number;




    On my where clauses community_code is a text field

    also phase is a long integer.

    However I am passing the whole thing to the query via querydef as a string. Would that make a differnce since "phase" is a long integer??

    Thanks for the help!

    Joe

  4. #4
    Join Date
    Mar 2006
    Location
    Baltimore, Maryland.. It's a big place you won't be able to find me!
    Posts
    15
    Got it to work.. I had to change the field type for phase to a text field in all the tables. Anyway it works now...

    thanks

    Joe

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    You didn't have to change your tables:

    WHERE (((Community_tbl.Community_Code)='BLK') AND ((Trade_Partner_Products_Tbl.Phase)=363))
    ORDER BY Take_Offs_Tbl.House_Code, Take_Offs_Tbl.Option_Number;


    should have done the trick.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •