473,670 Members | 2,618 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Select ALL from an unbound form for query parameters

Hi,

Any help on this one will be greatly appreciated as I have spent too
long banging my head against the screen trying to get it sorted.
Basically I would like to have a select all records option in a combo
box that is used in an unbound form for my report parameter. I have
tried to explain it in further detail below: -

In my DB I have numerous forms are used to input details into a table
(TblMasterTable ).

In the input forms I have combo boxes that are used to populate fields
in the master table. The combo boxes in the forms are bound to tables
(e.g. TblComboHRAreas ).

I have reports set-up that run from queries that use the data in the
master table. The parameters for the queries are linked to an unbound
form (FrmParam) that has the following fields on it: -

StartDate
EndDate
HRArea (Combo box that uses the records in TblComboHRAreas for its
selection)
Team (Combo box that uses the records in TblComboTeam for its
selection)

I have the following expressions in the queries for the parameter
fields:

HRArea (expression): - [Forms]![FrmParam]![HRArea]

The reports work fine with you select a HR Area from the combo box.
The problem I am experiencing is to select all records from the combo
source (TblComboHRArea s).

I have tried replacing the above expression with the following: -

Like IIf([Forms]![FrmParam]![HRArea]=0,"*",[Forms]![FrmParam]![HRArea])

I have also tried replacing the 0 with isnull, "", nil etc. with no
success as the problem seem to be that when the wildcard * is does not
extract all records instead I get no records.

Thats about it. Thanks in advance if you can help solve my problem.

Dec 22 '05 #1
3 2266
Try this

Change the criteria in the query to

[Forms]![FrmParam]![HRArea] OR [Forms]![FrmParam]![HRArea] Is Null

You can use this technique for multiple fields in your query but the
query design gets complicated quickly. I find that for simplicity, a
set of cascading queries works quite well so that each query filters a
bit of the info and is used to build the next one.

Geoff

Dec 22 '05 #2

Hi Geoff,

Thanks for your suggested solution. It was simple to implement and
worked perfectly, it will also go along way towards helping my sanity.

Thanks again and I hope you enjoy the New Year celebrations.

Cheers,

Stig
*** Sent via Developersdex http://www.developersdex.com ***
Dec 29 '05 #3
Nice to know it works OK. I found this one at

http://www.mvps.org/access/

It's good place to look for help.

Happy New Year

Dec 30 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
2922
by: Jos? | last post by:
This one droves me completely mad. I did not succeed to exploit the track given to me by Bob. I have : three tables : Clubs, Persons and ClubsPersons that join the two first in a many to many relationship. Club has two (2) fields : ClubId (Autonumber) and ClubName (Text). Peoples has two(2) fields : (PersonId (Autonumber) and PersonName (Text) ClubsPersons has two (2) fields : ClubId (LongInteger) and PersonId
1
2909
by: longtim | last post by:
I have been having endless difficulty creating reports/queries that set any relevent parameters from controls in forms. I am creating an application under access 2003 but will target access 2000. The access file is in access 2000 format. I have a form that will hold the relevent parameters for the query/report that reports the statistics for all job records that match a certain criteria. These are: - A Customer Name.
1
9619
by: huela | last post by:
Somehow i have to display the recordset in unbound form When i use the following display the data in recordset to unbound form. I have a question: i can only display the last record data, all previous data are overwrittend. why i can't create the continues form or datasheet display the data line by line. Thanks a lot for the help
2
1793
by: Arnold | last post by:
Greetings Gurus, In a report showing the names of students and their progress, I am getting an error in the name field (Name: #Error). The report gets its data from an unbound form containing two unbound textboxes, "txtStartDate" and "txtEndDate," and a multi-select listbox, "listName." I think the error occurs when there is no correct combination of data--for instance, no records for John Doe entered during this past week.
3
1963
by: phonl | last post by:
I am a vb6 ADO developer looking at vb.net 2005 and ADO2.net. I used the vb.net 2005 data wizard to bind some controls to a database. Now I want to run a select query and have the bound controls reflect the change from the query. How would I do that? The wizard created a TableAdapter, BindingSource, and DataSet. Somehow I need to run feed them a select query to update the data.
2
3599
by: jay | last post by:
Hi. I have created an unbound combo box in a form whose purpose is to pass parameters to a query and then requery the requery. The unbound combo box is named "ClosedFilter" The two parameters it will pass to the query called "qry_WkReq" is "Yes" and "No." (The field being requeried is a Yes/No type field.) After this, I created an event procedure after update and coded:
2
5731
by: Mark Roughton | last post by:
I have a form where the users need to view records for various criteria, one of which is a date field on which they may wish to view all related data for the selected date, for all dates upto and icluding the selected date or all records on or after the selected date The user selects either "=", >=" or "<=" from a combo box and then a date from another combobox. The combination of thse two choices is then set in an unbound textbox so...
16
5173
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub Storage_Click() On Error GoTo Err_Storage_Click
7
12505
by: DeZZar | last post by:
Hi all, Unfortunately I am quite a novice with Access!! I've created a number of data bases for my work however becuase my skills are limited to really built in functionality and wizards my programs are not really user friendly. I have searched and searched and tried numerous times to get the following right to no avail - and I think its really becuase of my lack of understanding.
0
8468
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8386
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8901
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8814
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8591
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8660
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7415
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5683
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4209
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.