Multiple field query

[Caspius]

My second post. Heres a link to an image of the database below

http://casp.gamecommunity.co.uk/database.gif

In the search section I want to be able to search multiple fields. The date from and to data will come from the same field, Coverage is a drop box and Organisation and keywords could be anything.

I know I will have to grab the text from the text boxes (curently named Textdateto, TextDateFrom, TextOrg, TextKey, and TextCoverage), (the fields are called Dt, Organisation, Keyword and Coverage) use a select query via the on click property of the search button. But I am bit lost on how to put this into practice.

Does that make sense?

Casp

 

[Premy]

Well first of all, u should always try to limit criteria collection to the available underlying data. This means that instead of having a textbox that ask for "Date Purchased" for example, u should provide a combo which displays only the dates for which there are purchases. This, in order to avoid annoying message boxes that say "No purchase for this date", after the user has entered a date and pressed the button. Providing him with a list of the dates that DO have purchases, makes things pretty much easier and contributes to a more intelligent application.

Once you have collected all criteria, u can run a query like this:

Sub btnSearch_Click()
Dim S as string
S = "Select tblPurchases.ItemNo, tblPurchases.ItemName tblPurchases.DatePurchased FROM tblPurchases ORDER BY tblPurchases.ItemNO WHERE tblPurchases.ItemNo =" & Me.cboItemNo & " AND tblPurchases.DatePurchased =" & Me.cboDate
Me.PurchasesSubForm.Form.Recordsource = S
End Sub

This presupposes a MainForm with a subform, of course. If you're trying to open a second form with this criteria, u can put this code in the 2nd form's load event with slight changes:
Sub Form_Load()
Dim S as string
S = "Select tblPurchases.ItemNo, tblPurchases.ItemName tblPurchases.DatePurchased FROM tblPurchases ORDER BY tblPurchases.ItemNO WHERE tblPurchases.ItemNo = Forms!frmCriteria!cboItemNo AND tblPurchases.DatePurchased Forms!frmCriteria!cboDate"
Me.Recordsource = S
End Sub

The query given here pulls data from just 1 table, but u can have queries pulling data from several tables. In order to get the syntax right u can first design the query in the query designer, then transform it to a VBA run time query.

HTH
Premy

 

[Caspius]

I have done a query with preset criteria.

SELECT tblMain.ID1, tblMain.Dt, tblMain.Dt, tblMain.Coverage
FROM tblMain
WHERE (((tblMain.Dt)=#5/10/2006#) AND ((tblMain.Dt)=#5/10/2007#) AND ((tblMain.Keyword) Like "*health*") AND ((tblMain.Coverage)="Balanced"));

But how would I integrate this into the form search button, as in the image, I suspect I'd need to put the code into the On Click Event but I am also not sure how would I grab the data from the text boxes and use that as criteria instead and put that into a form.

Thanks

 

[ajetrumpet]

Caspius,

I think Premy has pretty much got things strait here, but here is a consolidated version....

The only thing you need to know is how to reference a control. It is:
Object Collection!Object Name!SubObject Name

In other words: Forms!FormName!ControlName

Like in Premy's above SQL statement, that syntax should be used to reference, not "Me.Control"

Also, I'm not sure if you're aware of this, but if you have one field called "Dt" and you write the WHERE clause as you have it now, you probably won't get anything back. Your WHERE clause in the above post says this: Give me all of the records that contain the dates 5/10/2006 and 5/10/2007 in the same cell of the "Dt" field.

 

[Premy]

How could u have 2 criterias for the same field? You're telling the query to retrieve 1 or more rows where field tblMain.Dt has the value 5/10/2006 AND the value 5/10/2007; it has to be either one or the other.

In my example I showed u 2 ways to grab data from your form controls (textbox, combo, etc.):

Running the query from a MainForm and display the result in a subform:

Sub btnSearch_Click()
Dim S as string
S = "SELECT tblMain.ID1, tblMain.Dt, tblMain.Dt, tblMain.Coverage
FROM tblMain WHERE tblMain.Dt=#" & Me.txtDate & "# AND tblMain.Keyword Like'" & Me.txtHealthStatus & "*'" AND tblMain.Coverage='" & Me.txtBalancedOrNot & "'"

Me.frmSub.Form.Recordset = S.

Embedding the query in the forms RecordSource:

SELECT tblMain.ID1, tblMain.Dt, tblMain.Dt, tblMain.Coverage
FROM tblMain WHERE tblMain.Dt= Forms!frmSearch!txtDate AND tblMain.Keyword Like Forms!frmSearch!txtHealthStatus & "*" AND tblMain.Coverage= Forms!frmSearch!txtBalancedOrNot;

Regards,
Premy

 

[Premy]

That's right ajetrumpet...

 

[Caspius]

Sorry yeah, I was just confused.

The date is supposed to be between those two dates not equal them as I had done. Still trying to get me head round Access, not just how you write the code but also how you implemement it. I'll have a look at this today.

Thanks for your help anyway.

 

[Jon K]

I have attached a sample database.

You can open the form frmSearch, enter some criteria and click on the command button to open another form frmSearchResults to view/edit the search results. frmSearchResults is a bound form with the query qrySearch as its record source.

The list box on frmSearch is placed there just for viewing the table contents and can be deleted.

Hope it helps.
.

 

[Caspius]

Thanks for all your help on this.

Final code at the bottom of post. I'm sure once the users can see what they can get out of it there will more added to it. I've actually got my head around how queiries work again so its been a good learning exercise.

I've been a while replying because I couldn't get the dates to work. Thought its was something to do with being in the UK dd/mm/yyyy vs the US mm/dd/yyyy so gave up. Just realised I had the [TextDateFrom] and the [TextDateTo] the wrong way round on the input form. eg where you type the date from was actually being put into [TextDateTo] Doh! Thanks to JonK as well, I'd nearly got there, think I would have spent ages trying to get the keyword bit to work, not realising how to use wildcards in the formulae. [Keyword] Like "*" & [Forms]![frmSwitchboard]![TextKey] & "*" Or [Forms]![frmSwitchboard]![TextKey] Is Null

I'm still developing it so probably will be back.

SELECT tblMain.ID1, tblMain.Dt, tblMain.Tm, tblMain.Organisation, tblMain.Media, tblMain.ContactNo, tblMain.Email, tblMain.Keyword, tblMain.Enq, tblMain.CurrentStatus, tblMain.TeamLead, tblMain.Action, tblMain.Lead, tblMain.Statement, tblMain.Iname, tblMain.Iposition, tblMain.Itraining, tblMain.Coverage
FROM tblMain
WHERE ((([DT]>=[Forms]![frmSwitchboard]![TextDateFrom] Or [Forms]![frmSwitchboard]![TextDateFrom] Is Null)=True) AND (([DT]<=[Forms]![frmSwitchboard]![TextDateTo] Or [Forms]![frmSwitchboard]![TextDateTo] Is Null)=True) AND (([organisation]=[Forms]![frmSwitchboard]![TextOrg] Or [Forms]![frmSwitchboard]![TextOrg] Is Null)=True) AND (([Keyword] Like "*" & [Forms]![frmSwitchboard]![TextKey] & "*" Or [Forms]![frmSwitchboard]![TextKey] Is Null)=True) AND (([Coverage]=[forms]![frmSwitchboard]![TextCoverage] Or [forms]![frmSwitchboard]![TextCoverage] Is Null)=True));