Find Next Available Date

I have a query that filters out weekends and holiday dates. This query drives 
a combo box on my form. I would like the default value in this combo box to 
be the next available maturity date from the filtered query. The next 
available date will be driven off of a text box "trade_date" that defaults to 
date(). I have been struggling with this one. Any ideas?
0
Utf
11/11/2007 11:50:00 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1346 Views

Similar Articles

[PageSpeed] 22

Nate,

There might be a simpler way suggested by one of our
experts, but I like to deal with dates using values based
on the current default epoch in Access. That said, it's
fairly straight forward in code to find the current day's
date in a bracketed query where weekends and holiday
entries carry with them a flag hidden in an un-displayed
column in your combo. Once found, it then becomes
easy to advance to the next non-weekend, non-holiday
entry in the query.

If this idea doesn't lend itself to your current database
structure, let us know a little bit more about what you
have in your database to work with. If your application
isn't driven by your database, it will usually give difficulty
trying to solve these types of issues.

Bill


"Nate" <Nate@discussions.microsoft.com> wrote in message 
news:6F456010-ACF1-42B5-BF05-5237C0BA6035@microsoft.com...
>I have a query that filters out weekends and holiday dates. This query 
>drives
> a combo box on my form. I would like the default value in this combo box 
> to
> be the next available maturity date from the filtered query. The next
> available date will be driven off of a text box "trade_date" that defaults 
> to
> date(). I have been struggling with this one. Any ideas? 


0
Bill
11/12/2007 1:21:48 AM
Reply:

Similar Artilces:

Sales Pipeline report
I know you used to be able to view the sales pipeline report by product, but I don't seem to have that option available in the "group by" ddl. Am I missing something? HELP! Viewing the sales pipeline by product is an important requirement. I haven't used this report, but it should be relatively easy to build your own SQL view for the report to group the output by product (clone the existing report and modify the where clause). Thanks for the reply. According to the help files, this group by option should be available. I opened the report in Visual Studio, and low-and-...

Date on Template
I created some templates and am choosing to bring over a date to be shown in the template when it is run. I have selected this attribute to be date only and not date & time. Every time I run the template it still shows date+time+time zone. Is there a way to show only the date and not the rest of the data. Thanks for any help anyone can provide. ...

Calculate Dates with If Then Else Statements
I need to calculate total years’ full time experience (i.e. 2.5 years, 3.25 years, etc.) in an Access 2003 database form. This is what I am trying to do: If the Full-Time End Date is Blank, use Today's Date, ELSE if the Full-Time End Date has a Date, use that Date. Separately, these two formulas work: =DateDiff("m",[FullTimeStartDate],Now())/12 =DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12 My problem is writing a statement to calculate using the END DATE if one is entered, or NOW, if there is no end date. I have tried many combinations, but can’t ge...

Filter Date
Hi, I have a form which contains a listbox (holding the values Process Date and Quantity) and I have a textbox which displayed the current month. What I want to do is when I click on a command button (cmdMonthDown) and the textbox value changes from May to April I want the values in the listbox to be filtered so that only those values are disiplayed that were entered in April. If I'm not making sense please let me know otherwise your help would be appreciated. Thanks Reference the text box as a parameter in the list box's RowSourcee, e.g. if the text box shows the month name in fu...

Task start/end dates from MS project to Workspace site
We are currently running MSPS 2007 with WSS 3.0. I am trying to create a list in one of the project work spaces. I would like to auto-populate two columns in that list with the start and end date of certain tasks from the Project Plan in MS project. I am thinking we can do this by linking these columns to the the project SQL database fields. So, 1) Do I use the published SQL databse for this? 2) Which fields in the SQL database would I have to link to get the Start and End dates? EggHeadCafe - Software Developer Portal of Choice AutoList in ASP.Net http://www.eggheadcafe.com/tutorial...

Date Format when incoming date can be 0
I have a date fiels yyyymmdd which can have a value of 0 or a valid date in recent past. Excel displays the 0 date as 01/01/1900 and the non zero dates as expected. Cant figure this out , any help would be appreciated. Excel uses a sequencial number from either 1/1/1900, or 1/1/1904 (depending on your options) as the date value for Example 6/3/2005 is 38506 using the 1/1/1900 if the cell is formatted as a date it is responding with the date it thinks you mean. If you want the cell to display a zero =if(Date=0,"0",date) "DP NY10601" wrote: > I have a date fiels y...

