Results 1 to 14 of 14

Thread: Query question

  1. #1
    Join Date
    May 2012
    Posts
    33

    Unanswered: Query question

    Hi.

    What is wrong with this query:

    PHP Code:
    SELECT Main.[IssueID], Main.[ProjectID], Main.[MainSystem], Main.[SubSystem], Main.[Component], Main.[IssueHeader], Main.[ReportedBy], Main.[ReportedDate], Main.[IssueDescription], Main.[IssueConsequence], Main.[IssueFix], Main.[SolvedBy], Main.[SolvedDate], Main.[Attachment], Main.[Attachment].[FileData], [Attachment].[FileFlags] AS Expr1Main.[Attachment].[FileName], [Attachment].[FileTimeStamp] AS Expr2Main.[Attachment].[FileType], [Attachment].[FileURL] AS Expr3
    FROM Main
    WHERE 
    (Main.ProjectID=[Forms]![Reporting]![Combo28]); 
    I cant find any rows running this query even if i have same value in Combo28 as I do in main table ProjectID... I use office 2010!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    First question, I guess, would be whether or not the Form named Reporting is Open when the Query is being Run?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    May 2012
    Posts
    33
    Yes it is open. I run the query from this form.

    I attach the whole project so you can see.
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what datatype is Main.ProjectID.. if its string then you need to qualify the [Forms]![Reporting]![Combo28]); with either ' or " so the SQL runtime knows where the value starts and stops

    for legibility I'd ditch all the square brakcets, unless you have to use them becuase you have a space in table or column names

    Main.[Attachment].[FileData] looks suspicious to me

    ..as does
    [Attachment].[FileFlags]
    looks like you are intending to join a table but haven't declared a join

    what I'd suggest you do right now is cut and paster your query from the form/report and paste it into the query browser SQL pane and work on it until you get the results you expect. replace the forms!.... expression with a known good value.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2012
    Posts
    33
    Im not sure i I follow you. You are right the combo is declared as text. How can I use ' or " in this query?

    I am able to use input from forms when it is other controls like textboxes or checkboxes etc.. But not combo's. The text boxes are decleared as memo

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no its the datatype of the column in the table

    if its numeric then
    Code:
    "select .... WHERE Main.ProjectID=" & Forms!Reporting!Combo28
    if its string
    Code:
    "select... WHERE Main.ProjectID='" & Forms!Reporting!Combo28 & "'"
    or
    Code:
    strSQL = "SELECT Main.IssueID, Main.ProjectID, Main.MainSystem, Main.SubSystem, 
    Main.Component, Main.IssueHeader, Main.ReportedBy, Main.ReportedDate, Main.IssueDescription,
    Main.IssueConsequence, Main.IssueFix, Main.SolvedBy, Main.SolvedDate, Main.Attachment, 
    Main.Attachment.FileData, Attachment.FileFlags AS Expr1,
    Main.Attachment.FileName, Attachment.FileTimeStamp AS Expr2,
     Main.Attachment.FileType, Attachment.FileURL AS Expr3 
    FROM Main 
    WHERE Main.ProjectID = '" & Forms![Reporting]![Combo28]& "';"
    its easier to debug SQL if you assign it to a variable then use that variable where ever
    text in red is deffo incorrect syntax in Access SQL. that style of qualificartion woudl suggest ta database of main, a table of attachement and a column of whatever
    text in blue is also wrong but suggests that there is a table called attachement which isn't declared in a join
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2012
    Posts
    33
    Thanks for input. The result is the same.

    I use this query now:
    PHP Code:
    SELECT *
    FROM Main
    WHERE Main
    .ProjectID '" & Forms![Reporting]![Combo28]& "'
    Try yourself.

    Any more tip?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the value of
    Code:
    Forms![Reporting]![Combo28]& "';
    what is the value of your SQL
    ..place a watch/breakpoint on the line of code that generates the error and then step through the code,examining what values are being set
    https://www.google.co.uk/#hl=en&scli...w=1920&bih=995

    alternatively assign the SQL to a variable and then display it using, say a msgbox
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your problem is that you have stored the project name in Main.ProjectID. your combo box is using ProjectID as its source but returning the ID.. a numeric value. so there will never be a match between, say 'Ensco Riog 72' (Main.ProjectID) and 1 ProjectID.ID (from combo28)
    .
    It look like you are using a lookup table rather than a foreign key to the project table
    ..which isn't that surprising as you have no relationships defiend

    The lookup wizard in Access is an abomination that should never ever be used unless you have a totally static range of values. it always causes problems in the end.

    I'd strongly recommend that you tidy up your table names and column names before proceeding further (its not necessary but it makes reading your code/tables easier). All tables and columns should describe what they contain. in this case you have a table called ProjectID, which should probably be called Projects.Try to avoid repeating the table name in the column names... why it gets tortuous
    select Projects.ProjectName, Projects.ProjectNo from Projects

    understand realtionships before designing a live db
    Fundamentals of Relational Database Design -- r937.com
    The Relational Data Model, Normalisation and effective Database Design
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2012
    Posts
    33
    Sorry im new to ms access. How do I debug SQL in MS acces and how do I add a watch/breakpoint.

    If I leave the Forms in the query like this: Main.ProjectID= Reporting!Combo28, then an popup appares and when I type in the text manually, then it will find the record.

    I suspect it has something to do with type declaration.. I have no idea how to solve this. I would be very thankfull if someone tested my project and found the solution to how to send the combo selection into the query and make it work...

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry im new to ms access. How do I debug SQL in MS acces and how do I add a watch/breakpoint.
    see reference in post #8

    You need to get to grips with the relational model
    see references in post #9

    your problem right now is the physical design of the system, get that right and you can then move on user interface design. don't worry about combo boxes or whatever until you have got your table design sorted out.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    May 2012
    Posts
    33
    So you are saying I sending the ProjectID.ID and not the ProjectID.ProjectName to the query?

    Is it an easy way of sending the ProjectName instead of ID? Its just this little mistake to solve before my database is finish!!! I dont have time to spend days learning the fundamentals correctly

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes its easily enough done, but its the wrong way

    you should store the ID of the project in table Main.
    using the Access lookup wizard is not a smart call. its fine if you know the full range of values at design time (otherwise each time you add a new project then you need to make changes tot he application NOT the data.

    you are part way there by having separate tables for Project and so on, but your use of lookup wizard makes those tables redundant

    you haven't defined any relationships (a fairly serious omission in a relational database)

    the quick fix is to use the text value from the combo box in the query, but its the wrong solution.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    May 2012
    Posts
    33
    Thanks. I found a solution. Its not the best way i guess. The idea about this db was to have 1 table and the other tables just as lookup for comboboxes. hence no relationships needed. Then filter with query and creating report from query. I will read up on database design so I get more understanding on how to build this professionally.

    Thanks!

Posting Permissions

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