Database field search trouble

[DK47]

I am having trouble getting my ado to bring up records from an SQL server data base based on a search for a perticular password.

I know the connection is good becuase I can get the ado to load text boxes when the form loads but when I code the record source to call up a record based on a known password, the ado dims out and no records return.

I have a feeling the ptoblem is syntax but I have used this structure before, with no trouble.

The password field in the table tblCust is nvarchar (100) and the passwords are input as numbers and letters.

Here is the code that I have tried.

Private Sub Command1_Click()
frmListing.adoAccess.RecordSource = "SELECT * FROM tblCust WHERE Password = '" & Text2.Text & "' "
frmListing.adoAccess.Refresh

End Sub


Any help will be appreciated.
Regards,
Dwight

 

[gjconely]

Add [] around password. Try using
..." WHERE [PASSWORD] LIKE '" & Text2.Text & "'"

Greg Conely

http://www.ImageMentor.net

 

[DK47]

Thanks Greg,
I'll give it a try.
Regards,
Dwight

 

[DK47]

Well i got my password function working but I don't understand why.

While this:

Private Sub Command1_Click()
frmListing.adoAccess.RecordSource = "SELECT * FROM tblCust WHERE Password = '" & Text2.Text & "' "
frmListing.adoAccess.Refresh

End Sub

Did not work. When I used the exact same code in the form_Load() it did work?????

Private Sub Form_Load()
frmListing.adoAccess.RecordSource = "SELECT * FROM tblCust WHERE Password = '" & Text2.Text & "' "
frmListing.adoAccess.Refresh

End Sub

I do not know why it would not work under the comand procedure. Anyone got any ideas?
Dwight

 

[AndyWatt]

Dwight,

As gjconely said:

Add [] around password. Try using
..." WHERE [PASSWORD] = '" & Text2.Text & "'"

"Password" is an SQL reserved word - you need to put it in square brackets otherwise you confuse it.

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"A computer program does what you tell it to do, not what you want it to do." -- Greer's Third Law

 

[DK47]

Andy,
Thank you very much.
Looks like the more I learn the more I find out I need to learn.

Regards,
Dwight

PS
Would it be considered practical to always include table fields in square brackets to insure against using reserved words for whatever server is being used?
DK