Query Criteria From Listbox Problem

Dec 6, 2005

Hi there. I have a problem with queries that i am creating on the fly with access. My setup is this:

A table called Order which has various fields including a field called Date Taken. The format of this is Date/Time with the format d/m/yyyy.

A form called frmMultiSelect which has a listbox called lstdates and a command button called cmdOpenQuery.

in lstdates the row source is: SELECT DISTINCT Order.[Date Taken] FROM [Order] UNION select "......ALL......" from [Order];

And in the button onClick event i have the following code (adapted from a web source):

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM [Order]"

'Build the IN string by looping through the listbox
For i = 0 To lstDates.ListCount - 1
If lstDates.Selected(i) Then
If lstDates.Column(0, i) = "......ALL......" Then
flgSelectAll = True
End If
strIN = strIN & "#" & lstDates.Column(0, i) & "#,"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Date Taken]=" & Left(strIN, Len(strIN) - 1)

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qryCompanyCounties"
Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCompanyCounties", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.lstDates.ItemsSelected
Me.lstDates.Selected(varItem) = False
Next varItem


Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub

When i open the form and look at the list box the dates are there along with the ......ALL...... however the dates whose day is less than 10 are displayed with the leading 0 e.g 04/12/2005.
When i click the run query command button on one of these dates the query is opened blank. the sql code output is:
SELECT *
FROM [Order]
WHERE [Date Taken]=#04/12/2005#;

If it was changed to 4/12/2005 it works but i dont know why access is putting in this leading 0.

Please help me!! I can send the database if needed

Thanks very much

View Replies


ADVERTISEMENT

Forms :: ListBox As Search Criteria In Query

Jan 28, 2014

I have a search form that uses several comboboxes, textboxes and checkboxes that are used as criteria in a query. You enter in the relevant information, hit search and a report opens based on the filtered query.

What I would like to do is change one of these combo boxes to a listbox and use the multiple selection as the query criteria. I know it's not as simple as just putting the listbox as a criteria in the query, and I've also tried many different variations on the varItem and strWhere code.

View 14 Replies View Related

How Make Query Criteria Come From Selected Records On A Listbox

Mar 15, 2008

I have a form which contains a listbox. It is a multi-select listbox. And I have a button on the form which runs a report. But I want the report to only show the selected records from the listbox. The report gets its records from an underlying query. But how do I make the selected records on the listbox become the query criteria?

View 4 Replies View Related

Form Asks For Query Criteria On Loading (listbox Problem)

Feb 1, 2006

I have a listbox that uses a query to reference a value in the current form. The problem is for some reason Access is trying to fill this listbox before even showing the form, so I get prompted to manually enter the query criteria when it should be picking it up from the form.

This is bizarre because it's only just started doing it. I've removed the listbox and created it again, but no help. I have listboxes such as this all over the place, but this particular one has just started playing up! Why?

View 6 Replies View Related

Modules & VBA :: Criteria On MS Access Query Not Working With Multi-Select Listbox

Aug 22, 2014

I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.

Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

[Code] ....

View 1 Replies View Related

Multiple Criteria Off A Listbox

Jun 8, 2007

I have a tutorial on how to do this, but they use an SQL code query. Is there anyway to do this by adding some type of criteria to an object query?

View 3 Replies View Related

Copying Data Within Same Form From A Listbox Containing A Query To A Blank Listbox?

Apr 21, 2006

Hi, I'm new here, so I hope I'm posting this in the correct place. I've searched the forum to see if there are any existing threads that might help me, but I've not found anything that does...
(I think this thread ( http://www.access-programmers.co.uk/forums/showthread.php?t=93444&highlight=Copying+data )may be trying to achieve something similar to me, but I'm a beginner and don't really understand it)

I shall stop waffling! I'm not entirely sure that what I'm trying to achieve is possible, I expect it probably is!

Right, I have a form (frmGroupRegister, which contains exactly the same fields as the table it comes from, tblGroupRegister), which consists of three things:

-GroupDate - The date a group took place on. It is my primary key, as no more than one group occurs on a specific date.

-ParentList (A listbox which contains a query showing the ID number, forename and surname of everyone in a table, tblParentDetails)

-ParentsAttending (A blank listbox)

I would like to place buttons in between the ParentList and ParentsAttending, which would allow users to conduct a 'register' of attendance by copying individual/multiple details from ParentList into ParentsAttending (much like you get when choosing which fields to include in a form when using a wizard for example). I would also like them to be able to remove people from ParentsAttending by using a button in case of accidentally adding the wrong person into the ParentsAttending box.

I'm aware that another, probably simpler way of achieving this would be to use a tick-box system, but I feel that visually, the first method would both look better and demonstrate who is present more clearly.

Any help would be much appreciated, but my Access skills are quite basic and things will probably need to be spelled out for me.
I'm using Access 2000 and Windows XP.
Thanks for your help,
Alice :)

View 1 Replies View Related

Modules & VBA :: Code To Add Data In The Listbox That Meets Certain Criteria?

Jul 17, 2014

I have a Access table that stores BatchNo,Scandate,NewBatchNo . As I can't attach Access database here so I have exported data from Access to Excel in Sheet1 to show how the data is stored in Access table.

Now I have to write code in VBA that will check the last NewBatchNo in the table . In the attached workbook its 194389. Now vba code should check the NewBatchNo which are blank before 194389 . Now in the records where NewBatchNo is blank , it should add the corresponding BatchNo,ScanDate in the listbox1 in form1.

e.g Sheet2 in the attached workbook stores Bathcno and Scandate of those records where NewBatchNo is null and before the Last used NewBatchNo which is 194389 in our example.

View 4 Replies View Related

Setting Query Criteria To Be 'blank' Depending On The Criteria Of A Combo Box

Oct 21, 2006

I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).

