Parameters 01-08-10

When using parameters:  If the user wants all records to be returned, without 
the filtering that the parameter was set up for, is there a word, such as 
"All" or "Any" that could be entered in the parameter dialogue box to get 
that result... basically negate the parameter without deleting it?  Would 
that have to be accounted for in the expression?  
0
Utf
1/8/2010 3:46:02 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
669 Views

Similar Articles

[PageSpeed] 28

The expression must be written differently.  
There are several ways to do it.  One way is like this --
     Like [Enter parameter] & "*"
In this case if at the prompt the user just presses the ENTER key.

You could write it for user to enter ALL in this way --
       Like IIF([Enter parameter] = "ALL", "*", [Enter parameter]) 

-- 
Build a little, test a little.


"Reese" wrote:

> When using parameters:  If the user wants all records to be returned, without 
> the filtering that the parameter was set up for, is there a word, such as 
> "All" or "Any" that could be entered in the parameter dialogue box to get 
> that result... basically negate the parameter without deleting it?  Would 
> that have to be accounted for in the expression?  
0
Utf
1/8/2010 4:38:01 PM
On Fri, 8 Jan 2010 07:46:02 -0800, Reese <Reese@discussions.microsoft.com>
wrote:

>When using parameters:  If the user wants all records to be returned, without 
>the filtering that the parameter was set up for, is there a word, such as 
>"All" or "Any" that could be entered in the parameter dialogue box to get 
>that result... basically negate the parameter without deleting it?  Would 
>that have to be accounted for in the expression?  

I'll have to disagree with Karl here: the * wildcard will not find NULL
values, and may not work as desired for datatypes other than text. 

Try a criterion of

= [parameter] OR [parameter] IS NULL

If the user leaves the parameter blank, it will find all records.

If you wish, you could use

= [parameter] OR ([parameter] = "ALL")

to allow the user to enter or select (from a combo box, say) ALL.
-- 

             John W. Vinson [MVP]
0
John
1/8/2010 8:06:27 PM
Thank you.  I'm having some trouble getting it to work.  Here's my 
expression:  

Like [Enter 1st 3 Letters of Model] & "*" 

How would I apply your solution to that expression without messing up the 
original expression? 

Thanks, 
Reese

"John W. Vinson" wrote:

> On Fri, 8 Jan 2010 07:46:02 -0800, Reese <Reese@discussions.microsoft.com>
> wrote:
> 
> >When using parameters:  If the user wants all records to be returned, without 
> >the filtering that the parameter was set up for, is there a word, such as 
> >"All" or "Any" that could be entered in the parameter dialogue box to get 
> >that result... basically negate the parameter without deleting it?  Would 
> >that have to be accounted for in the expression?  
> 
> I'll have to disagree with Karl here: the * wildcard will not find NULL
> values, and may not work as desired for datatypes other than text. 
> 
> Try a criterion of
> 
> = [parameter] OR [parameter] IS NULL
> 
> If the user leaves the parameter blank, it will find all records.
> 
> If you wish, you could use
> 
> = [parameter] OR ([parameter] = "ALL")
> 
> to allow the user to enter or select (from a combo box, say) ALL.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
1/9/2010 5:07:01 PM
Thanks Karl.  I'm using that exact expression so that I can enter the first 3 
letters of the model I'm looking for and Access will return any record with 
those first 3 letters.  That works just fine, but if I just hit "enter" 
without putting in the first 3 letters, I get no returns.  This is my 
expression:  

Like [Enter 1st Three Letters of Model] & "*"

What I'm looking to do is to "Enter Through" the dialogue box (leave it 
blank) and get all records to return.  

"KARL DEWEY" wrote:

> The expression must be written differently.  
> There are several ways to do it.  One way is like this --
>      Like [Enter parameter] & "*"
> In this case if at the prompt the user just presses the ENTER key.
> 
> You could write it for user to enter ALL in this way --
>        Like IIF([Enter parameter] = "ALL", "*", [Enter parameter]) 
> 
> -- 
> Build a little, test a little.
> 
> 
> "Reese" wrote:
> 
> > When using parameters:  If the user wants all records to be returned, without 
> > the filtering that the parameter was set up for, is there a word, such as 
> > "All" or "Any" that could be entered in the parameter dialogue box to get 
> > that result... basically negate the parameter without deleting it?  Would 
> > that have to be accounted for in the expression?  
0
Utf
1/9/2010 5:33:01 PM
Exactly as he said. If you wish the user to enter the word "All" to get all 
the records:
Like [Enter 1st 3 Letters of Model] & "*" OR [Enter 1st 3 Letters of Model] 
= "All"

