building sql string

[bbxrider]

nothing seems to work in the where clause, see error at end of post, 3075 syntax error, missing operator



I build the query string below and eventually execute with Set rst = dbs.OpenRecordset(strSQL1) which is where I get the error

strSQL1 = "select transaction_history.[Activity Type] " & _
" from transaction_history " & _
"where [transaction_history]![Activity Type] = " & _
"""a value to match"""

note the above resolves to:
select transaction_history.[Activity Type] from transaction_history where [transaction_history]![Activity Type] = "someValue"

I have tried all these where clauses:
where [transaction_history]![Activity Type] =
where transaction_history.[Activity Type] = (note this syntax seems to be fine in the select clause)
where [Activity Type] =

[bbxrider]

and I just tried using the same syntax that the query builder comes up with
for the where clause and it gives the same 3075 error

SELECT transaction_history.[Activity Type]
FROM transaction_history
WHERE (((transaction_history.[Activity Type])="someValue"));

[June7]

What data type is [Activity Type] field?

Don't forget the semicolon at end of statement.

Try:

strSQL1 = "SELECT transaction_history.[Activity Type] FROM transaction_history " & _
"WHERE [transaction_history]![Activity Type] = 'a value to match';"

If you want to reference data control for parameter:

strSQL1 = "SELECT transaction_history.[Activity Type] FROM transaction_history " & _
"WHERE [transaction_history]![Activity Type] = '" & Me.controlname & "';"

[bbxrider]

thanks, got it working, even without the ; and with a selected value from a listbox control
very embarrassing had a line code that messed up the string will be using it eventually as part of the logic when
I build a where with dynamic number of 'or' criteria from list box, it should have been commented out for now
'strSQL1 = Left$(strSQL1, Len(strSQL1) - 12)

but now have new problem, not sure whether to continue this post or start a new
I have to search a substring of the table field based on where there is a period in the field,
so using a mid with instr, which I am using successfully in another access query but sql not liking it here
new string
strSQL1 = "select transaction_history.[Activity type], transaction_history.[detail], " & _
" from transaction_history " & _
"where mid(transaction_history.[Detail],InStr(transaction_history.[Detail],period)+1) = " & _
"'campaign_list.ItemData(varItem)';"


I used the 'period' variable to get by all the quotes within quotes to specify the search for a period - .
dim period as string
period = "."
based on the error description it seems to be unhappy with the mid statement somewhere, and I presume I'm using mid here because of ms as opposed to using substring

[June7]

Concatenate variables, don't include them within quotes. Even with the variable to hold dot (.), still need text delimiters. I find using apostrophe as delimiter best.
Also extraneous comma after [detail].

strSQL1 = "select transaction_history.[Activity type], transaction_history.[detail] from transaction_history " & _
"where mid(transaction_history.[Detail],InStr(transaction_history.[Detail], '.')+1) = '" & campaign_list.ItemData(varItem) & "';"

[bbxrider]

thanks very much for keeping up with this, I finally gave up on the mid and am using like with wild cards, it codes so much easier and works ok for me.
so I have ended up building this string which worked until I added the inner join, and m.[email], not sure what syntax I need here. perhaps this needs a new post? with access sql I am never quite sure when to surround a column name with the [] V=brackets

select transaction_history.[Activity type], mid(transaction_history.[Detail],InStr(transaction_history.[Detail],'.')+1) as campaign, m.tier, transaction_history.[email] from transaction_history, [Master Email List Data Base] m INNER JOIN transaction_history ON m.Email = transaction_history.Email where transaction_history.[Detail] like '*Broker - nov campaign - Tier2>>O' OR transaction_history.[Detail] like '*Broker - Touch 2 - Tier2>>O' order by campaign, m.email;

the error I get on the Set rst = dbs.OpenRecordset(strSQL1) is below

[June7]

Never hurts to include the [] but they are necessary if names have spaces or special characters/punctuation (underscore is exception) or are reserved words.

Try:

strSQL1 = "SELECT transaction_history.[Activity type], Mid(transaction_history.[Detail],InStr(transaction_history.[Detail],'.')+1) as campaign, m.tier, transaction_history.[email] FROM [Master Email List Data Base] AS m INNER JOIN transaction_history ON m.Email = transaction_history.Email WHERE transaction_history.[Detail] Like '*Broker - nov campaign - Tier2>>O' OR transaction_history.[Detail] Like '*Broker - Touch 2 - Tier2>>O' ORDER BY Mid(transaction_history.[Detail],InStr(transaction_history.[Detail],'.')+1), m.email;"

[bbxrider]

thanks again for sticking with this, unfortunately still same 3074 error
why did you suggest the mid syntax in the order by instead of the named column "campaign"?
when I had it working without the inner join the "campaign" name seemed to work ok, at least
I could get a value by referencing it cycling thru the rs, this worked:
campaign = rst![campaign]


select transaction_history.[Activity type], mid(transaction_history.[Detail],InStr(transaction_history.[Detail],'.')+1) as campaign, m.[tier], transaction_history.[email] from transaction_history, [Master Email List Data Base] as m INNER JOIN transaction_history ON m.Email = transaction_history.Email where transaction_history.[Detail] like '*Broker - nov campaign - Tier2>>O' OR transaction_history.[Detail] like '*Broker - Touch 2 - Tier2>>O' order by mid(transaction_history.[Detail],InStr(transaction_history.[Detail],'.')+1), transaction_history.[email];

[bbxrider]

ok, finally, phew, got it
select transaction_history.[Activity type], mid(transaction_history.[Detail],InStr(transaction_history.[Detail],'.')+1) as campaign, m.[tier], transaction_history.[email] from [Master Email List Data Base] as m INNER JOIN transaction_history ON m.Email = transaction_history.Email where transaction_history.[Detail] like '*Broker - nov campaign - Tier2>>O' OR transaction_history.[Detail] like '*Broker - Touch 2 - Tier2>>O' order by mid(transaction_history.[Detail],InStr(transaction_history.[Detail],'.')+1), transaction_history.[email];

evidently when using inner join the table named in the inner join also qualifies as naming in the from, so it can't be named in the from, got the idea from this post:
http://office.microsoft.com/en-us/ac...080760180.aspx
I also created the functionality as close as possible using the create query tool to see what it generated for sql, it came up with:
SELECT transaction_history.[Date/Time (PST)] AS [dateTime], transaction_history.[Activity Type], transaction_history.Email, transaction_history.Detail, [Master Email List Data Base].Tier
FROM [Master Email List Data Base] INNER JOIN transaction_history ON [Master Email List Data Base].Email = transaction_history.Email
WHERE (((transaction_history.[Activity Type]) Like "send email" Or (transaction_history.[Activity Type]) Like "open email"))
ORDER BY transaction_history.[Activity Type];

so it was pretty clear from the above not to name the inner join table in the from,
this has been quite an education and thanks, thanks again for all your support