Date Query: Select all items before 5/Sep/2007

I have a table look like this:

ID	Name	                Trade_Date	Settle_Date
1	AA Company	20070903	               20071005
2	BB Company	20070904	               20070910
3	ABC Insitute	20070905	               20070913
4	ST Shop	                20070906	               20071021
----------------------------------------------------------------------------------------
My Question1:


I need an SQL Qeury, 

to select all items where the Trade_date is greater 2007/Sep/05

----------------------------------------------------------------------------------------
Question 2: 
Do i have to adjust the date format eg: dd/mm/yyyy or /mm/dd/yyyy or 
dd/mmm/yyyy   , something like that ?...



Thank you very much ! ! ^_^!


-- 
---------
0
Utf
9/10/2007 3:14:00 AM
access.queries 6343 articles. 1 followers. Follow

6 Replies
782 Views

Similar Articles

[PageSpeed] 0

SELECT *
FROM [look like this]
WHERE Trade_Date > 20070905;

To convert you number ot a date, try an expression like:
DateSerial([DateField]\10000,([DateField] mod 1000)\100,[DateField] Mod 100)

-- 
Duane Hookom
Microsoft Access MVP


"ali" wrote:

> I have a table look like this:
> 
> ID	Name	                Trade_Date	Settle_Date
> 1	AA Company	20070903	               20071005
> 2	BB Company	20070904	               20070910
> 3	ABC Insitute	20070905	               20070913
> 4	ST Shop	                20070906	               20071021
> ----------------------------------------------------------------------------------------
> My Question1:
> 
> 
> I need an SQL Qeury, 
> 
> to select all items where the Trade_date is greater 2007/Sep/05
> 
> ----------------------------------------------------------------------------------------
> Question 2: 
> Do i have to adjust the date format eg: dd/mm/yyyy or /mm/dd/yyyy or 
> dd/mmm/yyyy   , something like that ?...
> 
> 
> 
> Thank you very much ! ! ^_^!
> 
> 
> -- 
> ---------
0
Utf
9/10/2007 3:34:00 AM
It doesn't work !!!

I mean, this SQL Query returns all data, it doesn't filter the dates at all. !

does it have anything to do with the date format ? My Data Type for that is 
"data/time" and format is "yyyymmdd".

please kindly help me. Thank you so much ^_^!

-- 
---------


"Duane Hookom" wrote:

> SELECT *
> FROM [look like this]
> WHERE Trade_Date > 20070905;
> 
> To convert you number ot a date, try an expression like:
> DateSerial([DateField]\10000,([DateField] mod 1000)\100,[DateField] Mod 100)
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "ali" wrote:
> 
> > I have a table look like this:
> > 
> > ID	Name	                Trade_Date	Settle_Date
> > 1	AA Company	20070903	               20071005
> > 2	BB Company	20070904	               20070910
> > 3	ABC Insitute	20070905	               20070913
> > 4	ST Shop	                20070906	               20071021
> > ----------------------------------------------------------------------------------------
> > My Question1:
> > 
> > 
> > I need an SQL Qeury, 
> > 
> > to select all items where the Trade_date is greater 2007/Sep/05
> > 
> > ----------------------------------------------------------------------------------------
> > Question 2: 
> > Do i have to adjust the date format eg: dd/mm/yyyy or /mm/dd/yyyy or 
> > dd/mmm/yyyy   , something like that ?...
> > 
> > 
> > 
> > Thank you very much ! ! ^_^!
> > 
> > 
> > -- 
> > ---------
0
Utf
9/10/2007 3:52:00 AM
I'm not sure why you didn't mention the date data type in your first posting. 
Try:
SELECT *
FROM [look like this and still no name]
WHERE Trade_Date >#09/05/2007#
-- 
Duane Hookom
Microsoft Access MVP


"ali" wrote:

