By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

Syntax Error in Query Expression

P: 3
here is my SQL statement:

Expand|Select|Wrap|Line Numbers
  1. SELECT [SPO Data Entry Table].[SPO#]
  2.      , [Standard Brk].*
  3.      , [Standard SubBrk].*
  4. FROM  ([SPO Data Entry Table]
  5.        INNER JOIN
  6.        [Standard Brk]
  7.   ON   [SPO Data Entry Table].[SPO#] = CDBL([Standard Brk.[SPO#]]))
  8.        INNER JOIN
  9.        [Standard SubBrk]
  10.   ON   [Standard Brk].[Test #] = [Standard SubBrk].[Unit No]
when trying to run the query I get the syntax error in query expression within the FROM clause: [SPO Data Entry Table].[SPO#] = CDBL([Standard Brk.[SPO]]))

those two fields are different types
spo data entry table.SPO# = autonumber
Standard Brk.SPO = text
Sep 8 '11 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,429
If you look at the reference passed to CDBL() in line #7 you'll see :
Expand|Select|Wrap|Line Numbers
  1. [Standard Brk.[SPO]]
The closing bracket (]) that should be after Standard Brk is found after the closing bracket of [SPO] instead. Did you not look at the error message? It's pretty clear from that what the problem is.
Sep 8 '11 #2

P: 3
Thank you for your help....this is new to me. I have made the changes you suggested. I am now getting a type mismatch in experssion

What am I doing wrong?

Expand|Select|Wrap|Line Numbers
  1. SELECT [SPO Data Entry Table].[SPO#]
  2.      , [Standard Brk].*
  3.      , [Standard SubBrk].*
  4. FROM  ([SPO Data Entry Table]
  5.        INNER JOIN
  6.        [Standard Brk]
  7.   ON   [SPO Data Entry Table].[SPO#] = CDBL([Standard Brk].[SPO#]))
  8.        INNER JOIN
  9.        [Standard SubBrk]
  10.   ON   [Standard Brk].[Test #] = [Standard SubBrk].[Unit No]
Sep 8 '11 #3

NeoPa
Expert Mod 15k+
P: 31,429
[SPO Data Entry Table].[SPO#] is an AutoNumber field (IE. Long), but you're comparing it to a value returned from CDbl(). Use Int() or CLng() instead.
Sep 8 '11 #4

P: 3
I am now getting an Type mismatch error. I have used both Int() and CLng() and get the same error message for both. I appreciate all the help. Here is what my SQL statement looks like now.

Expand|Select|Wrap|Line Numbers
  1. SELECT [SPO Data Entry Table].[SPO#]
  2.      , [Standard Brk].*
  3.      , [Standard SubBrk].*
  4. FROM   ([SPO Data Entry Table]
  5.        INNER JOIN
  6.        [Standard Brk]
  7.   ON   [SPO Data Entry Table].[SPO#] = CLng([Standard Brk].[SPO#]))
  8.        INNER JOIN
  9.        [Standard SubBrk]
  10.   ON   [Standard Brk].[Test #] = [Standard SubBrk].[Unit No]
Sep 12 '11 #5

NeoPa
Expert Mod 15k+
P: 31,429
I just used the time I had available fixing your post. Such a waste of your time. Please read [code] Tags Must be Used before posting again.
Sep 12 '11 #6

NeoPa
Expert Mod 15k+
P: 31,429
If you're still getting a Type Mismatch error then I would assume that both of your comparisons (Lines #7 & #10) originally had problems. Line #7 should be alright now, which leaves the obvious question :
What are the Types of the two fields referenced on line #10 ([Test #] & [Unit No])?
Sep 12 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.