or, it you wish the user to enter the word "any":
Like [Enter 1st 3 Letters of Model] & "*" OR [Enter 1st 3 Letters of Model] 
= "Any"

Reese wrote:
> Thank you.  I'm having some trouble getting it to work.  Here's my
> expression:
>
> Like [Enter 1st 3 Letters of Model] & "*"
>
> How would I apply your solution to that expression without messing up
> the original expression?
>
> Thanks,
> Reese
>
> "John W. Vinson" wrote:
>
>> On Fri, 8 Jan 2010 07:46:02 -0800, Reese
>> <Reese@discussions.microsoft.com> wrote:
>>
>>> When using parameters:  If the user wants all records to be
>>> returned, without the filtering that the parameter was set up for,
>>> is there a word, such as "All" or "Any" that could be entered in
>>> the parameter dialogue box to get that result... basically negate
>>> the parameter without deleting it?  Would that have to be accounted
>>> for in the expression?
>>
>> I'll have to disagree with Karl here: the * wildcard will not find
>> NULL values, and may not work as desired for datatypes other than
>> text.
>>
>> Try a criterion of
>>
>> = [parameter] OR [parameter] IS NULL
>>
>> If the user leaves the parameter blank, it will find all records.
>>
>> If you wish, you could use
>>
>> = [parameter] OR ([parameter] = "ALL")
>>
>> to allow the user to enter or select (from a combo box, say) ALL.
>> --
>>
>>              John W. Vinson [MVP]
>> .

-- 
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" 


0
Bob
1/9/2010 5:50:29 PM
On Sat, 9 Jan 2010 09:07:01 -0800, Reese <Reese@discussions.microsoft.com>
wrote:

>Thank you.  I'm having some trouble getting it to work.  Here's my 
>expression:  
>
>Like [Enter 1st 3 Letters of Model] & "*" 
>
>How would I apply your solution to that expression without messing up the 
>original expression? 

If the user enters nothing at all in the textbox, this will translate to

Like "*"

which will return all records.

You don't need to do *anything at all* (other than some simple user training).
-- 

             John W. Vinson [MVP]
0
John
1/9/2010 6:50:14 PM
Reply:

Similar Artilces:

Accessing Global Parameters from the included stylesheet.
Hi all, I have a .Net program which access transforms a XML using an XSL stylesheet. The .net program calls Stylesheet main.xsl file. The main.xls contains only <xsl:include> elements to include other XSL files. like <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:include href="../one.xsl"/> <xsl:include href="../two.xsl"/> <xsl:include href="../three.xsl"/> <xsl:include href="../four.xsl"/> <xsl:include href="../five.xsl&quo...

Creating a form to supply parameters to a report
I have created a report using a form to supply parameterers. I used the instruction provide in Access Help under Using parameters with queries and reports. When I open the report the form opens but none of the text boxes or command buttons are visible. When I open the form in design view it runs a debug and opens visual basics with this highlighted in yellow: AccessObject = CurrentProject.AllForms(strFormName) This is the code provided in the instuctions. Any ideas how to fix this? Thanks! What exactly are you trying to do? Are you trying to create a report that when you open it,...

Pass changing values to parameter to Crystal Report scroll thru re
Hello Community In a C# and ASP.NET app I embed a Crystal Report. In the Crystal Report I create a parameter field named id. Then I read one id from a table and I pass the id from the table to Crystal Report where Crystal Report creates the report successfully. The problem is that now I am reading atleast 5 id’s each containing a different value from the table and I want Crystal Report to create a report for each one of the id’s such that the user can use the carat and scroll thru all of the reports from the first one to the last. Crystal Reports creates reports like...

Find the 10% value
Hi, I have a monthly report that contains data for a varying amount (each month) of users across several column. For each column I need to use conditional formatting to highlight the worst 10% for each measure. Currently I'm doing this manually by sorting the column ascending, then if there are 15000 users, look at the value corrensponding to line 1500, enter this in a cell which I then reference in my conditional format. What the best way to do this by formula? -- Rich http://www.rhodes-lindos.co.uk On Thu, 4 Mar 2010 20:41:46 -0000, "Rich" <REP...

check box 03-15-08
hi all, how can I do this? have a DB for Vehicles, if I enter the "year" is 10 years old or order, the check box "EXEMPT" will automaticaly checked. ex: enter year 1996, the check box "EXEMPT" will checked by it self. thankx Carol, You really shouldn't be saving the Exempt status in your table. That should be a calculated field. And a rule of thumb is to not save any value that can "derived or calculated from data you already have saved. This is similar to Price * Qty = LineTotal. As long as you capture the Price, and capture the Qty,...

