select query, w/string

[Trevor]

I am using a select quer that is supose to compare status in table[Pswrdtbl] to a dlookupstring (ststatus) and [Name] in the same table to Me.[Tech. Name] on the form and this isn't working,

Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [PsWrdTbl] WHERE" _
& " [Status]= '" & ststatus & "' AND [Name]= '" & Me.[Tech. Name]";")

thanks for helping

[Tommy]

Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [PsWrdTbl] WHERE" _
& " [Status]= '" & ststatus & "' AND [Name]= '" & Me.[Tech. Name]"';")

I thinkits because there is a ' missing at the end. :)

[matthewspatrick]

Tommy, think you missed an ampersand:



Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [PsWrdTbl] WHERE" _
& " [Status]= '" & ststatus & "' AND [Name]= '" & Me.[Tech. Name] & "';")


:)

[Trevor]

Thank you, guys, I can't tell yet if its woring because I have more work to do, but I just tried to get the recordcount for the select query with a text box to check the # to varify the query is working, and the recor count &will also end up playing a role in this but I have tried to get the record count frod the select by Me.[test] = recordset.recordcount (placed directly under the query and the textbox stays blank.

[Trevor]

So I am using:

Dim qryDB As DAO.Database
Dim loRst As DAO.Recordset
With loRst
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [PsWrdTbl] WHERE" _
& " [Status]= '" & ststatus & "' AND [Name]= '" & Me.[Tech. Name] & "';")
Me.[Test] = loRst.RecordCount
End With
Set loRst = Nothing
loRst.Close

to select the records with the status value by tech name on the form and try to get the record count of the query and my [test] texbox is staying blank when I run this

[Trevor]

I think this work

Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [PsWrdTbl] WHERE" _
& " [Status]= '" & ststatus & "' AND [Name]= '" & Me.[Tech. Name] & "';")

it weird, my lorst.recordset.count statment works but won't populate the textbox, I changed the textbox name and it populates , but I perform a selectt quey on a name that doen't exist , instead of the recordcount returning "0" it the textbox I have setup to test stays blank, but I guess that doesn't realy matter concidering that the texbox is only for validation durring testing, (sorry if my thinking outloud confused anyone , just wanted to give an update as to what was going on