Search Query In Access

bluenose83

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

Board Regular
Joined
Nov 21, 2019
Messages
91
Office Version
365, 2016
Platform
Windows, 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

Board Regular
Joined
Feb 5, 2018
Messages
222
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.
 

Forum statistics

Threads
1,077,831
Messages
5,336,653
Members
399,094
Latest member
Learner2019

Some videos you may like

This Week's Hot Topics

Top