netsys
Posts: 39
|
| Posted: 09/28/2011, 9:35 AM |
|
I have a CCDLookup that is erroring out with the following error:
Source: CCDLookUp function
Command Text: SELECT author FROM forum_messages WHERE message_id=
Error description: Syntax error (missing operator) in query expression 'message_id='. (Microsoft JET Database Engine)
The lookup is this:
messageID1 = CCDLookup("max(message_id)", "forum_messages", "message_id_parent=" &_
DBforum.ToSQL(forum_messages.TextBox1.Value, ccsInteger) , DBforum)
updatedby = CCDLookup("author", "forum_messages", "message_id=" & messageID1, DBforum)
The problem is only when messageID1 encounters a blank item in the column. Not all of the items have a message_id_parent entry, and if they don't, I get the Syntax error.
It seems like this should be a matter of using an If-else statement, but I can't quite figure out where to do this. Any ideas?
|
 |
 |
datadoit
|
| Posted: 09/28/2011, 9:56 AM |
|
You need " AND updated_by..."
|
|
|
|
datadoit
|
| Posted: 09/28/2011, 9:58 AM |
|
Read it wrong. Look at the IFNULL() function for your SQL. It'll set a
default value if it encounters NULL records.
|
|
|
|
netsys
Posts: 39
|
| Posted: 09/28/2011, 11:35 AM |
|
I found an example of a SQL statement with the IFNULL in the statement. I'm having trouble getting it to work.
Would it be something like this?
SELECT message_id, message_id_cat, message_id_parent, message_title, author, message_desc, date_add, (IFNULL(message_id_parent,0)), CatID, Cat_Desc
FROM forum_messages INNER JOIN forum_categories ON
forum_messages.message_id_cat = forum_categories.CatID
|
|
|
|
datadoit
|
| Posted: 09/28/2011, 12:27 PM |
|
Fiddle around between the ISNULL, IFNULL, NVL and COALESCE functions to
see what works in your environment.
http://www.w3schools.com/sql/sql_isnull.asp
|
|
|
|
|
