Search Query In Access

bluenose83

New Member
Joined
Aug 6, 2008
Messages
41
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
269
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
Hi, let me try to explain all

Table data looks like:
sample_table.JPG


Filter Form looks like:
your_form.JPG

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:
VBA Code:
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]
how_to_filter_query_basic.JPG


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

Hernan Torres
Mikel ERP
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,296
Members
417,135
Latest member
zeusmining

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top