SQL Statement in Access 97

[SmithVoice]

Up to you but you might want to get in the habit of being more specific in your SQL table mentions (as shown below with full referencing) Also, you don't need to include the final semi-colon. If you make this SQL into querydef without the semicolon you will see that Access adds it in the SQLview (like Access adds the hidden carrage returns between clauses) automatically.

Also up to you but I use chr(34) instead of embedded quotes, this adds typing up front but it also stops most quote related errors.

Um, I'm making the assumption that there is a single table named "[Open Position]" right?

So, try this and let me know if it gets any farther:

dim strSQL as string
dim rs as recordset

strSQL = "SELECT * FROM [Open Positions] " & _
"WHERE ([Open Positions].[HospitalCode] = " & chr(34) & strHospital & chr(34) & _
") AND ([Open Positions].[Division/Unit] = " & chr(34) & strUnit & chr(34) & _
") AND ([Open Positions].[Location] = " & chr(34) & strLocation & chr(34) & _
") AND ([Open Positions].[Job Number] = " & chr(34) & strJob & chr(34) & ")"

set rs = db.openrecordset (strSQL, dbopenforwardonly)

------------------
http://www.smithvoice.com/vbfun.htm

I know this is a VB forum, but I would appreciate any help with debugging this code in Access 97:

strSQL = "SELECT * FROM [Open Positions] " _
& "WHERE [HospitalCode] = '" & strHospital & "' _
AND [Division/Unit] = '" & strUnit & "' _
AND [Location} = '" & strLocation & "' _
AND [Job Number] = '" & strJob & "';"

[JHausmann]

You might want to consider aliasing, as well. I'm not sure what the length of the SQL statement can be in Access 2000 but, with large tables you can run out of "runway". I would change SmithVoice's SQL to:

strSQL = "SELECT * FROM [Open Positions] op" & _
"WHERE op.HospitalCode = " & chr(34) & strHospital & chr(34) & _
" AND op.[Division/Unit] = " & chr(34) & strUnit & chr(34) & _
" AND op.Location = " & chr(34) & strLocation & chr(34) & _
" AND op.[Job Number] = " & chr(34) & strJob & chr(34)


Some other things, and these will be a matter of personal preference.

1) Only use letters and numbers when creating tables/fields. Doing so allows you to avoid the brackets, which I believe makes SQL statements *harder* to read.

2) Only use brackets when you absolutely have to, again because their usage makes SQL statements harder to read.

3) Use the "as" construct if you use bound controls or simple reports that you want to change what is shown in the headers.

4)Use parenthesis for logic only.

Again, YMMV.

[VorTechS]

Hi Carla, or at least the tables kinda indicate it's you!

Well most people seem to have mentioned the fact that there is only one table...only no-one ACTUALLY spotted the real problem...

The problem is here: -

'AND [Location} = '

It's the curly brace..... that was it, that was the only problem with your SQL statement.

Regards and Hi's..
Stu..