Parameter Criteria Date Picker

I have some queries that give Start and End Dates in separate criteria text
boxes to select a range of dates. Can these text boxes be replaced by Date
Picker control or have the Date Picker appear in the text Box?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1

2
Bruister
3/27/2010 6:12:25 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
5016 Views

Similar Articles

[PageSpeed] 2

If you are using Access 2007, there's a built in date picker that should 
fire if you set the Format property of the text box to General Date, and 
leave the Input Mask blank.

For any version, there's a simple little Access form here:
    http://allenbrowne.com/ser-51.html
Copy into your database.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Bruister via AccessMonster.com" <u50445@uwe> wrote in message 
news:a59eeae05edfd@uwe...
> I have some queries that give Start and End Dates in separate criteria 
> text
> boxes to select a range of dates. Can these text boxes be replaced by Date
> Picker control or have the Date Picker appear in the text Box? 

0
Allen
3/27/2010 11:35:02 AM
Allen Browne wrote:
>If you are using Access 2007, there's a built in date picker that should 
>fire if you set the Format property of the text box to General Date, and 
>leave the Input Mask blank.
>
>For any version, there's a simple little Access form here:
>    http://allenbrowne.com/ser-51.html
>Copy into your database.
>
>> I have some queries that give Start and End Dates in separate criteria 
>> text
>> boxes to select a range of dates. Can these text boxes be replaced by Date
>> Picker control or have the Date Picker appear in the text Box?

Sorry but I didnt make my request clear enough. I meant the boxes that are
titled 'Enter Parameter Value' that appear. At present I type a date, hit
enter and the second box appears, also waiting for a date to be typed. Can
the date picker be used instead? Thanks

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1

0
Bruister
3/27/2010 2:17:39 PM
No, there is no ability to do this directly in the query.  You have to create 
a form to gather the values and then reference the OPEN form's controls as 
criteria in the query.

Check out this article for a detailed discussion.
     http://www.fontstuff.com/access/acctut08.htm

Or check out this from MS
    http://office.microsoft.com/en-us/access/HA011730581033.aspx

Or for another example
http://allenbrowne.com/ser-62.html

The following talks about comboboxes, but you can apply the same technique to 
a text control on the form and the calendar control.

A brief quote from a John Vinson (Access MVP) posting.

You'll need to create a small unbound Form (let's call it frmCriteria) with a 
Combo Box control (cboCrit) on it. Use the combo box wizard to select the 
table for the selections, and be sure that the bound field of the combo is the 
value you want to use as a criterion. Save this form.

Now use

=[Forms]![frmCriteria]![cboCrit]

as the criterion in your Query.

It's convenient to base a second Form or Report on the resulting query to 
display the results; if you put a button on frmCriteria to launch that form or 
report, the user can enter the criterion and view the results in one simple 
operation!

End quote

Keywords: Parameter Queries,  comboboxes

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Bruister via AccessMonster.com wrote:
> Allen Browne wrote:
>> If you are using Access 2007, there's a built in date picker that should 
>> fire if you set the Format property of the text box to General Date, and 
>> leave the Input Mask blank.
>>
>> For any version, there's a simple little Access form here:
>>    http://allenbrowne.com/ser-51.html
>> Copy into your database.
>>
>>> I have some queries that give Start and End Dates in separate criteria 
>>> text
>>> boxes to select a range of dates. Can these text boxes be replaced by Date
>>> Picker control or have the Date Picker appear in the text Box?
> 
> Sorry but I didnt make my request clear enough. I meant the boxes that are
> titled 'Enter Parameter Value' that appear. At present I type a date, hit
> enter and the second box appears, also waiting for a date to be typed. Can
> the date picker be used instead? Thanks
> 
0
John
3/27/2010 2:58:08 PM
Reply:

Similar Artilces:

Crm 4 event log warnings. CRM Parameter Filter
Hi all, I am looking for a solution on the following issue. Any suggestions will be very welcomed! Thank you in advance. Background: We just upgraded MS Crm 3 to Crm 4 Question: Every time when a user tries to save an update of a record. A warning will be logged into Event Viewer --> Application. It happens to all entities. For example: 1. I am doing some updates on the contact form http://localhost:5555/CAMELOT-DEV/sfa/conts/edit.aspx?id={488FF696-4F8C-DE11-AC94-00505698306E}# 2. Click save 3. I will find a warning message in Event Viewer --> Application Exception information: ...

