Volunteer needs help with Select Statement

[Volunteer]

Hi All,

Can you help with the following!

I want to extract different post codes from a table.
i.e BL9, BL7, M25, M26 etc

I can get the following code to produce a report for BL7 but don't know how to add the other ones.

Can anyone help please as I am doing this for a Charity and a bit stuck.

Option Explicit
Function BuryPostCode()
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT [First Name], " _
& "[Last Name] FROM [Members Contact Details];")
dbs.Close
DoCmd.OpenReport "Members Contact Details Report", acViewPreview, , "[Post Code] = 'BL7'"
End Function
Private Sub Command4_Click()
End Sub
Sub postcode()
End Sub

 

[namliam]

Change the BL7 to what ever needs changing too...
"[Post Code] = 'BL7'"
"[Post Code] = 'BL9'"

You do this by substiting in the postcode from the recordset which is something like so:
"[Post Code] = '" & rs![Your Postal Code field] & "'"

You will need to disambiguate your dim's
Dim dbs As DAO.Database, rst As DAO.Recordset
And loop the recordset, but you can find out how to loop it if you lookup recordset in the access help.

Good luck and come back with detailed questions if you need more help.

 

[Volunteer]

Sorry, I want to add all the above post code's into the statement.

Can you show me how to do this

Thank You

 

[DCrake]

If you design your query add another column:

PostcodeArea:Left([Postcode],3)

Then in the condition line under this column enter

In("BL7","BL9","Etc")

David

 

[Volunteer]

I have tried both of your comments but i can not get them to run.

I have put the code in red which I want to display in the reoprt.

Option Explicit
Function BuryPostCode()
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT [First Name], " _
& "[Last Name] FROM [Members Contact Details];")
dbs.Close
DoCmd.OpenReport "Members Contact Details Report", acViewPreview, , "[Post Code] = 'BL7' & 'BL9' & 'M25' 'ETC' "
End Function
Private Sub Command4_Click()
End Sub
Sub postcode()
End Sub

The above does not work is there a better way to do this please

Thanks

 

[Poppa Smurf]

You need to put the Post Code as a WHERE condition in your sql code.

The solution would be to use a query as suggested by David, then use Set rst = dbs.OpenRecordset("your query name")

Then use DoCmd.OpenReport "Members Contact Details Report", acViewPreview

 

[namliam]

If you design your query add another column:

PostcodeArea:Left([Postcode],3)

Then in the condition line under this column enter

In("BL7","BL9","Etc")

David

Look at dave's post and then look at your code again... find the difference.

 

[Brianwarnock]

You put etc in your list of postcodes, will some be only 2 characters M2 and some 4 BL11 say?
If so you will need to edit the postcode, a query like this will find what you want

SELECT IIf(Mid([pcode],3,1)=" ",Left([pcode],2),Left([pcode],4)) AS Expr1
FROM tablename
WHERE (((IIf(Mid([pcode],3,1)=" ",Left([pcode],2),Left([pcode],4))) In ("BL1","BL7","BL11")));


Brian