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

Running Parameter Query Via A Form and Encountering Problems

elak6
P: 21
I am trying to run the query below, but i keep on getting syntax error in query expression.
I dont know exactly what i have done wrong and any help to put me on the right path will be very much appreciated

Expand|Select|Wrap|Line Numbers
  1. SELECT TESTER.[Internal Part Number], TESTER.Type, TESTER.[Part Type], TESTER.[RoHS Compliant], TESTER.[Termination Finish], TESTER.[Surface Mount], TESTER.[Pk Reflow Temp (Cel)], TESTER.MSL, TESTER.[JESD-97 Code], TESTER.Revision, TESTER.Description, TESTER.[Requested Manufacturer], [TESTER].[Requested Part Number], TESTER.YTEOL, TESTER.[Life Cycle Stage], TESTER.[Board Name], TESTER.[Project Name], TESTER.Comments, TESTER.[Record Added Date]
  2. FROM TESTER
  3. WHERE (((Nz([TESTER.[Internal Part Number]],'')) Like Nz(Forms!dashboard![Internal Part Number],'*')) And ((Nz([TESTER.[Part Type]],'')) Like Nz(Forms!dashboard![Part Type],'*')) And ((Nz([TESTER.Type],'')) Like Nz(Forms!dashboard!Type,'*')) And ((Nz([TESTER.[RoHS Compliant]],'')) Like '*' & Nz(Forms!dashboard![RoHS Compliant],'*') & '*') And ((Nz([TESTER.[Termination Finish]],'')) Like Nz(Forms!dashboard![Termination Finish],'*')) And ((Nz([TESTER.Revision],'')) Like Nz(Forms!dashboard!Revision,'*')) And ((Nz([TESTER[.Requested Manufacturer]],'')) Like Nz(Forms!dashboard![Manufacturer Name],'*')) And ((Nz([[TESTER].[Requested Part Number]],'')) Like Nz(Forms!dashboard![Manufacturer Part Number],'*')) And ((Nz([TESTER.[Life Cycle Stage]],'')) Like Nz(Forms!dashboard![Life Cycle Stage],'*')) And ((Nz([TESTER.[Board Name]],'')) Like Nz(Forms!dashboard![Board Number & Revison],'*')));
  4. ;
Mar 5 '12 #1

✓ answered by NeoPa

If you were to tidy the SQL code up before posting (so that it's more than just words vomited together by some system - and readable), then you'd probably have noticed some of the problems yourself.

Try the following and see what you get :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Internal Part Number]
  2.      , [Type]
  3.      , [Part Type]
  4.      , [RoHS Compliant]
  5.      , [Termination Finish]
  6.      , [Surface Mount]
  7.      , [Pk Reflow Temp (Cel)]
  8.      , [MSL]
  9.      , [JESD-97 Code]
  10.      , [Revision]
  11.      , [Description]
  12.      , [Requested Manufacturer]
  13.      , [TESTER].[Requested Part Number]
  14.      , [YTEOL]
  15.      , [Life Cycle Stage]
  16.      , [Board Name]
  17.      , [Project Name]
  18.      , [Comments]
  19.      , [Record Added Date]
  20. FROM   [TESTER]
  21. WHERE ((Nz([Internal Part Number],'') Like Nz(Forms!dashboard![Internal Part Number],'*'))
  22.   AND  (Nz([Part Type],'') Like Nz(Forms!dashboard![Part Type],'*'))
  23.   AND  (Nz([Type],'') Like Nz(Forms!dashboard!Type,'*'))
  24.   AND  (Nz([RoHS Compliant],'') Like '*' & Nz(Forms!dashboard![RoHS Compliant],'*') & '*')
  25.   AND  (Nz([Termination Finish],'') Like Nz(Forms!dashboard![Termination Finish],'*'))
  26.   AND  (Nz([Revision],'') Like Nz(Forms!dashboard!Revision,'*'))
  27.   AND  (Nz([Requested Manufacturer],'') Like Nz(Forms!dashboard![Manufacturer Name],'*'))
  28.   AND  (Nz([Requested Part Number],'') Like Nz(Forms!dashboard![Manufacturer Part Number],'*'))
  29.   AND  (Nz([Life Cycle Stage],'') Like Nz(Forms!dashboard![Life Cycle Stage],'*'))
  30.   AND  (Nz([Board Name],'') Like Nz(Forms!dashboard![Board Number & Revison],'*')))

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
If you were to tidy the SQL code up before posting (so that it's more than just words vomited together by some system - and readable), then you'd probably have noticed some of the problems yourself.

Try the following and see what you get :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Internal Part Number]
  2.      , [Type]
  3.      , [Part Type]
  4.      , [RoHS Compliant]
  5.      , [Termination Finish]
  6.      , [Surface Mount]
  7.      , [Pk Reflow Temp (Cel)]
  8.      , [MSL]
  9.      , [JESD-97 Code]
  10.      , [Revision]
  11.      , [Description]
  12.      , [Requested Manufacturer]
  13.      , [TESTER].[Requested Part Number]
  14.      , [YTEOL]
  15.      , [Life Cycle Stage]
  16.      , [Board Name]
  17.      , [Project Name]
  18.      , [Comments]
  19.      , [Record Added Date]
  20. FROM   [TESTER]
  21. WHERE ((Nz([Internal Part Number],'') Like Nz(Forms!dashboard![Internal Part Number],'*'))
  22.   AND  (Nz([Part Type],'') Like Nz(Forms!dashboard![Part Type],'*'))
  23.   AND  (Nz([Type],'') Like Nz(Forms!dashboard!Type,'*'))
  24.   AND  (Nz([RoHS Compliant],'') Like '*' & Nz(Forms!dashboard![RoHS Compliant],'*') & '*')
  25.   AND  (Nz([Termination Finish],'') Like Nz(Forms!dashboard![Termination Finish],'*'))
  26.   AND  (Nz([Revision],'') Like Nz(Forms!dashboard!Revision,'*'))
  27.   AND  (Nz([Requested Manufacturer],'') Like Nz(Forms!dashboard![Manufacturer Name],'*'))
  28.   AND  (Nz([Requested Part Number],'') Like Nz(Forms!dashboard![Manufacturer Part Number],'*'))
  29.   AND  (Nz([Life Cycle Stage],'') Like Nz(Forms!dashboard![Life Cycle Stage],'*'))
  30.   AND  (Nz([Board Name],'') Like Nz(Forms!dashboard![Board Number & Revison],'*')))
Mar 5 '12 #2

elak6
P: 21
Hi NeoPa,

Thanks for helping me realise my errors
The code runs perfectly now :)
Thanks for all your help,
Really appreciate

Regards,
elak6
Mar 6 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
That format is a good one for a predefined QueryDef where you want it to handle anything entered, as well as to allow for items to remain unspecified. If you're building the SQL up in your VBA code though, it's easier to include them only if they're required (which you can determine before you create the SQL).
Mar 6 '12 #4

Post your reply

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