passing parameters to Crystal 07-01-03
Is it possible to programmatically pass parameters to a crystal report? say a quote id... If so, how is this done? Any insight would be greatly appreciated. Thanks, Justin Nope... Beautiful... eh? Rob Schrader FrontLine Technology "justin" <justinda@invision.net> wrote in message news:063e01c33fdf$10f73840$a001280a@phx.gbl... > > Is it possible to programmatically pass parameters to a > crystal report? say a quote id... > > If so, how is this done? > > Any insight would be greatly appreciated. > > > Thanks, > Justin You're...

Powerpoint 2007 03-12-10
I have tried to change the theme or template to an existing presentation but Powerpoint will not apply the change? Suggestions? How are you going about changing the theme/template? What is happening -- or not happening, as the case may be? Which version of PPT? Assuming PPT 2007, you might try hitting the Reset button on Home tab | Slides group (next to the New Slide button). Sometimes if there are a lot of customizations to the slide in question, you have to reset its layout. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoic...

Changing Account Distributions in GP 10
Hello, We have installed the SP 1&2, and the feature pack. When we are doing a voucher transaction and/or a reciept againt a PO, and want to send the expense to more than one account, we can't figure out how to do it. If anyone can help out we would greatly appreciate it! Thanks, Michelle Michelle: Search on this topic - 'Changing PURCH account at summary level GP 10 Options' This will explain the change in GP10 and some suggestions for work- arounds. Hope this helps, Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com Frank, Where shoul...

macros #10
If I have a macro that copies A1 and pastes it into b1, how can I change the macro to paste it into any selected cell? Hi HOHN, Try one of these... Sub S_Copy() ActiveCell.Value = Range("A1").Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Value = Range("A1").Value End Sub Where you will have to activate the S_Copy() macro with a button or such and the Selection_Change macro will copy A1 th the cell you click on. HTH Regards, Howard "JOHN" <JOHN@discussions.microsoft.com> wrote in message news:34A28B2B-D959-46...

EFT for Payables - GP 10
In version 9 or before, a client could select all cheques for payment, and the system would create cheques for vendors without EFT info, and create the EFT file for vendor with EFT. I cannot do this anymore (in GP 10). On the batch setup screen when you choose computer cheque, there is an option for payment type - Cheque or EFT. I have to create an EFT batch and use select cheques, then create a cheque batch, and process them separately. If I forgot to do the EFT batch first, then all vendors would get paid by cheques. If this is the case, this will cause grief to a lot of clients who are...

