Access 2000 SQL query

[mdmackillop]

I'm running the following code to create and run an SQL query

Option Compare Database
Option Explicit
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click
Dim SQL As String
SQL = ""
SQL = "SELECT [Contract Details].[Job No], [Contract Details].Date, [Contract Details].[Fee Code], [Contract Details].Client, [Contract Details].[Project Title], [Contract Details].[Contract Value], " & _
"comboJobStatus.Description " & vbCr & _
"FROM comboJobStatus RIGHT JOIN [Contract Details] ON comboJobStatus.Status = [Contract Details].Status " & vbCr & _
"WHERE ((([Contract Details].Client) Like " & """*""" & " &[Forms]![ContractListSelector]![txtClient]& " & """*""" & ") " & _
"AND ((comboJobStatus.Status)<>1) AND (([Contract Details].[Project Type])=[Forms]![ContractListSelector]![Combo0]));"

Debug.Print SQL
DoCmd.RunSQL SQL

Exit_Command4_Click:
Exit Sub
Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub



The result in the Debug window is:
SELECT [Contract Details].[Job No], [Contract Details].Date, [Contract Details].[Fee Code], [Contract Details].Client, [Contract Details].[Project Title], [Contract Details].[Contract Value], comboJobStatus.Description
FROM comboJobStatus RIGHT JOIN [Contract Details] ON comboJobStatus.Status = [Contract Details].Status
WHERE ((([Contract Details].Client) Like "*" &[Forms]![ContractListSelector]![txtClient]& "*") AND ((comboJobStatus.Status)<>1) AND (([Contract Details].[Project Type])=[Forms]![ContractListSelector]![Combo0]));

I can paste this result into a query as SQL and the query will run. When I run from the button I get:
"A RunSQL Action requires an argument consisting of an SQL statement."
What am I missing here?

[CreganTur]

Well... one issue is that you can't use Date as a field name- it's a reserved word in SQL (and VBA). Also, your strange like statement (Like " & """*""" &) is causing your SQL to pull &[Forms]![ContractListSelector]![txtClient] in as a literal string, instead of referencing your form value. I'm also guessing that txtClient and Combo0 are supposed to return string values, so I used the single quote data qualifier so that SQL knows to expect a String return value (if this is incorrect, then delete the qualifier).

There's also a little bloat code in there. Try this:
Option Compare Database
Option Explicit
Private Sub Command4_Click()

Dim SQL As String

SQL = "SELECT [Contract Details].[Job No], [Contract Details].Date, [Contract Details].[Fee Code], " _
& "[Contract Details].Client, [Contract Details].[Project Title], [Contract Details].[Contract Value], " & _
"comboJobStatus.Description " & vbCr & _
"FROM comboJobStatus RIGHT JOIN [Contract Details] ON comboJobStatus.Status = [Contract Details].Status " & vbCr & _
"WHERE ((([Contract Details].Client) Like * '" &[Forms]![ContractListSelector]![txtClient]& "' *) " & _
"AND ((comboJobStatus.Status)<>1) AND (([Contract Details].[Project Type])='" & [Forms]![ContractListSelector]![Combo0] & "'));"

Debug.Print SQL
DoCmd.RunSQL SQL

End Sub


I hope this works- I don't use LIKE statements often, so my syntx might be a little off.

HTH:thumb

[OBP]

Randy, I don't think you need the round Brackets.

[CreganTur]

Randy, I don't think you need the round Brackets.

No, you don't, but they are technically good coding practice, so I left them in... mainly because I C&Ped MD's code and then edited it. Although, to be honest, I don't write them in unless I have to. Makes life a lot easier!

[OBP]

This actually works for me.
Dim rs As Object, SQL As String
SQL = "SELECT STATEABBREVIATIONS.* " & _
"FROM STATEABBREVIATIONS " & _
"WHERE [STATE NAME] like '*' & '" & Me.Text4 & "' " & " & '*' "
Set rs = CurrentDb.OpenRecordset(SQL)
rs.MoveLast
MsgBox rs.RecordCount
rs.Close
Set rs = Nothing

[CreganTur]

"WHERE [STATE NAME] like '*' & '" & Me.Text4 & "' " & " & '*' "


That looks like a better way to handle the LIKE... have you verified that you have to wrap the asterisk with string qualifiers, or will it work without them? I'm curious for my own knowledge.

[OBP]

I dveloped that to work based on trial and error, so it might.

[mdmackillop]

Thanks for the replies.

I've been trying both approaches but no luck yet. I've attached a simplified sample with a small amount of data. Form1 works with Query1 using "normal" references. Form2 contains my attempts to build the SQL.

The reason for the SQL approach is that I'd like to option for partial matches in a number of fields, so to build the SQL from 3 or 4 fields.

[hansup]

Thanks for the replies.

I've been trying both approaches but no luck yet. I've attached a simplified sample with a small amount of data. Form1 works with Query1 using "normal" references. Form2 contains my attempts to build the SQL.

The reason for the SQL approach is that I'd like to option for partial matches in a number of fields, so to build the SQL from 3 or 4 fields. Look at help for the RunSQL Method:
SQLStatement Required Variant. A string expression that's a valid SQL statement for an action query or a data-definition query.

I think you will get that same run-time error message when you feed any SELECT statement to the RunSQL method.

I saved a copy of your Contract Details table as Contract_Details, then ran this statement in the immediate window:

DoCmd.RunSQL "DELETE FROM Contract_Details WHERE Client LIKE '*Fife*';"

It ran without error and deleted 5 records ... the wild card characters weren't the cause of your problem.

Hans

[CreganTur]

Holy crap Hans is right :doh:

I was so busy looking at the forest I forgot about the trees!

Since this is a SELECT statement you cannot run it through the DoCmd.RunSQL method. If you just want to see the results, then use the method I mentioned above: create a query that references the form objects and then use DoCmd.OpenQuery.

If you need to work with the records that are returned, then you must use a Recordset.

Man I feel like an idiot:doh:

[mdmackillop]

Thanks all, I'll check this out tomorrow.

[OBP]

The version that I posted uses a recordset, the other option is to set the form's record source to the SQL statement.