However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.

Any help would gratefully be appreciated. Thanks

View 5 Replies View Related

How To Query Data For Specific Criteria - Criteria Help

Aug 20, 2007

I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?

StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%

View 2 Replies View Related

Forms :: Dynamic Row Source For Listbox From Multi-select Listbox

Jun 10, 2015

I am using the selections made of the form to generate a query for the user.

I have a CITIES listbox that is populated with values from a stored query.

I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.

I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this

Code:

SELECT DISTINCT (t_location.LOCATION) AS Expr1
FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION
WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));

I also want multi-select so that is you can un-select all and get the results for all cities.

Here is my half thought approach.

Code:

Private Sub CityList_AfterUpdate()
'Dim LocQryStr As String
'Dim r As Integer
'Dim ctl9 As Control
'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _

[Code] ...

I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.

View 5 Replies View Related

Forms :: Listbox To Show Types Based On Section In Other Listbox

Sep 9, 2013

I have a list box called "product list box" based on a query called "searchqry", i also have another listbox called "type list box" , how do i get the type list box to only show "types" based on the section in products list box?

View 1 Replies View Related

Forms :: Passing Listbox Rowsource To Another Form Listbox

Dec 14, 2014

Using a popup form

1. On my main form, I have a listbox, I would like to edit the values of the listbox.

To do this, I have a popup form with 2 listboxes, one to have the values of the listbox on the main form, and the other listbox with option values for the 1st

1) how to i pass the rowsource sql of the listbox on the main form to the listbox on the popup form

2) how on closing the popup form, do i update the rowsource sql listbox on the main form from the changed value of the popup form listbox rowsource sql

View 3 Replies View Related

Forms :: Make Listbox Visible After Selection Of Another Listbox

Oct 23, 2013

Okay then, after much trouble and confusion, I finally realized I need to use an Extended listbox in order to allow for multiple items to be selected from a list on my form (rather than the evil multiple selection combobox!).

However, now I am trying to figure out how to make one listbox (IndustryClassification) only be visible if the item "Industry" is selected in another listbox (TypeOfBusiness). Coding I can use for this in the AfterUpdate event of the listbox?

View 7 Replies View Related

Listbox Query

Mar 3, 2008

Hi everyone,

can a listbox get its info from a query? i have been trying to create a calendar type form which is going ok and every day has a listbox to show the current events. each event will be put into a table via a form (events table ) and i was wanting to have every listbox look at the query based on the table based on the date as a criteria so every listbox will only show events related to that date. text boxs are not really an option as i need to show multiple events.

the fields in the table would be-
EventID
PersonID
Event Date
Event Time
Event Description
Event Notes
Event Priority

i was planning on only showing the time and description, then OnClick would open up the complete information.

i can relate the query to the main Calendar form too.

