Search Query In Access

[bluenose83]

Morning all, just after a bit of help with an access query.

I have one table with customer data on. I also have a form with text boxes which colleagues type into and it writes to the table. that works ok. I am trying to now create a search so people can go in and see how many times a particular bank account shows up.

I have a drop down combo box with a few selections, bank account, sort code etc. I then have a text box under this so the user can type what they want. So Bank account selected from drop down and 11111111 in text box. I have been trying to create a query which will bring back all the information from the table but which only match that bank account. Or whatever the user picks. So far, no luck getting this to work and am unsure what I am doing wrong. Any advice would be greatly appreciated. I am very new to access so ive basically been teaching myself as I go along so there is probably a better way round this
Cheers
Matt

 

[hernantorres23]

Hi, let me try to explain all

Table data looks like:

Filter Form looks like:

In this case the combo box have SELECT DISTINCT account FROM tblCustomer in Row Source and Row Type is Table/Query

VBA code for button over On Click Event is:

Option Compare Database
Option Explicit

Private Sub cmdExec_Click()
DoCmd.OpenQuery "qryAccount", acViewNormal
End Sub

And there is a Query filtered by the form:
[Forms]![filter]![account]

When button is pressed, qryAccount is executed, showing only the values correspond the combo box value

Hernan Torres
Mikel ERP

 

[hernantorres23]

Example

 

[welshgasman]

Try viewing this

He has other videos as well.
HTH

 

[JonXL]

Using the technique in welshgasman's video, you can modify it for your dynamic field selector by replacing the SQL "WHERE [Last Name] LIKE " with "WHERE [" & Me.FieldSelector.Value & "] LIKE ".

You would then construct the source of your dropdown to be either a query or a table with the first colum the display name of each keyword field and the second colum the actual name of those fields and bind it to column 2.

 

[bluenose83]

Thanks everyone, ill have a try with these today