(APPZ) Video2Brain.Autodesk.Inventor.2010.Workshops.fuer.Fortgeschrittene.GERMAN-Substance
=ybegin part=1 line=128 size=22368 name=Video2Brain.Autodesk.Inventor.2010.Workshops.fuer.Fortgeschrittene.GERMAN-Substance.par2 =ypart begin=1 end=22368 zk|\*zu~*******��Z���n��t��������|��[�3"��ͥ}zk|J\XZ*p���n���V�T)��ZS1%�ژ|�t/6�$�C�U����CP.*��^" �^.S�S6**:-****�����\l� ���Xk�������Xs�������X\Z[ZX���������X����Xp���������������Xqo|wkxW}��������X����Z[X���**zk|\*zu~�4******�`j~+��'��,�PVC���|��[� 3"��ͥ}zk|J\XZ*sp}m****V�T)��ZS1%�ژ|���\=@����%ʭ����|�3�\Ͽ���kt�ㅬZqM���X����4�y��j&b�8��] I�=J�u��mg1Cr�����)f'Ӿ *��}�Q�F�\>�&o_<P�֝�\\...

newsgroups 08-29-10
Hi, I'm using Windows 7 - IE 8 - Windows Live Mail. Since a week ago, I notice that the groups are not synchronized when pushing on the button. Is this due to some installed updates ? Thank you for the help. Jaypie "Jaypie" <jaypie281@sympatica.ca> wrote in message news:i5ee6p$dt$1@news.eternal-september.org... > Hi, > > I'm using Windows 7 - IE 8 - Windows Live Mail. > > Since a week ago, I notice that the groups are not > synchronized when pushing on the button. > > Is this due to some installed updates ? > ...

prova 12-17-08
dove sono chi sono bo? ...

Parameter Problem 02-28-08
I have a summary query that is summing the daily% [Sum of Daily%] and summing the hours [Sum of Hours]. When I run the query with these two columns, it runs fine and shows the values. I add a third column which is calculted as Rate%: [Sum of Daily%]/[Sum of Hours]. When I run the query, it is asking for values for [Sum of Daily%] and [Sum of Hours]. I doesn't matter what number (if any) I enter, I can just press return, and they run fine. How can I stop Access from asking for these parameters? Thanks for any suggestions! -Bill Please can you put me out of my misery. I can...

SQL 2005 Replication 01-04-10
In a publisher/subscription db replication scenario, I would like to alter db setting on the db that publishes. What would be the desired procedure for accomplishing this? Thanks in advance ...

Loosing parameters via xml control
Howdy I have an xslt that accepts parameters. In my code I set these paramaters and pass them to the xml control to transform and display the xml according to the xslt. When I throw the form directly into the response.write it shows my paramaters just fine. Since I altered the code to use the Xml Control, these parameters are not received by the Xslt upon transformation via the xml control. Here is my code. '*CTE00 - Throw the Document into a xml navigator. '************************************************* NavApproveCondition = DocApproveCondition.CreateNaviga...

Error Message 01-25-10
I keep getting an error message:"Sorry something unexpected happened and Windows Live Mail must close. Your most recent changes may not be saved." No error number, nothing at all. If I update my contacts it won't save it. Any ideas?? 2 easy choices; try system restore uninstall and install windows live mail again. System restore will not affect any mail that you may have; but always good to back up. -- richc46 *Richard* *If I have helped you in any way, please show your appreciation, by clicking the middle icon, on the upper right portion of your s...

hola 10-29-07
hollllallalalalalalala ...

Filecopy 05-17-10
Hi, line 1 start = now line 2 filecopy a, b line 3 end = now line 4 duration = end - start How can i be sure that line 3 will only execute AFTER the filecopy in line 2 is finished ? Thanks. As far as I know, filecopy is synchronous, so control doesn't pass to the next statement until the copy has been completed. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Rubens" <Rubens@discussions.microsoft.com> wrote in message news:5D01DD9A-EE05-429F-85CC-B64B4BB281...

Service Unavailable 01-23-06
Good afternoon. After a serious problem of energìa the servants were reinitiated. From that momentom has not been possible to raise the CRM. When it is tried to accede to the page of beginning of the CRM throws the error: Service Unavailable what was the serious problem? ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "Juan Carlos" <Juan Carlos@discussions.microsoft.com> wrote in message news:B886FB76-44DD-41F3-81C6-CBC7C12AF086@microsoft.com... > Good afternoon. After a serious problem of energ�a the serv...

Write Conflict 08-26-07
I have a bound form that I use to input records with. The only field is a Memo Field. This field is attached to tblReceipe, This table has only one record in it. And will always only have one record in it. After I write the contents of that record to another table I want to change the contents of that record in the tblReceipe table to " ". But I keep getting the write conflict message. Basically the problem is that when I open the input form I need that memo field to appear blank. Dim AboutSQL As String Dim MemoSQL As String DoCmd.SetWarnings False AboutSQL = "UPDATE ...

Outlook client error 08-23-05
Hi, I get the following errors only in the CRM outlook for sales client with all users and from all workstations. In Outlook client: 1. When in any folder (example: the Leads folder) and you click the Expansion arrow next to the Lead name i get this error: "The stylesheet does not contain a document element. The stylesheet may be empty, or it may not be a well-formed XML document" 2. When i click the CRM contact button, the window opens and i can type all my information but when i click save, i get this error: "An error occurred while trying to synchronize the contact i...

OLE Notes
Does anyone know why I am not seeing the OLE notes button in the customer card note attachment window? Thanks! Craig Craig, Make sure that the OLE objects Path is defined in your DEX.ini. Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 Great Package For Business Solutions daoudm@greatpbs.com http://www.greatpbs.com http://mohdaoud.blogspot.com/ "Craig" wrote: > Does anyone know why I am not seeing the OLE notes button in the customer > card note attachment window? > > Thanks! > > Craig Yes, the path has been specifie...

Comparing Tables 02-22-10
I have looked at the postings for this but don't quite understand. I have an excel doc that is now a table used in DB. There are several fields, Carrier, Client_Name, Appeals_Phone, Fax, Comp_Add2, City, State, Zip I get a revised table every quarter and would like to compare the old and new and see what changes there are. For example Carrier 1234, Client Sesame Street, Appeals_Phone 999-999-9999, Fax 123-456-7890, Comp Ses St, Add2 1234 Big Bird Lane, Yardley, PA, 12346 Now a new list comes in and the address is different 4567 Big Bird Lane. How do I run a query against the 2 ...

Alternating Positioning of Header 03-12-10
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I'm writing a reference book, using Word 2008 if it will do what I need it to do. Each chapter will be its own section so that I can have a unique header, and I'll suppress the header on the first page of each chapter. However, I want both the header and page number to be to the left on even pages, and to the right on odd ones, and I can't seem to find a way to do this. <br><br>I used Word 2003 for Windows to write the first book I did, and I ended up hiring a guy to write a VB macro to...