Solved

Sntax SQL error using OpenRecordSet

Posted on 2003-11-09
4
513 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
4 Comments
 
LVL 3

Accepted Solution

by:
Drizzt95 earned 125 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 125 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 33

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running sum query 6 32
DBF to ... Converter 5 43
Cross Tab with two column values 7 34
How autonumber field on a form 16 14
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now