Error in "Order by Clause" 2

[DrillMonkey]

Hi,
I am using a form with a combo box and a text box to accept a string to search 2 tables. I recive the following error

********************************
run-time error 3138
Syntax error in ORDER BY clause.
********************************
Row source type: Field List
Row source: see code

 SELECT [tblWarehouse].[Date], [tblWarehouse].[Signature], [tblWarehouse].[Driver], [tblPurchData].[Purchase Order], [tblPurchData].[Requisition], [tblPurchData].[Stores Order], [tblPurchData].[Ticket], [tblPurchData].[Memo Number], [tblPurchData].[Discription], [tblPurchData].[Parcels] FROM tblWarehouse INNER JOIN tblPurchData ON [tblWarehouse].[WarehouseID]=[tblPurchData].[WarehouseID];

Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        
        'Filter Form1 based on search criteria
        Form_ListFrm.RecordSource = "select * from tblWarehouse where " & GCriteria
        'Form_ListFrm.Caption = "tblWarehouse (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
        
        'Close frmSearch
        DoCmd.Close acForm, "frmSearch"
         'Open report
        DoCmd.OpenReport "rptWarehouse", acViewPreview, , GCriteria
        DoCmd.Maximize              
        'MsgBox "Results have been filtered."
        
    End If
    
End Sub

I think I am missing a ORDER BY Clause but am not sure which part of the row source it goes..Thank you

 

[DrillMonkey]

SELECT tblWarehouse.*, tblPurchData.* FROM tblWarehouse INNER JOIN tblPurchData ON tblWarehouse.WarehouseID = tblPurchData.WarehouseID;

 

[DrillMonkey]

I created that from the builder in the record source properties

 

[dhookom]

Open a query based on the provided sql to see if your records are what you would expect. Set the criteria in the query to school = "Lexington" and see if the query returns the appropriate number of records.

Does your report contain a subreport?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrillMonkey]

Yes the query returns the infromation correctly, not truncated. And no there are no subreports.

 

[DrillMonkey]

also I have no "criteria" in the query just raw data but displaying correctly

 

[dhookom]

I'm not sure why you mention "truncated" when you stated earlier "I should get 4 records.But instead I get 8, 4 mixed from an unrelated records".

Can you be more specific regarding your issues? Can you explain what you mean by "plus a truncated version that shares information from the parent table." Truncated generally suggests a value from a memo field displays less characters than are actually stored in the table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrillMonkey]

my record source for my report is wrong I suspect.

SELECT * FROM tblWarehouse, tblPurchData;

how do I set it for the sql query?

 

[dhookom]

Did you read the resource that PH suggested on SQL joins? What happened to your SQL you posted:

SELECT tblWarehouse.*, tblPurchData.* FROM tblWarehouse INNER JOIN tblPurchData ON tblWarehouse.WarehouseID = tblPurchData.WarehouseID;

I actually prefer to specify the field names so that there are not two WarehouseID fields in the report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrillMonkey]

I think I got it...used the query builder and set the record source from there...right?

 

[DrillMonkey]

Thats why it was truncated cause I used the wrong record source for my report...Duh

 

[dhookom]

Are your issues all resolved?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DrillMonkey]

Yes Duane,

I think we can close this thread many thanks!