ACCESS FEED: RE: [MS_AccessPros] FindFirst method syntax

Senin, 16 September 2013

RE: [MS_AccessPros] FindFirst method syntax

Hello Phucon

' rs.FindFirst "AcctId = " & "" & Me.txtsAcctID & ""

Two adjacent double-quotes (as highlighted) constitute an empty string. They are therefore adding nothing to the string you are constructing.

The rule in SQL is that to compare a text field value with a literal string, you must enclose ("delimit") the string in quotes. Access SQL allows you to use either single or double quotes as text delimiters. So, either of these will work:

AccID = "a101"
AccID = 'a101'

Since your literal string value is in a variable (in this case, the control value Me.txtsAcctID) you must construct the string by concatenating all the separate parts – these are:

1. /AccId = / (a literal string)

2. starting delimiter (quote)

3. Me.txtsAcctID (the variable value)

4. ending delimiter (quote)

You are constructing this string in VBA, so you must use VBA rules, which are:

1. Literal strings are delimited by double-quotes only

2. A double-quote which is part of a literal string is expressed as TWO adjacent double-quotes (many other languages, including SQL, use this method of doubling the delimiter)

3. Multiple strings are concatenated using the & operator

In VBA you can express quotes in strings as literal characters, or using the Chr() function.

A single-quote can be "'" (double-single-double) or you can use Chr(39)
A double-quote can be """" (4 x double) or you can use Chr(34)

So any of these should work – using single-quotes:

rs.FindFirst "AcctId = " & "'" & Me.txtsAcctID & "'"
rs.FindFirst "AcctId = '" & Me.txtsAcctID & "'" ' (the opening delimiter is part of the first literal)
rs.FindFirst "AcctId = " & Chr(39) & Me.txtsAcctID & Chr(39)

Or, using double-quotes:

rs.FindFirst "AcctId = " & """" & Me.txtsAcctID & """"
rs.FindFirst "AcctId = """ & Me.txtsAcctID & """" ' (the opening delimiter is part of the first literal)
rs.FindFirst "AcctId = " & Chr(34) & Me.txtsAcctID & Chr(34)

So, there you are – you have six different possibilities! Take your pick and have fun :-)

Best wishes,
Graham

_____

Graham Mandeno
Microsoft Access MVP 1996 - 2013

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of saigonf7q5@yahoo.com
Sent: Tuesday, 17 September 2013 10:26
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] FindFirst method syntax

Hello

The syntax in my FindFirst method (in orange color) generated this error:

Error 3070 (the Microsoft Access database engine does not recognize 'a101' as a valid field name or expression)

I thought it's correct as the other 2 (color in blue). If I remember correctly, I think I have seen it somewhere, either 1 is correct. Am I wrong?

Phucon

Private Sub cmdSave_Click()

On Error GoTo ErrorHandler

If IsNull(Me.txtsAcctID) Or IsNull(Me.txtsName) Then

MsgBox "Acct ID and Name cannot be left blank."

GoTo ExitProcedure

End If

Set db = CurrentDb

Set rs = db.OpenRecordset("tblPled", dbOpenDynaset)

'rs.FindFirst "AcctId = " & Chr$(34) & Me.txtsAcctID & Chr$(34)

rs.FindFirst "AcctId = " & "' & (Me.txtsAcctID) & '"

' rs.FindFirst "AcctId = " & "" & Me.txtsAcctID & ""

With rs

If .NoMatch Then

.AddNew

!sAcctID = Me.txtsAcctID

!sName = Me.txtsName

.Update

.Close

Else

MsgBox "Cannot save record. This Acct ID already exist.", vbExclamation, "Invalid Account ID"

GoTo ExitProcedure

End If

End With

ExitProcedure:

TempVars.Remove "DateIsBlank"

Exit Sub

ErrorHandler:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSave_Click"

Resume ExitProcedure

End Sub

[Non-text portions of this message have been removed]