Macro: Find matching DATA, copy correspondently values into another list
Okey guys - please advise me on this. (There was unfortunately no quick response to my latest problem - and I begun to swim... However - that brought me out on deep VBA-water...again - and now need a life buoy (metaphorical spoken)). The core of my macro problem is to look for all the values in column A (Range A5:A??) in Sheet "BOARD" (not bored!!!) that match values in another list placed in column A in sheet "LIST". When finding the match I will copy correspondent celle.value in column D / sheet "BOARD" into column B in sheet "LIST" - this w...

Posted Batches are still available
Hi: We are using GP 9 with SQL 2005. We have a situation, where we have posted the batches but those are still available to post. Those are showing no. of documents and their batch totals, but when we print edit list of those batches, there is nothing. What is causing this situation? Thanks in advance. Are those Sales transaction batches? If yes, they may be order or quote in your batch. "Naseer Ahmed Farooqi" wrote: > Hi: > > We are using GP 9 with SQL 2005. We have a situation, where we have posted > the batches but those are still available to post. Those...

Date Range
Hi, When I want the date rage appeared on the report header, I use textbox and put (qry criteria) in its recordsource. It works fine. However, For this time, I got a message like this ‘First([Between [From this date] And [To this date]])" Is there something wrong with the qry criteria? Would you please show me how to fix the problem? Thanks Chi It's very hard to understand the crtieria, can you post the full SQL What is the First in the beginning? Why there are to square brackes in the end And in the beinning? First([Between [From this date] And [To this date]])" T...

how to expand available fonts for email in MS CRM?
Hi all, By default Microsoft CRM offers the following fonts for email messages and templates: - Arial - Arial Black - Tahoma - Verdana - Garamond - Times New Roman - Courier New Is there a (supported) way to expand this list with other fonts? Thank you for your reply, Basman ...

ADO Find method
I would like to know if there is a way to use the ADO Find method to find one of several values for a single column. See the following example: rstOpenJobProcess.Find "[proc#] = '940' or '941'" I would like to search for proc# '940' or '941'. I want to avoid using a like statement in the criteria (such as [proc#] Like '94*') because I may use '942' for something else in the future that I would not want to search for. Is there a way to use the Find method in this way? Alternatively, is there a clever workaround? Th...

how to calculate the substraction between two dates
hi, i have two cells ,in which there are two dates. (for instance ,one cel l 08/02 ,the other 07/30) if the gap of the two dates is 2 days , something will be set to do. but it is difficult to judge the the substraction between two dates any good ideas. thanks Perhaps this might be of help .. Assume the *dates* "2-Aug-2004" and "30-Jul-2004" are input in A1 and B1, and A1:B1 is custom formatted as: mm/dd viz. it'll show as: In A1: 08/02 In B1: 07/30 Try in C1: =DAY(ABS(A1-B1))-1 Format C1 as: General or Number C1 will return: 2 i.e. the number of days...

Finding data
Hi all, first post here at these forums :) I have a question/ problem that hopefully someone can answer for me. If anyone has the time, could they have a look at the attachment, i was easier that trying to explain it. But in a nutshell it involve finding data within sheets and totaling them on another sheet. The problem is that in my real world example, I will have 40 sheets and 100 rows of data, so performing it manually is out of th question. cheers, and thanks for your time. Charli +------------------------------------------------------------------- |Filename: help1.zip ...

How to Find Record if Exists, Add if not?
How to Find Record if Exists, Add if not? I have a form where user will enter the vendor ID, which is a unique ID. They enter it off a long list of information listing many vendors and it's hard to know if the vendorID already exists in the database, until they tab through all the fields and get the duplicate record error. Then they press Esc and do a search on that vendorID to pull up the existing record to update. VendorID format is similar to this A123456789ST (Usees a prefix, Vendor FEIN, State abbrev.) I want to automate this. when they enter the VendorID, I want it to s...

