Results 1 to 8 of 8
  1. #1
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39

    Not getting record to return from table with my query


    Hi, I have an access database with a table 'Projects' that has fields 'ProjectID' and "Type' among others and I'm trying to write a function that I can use to query the table in Excel and request whatever field info I want for a record. I've been querying fine when I'm selecting all the records, but I'm not sure what's wrong with my syntax for selecting just a single record based on its key (ProjectID).

    Here is the query written in VBA:

    SQLStr = "SELECT " & returnvalue & " FROM " & table & " WHERE '" & recordlabel & "' = '" & recordid & "';"

    Can someone point me to what's wrong with this? I am using values specifically from the table I'm querying but it's not returning a value I know exists.

    Thanks

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can you provide substitute data for the variables and re-write that? returnvalue reads like it's criteria, which it cannot be. recordlabel looks like it's a field name yet you wrap it in single quotes. Neither of those things makes sense to me. Or you can try building a query as normal and see if you get what you want, then convert that to something that uses the variables instead. Just watch out for things like text that needs delimiting and dealing with any field or table names that contain spaces.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Quote Originally Posted by Micron View Post
    Can you provide substitute data for the variables and re-write that? returnvalue reads like it's criteria, which it cannot be. recordlabel looks like it's a field name yet you wrap it in single quotes. Neither of those things makes sense to me. Or you can try building a query as normal and see if you get what you want, then convert that to something that uses the variables instead. Just watch out for things like text that needs delimiting and dealing with any field or table names that contain spaces.
    returnvalue is just the name of the field I want to get. Sorry my naming scheme isn't conventional, I'm new to this. And yeah the single quotes is a mistake I guess.. I was getting a "data type mismatch in criteria experssion" error that was fixed when I wrapped it in single quotes so I guess there is something else wrong. Here is what it would look like (with single quotes fixed on recordlabel)

    SQLStr = "SELECT size FROM Inventory WHERE itemid = '100';"

  4. #4
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Figured it out. Here is working query:

    SQLStr = "SELECT " & table & "." & "[" & recordlabel & "], " & table & "." & "[" & returnvalue & "]" & " FROM " & table & " WHERE (((" & table & ".[" & recordlabel & "]) = " & recordid & "));"

    recordid is a number so it shouldn't have had quotes

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Thanks for posting the solution - you can mark this thread as solved. To get more focused answers, do provide details. Your original post never mentioned an error message.

    It's kind of odd to be using variables for table and field names for a simple select query I'd say. FWIW you could shorten it a bit
    Code:
    SQLStr = "SELECT " & table & ".[" & recordlabel & "], " & table & ".[" & returnvalue & "] FROM " & table & " WHERE (((" & table & ".[" & recordlabel & "]) = " & recordid & "))"
    or maybe even
    Code:
    SQLStr = "SELECT T.[" & recordlabel & "], T.[" & returnvalue & "] FROM " & table & " AS T WHERE T.[" & recordlabel & "] = T.[" & recordid & "]"
    Not sure why you have square brackets in only part of your string, so I put them in throughout. If they're not needed at the end as you have it, they shouldn't be needed at the beginning.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @pharmacologist

    Quote Originally Posted by pharmacologist View Post
    SQLStr = "SELECT size FROM Inventory WHERE itemid = '100';"
    Quote Originally Posted by pharmacologist View Post
    SQLStr = "SELECT " & table & "." & "[" & recordlabel & "], " & table & "." & "[" & returnvalue & "]" & " FROM " & table & " WHERE (((" & table & ".[" & recordlabel & "]) = " & recordid & "));"
    Be aware that "size" and "table" are reserved words in Access and shouldn't be used for object names. The Access Gnomes can and will mess with your mind and code when reserved words are used as object names!!

    See Problem names and reserved words in Access

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @ssanfu - you are quite right. I might have been remiss in just thinking that what was written was pseudo code. We get a lot of that, don't we?
    I took "table" etc, to be placeholders for things we're not supposed to know about.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @ Micron - right you are - sometimes too much pseudo code.
    But in this case, in Post #4, pharmacologist wrote that he/she had a "working query" with"table" as a variable name. Just trying to save them some headaches........

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Nested Query, Return most recent record
    By kaylachris in forum Queries
    Replies: 2
    Last Post: 03-21-2019, 08:14 PM
  2. Replies: 2
    Last Post: 10-01-2015, 10:10 AM
  3. Replies: 3
    Last Post: 08-02-2015, 12:42 AM
  4. Query to return name if no record found
    By jrosen12 in forum Queries
    Replies: 1
    Last Post: 12-18-2014, 06:32 PM
  5. Return Record # In Query
    By redwinger354 in forum Access
    Replies: 1
    Last Post: 09-15-2007, 01:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums