SQL as RecordSource

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When a user hits a cmd button, I want the change the recordsource of the
form but it is based on criteria entered by the user. It says I have syntax
error. I think it is about using the quotations marks (i.e. "Robert") in the
syntax. How do I get around this? Thanks for any help.

strSQL = "SELECT [Employment CrossMatch].*, [Employment CrossMatch].SendBack
FROM [Employment CrossMatch]WHERE ((([Employment
CrossMatch].ESDStaffName)="Robert")) OR ((([Employment
CrossMatch].SendBack)="FAX"));"

Form.RecordSource = strSQL
 
use single quotes ' when you are already inside a double-quoted string

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
In addition to Crystal's correct answer, I just wanted to question why
you've explicitly added [Employment CrossMatch].SendBack to the SQL, given
that you've already got [Employment CrossMatch].*

That means you're going to have the SendBack column appearing twice, once
named something like Expr1.
 
Thanks, Doug!

I also see that you are missing a space before the WHERE keyword...

To expand on what Doug said, you don't need SendBack to show twice

on the grid for yur source...

field --> SendBack
table --> [Employment CrossMatch]
Show --> No (instead if yes)
(uncheck the Show box in the grid)

SendBack is implicitly implied in

[Employment CrossMatch].*

and, since you used as criteria, it also appear in another column, but
that should not show

'~~~~~~~~~~~~
OR, if you are constructing the "filter" from form controls...


instead of
Form.RecordSource = strSQL

.... apply a filter...

'~~~~~~~~~~~~
dim strFilter as string
strFilter = "(ESDStaffName ='" & me.name_filter_controlname _
& "') OR (SendBack = 'FAX')"
me.filter = strFilter
me.requery
'~~~~~~~~~~~~

if me.name_filter_controlname = "Robert"
then

strFilter = "(ESDStaffName = 'Robert' OR ...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Back
Top