Entering Dates in Excel
Hi, Is it possible to just enter 020304 in a cell and have the date come out correctly or must I put -'s or \'s to seperate the numbers Hi John without using VBA this is (AFAIK) not possible. Have a look at http://www.cpearson.com/excel/DateTimeEntry.htm for a VBA solution Frank John wrote: > Hi, > > Is it possible to just enter 020304 in a cell and have > the date come out correctly or must I put -'s or \'s to > seperate the numbers ...

filtering records by custom criteria
Problem: Activities such as bulk emails or calls often apply only to such a particular group of people or accounts, where particular conditions are met. E.g. send a particular mail to all those leads, who expressed a particular field of interest (non-standard fields) and who have downloaded a particular document or software (non-standard fields). Another example: Send a mail (e.g. upgade notification) to all those accounts who have bought a particular product (standard field) and a corresponding maintenance license (non-standard field), and where the license's expiry date (non-stan...

Report Criteria?
Hi! I have a problem with my report about Accounts Receivable. It is supposed to show all the Accounts Receivable, regardless if whether they are paid or not. Now, the problem is that in the textbox "Total", it should only include those which are not paid,which could be determined by the checkbox "Paid" which is also shown in the report. If there is a check on the box,it means that the account has already been paid. The textbox "Total" sums the value from the textbox "Amount". The values are from the same query, What criteria should be in placed and wh...

Count if between date parameters
I need create a formula that show how many patients were available to be seen on each day of the month. I would like to make the cut-off for changes of dates as 3pm, meaning that one day is from 3pm - 3pm the next day. e.g. - 12/30/09 available patients would be all those there from 3:01pm on 12/29/09 to 3:00pm on 12/30/09. If they're available for the full day count them only. Using this logic with the data below on 12/30/09 only 2 patients were available to be seen. Name Request DT Res DT P1 12/27/2009 1:26 12/31/2009 16:28 P2 12/27/2009 12:00 12/30/2009 7:20 P3 ...

Adding a second criteria to indirect Sumproduct
Sorry for all the questions lately - this project is really testing my skills :S I have a formula I'm using to SUMIF multiple data across various sheets: =SUMPRODUCT(SUMIF(INDIRECT({"18","19","20","21","22","23","24"}&"! B16:B300"),A15,INDIRECT({"18","19","20","21","22","23","24"}&"! W16:W300"))) However, I'm looking to add a second criteria to this. Right now the data in range B16:B300 has to match A15, but I also nee...

How to plot Date Ranges on Same Graph
I'm trying to take our personnel spreadsheet and chart employee arrival and departure data so that we can graphically see where the gaps are and whether or not we need to fill certain positions earlier than expected. When a date changes, the chart needs to automatically re-draw to correct itself. I thought, "oh, this will be easy- I'll simply chart the date ranges with everyone on their own line," and told my boss it would take 5 minutes. Well, that was a week ago! He's getting antsy, and I'm getting embarrassed. So basically, if person X is here from May throu...

date track based on svc cond.
Hello, I'm looking to track material out for repair by the type of service requested. In cell (G:2) will be the type of service: BCK = bench check = this will have a projected return date (I:2)within three days of the repair order date (H:2) REP = repaired = This will have a projected return date (I:2) within 7 days of the repair order date (H:2) OVH = overhaule = this will have a projected return date (I:2) within 14 days of the repair order date. With all this I would also like to include the warranty schedule from the date of the service reurn date(J2) BCK= no warranty REP= 90...

Macro
Hi, I have this database from column C to T and row 4 onwards. What I hope to achieve is to copy the entire row from C to T to another sheet (similarly from Column C to T and row 4 onwards) if the cell for that row in Column L is "Lost Case". At the same time, the selected rows to be copied in the original sheet should be deleted after being copied. Another consideration is that new data will be added to the original sheet from time to time and thus the database might expand or shrink (after being copied and delete, and the addition or new data). Thus, when the macr...

