Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-26-2019, 08:42 AM   #1
ThirtyCal
Newly Registered User
 
Join Date: Aug 2019
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
ThirtyCal is on a distinguished road
Query statement syntax help

Can someone tell me where i messed up this conversion from the query builder SQL to VBA.



Code:
        Dim Descriptionqryidkey As String
        Descriptionqryidkey = "SELECT Itemstbl.ItemIDKey " & vbCrLf & _
        "FROM ItemsNotestbl RIGHT JOIN Itemstbl ON ItemsNotestbl.key = Itemstbl.key " & vbCrLf & _
        "WHERE (((ItemsNotestbl.txt) Like ""*"" & me.ItemNotesWildcardtxt.value & ""*""));"
        Debug.Print Descriptionqryidkey
        DoCmd.OpenForm "itemdatafrm", , , "[ItemIDKey]=" & Descriptionqryidkey

ThirtyCal is offline   Reply With Quote
Old 08-26-2019, 08:47 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,689
Thanks: 50
Thanked 1,078 Times in 1,059 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Query statement syntax help

Hi. If you're building a SQL statement, you don't want/need to use/add vbCrLf in it.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 08-26-2019, 08:50 AM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,426
Thanks: 68
Thanked 2,709 Times in 2,594 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Query statement syntax help

firstly you don't need vbCRLf on the query string.

next the Where parameter should not be a Query string. it should be a number that will filter the form on ItemIDKey.

if your intention is to use the DescriptionqryIDKey as recordsource of the form:



DoCmd.OpenForm "itemdatafrm"
Forms("Itemdatafrm").Form.Recordsource=Description qryidkey

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-26-2019, 09:27 AM   #4
ThirtyCal
Newly Registered User
 
Join Date: Aug 2019
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
ThirtyCal is on a distinguished road
Re: Query statement syntax help

No my intention is to open the form and filter by itemIDkey based on the results of a query not change the record source. The query performs a wild card search of the notes for each item. each item may have several "notes" records in a "notes" table linked by a key. Here is the original SQL statement from the query builder.



Code:
SELECT Itemstbl.ItemIDKey
FROM ItemsNotestbl RIGHT JOIN Itemstbl ON ItemsNotestbl.key = Itemstbl.key
WHERE (((ItemsNotestbl.txt) Like "*" & [Forms]![CrossReferencefrm]![ItemNotesWildcardtxt] & "*"));
ThirtyCal is offline   Reply With Quote
Old 08-26-2019, 09:46 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,367
Thanks: 87
Thanked 1,644 Times in 1,526 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Query statement syntax help

Both Arnel and theDBguy have told you to leave out the vbCrLf but perhaps it is not clear why.

Code:
Descriptionqryidkey = "SELECT Itemstbl.ItemIDKey " & vbCrLf & _
        "FROM ItemsNotestbl RIGHT JOIN Itemstbl ON ItemsNotestbl.key = Itemstbl.key " & vbCrLf & _
        "WHERE (((ItemsNotestbl.txt) Like '*' & me.ItemNotesWildcardtxt.value & '*'));"
First, the items in red are neither required nor desired. It is like the difference between how a number is stored and how a number is displayed. The vbCrLf makes that string usable for display formatting (i.e. showing the actual SQL string) but interferes with the query parser (an internal program user) for the string for query purposes.

Second, the items in green lead to questions. Do you actually have a field name TXT in ItemsNotestbl? Do you actually have a field named KEY in Itemstbl and ItemsNotestbl? And I presume you were looking at the value of a control for that reference to .Value?

KEY is listed as an Access reserved word so should not be used as a field name. You can use TXT (but not TEXT) as a field name, I suppose. There CAN be value in brevity. Just be careful to not use TEXT in that place. In the place where you used .VALUE it is legal but not needed because for any control that actually HAS a value, .VALUE is the default property if you don't specify a property.

As to the quoting, you can simplify the asterisk inclusion as shown in blue.

I'm not entirely sure about the locations of the parentheses, though it is obvious that the Access query builder was involved. Whenever you see too many parentheses, the query builder was there.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-26-2019, 10:20 AM   #6
ThirtyCal
Newly Registered User
 
Join Date: Aug 2019
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
ThirtyCal is on a distinguished road
Re: Query statement syntax help

Thanks I will give everyones suggestions a try. I know the field names are an issue but I am dealing with a access97 database that was nothing but tables to begin with. I will change them but I have so much code tied up in these old carry overs I am hesitant to do anything. I should have changed everything at the beginning like I wanted to. I was busy importing excel files as well that used the same names and still have many to go.
ThirtyCal is offline   Reply With Quote
Old 08-26-2019, 10:33 AM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,426
Thanks: 68
Thanked 2,709 Times in 2,594 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Query statement syntax help

