FindFirst Problem

[Jeefgeorge]

I am setting up a module to export a query to excel. I want 2 ways to export
the query. The first way works....from a form the query and export is run
from a command button - the query is filtered based on the ProjNo field on
the form. The second way I would like to run the export from the
switchboard. Currently I open the form (eventually open hidden but the code
should run either way), ask the user for a Project Number and use the
..FindFirst method to navigate to that project. From here I believe I can run
the same code that is performed by the command button on the form.

I have two test records in my database for development purposes. The two
projects numbers are "Test-0001" which is the format of the actual project
numbers (????99-0000) and "12345". When I run the code, if I type either of
these or "0001" I get a NoMatch. Why does the module not find the record?
Does it matter that my on load, my form goes to a new record?


Code:
Public Sub ExportEstimate()
'On Error GoTo Err_Export
Dim Dsktp, Tmplt, Msg, Proj As String, CurUser As Object, Warn As Integer
DoCmd.OpenForm "GenerateEstimate", acNormal ', , , , acHidden
Proj = InputBox("Enter Project Number")
If Not IsNull(Proj) Then
If Len(Proj) = 4 Then
With Forms![GeneratEestimate].RecordsetClone
.FindFirst "Right([ProjNo],4) =" & """ & Proj & """
If .NoMatch Then
MsgBox "No Project Found"
Else
Forms![GeneratEestimate].Bookmark = .Bookmark
End If
End With
Else
With Forms![GeneratEestimate].RecordsetClone
.FindFirst "[ProjNo] =" & """ & Proj & """
If .NoMatch Then
MsgBox "No Project Found"
Else
Forms![GeneratEestimate].Bookmark = .Bookmark
End If
End With
End If
'Run Query and Export Code Here, uses Dsktp, Tmplt, Msg, CurUser, Warn
End If
Exit_Export:
Exit Sub
'Err_Export:
'MsgBox Err.Description
'Resume Exit_Export
End Sub

 

[Jeefgeorge]

I fixed it.....

I replced
.FindFirst "Right([ProjNo],4) =" & """ & Proj & """
with
.FindFirst "Right([ProjNo],4) =" & Chr(34) & Proj & Chr(34)
AND
.FindFirst "[ProjNo] =" & """ & Proj & """
with
.FindFirst "[ProjNo] =" & Chr(34) & Proj & Chr(34)

 

[Marshall Barton]

I fixed it.....

I replced
.FindFirst "Right([ProjNo],4) =" & """ & Proj & """
with
.FindFirst "Right([ProjNo],4) =" & Chr(34) & Proj & Chr(34)
AND
.FindFirst "[ProjNo] =" & """ & Proj & """
with
.FindFirst "[ProjNo] =" & Chr(34) & Proj & Chr(34)

That is certainly a valid way to do that so no need to make
any other changes.

Your original problem was that you did not have the correct
number of quotes. The basic rule of putting a quote inside
quotes is to use two quotes where you want one quote in the
result.

Note that Chr(34) is the same as """" so you could have
solved the problem by using:
.FindFirst "[ProjNo] =" & """" & Proj & """"

You could also have combined the first set of quotes into
the initial part of the expression:

.FindFirst "[ProjNo] =""" & Proj & """"