Query Parameter
I want to supply a parameter to a query to define the from date and to date and retrieve data falling under this from and to date. but, i do not want it to be prompted, as the same criteria is to be used in more than one table. so, i want to store the from date and o date in a temp table and related this to the date field in different tables. thanks You could create a table (tblDateRange) with 2 date fields (datStart and datEnd) and only one record. Then add tblDateRange to your query and don't join it. Set the criteria under your date field to: Between [datStart] and [datEnd] ...

CRM 3.0 - Workflow
Hello, I would like to create a task x days before the opportunity estimated close date, using a workflow rule. Do someone know how we can do it ? Perhaps with the new function in CRM 3.0 "substract date and time" but I don't understand how to use it. Thanks for your help. WTCSO There is a "wait for timer" condition to do this. You choose this condition, and then you can set the amount of time to wait, before/after/from now on, and an entity field to check for. In your case you choose 1 day, before, Est.Close Date field. Be sure to check the "reevaluat...

Using Exchange 2003 Up to Date Feature
My boss has a Samsung Smart Phone and he asked me to see if he can get his email sent to his phone when he's traveling. I have user initiated sync and up-to-date enabled on his account but I don't know what else I need to do to make this work. Has anyone set this up? A link to an article explaining this process will be fine and appreciated. Thanks, Rafael ...

optional method parameter in MFC ActiveX
How do you set a method parameter as optional in an MFC ActiveX control? Kurt "Kurt" <k_nojunk@larimore.net> wrote in message news:%23HK$bCR4DHA.2468@TK2MSFTNGP09.phx.gbl... > How do you set a method parameter as optional in an MFC ActiveX control? > > Kurt Depends, (1) do you mean when you create an activex control yourself, and you wish to make a parameter optional? Or (2) do you want to pass an optional argument to a method of the ActiveX control? Solution (1) : Make it a variant parameter. If the variant is of type VT_ERROR and the value is DISP_E_PARAMNOTFOU...