if there are multiple ItemIDKeys that were return, you need to loop through the recordset:
Code:
        Dim strIDs As String
        Dim rs As DAO.Recordset

        Dim Descriptionqryidkey As String
        Descriptionqryidkey = "SELECT Itemstbl.ItemIDKey " & _
        "FROM ItemsNotestbl RIGHT JOIN Itemstbl ON ItemsNotestbl.key = Itemstbl.key "  & _
        "WHERE (((ItemsNotestbl.txt) Like ""*"" & me.ItemNotesWildcardtxt.value & ""*""));"
        Debug.Print Descriptionqryidkey

        set rs = Currentdb.Openrecordset(Descriptionqryidkey, dbOpenSnapshot)
        with rs
             If Not (.BOF And .EOF) Then .MoveFirst
             While Not .EOF
                  strIDs = strIDs & ![ItemIDKey] & ","
                  .MoveNext
             Wend
             .Close
        End With
        Set rs=Nothing
        If strIDs <> "" Then
            strIDs = Left(strIDs, Len(strIDs)-1)
            DoCmd.OpenForm "itemdatafrm", , , "[ItemIDKey] In (" & strIDs & ")"
        Else
            'no filter
            DoCmd.OpenForm "itemdatafrm"
        End If

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-26-2019, 10:45 AM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,163
Thanks: 15
Thanked 1,576 Times in 1,498 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Query statement syntax help

What no one has noticed is that you are trying to use a select query in your OPEN form command. That is not possible.

You can pass in a query string using the OpenArgs argument but you would need code in the form or report to place that SQL string into the form/report's RecordSource.

The where argument of the openForm method is only the WHERE clause without the WHERE.

If I understand what you are trying to do, and I may not, you will need to use a DLookup() to run a query to get the string you want and then just use the string returned by the dLookup() in the WHERE argument
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-26-2019, 01:05 PM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,367
Thanks: 87
Thanked 1,644 Times in 1,526 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Query statement syntax help

Good catch, Pat.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-26-2019, 01:50 PM   #10
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,276
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Query statement syntax help

Yes, good catch, Pat.

However, semantics might confuse. DLookup would reference a query object (and I guess that can be considered as 'running' it). So the SELECT SQL would have to be a saved query object (unfiltered) that DLookup could pull from (filter in the DLookup). Otherwise, the SELECT would have to be used to open a filtered recordset object and reference field of recordset to get the ItemIDKey,
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 08-26-2019, 01:58 PM   #11
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,388
Thanks: 112
Thanked 2,849 Times in 2,597 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Query statement syntax help

I don't believe you need to save the query
Save the DLookup value as a variable then use that value in the 'WHERE' clause.
Job done!?

EDIT: Just realised Pat said that previously
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-26-2019, 04:33 PM   #12
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,163
Thanks: 15
Thanked 1,576 Times in 1,498 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Query statement syntax help

Since there is a join, the select query will need to be saved so the dLookup() can reference the query:

Descriptionqryidkey = dLookup("ItemIDKey", "yourqueryname", "txt Like """*""" & me.ItemNotesWildcardtxt & ""*"")

I never do quotes this way since I can never get the number of " for each condition correct. I always create a constant and use that. So if you get an error, fix the number of ".
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-26-2019, 05:17 PM   #13
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,276
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Query statement syntax help

I use apostrophes:
Code:
Descriptionqryidkey = DLookup("ItemIDKey", "yourqueryname", "txt Like '*" & me.ItemNotesWildcardtxt & "*'")
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 08-26-2019, 06:22 PM   #14
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,426
Thanks: 68
Thanked 2,709 Times in 2,594 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Query statement syntax help

see post #7.
you are only returning 1 record when using dlookup.
there is a possibility that it might return more records since the criteria is not an exact match.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-26-2019, 10:12 PM   #15
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,163
Thanks: 15
Thanked 1,576 Times in 1,498 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Query statement syntax help

Quote:
I use apostrophes:
Except that in comments or people's names, the string can contain an apostrophe and that will make the query fail. Try it. Add a name like O'Brien to a query that encloses the search string in apostrophes.

Good point arnel but thirtycal would have had the same problem with his original version had it worked. It is a logic problem that needs to be solved regardless before we can proceed to a real solution.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Average IF statement-syntax error (missing operator) in a query expression mato61 Queries 8 06-20-2014 04:20 AM
syntax on update query-WHERE statement starrcruise Queries 6 03-02-2012 06:32 AM
syntax error -- Case statement in Query dai_lo Queries 3 01-17-2008 07:07 AM
Iif statement syntax help twitchie Reports 1 10-12-2005 09:24 PM
SQL Statement Syntax dudezzz Modules & VBA 1 07-26-2005 03:10 PM




All times are GMT -8. The time now is 07:43 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World