WHERE clause help

[m1kee]

just a quick one
using a WHERE clause in a VBA SQL module and I keep getting an "invalid use of bracketing" error
the code line is:

strWHERE = "WHERE [qryAddressUK.Postcode] =" & Postcode

I'm trying to allow the user to select the postcode from a form, can't figure whats missing.

Many thanks,
Mike.

Do or do not, there is no try. - Yoda

 

[formerTexan]

Good morning Mike,

What is postcode in your WHERE clause? A variable name, string, field name, control name?

And is this DAO or ADO code?

Cheers,
Bill

 

[Crowley16]

I think you probably need to put quotes around your postcode...

something like:

strWHERE = "WHERE [qryAddressUK.Postcode] ='" & Postcode & "'"

of course, don't forget the ";" at the end of the string

 

[lespaul]

I think it should be:

qryAddressUK.[Postcode]

You will also need the ' around your variable only if the field Postcode is a string

Leslie

 

[Crowley16]

I don't think postcode is a reserved word...

so, qryAddressUK.Postcode should be fine...

 

[m1kee]

Hi all, tried all of the above and others, still no joy, this is the whole code block, hope someone has the answer.

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim db As DAO.Database
Dim qdef As DAO.QueryDef
Dim strSELECT As String
Dim strWhere As String

Set db = CurrentDb()

strSELECT = "SELECT DISTINCT * FROM qryAddressUK"

strWhere = "WHERE [postcode] = me.postcode;" <---- PROBLEM CODE
' [Postcode] is a field in the query
' Me.Postcode is the selection text box on the form

strSELECT = strSELECT & strWhere

db.QueryDefs.Delete "testquery"
Set qdef = db.CreateQueryDef("testquery", strSELECT)

DoCmd.OpenQuery "testquery", acViewNormal

Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click

End Sub


Do or do not, there is no try. - Yoda

 

[lespaul]

you need a space in your string!!!

strSELECT = "SELECT DISTINCT * FROM qryAddressUK"

strWhere = "WHERE [postcode] = me.postcode;


when you combine these you end up with:

SELECT DISTINCT * FROM qryAddressUKWHERE [postcode] = me.postcode

you either need to add a space at the end of your select or at the beginning of your where or in between the concatenation:

strSelect + " " + strWhere



Leslie

 

[m1kee]

Leslie, thanks thats better, but now I get a parameter box asking me to put the value in. Even though it is on the form.

So it nearly there.

Thanks
Mike.

Do or do not, there is no try. - Yoda

 

[PHV]

Have you tried this ?
strWhere = " WHERE [postcode] = '" & me.postcode & "';"
If the table column postcode is numeric, get rid of the surrounding single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[m1kee]

Many thanks to all

Now sorted, just one little quote or space out of place and it's goes wrong, but never tells you where it is

Cheers,
Mikee.

Do or do not, there is no try. - Yoda