iif and date range
I have following criteria for my query. I wanted different date range if it is Friday or not. IIf(Weekday(Date())=6,Between Day(Date()) And Day(Date()+2),Between Day(Date()) And Day(Date()+1)) I tested bith criteria are working, but it does not work when I added the iif. Are there anything wrong when I add iif to specify different date range? Your help is great appreciated, Souris wrote: >I have following criteria for my query. > >I wanted different date range if it is Friday or not. > >IIf(Weekday(Date())=6,Between Day(Date()) And Day(Date()+2),Between >Day(Date())...

Cannot scale dates in pivot chart
I've created a pivot chart (from a pivot table) that graphs dates on th x-axis vs. numeric values on the y-axis. The dates are not contiguou in the source data. As a result, the pivot chart only displays th dates in the source data and skews the graph. If I was to create simple graph of dates vs. numbers and select the format axis, I hav options of selecting the starting date, the ending date, the number o days between ticks, etc. This data is not skewed. Is there a way t get Excel to recognize that the x-axis data is a date and/or get it t realize I'm dealing with a date ra...

Text import
Hi I have a text file which I import into Access table using transfertext method. everything is okay except few date columns are in ddmmyyyy format In some of the columns I have a date in the follwoing format ddmmmyyyy (i.e 13Oct2007) - without any slash or hyphen. How can I get this in proper data format 10/13/2008 (mm/dd/yyyy) either at the time of importing or after importing get this proper date in a different column. Aslo, I have some dollar amount columns and signage (negative or positve) is given in a separate column. So all the dollar amounts are abosolute value. Based on the...

Criteria in Advanced Filter
Please tell me how to extract records in a certain range. I need to have all rows where date is from 3 to 8 for example. I'm using Advanced filter and 2 rows for criteria. When I enter data in the 2 rows for given column, I have and OR condition. I need an AND condition. Christine If you want an OR condition, you would have the criteria range in e.g. L1:M3 with Head1 Head2 a b For an AND condition use e.g. criteria range L1:M2 and Head1 Head2 a b -- Best Regards Leo Heuser Followup to newsgroup only please. "Christine" <cls...

Current date in cell
How do I put a current date in cell, meaning a formula so that when I open the spreadsheet the cell will have todays date in it. 4th July 2009 thanks =today() in the cell but it will change with each day and calculate with any other calculation. Or, put in the ThisWorkbook module in the workbook_open event sheets("yoursheetnamehere").range("yourrnage")=date -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "SS" <nonense50@blueyonder.co.uk> wrote in message news:QaP3m.46662$Eq6.15411@newsfe24.ams2... > How do I pu...

Too few parameters?
Hello All I have SELECT statement that I have built using the SQL view of a query. The SELECT statement contains WHERE, GROUP BY, HAVING and ORDER BY parameters. All has been working fine, until today when I have needed to add an extra criteria to the HAVING part: This worked: "HAVING ((([x confirmed].name)=" & Chr(34) & [Forms]![staffs subform new]![employee] & Chr(34) & "))" but this does not: "HAVING ((([x confirmed].entered) =" & Chr(34) & [forms]![staffs subform new]![child813]![entered] & Chr(34) & ")...

Printing a Report with Parameters
When printing a report with Parameters. The report will come up on the screen after you enter the parameter. When you then print the report it asks for the parameter again. After we click finish, the following error appears on the screen 'Information is needed before this report can be processed (On Cache Page/Server EIACRM.pgsvr) The report will not print. what server os is your crm web running and also what is your domain controller(s) running? also its possible you have embedded the servername in the report. open the report in crystal designer and go to database-verify dat...

Count between dates
in Column A I have dates. Column D also has Dates. Column A are Dates entered Column D are Mitigated Dates What I am trying to do is count the Dates between 01/01/09 anf 31/01/09 in Column D but only if The Dates in Column A are within the same Parameters Dates Entered Mitigated Date 01/01/09 13/03/09 01/02/09 23/01/09 25/01/09 15/01/09 14/01/09 13/03/09 Answer = 1 Thank you for your assistance =SUMPRODUCT(--(A2:A200>=--"2009-01-01"),--(A2:A200<"2009-0...

MSN Parameters
I have a free e-mail account at msn.com. I had to install a new hard drive and reinstall my software. What are the parameters to access my e-mail at msn? POP3, HTTP, etc. If POP3 what are the parameters for outgoing and incoming e-mail? Thanks. JustAsking JustAsking wrote: And you are using WHICH version of Outlook? > I have a free e-mail account at msn.com. That doesn't make clear whether you have a classic MSN Hotmail account or a new Windows Live Hotmail account. You sure you didn't get converted to a Windows Live Hotmail account? For HOW LONG have you had the ...

Excel date intervals look up
Hi Im trying to lookup a value based on date intervals for academic years, using birthday to find the year. So for example if I enter a birthday of 22/09/1997 then it will bring me the name of the academic year. Please I appreciate any help. below is my table to look up. 01/09/2005 31/08/2006 Reception 01/09/2004 31/08/2005 Reception 01/09/2003 31/08/2004 Year 1 01/09/2002 31/08/2003 Year 2 Juniors 01/09/2001 31/08/2002 Year 3 01/09/2000 31/08/2001 Year 4 01/09/1999 31/08/2000 Year 5 01/09/1998 31/08/1999 Year 6 - 11+ transfer 2010** High school year - 2009 01/09/1997 ...

group totals w/criteria?
SSRS 2005 I'm relatively new to RS and have a challenge I don't know how to overcome. I have a report where I need to subtract one group total from another. I read that this isn't possible in 2005, but that 2008 will support aggregations on aggregations. I'm sure someone has figured out how to do this in 2005 though, so any words of wisdom? This is an Accounting report, where I need to get the value for the Contribution Margin total. The aggregation I'm looking for is this: Contribution Margin = sum(Revenue) - sum(Expenses) The challenge is the the...

Taking multiple parameters
I am running a query inside my macro that creates a worksheet full of some info. I want users to be able to take the results of one macro, and use it as arguments for another macro. So lets say I run 1 macro, I get 2 user ids as a result, say: 1, 2 Now I want to use these user ids as input parameters. Keep in mind these input parameters might vary. So how do I supply these as parameters to another macro? Pass the calculated or retreived values as parameters to the second macro: Sub Macro1() IDValue1 = "ID 123" IDValue2 = "ID 456" Macro2 IDValue1, IDValue2 End Sub Sub M...