any advise would be great. i can work out the links if it can be done but i dont have access at work so cannot try anything until i get home :(


Many Thanks,

Nigel

View 7 Replies View Related

Listbox Query

Jan 10, 2008

Hi all,

I've two listboxes.

The first listbox contains Roses, lilies, Cactus, etc. When the user select Roses, the second listbox will display Pink roses, red roses, white roses, etc, and not all the other flowers that's in the table.

What should I do? I'm using Access 2003. Thanks in advance!

View 2 Replies View Related

Update Query And Listbox

Jul 28, 2005

Im running an update query but in the criterial field i would like to include something that instructs the query to be executed only on the value selected in my listbox.

i need some help with the syntax cause i dont know how to bound the query's execution to the selected/highlighted value in the listbox.

View 3 Replies View Related

Trying To Populate A Listbox From A Query

Aug 19, 2005

I want to pass a search parameter from a form to a query then use the query results to populate a multi-column list box. So far, I can pass the parameter to the query and generate results, however, I want the query results sent to a listbox rather than create a new window.

I set up the listbox to receive results from the query, but when the form opens the listbox is already populated with arbitrary query results. I want the listbox to be empty when the form is opened, I submit a parameter to a query, and the listbox fills with the results

Additionally, once the listbox is populated, I want to clear the listbox but am having the error: The RowSourceType property must be set to 'Value List' to use this method.

View 2 Replies View Related

Only First Column Of Listbox For Query

May 19, 2006

I'm still a little new to this, so please bear with me. I've searched to no avail for what I am looking for, so if there is an existing thread and you would rather point me in it's direction as opposed to answering my question, that would be fine.

I want one of the parameters of my delete query to be only the first column of my "ProdList" listbox, but don't know how to specify this. This is what my query looks like so far... Can someone tell me how do i need to change it?

DELETE ProductionLog.Username, ProductionLog.Date, ProductionLog.OrderNumber
FROM ProductionLog
WHERE (((ProductionLog.Username)=fOSUserName()) AND ((ProductionLog.Date)=[Forms]![Production]![TheDate]) AND ((ProductionLog.OrderNumber)=[Forms]![Production]![ProdList]));

View 2 Replies View Related

Query To Update A Listbox

May 5, 2007

I have a query which returns names to a listbox. The query works by a Command Button and 'Requerys' by using the Command Button. But, I cant get the ListBox to refresh on a Requery. All it does is add results of the Requery to the bottom of the list. I'm obviously missing something basic....any ideas?

View 1 Replies View Related

Listbox Query Problem...

May 25, 2007

Hi guys,

I'm working on a company recruitment database. I've stripped out where my problem is and attached the relevant file/tables/queries, etc...

I have a form with 2 listboxes (left and right) that are used to select various combinations of employees depending on job role and skill types. The results are displayed in listbox 3 (bottom) at the bottom of the page. You can then use this list to view more detailed records which open up on the right of the main form...

The left listbox which selects job roles works fine, however I want the right listbox to ONLY show employees that match ONLY instances of skills selected.

What currently happens is that employees are shown that match ANY of the skills chosen instead of ONLY the skills chosen...

I've tried IN, = , and different operators but cannot seem to find a resolution...

Note: (There's an annoying msgbox that displays the rowsource variable every time you click the listboxes that I used for testing LOL)

The query's are addressed in VBA behind the main form...

Any help would be appreciated

dazza61

View 2 Replies View Related

Inserting Name From Listbox Into Query

Nov 8, 2004

I have a listbox inside a subform. I want to use the 'click' Event on a "search" command button (found on the main form) to trigger
a query from inside that listbox and fill it with values from a table. The following is the argument I have written for that event procedure:


Private Sub CommandSearch_Click()
Me.GS_SearchSF.Form.listSF1.RowSource = _
"SELECT MaxSize1 " & _
"FROM GS_Ctg_Float " & _
"WHERE Supplier = '" & Me.GS_SearchSF.Form.SupplierQuery.Value & "';" 'THIS ONE ISN'T WORKING
End Sub


Except as you might imagine, it is not working. It works when I just type a name from that table into the argument in place of
the text seen above in red. For instance:

Private Sub CommandSearch_Click()
Me.GS_SearchSF.Form.listSF1.RowSource = _
"SELECT MaxSize1 " & _
"FROM GS_Ctg_Float " & _
"WHERE Supplier = 'Viracon';" 'THIS ONE WORKS
End Sub

What am i doing wrong?

Here's some background:

GS_SearchSF is the name of my subform

listSF1 is the name of the list box in the SubForm that the
query is supposed to fill

SupplierQuery is the name of the list box in the subform
I want to extract the value from and put into the query

View 8 Replies View Related

Parameter Query Based On A ListBox

Jul 1, 2006

I have a listbox on a form and I want to refer to the second column and use this value in a parameter query.

I'm using the syntax

Forms!formname!listboxname.Column(1)

but it doesn't work. I have attached the problem.

View 3 Replies View Related

How Can I Have A Listbox Let Users Choose What Query To Run?

Jan 6, 2008

I want to have users be able to choose query "x" "y" or "z" from a listbox. Upon selection of query "x" "y" or "z" from the listbox, I want the query to run? I want to run this query from form "RunQueries" Does anyone know how I can do this? Thank you in advance for any suggestions, I appreciate all the help I can get

View 10 Replies View Related

Connect A Listbox To A Table/query?

Sep 25, 2006

I have a table of customers and have table of stuff they can buy. Those two tables I use in the next table where their ordes get registred. I have and order form where I have a listbox. When I create a new order I want to have the customersname in a listbox but I want the customersID(primary key) returned in order table. How do I do that? :confused:

View 1 Replies View Related

Queries :: Query Using Input And Listbox?

Nov 11, 2014

The query below is supposed to accept some characters from the user and bring up a list of records satisfying the criteria. TelephoneAAA is a table with many columns. The query does not give any error message receives input from user runs quietly and does not give any result. I know that the table contains sufficient data. Probably I am missing something obvious but what.

Code:
RowSourceQy = " SELECT * from [TelephoneAAA] " & _
"WHERE (([TelephoneAAA].[SOYADI]) Like ((' * ')+ [Word] +(' * '))) OR " & _
"(([TelephoneAAA].[ADI]) Like ((' * ')+[Word]+(' * '))) OR " & _
"(([TelephoneAAA].[ADRES]) Like ((' * ')+[Word]+(' * ')))" & _
"ORDER BY [TelephoneAAA].[SOYADI], [TelephoneAAA].[ADI], [TelephoneAAA].[TEL];"

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved