Help with Syntax

[Superock]

I am creating an sql string, so far all is good except for this part,

If Me![cboSPOC] <> "" Then
sCriteria = sCriteria & " AND qryMain.tblSPOC!PrimeFirstName & ' ' & tblSPOC!PrimeLastName AS [SPOC (Single Point of Contact)] Like """ & cboSPOC & "*"""
End If

It is supposed to utelize the combonation name, instead it requests me to enter values for PrimeFirstName and then PrimeLastName. I know my syntax must be wrong, pls help

 

[Superock]

Solution Found

Well I have noticed that I have had quite a few view and no replies, is this a difficult one....

Anyway I also wanted to update on my own progress.

I replaced the whole line with
sCriteria = sCriteria & " AND qryMain.SPOC Like """ & cboSPOC & "*"""

Where SPOC is the "As" name. Is there any advantage, disadvantage to this. Also I used to have this line:
sSql = "SELECT DISTINCT tblAssets.AssetID, tblDepartment.ExecutiveName, tblSPOC!PrimeFirstName & ' ' & tblSPOC!PrimeLastName AS [SPOC (Single Point of Contact)]..........Continues

and replaced with:

sSql = "SELECT DISTINCT tblAssets.AssetID, tblDepartment.ExecutiveName, qryMain.SPOC................Continues

Although this works I do not know why or what problems may occur with it.

Could someone explain to me what I have done??

 

[modest]

It isn't a difficult problem.. I think most of us are having trouble understanding what you want and what you're doing.

It's obvious that there are some errors, but how can we give you directions if we don't know where you're going?

 

[Superock]

All of it

Sorry, I did not mean to be vague.

I am building an sql string in vb, when I run the string I am prompted to enter values for the fields (I have marked these in red) that are supposed to be concatenated. I believe that I am using incorrect syntax and need some guidance on this.

I did some experimentation and found that I am able to replace tblSPOC!PrimeFirstName & ' ' & tblSPOC!PrimeLastName AS [SPOC (Single Point of Contact)] with qryMain.SPOC and then it works.

What I would like to know is where did I make the mistake in my syntax because I am sure I will be faced with this same problem again and then I would also like to know what are the consequences of replacing the direct reference with the qryMain.SPOC reference?

Here is the code I liberated from another example and modified to suit my need.

Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "


If Me![cboExec] <> "" Then
sCriteria = sCriteria & " AND qryMain.ExecutiveName like """ & cboExec & "*"""
End If

If Me![cboSPOC] <> "" Then
sCriteria = sCriteria & " AND qryMain.SPOC Like """ & cboSPOC & "*"""
End If

If Me![cboCategory] <> "" Then
sCriteria = sCriteria & " AND qryMain.AssetCategory = """ & cboCategory & """"
End If

If Me![cboAcquisition] <> "" Then
sCriteria = sCriteria & " AND qryMain.Acquisition like """ & cboAcquisition & "*"""
End If

If Nz(DCount("*", "qryMain", Right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
sSql = "SELECT DISTINCT tblAssets.AssetID, tblDepartment.ExecutiveName, tblSPOC!PrimeFirstName & ' ' & tblSPOC!PrimeLastName AS [SPOC (Single Point of Contact)], tblSPOC.PrimePhone, tblSPOC.[SPOC Backup Name and Phone No], tblSPOC.BusinessGroup, tblOfficeConnect.OCConverted, tblOfficeConnect.OCcDate, qryMain.[Delivery Address], tblDepartment.BankingGroup, tblOfficeConnect.BUID, tblEmployees.FirstName, tblEmployees.LastName, tblAssets.ServiceTag, tblAssets.AssetTag, tblAssetCategory.AssetCategory, tblAssets.Make, tblAssets.Model, tblAcquisition.Acquisition, tblAssets.DateAcquired, tblAssets.LeaseEndDate, tblCostCentre.CostCentre, tblPlatform.Platform, tblAssets.SerialNumber, tblOfficeConnect.POnumber, tblAssets.LDSNumber, tblOfficeConnect.OCRefreshRequired, tblAssets.RefreshRequired, tblSchedule.Comments from qryMain " & sCriteria
Forms![frmAssetMain]![fsubAssetMain].Form.RecordSource = sSql
Forms![frmAssetMain]![fsubAssetMain].Form.Requery
Else
MsgBox "The search failed find any records" & vbCr & vbCr & _
"that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
End If



Am I making sense??