> It doesn't work !!!
> 
> I mean, this SQL Query returns all data, it doesn't filter the dates at all. !
> 
> does it have anything to do with the date format ? My Data Type for that is 
> "data/time" and format is "yyyymmdd".
> 
> please kindly help me. Thank you so much ^_^!
> 
> -- 
> ---------
> 
> 
> "Duane Hookom" wrote:
> 
> > SELECT *
> > FROM [look like this]
> > WHERE Trade_Date > 20070905;
> > 
> > To convert you number ot a date, try an expression like:
> > DateSerial([DateField]\10000,([DateField] mod 1000)\100,[DateField] Mod 100)
> > 
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> > 
> > 
> > "ali" wrote:
> > 
> > > I have a table look like this:
> > > 
> > > ID	Name	                Trade_Date	Settle_Date
> > > 1	AA Company	20070903	               20071005
> > > 2	BB Company	20070904	               20070910
> > > 3	ABC Insitute	20070905	               20070913
> > > 4	ST Shop	                20070906	               20071021
> > > ----------------------------------------------------------------------------------------
> > > My Question1:
> > > 
> > > 
> > > I need an SQL Qeury, 
> > > 
> > > to select all items where the Trade_date is greater 2007/Sep/05
> > > 
> > > ----------------------------------------------------------------------------------------
> > > Question 2: 
> > > Do i have to adjust the date format eg: dd/mm/yyyy or /mm/dd/yyyy or 
> > > dd/mmm/yyyy   , something like that ?...
> > > 
> > > 
> > > 
> > > Thank you very much ! ! ^_^!
> > > 
> > > 
> > > -- 
> > > ---------
0
Utf
9/10/2007 4:16:01 AM
Hey!, Duane, Big thanks from me!!  it works now~!!!
sorry my table's name is "trade1",

can you please explain me what "#" (the sharp sign) means in SQL ?.

Thank you so much !


-- 
---------


"Duane Hookom" wrote:

> I'm not sure why you didn't mention the date data type in your first posting. 
> Try:
> SELECT *
> FROM [look like this and still no name]
> WHERE Trade_Date >#09/05/2007#
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "ali" wrote:
> 
> > It doesn't work !!!
> > 
> > I mean, this SQL Query returns all data, it doesn't filter the dates at all. !
> > 
> > does it have anything to do with the date format ? My Data Type for that is 
> > "data/time" and format is "yyyymmdd".
> > 
> > please kindly help me. Thank you so much ^_^!
> > 
> > -- 
> > ---------
> > 
> > 
> > "Duane Hookom" wrote:
> > 
> > > SELECT *
> > > FROM [look like this]
> > > WHERE Trade_Date > 20070905;
> > > 
> > > To convert you number ot a date, try an expression like:
> > > DateSerial([DateField]\10000,([DateField] mod 1000)\100,[DateField] Mod 100)
> > > 
> > > -- 
> > > Duane Hookom
> > > Microsoft Access MVP
> > > 
> > > 
> > > "ali" wrote:
> > > 
> > > > I have a table look like this:
> > > > 
> > > > ID	Name	                Trade_Date	Settle_Date
> > > > 1	AA Company	20070903	               20071005
> > > > 2	BB Company	20070904	               20070910
> > > > 3	ABC Insitute	20070905	               20070913
> > > > 4	ST Shop	                20070906	               20071021
> > > > ----------------------------------------------------------------------------------------
> > > > My Question1:
> > > > 
> > > > 
> > > > I need an SQL Qeury, 
> > > > 
> > > > to select all items where the Trade_date is greater 2007/Sep/05
> > > > 
> > > > ----------------------------------------------------------------------------------------
> > > > Question 2: 
> > > > Do i have to adjust the date format eg: dd/mm/yyyy or /mm/dd/yyyy or 
> > > > dd/mmm/yyyy   , something like that ?...
> > > > 
> > > > 
> > > > 
> > > > Thank you very much ! ! ^_^!
> > > > 
> > > > 
> > > > -- 
> > > > ---------
0
Utf
9/10/2007 4:44:01 AM
On Sun, 9 Sep 2007 20:52:00 -0700, ali <allen_pw@yahoo.com> wrote:

