Form field not interpreted correctly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

upsman - 08 Sep 2007 04:29 GMT

I have a field on a form, that for the purpose of my test, contains the value
"HSN". I have the following code in the OnOpen event of a completely
different form:

Dim stLinkCriteria As String
Dim stCampus

MsgBox "campus = " & Forms!Switchboard!txtCampus

stLinkCriteria = "SELECT * FROM Classes WHERE Campus = " & Forms!Switchboard!
txtCampus
Me.RecordSource = stLinkCriteria

When the form opens, the msgbox says "campus = HSN". This is correct. But
when it gets to the stLinkCriteria statement, an 'Enter Parameter Value' box
pops up and asks for me to enter a value for the field "HSN". Why does it
think "HSN" is a field and not the value in the field? I want to select all
records where Classes.Campus matches whatever value is in Forms!Switchboard!
txtCampus. How do I do that?

Rod

Reply to this Message

Ken Snell (MVP) - 08 Sep 2007 04:36 GMT

Delimit text value HSN with ' characters:

stLinkCriteria = "SELECT * FROM Classes WHERE Campus = '" &
Forms!Switchboard!txtCampus & "'"

Signature

Ken Snell
<MS ACCESS MVP>

>I have a field on a form, that for the purpose of my test, contains the
>value
[quoted text clipped - 24 lines]
>
> Rod

Reply to this Message

Ed Metcalfe - 08 Sep 2007 04:37 GMT

>I have a field on a form, that for the purpose of my test, contains the
>value
[quoted text clipped - 24 lines]
>
> Rod

Try:

stLinkCriteria = "SELECT * FROM Classes WHERE Campus = " & """" &
Forms!Switchboard!
txtCampus & """"

Ed Metcalfe.

Reply to this Message

upsman - 08 Sep 2007 17:52 GMT

Thanks guys, that worked. When and where to use "" still confuses me.

>>I have a field on a form, that for the purpose of my test, contains the
>>value
[quoted text clipped - 9 lines]
>
>Ed Metcalfe.

Reply to this Message

Ed Metcalfe - 08 Sep 2007 19:49 GMT

> Thanks guys, that worked. When and where to use "" still confuses me.

Different data types need to be "delimited" or "qualified" with different
characters. In MS Access these are:

Text/String - A single or double quote.
Numeric - No qualifier needed.
Date/Time - # symbol.

Your SQL statement was giving you problems as the value contained in your
textbox is a string but you were not including the text qualifier. So what
should have been:

SELECT * FROM Classes WHERE Campus = "HSN"

was actually:

SELECT * FROM Classes WHERE Campus = HSN

Ed Metcalfe.

Reply to this Message