Results 1 to 15 of 15
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DAO-SELECT * FROM Only order by (Word97SR2)

    I am my wit's end - not a long way to go nowadays.

    Why doesn't the SELECT do what I want it to do?

    I want to get all the records for Alberta (prov =AB) and then all the records for BritishColumbia (prov=BC),
    and within each province I want to get members whose key is akin to "abrams", then "belushi" etc.

    It ought to be straightforward, yet the first record returned is from "ON"tario with a member key of "karen" or similar.

    'Only' is a query based on a table T1 of member details and a table T2 of donations.



    <pre> strSelect = "SELECT * FROM Only order by 'Prov ,T1-main.Mbr_key'"
    Set rst = U.rstOpenRecordset(dbs, strSelect)
    If rst.RecordCount > 0 Then
    With rst
    .MoveLast
    .MoveFirst
    MsgBox .RecordCount ' This returns the correct figure, so far so good .....
    strOldkey = .Fields("T1-main.Mbr_key")
    strRecord = ""
    strRecord = strRecord & .Fields("T1-main.Mbr_key") ' ?????
    strRecord = strRecord & vbTab & .Fields("Mbr_Prov") ' ?????
    </pre>


    I fully realise that approximately three seconds after clicking "PostIt" I shall be slapping myself in the forehead .....

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    Well, I may be revealing my deep understanding of sql, but

    select * from only (is only the name of the table??)
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    Try this as your Select String:

    "SELECT * FROM Only ORDER BY Prov, T1-main.Mbr_Key"

    You shouldn't need the single quotes around the ORDER BY clause.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    > Try this as your Select String:
    "SELECT * FROM Only ORDER BY Prov, T1-main.Mbr_Key"

    Bryan, Thanks for the suggestion. This is one of the ploys I tried before posting, which is why I was bashing my head against a wall.

    Running the SELECT above yields me "Run Time Error 3061 : Too few parameters, expected 3".


    I've spent about twice as much time on this as I would if I'd just dumped the records to a text file and summarized them in the old DOs PCFiledB.

    I'm sure it is something blatantly obvious.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    "Only" is the name of a query. Aren't we supposed to be using queries?

    I have two tables (Main and Donations) linked as a query LINK, which is the master query, so to speak.

    I have a query ONLY based on LINK which is Only records for Canada.

    This ought to be straightforward. The main problem is that i probably know less than you do (grin!)

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    I didn't know you could name a query after FROM. I thought the syntax
    required a table or view name.

    I'm just not keeping up with the times...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  7. #7
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    Chris,

    A couple of things that come to mind then:

    1) Prov, T1-main.Mbr_Key are not valid Field names in your query Only

    To make sure the field names are what you expect them to be, try this

    <pre>dim fld as DAO.Field
    dim strSQL as string
    dim rst as dao.recordset

    strSQL = "SELECT * FROM Only"
    Set rst = db.Openrecrodset(strSQL) ' where db is your DB connection

    for each fld in rst.fields
    debug.print fld.name
    next

    set rst = nothing</pre>


    Then look in the Immediate window for the field names. I have a feeling that the Query Only is not returning the fields you expect.

    2) Try adding the Order By fields one at a time. First Prov, then replace Prov with T1-main.Mbr_Key if the first order by comes out correctly. Then put them both in.

    3) Try using the QBE grid in Access. use that to get the results as you expect and then copy and paste the SQL from the QBE grid into your code.

    4) Failing all that, can you post a small sample of the tables, queries and code you are using and maybe that will shed some light.

    THat should keep your head banging for a while <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  8. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    One last thing just sprang to mind. Is Only a reserved word?

    Try renaming the query to something else and give it a try
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  9. #9
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    One last thing just sprang to mind. Is Only a reserved word?

    Try renaming the query to something else and give it a try
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  10. #10
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    Chris,

    I just went to MSDN, Microsoft Jet 4.0 ANSI Reserved Words specifically, and had a look at the reserved Keywords in Jet 4.0.

    Only is a reserved keyword, so THAT may be messing things up.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    <img src=/S/woops.gif border=0 alt=woops width=58 height=36> You've been asleep at the switch, Kevin. Queries have been legal sources for other queries as long as I can recall. A view is just a server version of a simple query. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  12. #12
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    Honestly, that was where I was heading, but I looked up Only in the Index of my (old) SQL Transact manual and didn't find it.

    Good Work!
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    > 1) Prov, T1-main.Mbr_Key are not valid Field names in your query Only. I have a feeling that the Query Only is not returning the fields you expect.


    In think this is close to the truth.

    I made a small experiment, renaming the table "T1-main" to be "T1Main". Removing the hyphen removed the objections from the VBA/SQL SELECT statement, but of course devastated the queries which were built on "T1-main".


    After sleeping on it, I started from scratch this morning, and called the tables "Main" and "Donations" (no hyphens in their names, right?) and quickly rebuilt the query LINK which joins those two tables, and bypassed the query ONLY.

    The VBA Select with Order by worked just fine, darn it.

    I have pasted the snippet below.



    > 2) Try adding the Order By fields one at a time. First Prov, then replace Prov with

    This I had tried earlier, using the old divide-and-conquer approach. It had not worked.





    <pre> Dim strSelect As String
    strSelect = "SELECT * FROM Link2 order by Mbr_Prov, Mbr_key"
    Set rst = U.rstOpenRecordset(dbs, strSelect)
    </pre>


  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    > I didn't know you could name a query after FROM.

    I thank God that no English Grammarians are trying to parse this (grin!)

    You can (use a Query). My understanding is that in Access Query is to Table as in VBA Range is to Selection.

    I like using Queries much better than Tables. I just am nowhere near as proficient as I'd like to be. It's a struggle for people of my intellect ....



    But also see my post to Bryan.



    I was most concerned that, on searching the Lounge, I didn't stumble across several other newbie posts demonstrating a similar problem.

    I have a vague feeling of un-ease about the hyphen in the table "T1-Main"; I have a sense that it tripped me up 6 months ago.

  15. #15
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO-SELECT * FROM Only order by (Word97SR2)

    Chris,

    I'm glad you figured it out.

    Now that you mention the -, it makes sense that the recordset would choke on the -

    For future reference, just wrap the tbl-Main in <!t>[ ]<!/t>'s. You would end up with a SQL statement of:

    "SELECT * FROM Only ORDER BY Prov, <!t>[T1-main]<!/t>.Mbr_Key"
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

Posting Permissions

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