>It doesn't work !!!
>
>I mean, this SQL Query returns all data, it doesn't filter the dates at all. !
>
>does it have anything to do with the date format ? My Data Type for that is 
>"data/time" and format is "yyyymmdd".
>
>please kindly help me. Thank you so much ^_^!

The *FORMAT* is irrelevant, then. The data is stored as a Double Float count
of days (and fractions of a day, times) since midnight, December 30, 1899 -
regardless of the format.

To get all dates since September 1, use a criterion

> #1/9/2007#

or 

> [Enter start date:]


             John W. Vinson [MVP]
0
John
9/10/2007 5:13:04 AM
Access uses various delimiters for various data types. You may notice that 
text/string values are delimited with either single or double quotes. Dates 
are delimited with #. Numbers (my favorite) don't have any delimiter.

MS SQL Server uses single quotes for dates and strings ;-)
-- 
Duane Hookom
Microsoft Access MVP


"ali" wrote:

> Hey!, Duane, Big thanks from me!!  it works now~!!!
> sorry my table's name is "trade1",
> 
> can you please explain me what "#" (the sharp sign) means in SQL ?.
> 
> Thank you so much !
> 
> 
> -- 
> ---------
> 
> 
> "Duane Hookom" wrote:
> 
> > I'm not sure why you didn't mention the date data type in your first posting. 
> > Try:
> > SELECT *
> > FROM [look like this and still no name]
> > WHERE Trade_Date >#09/05/2007#
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> > 
> > 
> > "ali" wrote:
> > 
> > > It doesn't work !!!
> > > 
> > > I mean, this SQL Query returns all data, it doesn't filter the dates at all. !
> > > 
> > > does it have anything to do with the date format ? My Data Type for that is 
> > > "data/time" and format is "yyyymmdd".
> > > 
> > > please kindly help me. Thank you so much ^_^!
> > > 
> > > -- 
> > > ---------
> > > 
> > > 
> > > "Duane Hookom" wrote:
> > > 
> > > > SELECT *
> > > > FROM [look like this]
> > > > WHERE Trade_Date > 20070905;
> > > > 
> > > > To convert you number ot a date, try an expression like:
> > > > DateSerial([DateField]\10000,([DateField] mod 1000)\100,[DateField] Mod 100)
> > > > 
> > > > -- 
> > > > Duane Hookom
> > > > Microsoft Access MVP
> > > > 
> > > > 
> > > > "ali" wrote:
> > > > 
> > > > > I have a table look like this:
> > > > > 
> > > > > ID	Name	                Trade_Date	Settle_Date
> > > > > 1	AA Company	20070903	               20071005
> > > > > 2	BB Company	20070904	               20070910
> > > > > 3	ABC Insitute	20070905	               20070913
> > > > > 4	ST Shop	                20070906	               20071021
> > > > > ----------------------------------------------------------------------------------------
> > > > > My Question1:
> > > > > 
> > > > > 
> > > > > I need an SQL Qeury, 
> > > > > 
> > > > > to select all items where the Trade_date is greater 2007/Sep/05
> > > > > 
> > > > > ----------------------------------------------------------------------------------------
> > > > > Question 2: 
> > > > > Do i have to adjust the date format eg: dd/mm/yyyy or /mm/dd/yyyy or 
> > > > > dd/mmm/yyyy   , something like that ?...
> > > > > 
> > > > > 
> > > > > 
> > > > > Thank you very much ! ! ^_^!
> > > > > 
> > > > > 
> > > > > -- 
> > > > > ---------
0
Utf
9/10/2007 1:22:03 PM
Reply:

Similar Artilces:

