Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Sntax SQL error using OpenRecordSet

Posted on 2003-11-09
4
Medium Priority
?
520 Views
Last Modified: 2010-08-05
What is wrong with this code?

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [E-mail address] FROM [Broker Contacts] where [E-mail address] is not null  and [Broker Contacts].[contact name]=[Forms]![Loan Information]![Contact Name]")

I Get ERROR 3061,  Too few Parameters, Expected one"

THEN

I tried this instead:
 Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [E-mail address] FROM [Broker Contacts] where [E-mail address] is not null and [Broker Contacts].[contact name]=" & Me("contact Name") & "")

I get ERROR 3075, Sntax error,  (missing operator) in query expression ' [E-mail address] is not null and [Broker Contacts].[contact name]=Rob Martin'.

Any ideas????
0
Comment
Question by:BonnyFreund
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 3

Accepted Solution

by:
Drizzt95 earned 500 total points
ID: 9711235
Try this instead:

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [E-mail address] FROM [Broker Contacts] where [E-mail address] is not null  and [Broker Contacts].[contact name]= '" & [Forms]![Loan Information]![Contact Name] & "'")
0
 
LVL 9

Assisted Solution

by:svenkarlsen
svenkarlsen earned 500 total points
ID: 9711256
Hi BonnyFreund,

When referring to [Contact Name], it need to be quoted in the string, - i.e.: a single quote must be put in front and behind the [Contact Name]:

 Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [E-mail address] FROM [Broker Contacts] where [E-mail address] is not null and [Broker Contacts].[contact name]='" & Me("contact Name") & "'")

Kind regards,
Sven
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9711647
Two points,

- Use chr(34) to handle single quete required  in the SQL syntax and single quote inside a name like O'Conner:

 Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [E-mail address] FROM [Broker Contacts] where [E-mail address] is not null and [Broker Contacts].[contact name]=" & chr(34) & Me("contact Name") & CHR(34))

- In case where you have a name but no email, it may exclude even without "[E-mail address] is not null and".  If you need on purpose to show missing emails for you to fill them in, above SQL need some more revisions.

Mike
0
 

Author Comment

by:BonnyFreund
ID: 9711673
Cheers to you both.  I knew it was something silly like a missing tick mark, I just was tired of guessing the syntax.  I appreciate both of your help!
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

647 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question