download the Credit Card transaction date not the the posting date
Then I download a statement from a Credit card and import it in to MS Money I always get the Posting Date not the Transaction date. Do I need another column in the register, help does not tell me how to do that? Or is the only solution changing each entry by hand? ( that doesn't make sense) This credit card offers downloads in either QIF or spreadsheet. In microsoft.public.money, riskymanr wrote: >Then I download a statement from a Credit card and import it in to MS Money I >always get the Posting Date not the Transaction date. Do you mean you first enter the transactions by...

Advanced find and seeing full path to mail
When doing an advanced find Outlook only show the sub- folder it is in. How do you get it to list the full path. Listing a sub-folder can be very hard if there are folders with the same name. This is with Outlook 2000. ...

If Formula , Adding Days to a Date
A B 1 06/06/10 I want to make a formula that calculate as following; in Cell (B1) if the date in future the result will be [Enrolled], if not it will be Cell (A1) + 730 Days On Sat, 27 Mar 2010 02:14:01 -0700, Khalid A. Al-Otaibi <KhalidAAlOtaibi@discussions.microsoft.com> wrote: > A B >1 06/06/10 > >I want to make a formula that calculate as following; > >in Cell (B1) if the date in future the result will be [Enrolled], if not it >will be Cell (A1) + 730 Days Try this formula in cell B1: =IF(A1>TODAY(), "[En...

lookup a date from an array of date ranges if conditions are met
Please help...i need to lookup up a value on a specific date range. for example: column A: column b (from 9/01/2003 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Sounds like autofilter could be something, select the data (should have headers), do data>filter>autofilter, filter on the date column using custom and gate than or equal to and put 09/01/03 as a criteria -- Regards, Peo Sjoblom "nscanceran" <nscanceran.wj1qn@excelforum-no...

Release Date of V 4.0
Hi Does any one know the official release date for CRM V 4.0? Will CRM V 4.0 be available for partners before it is available for the general public? Thanks alot ...

Asked before cannot find question or answer anymore "Caracter mess
When I am using Word I have no problem typing this caracter ? (question mark). however if I type this in OOUTLOOK, than it appears like this É or like this _ It has just done it lately, so I must have changed a setting unknowingly. Can someone please help to get the question mark ? back again... thank you. By the way this time I have checked the "Notify me of repies" Verify your keyboard language and layout settings in Regional and Language option in Control Panel. For more details see; http://www.msoutlook.info/question/16 -- Robert Sparnaaij [MVP-Outlook] Coauthor, C...

Date and Time formular
I'm trying to take one date and time away from another but having a problem fathomining it out eg 01.04.2008 10:55:27 - 01.04.2008 9:37:13 The date and time are in the same cell eg 01.04.2008 10:55:27 is in A1 A formula for this would be great if anyone has one to hand Thanks Just subtract =End_Time-Start_Time -- Regards, Peo Sjoblom "SS" <Student.2@uk.bosch.com> wrote in message news:g0hgsk$jgc$1@news4.fe.internet.bosch.com... > I'm trying to take one date and time away from another but having a > problem fathomining it out > > eg >...

Date Formatting Problem
Hi I run Excel 2K I download info from the mainframe into a workbook. This information contains a date for each entry. This is the format that the "date" is downloaded in (29:53.0)....when the curser is placed on this example it displays it as 31/07/2009 12:29:53 AM in the "edit bar" of the spreadsheet. I can then format the (29:53.0) to dd/mmm/yy which then reads 31/Jul/09. However in the edit bar still shows it as 31/07/2009 12:29:53 AM. I need to be able to format it so that in the edit bar it only shows the dd/mmm/yy and not have the time format attched to i...

Date and Time #2
I see how that NOW() function works but is it possible to display only the time and not the date? Nick Use now(), then format the cell to only show time format=>Cells=>Number Tab, select time and pick a format. -- Regards, Tom Ogilvy Lord Of The Morning <Not@aol.com> wrote in message news:vmjrr6dsduv569@corp.supernews.com... > I see how that NOW() function works but is it possible to display only the > time and not the date? > Nick > > Yes, with TIME you'll get only the (current) time "Lord Of The Morning" <Not@aol.com> wrote in message...

Problem with query dates
Hi My records date range in the database is Betwene 20/05/20010 and 22/05/20010 . When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the records less than this date, OK. But if I change the month, this date to 24/04/2010 00:00:00 shows the same records. That is even if I change the month the records that appear are always less than the 24 days without following month. There lies my problem. Note: My date style is dd-mm-yyyy and time is hh: mm: ss I really need to fix this.Seabra Dim Q1, SQL As String Dim DateTime1 As Date Dim ConnString As S...

How to add time from multiple cells & date Cells & machine type c
I'm trying to add up the amount of machinery downtime from a number of different cells but the thing is that I need to only have it added up by the date set in another cell (Which can have multiple cells of the same date) as well as the specific name of that machinery in another cell(which can also have multiples of the same machinery name). How can i get to just add up for that specific date, machine name, and time. Example: Date (Cell 1A thru 50A) Machinery Name(Cell 1B thru 50B) Downtime(Cell 1J thru 50J) Please help Thanks Example:Sheet1 Data Column A ...