Tracking Dates For Future Occurrences
Can this be done? I want to track a yearly review. I would like the date, once entered - say 6/1/2009, to conditionally format to change yellow 30 days before, then red 15 days before, and then to stay red until the date is updated again for say 6/1/2010. Can this be done? I am new to all this, thanks.. In 2007 Also.. "Knee2no" wrote: > Can this be done? I want to track a yearly review. I would like the date, > once entered - say 6/1/2009, to conditionally format to change yellow 30 days > before, then red 15 days before, and then to stay red until the date is...

RNDIS, WLK 1.5, crash
Moin, I test a RNDIS device which is connected via USB to the host. During power management tests the RNDIS driver part usb8023x.sys holds the IRP too long and the driver verifier complains about it with DRIVER_POWER_STATE_FAILURE (9f) A driver is causing an inconsistent power state. The tests fail on XP x86 SP 3 and Vista x86 SP2. Does MS provide a fixed driver ? Best regards, Stefan P.S.: The first analysis with WInDbg shows ******************************************************************************* * ...

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 ...

Creating a Combined Date/Time Shortcut
I know Excel provides shortcuts for entering the date (CTRL + ;) and the = time (CTRL + SHIFT + ;), but I really need one that will enter both the = date=20 and time in the same cell at the same time. (Since Excel does provide=20 formatting for same-cell date and time, it seems kind of odd that there = isn't=20 a shortcut to facilitate entry.) Is it possible to create an entirely = new shortcut=20 that doesn't use macros? (This last point is important, since the = workbook is=20 shared on a network.) Any help anyone can offer would be much appreciated--I'm getting=20 desperate. T...

parsing a date and time field #2
I am having trouble parsing the date and time in a field. I download data from a data base and the date and time come together in one field. I want to seperate the two. The date and time comes across as the following: "2/1/2009 14:37" in the cell. When I parse it, it seperates into three columns as follows: "2/1/2009", 2:37 AM", and "PM" I can see what is going on but I would like to get two columns with one as the date and the other as the correct time. are they any ideas on how to address this? Try using the TimeValue and DateValue functions. First format ...

checkbox as query criteria
Hi, in an Access form i've put a combo box that has a query as data source. On this form I'd like to add a check box that, when it is selected, adds the "is not null" criteria on a query field. There is a simple way to do this? Thank you very much for help. -- Remigio www.sacraspina.it www.amicitosondoro.it www.icmonteodorisio.it www.parrocchiacupello.it www.cralnuovainiziativa.it www.associazionehistonium.it On Sat, 1 May 2010 05:55:14 -0700 (PDT), remigio <linoreale@gmail.com> wrote: Create two queries. Then switch between them based on your n...

How to transform HTTP query string (HTML data) to xml?
Dear experts, I need to send a simple HTML form to an ASP.NET page which has to create a xml object. What technique would you recommend me to use in order to transform a HTML form data to xml? What naming convention for the input fields would be best so that I can easily parse the query string in the asp page and create the xml object? I also dispose of the .xsd schema of the xml that must be created if that could be of some use. At every request the .xsd could change so I need some general algorithm. I would greatly appreciate your help. ...

SharePoint (WSS) 2007 / Outlook 2007 - Alert
This is a multi-part message in MIME format. ------=_NextPart_000_00AD_01C70E47.83B45950 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable We have setup a SharePoint Server (WSS) 2007 and are using the alerting = functionality. After we changes stuff on WSS, an alert message is generated and sent to = an email account. This e-mail account is hosted on an Exchange 2003 server. When using Outlook 2007 to open the e-mail, we get: Cannot open this item. Wenn using OWA the e-mail can be opene fine. Does somehow have an explanation for th...

Exchange 5.5 to 2003 upgrade issue 3
We have upgraded our Exchange 5.5 server to 2003 and moved all the mailboxes and moved and re-homed the folders following the KB articles. We turned off the 5.5 services and for a week everything was working fine. Now for some reason the messages to the main distribution list is being sent to the old 5.5 server on a x400 protocol. How can I stop this and assure that the 2003 server is self contained so I can get rid of this server? ...

Changing Item #'s
Is it possible to change the item #'s after the item has already been entered? TMM: No there isn't in the "base" product, but MBS does have a tool that you can use called Item Modifier - this will allow you to change an Item number from one value to another. You can email this address below and they can answer any questions you have about the tool and can tell you what the cost of it is. mbsprofessionalservices@microsoft.com Hope that helps you out, JG "TMM" wrote: > Is it possible to change the item #'s after the item has already been entered? ...

"the wizard is unable to open your query in datasheet mode, possi
"the wizard is unable to open your query in datasheet mode, possibly because another user has a source table open in exclusive mode. your query will be opened in design view" what this message mean ,and how can i solve it Hi - Please provide more details as to what you are doing when you get the error, what your database setup is (i.e. split Fe/BE?, multiuser?, version? etc). Without more information, we can only guess. Thanks - John amr wrote: >"the wizard is unable to open your query in datasheet mode, possibly because >another user has a ...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

Sumproduct with multiple date criteria
Having a tough time with this one. Sheet 1 Column A = Start Date, Column B = End Date, Column C = Quantity. Sheet 2 Row A = Start Date, Row B = End Date. I would like Row C to sum quantity from sheet 1 where ever the two date ranges intersect. The date ranges on sheet 2 represent the beginning and ending of a week (Mon-Sun). Sheet 1 Column A Column B Column C 01JAN2010 24JAN2010 1,000 Sheet 2 Row A 04JAN2010 11JAN2010 18JAN2010 25JAN2010 Row B 10JAN2010 17JAN2010 24JAN2010 31JAN2010 Row c 1,000 1,000 1,000 0 Do the Sheet 2 Star...

How do I setup an SMTP gateway / smart host for Exchange 5.5 internet bound mail
My current setup has a Windows NT 4.0 Exchange 5.5 server setting on our company LAN behind a Microsoft ISA Server. The Exchange server's Internet Mail Connector binds to (I assume this is the proper terms) port 25 on the ISA server for inbound and outbound Internet Mail. Our company has finally upgraded to a new Cisco Pix Firewall which we are able to setup a DMZ on. The suggestion by our ISP is to setup an SMTP gateway in the DMZ and have our internal Exchange server send mail to it destined for the Internet. I am aware that most of the work for making the connections from the SMTP ...

Deleted items folder in o2k3
The big boss refuses to empty or archive his deleted items, now holding some 25,000 messages and approaching 3 gigs in size for the deleted items folder alone. Does Outlook treat messages in the deleted items folder any differently than messages in other folders? Would it be better for him to never delete messages since he doesn't really want to delete them? It would be better to not delete them since Deleted Items... is for things you want to get rid of. "Wowbagger" <none> wrote in message news:uKyAPby2GHA.3656@TK2MSFTNGP04.phx.gbl... > The big boss refuses ...

selected row count of list box
A2k Is there a way to get the selected row count of a list box dynamically as the user selects rows? Delphi has an event called "OnSelectionChanged" but Access is much more limited. I don't want the user to have to exit the list box or click a button or anything manual in order to see the # of rows he's selected. How can this be done? I know about "lstCusts.ItemsSelected.Count" but not sure what event to use it in to accomplish what I need. Thanks, Keith Never mind. I did this and it handles both mouse and keyboard selections: Private Sub lstCusts_AfterUpdat...

Select all of a certain column across multiple worksheets
Is there an easy way to simultaneously select (or do a find-and-replace) on column H on every worksheet of an entire multi-sheet workbook, without having to select that column on each individual worksheet by hand? Charles Belov SFMTA Webmaster http://www.sfmta.com/webmaster Right-click on first sheet tab and "Select all sheets" In activesheet select the column............will be selected on all sheets. Gord Dibben MS Excel MVP On Mon, 24 Aug 2009 16:43:02 -0700, "Charles Belov" <invalid@invalid.invalid> wrote: >Is there an easy way to simultaneously se...

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

Can't receive EMail #5
Keep getting this pop up along with 'server did not connect within 60 seconfs, do I want to wait A time-out occurred while communicating with the server. Account: 'Richard', Server: 'pop.east.cox.net', Protocol: POP3, Port: 110, Secure(SSL): No, Error Number: 0x800CCC19 Please reply using lmmurray@cox.net Thanks! Hi RASZ, please read this information from Microsoft http://support.microsoft.com/default.aspx?scid=kb;EN-US;813518 -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. Bravestar@Datenschutzminis...

Backing Up Outlook #5
How do I backup copies of all mye-mail folders? Hod do I backup all my contacts within the address book? Hello, You can do a total backup of your Outlook PST file using a back up program in Outlook. To download the ad in go to http://www.microsoft.com/downloads/details.aspx? FamilyId=8B081F3A-B7D0-4B16-B8AF- 5A6322F4FD01&displaylang=en If you have trouble email me at outlook@theblueedge.net Chris >-----Original Message----- >How do I backup copies of all mye-mail folders? Hod do I >backup all my contacts within the address book? >. > "Damon" <damo...

Converting date from an external source
I am having an issued with converting a date from external data source. the data has the timestamp in the general date form mm/dd/yyyy 00:00:00. I want to convert the date to mm/dd/yyyy format so when i run a query for a single day it will return the data for that date, I can currently return the data but i have to set the parameter in the mm/dd/yyyy 00:00:00 format, i want to simply return the data by setting the parameter in the mm/dd/yyyy format Don't confuse how data is stored with how it is presented. As long as you import the date into a field defined as a Date data type, you...

Chart printing issue in Excel 2007
A spreadsheet with charts was created using Excel 2003. I have Excel 2007 and saved it in compatibility mode. I inserted a couple colored lines on the chart and created my own legend based on these. A couple of issues: 1. When I close the file or even minimize, 2 of the colored lines on a couple of my legends disappear upon reopening. 2. When I try to print a chart, it looks good in Print Preview, but then looks magnified,half off the page, and only one of my drawn lines is printed. When someone with 2003 prints, the sizing is correct, but all of the colored drawn lines are missing...

How do I import from LotusOrg 6.0?Import command only has 5.0
I am trying to import my calendar data from Lotus Org V6.X. Under the file command, it will only import from V5.X. Does anybody have any help for me since I would like to convert to Outlook from Lotus Organizer. Don Kiamie donalbert@mindspring.com In news:32C8F514-3EA5-4802-B1A4-F9C66E77293A@microsoft.com, DonAlbert <DonAlbert@discussions.microsoft.com> typed: > I am trying to import my calendar data from Lotus Org V6.X. Under > the file command, it will only import from V5.X. Does anybody have > any help for me since I would like to convert to Outlook from Lotus > ...

emails not showing up in deleted items until i do a find?
This just started happening a last week. A message comes in the Inbox and I click the delete key so delete it.. so far ok. then i go into the deleted items folder it's not visible. but if i do a find then they show up in the find box but still not in the folder. any ideas? this program makes me crazy sometimes *s* thanks starindy <anonymous@discussions.microsoft.com> wrote: > This just started happening a last week. A message comes > in the Inbox and I click the delete key so delete it.. so > far ok. then i go into the deleted items folder it's not > visible. b...

Install 2003 after 2007?
I had Office 2003 and upgraded to 2007, other than Outlook I didn't like it. I wanted to reinstall 2003 Excel & Word. Per instructions here I uninstalled 2007 except Outlook and upon attempting to reinstall 2003 I get the error message, "Business Contact Manager requires Outlook 2003, please install Outlook 2003 before running setup". Help please. Thanks in advance... Bob Answered in the other group post "Bob Newman" <bobnewman@cox.net> wrote in message news:jH5Xl.21061$IP7.4196@newsfe23.iad... >I had Office 2003 and upgraded to 2007, other tha...