Reference a Date and Time in Query

Hello,

I am trying to use a query to filter the table results by Date and
Time. The table data is in the format:

mm/dd/yyyy HH:MM:SS *M

I already have a query that selects the data between two dates. But I
also want to be able to select two dates and two times. Such as
Between 3/15/2007 9:00 AM and 3/15/2007 10:00 AM, or use two different
dates and times, etc.

I have gotten close by using:

Between ([Forms]![RC_WithTime]![Calendar1]+TimeSerial(9,0,0)) And
([Forms]![RC_WithTime]![Calendar2]+TimeSerial(10,0,0))

but as soon as I try to convert the TimeSerial to link to forms it
says that there is an error or it is too complex.

Is there a way to do this?

0
tmm786
9/17/2007 3:07:00 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1022 Views

Similar Articles

[PageSpeed] 14

On Sep 17, 1:44 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:
> >>The table data is in the format: mm/dd/yyyy HH:MM:SS *M
>
> What is the asterisk and 'M' after your date and time?
>
> Is the field a text filed or is it a DateTime datatype in your table?  It do
> make a difference.
>
> If it is a DateTime field you should be able to use this ---
> Between [Forms]![RC_WithTime]![Calendar1] And
> [Forms]![RC_WithTime]![Calendar2]
>    and enter date time like this ---
>    09/17/2007 11:23:00
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
>
> "tmm786" wrote:
> > Hello,
>
> > I am trying to use a query to filter the table results by Date and
> > Time. The table data is in the format:
>
> > mm/dd/yyyy HH:MM:SS *M
>
> > I already have a query that selects the data between two dates. But I
> > also want to be able to select two dates and two times. Such as
> > Between 3/15/2007 9:00 AM and 3/15/2007 10:00 AM, or use two different
> > dates and times, etc.
>
> > I have gotten close by using:
>
> > Between ([Forms]![RC_WithTime]![Calendar1]+TimeSerial(9,0,0)) And
> > ([Forms]![RC_WithTime]![Calendar2]+TimeSerial(10,0,0))
>
> > but as soon as I try to convert the TimeSerial to link to forms it
> > says that there is an error or it is too complex.
>
> > Is there a way to do this?- Hide quoted text -
>
> - Show quoted text -

I was trying to show with the * that it was AM or PM.  It is a Date/
TimeField.  I think I have gotten it figured out though.  I ended up
finding a different Date/Time Picker that worked for what I needed.

Thanks for the reply.

0
tmm786
9/17/2007 6:32:41 PM
Reply:

Similar Artilces:

Unreadable date
I enter a date into a cell, such as 1/16/2006, and it appears as ######## when I hit enter or click on another cell. Why? the ######## just shows when there is not enough room to show the value of the cell. Either change the format to something smaller m/d/yyyy or enlarge the column "Mark" wrote: > I enter a date into a cell, such as 1/16/2006, and it appears as ######## > when I hit enter or click on another cell. Why? ...

00/01/1900 In the date cell when linked cell is empty?
Hi I have 52 worksheets all linked up to a total sheet that is working very well thanks to RD All I need to do now is sort out the date this is copied from sheet1 onto the totals page and works fine when the date is put into sheet1. However if the cell is blank on sheet2 the date is put into the totals sheet as 00/01/1900 when the date is put in sheet2 all is well with the right date entered into the totals sheet. So how do I get the totals date to stay blank until the weekly sheets have a date put into the sheet please. the format I am using is below many thanks for all the help I ...

Need to reference a block of cells from another sheet.
Dear All, apologies if this is probably a mundane question, but I didn't know what 'search terms' to put in google to return any results.. I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet) to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet), tabs 1-11 (sheets) are changed too.. the cells themselves will never move location, just the data within them.. Is it possible? Regards Paul. On your sheet1 select the cell that should receive the data from Sheet12; Hold down the Shift key and Click on Sheet11 << this shoul...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

Date Format Goofiness
Just loaded Money 2004 Deluxe and imported my checking register from Money 2001 Standard. Everything came across fine but the date format changed to day-month-year from month-day-year or as an example what was 12 31 03 is now 31 12 03. I'd like to change the format back to American standards. Any ideas? I can't find any date format change options within Money and my Win XP (Home) settings are already correct. ...

Coping with missing year value in date data type field (Rephrased/Repost)
Hi! Suppose I am collecting Date of Birth data for a contacts application. However, some contacts are only willing to provide day and month information, but withold the year of birth. What's a good way to deal with this? I can think of two, but... 1. Use a "dummy" value for an unknown year, (e.g. 1900). This would allow the use of a date/time data type and be fairly easy to implement, but it sure feels like I would be breaking some kind of relational rule to enter "fake" data. 2. Create three text fields (txtDay, txtMonth, txtYear). This would allow the use of...

Calculation on the time it takes to move a mailbox.
As anybody every find a good method to calculate mailbox move time. How long would it take a 1.5G Mailbox to move between 2 Servers? "wb" <wb@discussions.microsoft.com> wrote: >As anybody every find a good method to calculate mailbox move time. > >How long would it take a 1.5G Mailbox to move between 2 Servers? > > You haven't said how fast the sending or receiving servers are, not have you specified whether the servers are on a Gigabit link or just 100BaseTX. Neither have you said how busy the servers are likely to be during the move doing normal serv...

What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under the Inventory account and Credit for COGS account in the Transaction Entry Zoom. We're trying to figure out where that amount came from but we couldn't zoom more than what it's currently shown. In both fields, Reference and Distribution Reference, it's shown IVA#######... that is unknown to or setup by us in anywhere, i.e. Audit Trail, Source Document. Does this amount affect the dollar in the accounts mentioned? And can anyone please help to where we could drill down the IVA details?...

Date Formula #2
I am trying to write a formula that works off what is in two separate cells. It has several parts and maybe not all of them can be done. It would be great if someone could help me out. Here goes. If A2 & A3 each have a "N" in it then I want cell A5 to show today's date plus 20 weeks. However, if only 1 of the cells A2 or A3 has a "N" in I still want A5 to show today's date plus 20 weeks. Can this also be done? If both A2 & A3 have "Y" in them then I want A5 to freeze with the date last show when 1 of the two cells had a "N" it ...

macro button refers to prev. workbook?
Hi, When I create a macro in Excel, and assign a button to it, it works fine the first time, but when I re-open the workbook for a second time, and try to run the macro again, the macro seems to refer to the previous workbook...For some reason, the macro saves the name of the previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select name = ActiveSheet.Range("B6").Value adress = ActiveSheet.Range("B4").Value ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

[ANN] Daylight Saving Time
The US made the switch to Daylight Saving Time this past week-end. If you use Entourage 2004 or 2008 and it's been running through the weekend, you'll need to quit it and re-launch before it recognizes the time change. Continue reading at <http://blog.entourage.mvps.org/> -- Diane, Microsoft Mac MVP (MVPs are not Microsoft Employees) Entourage Help Page <http://www.entourage.mvps.org/> Entourage Help Blog <http://blog.entourage.mvps.org/> ...

Purchase order and transfers date filter
We believe that Microsoft should change the default date filter on the purchase order/transfer screen to "All Dates" rather then the current month to date. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/N...

Schedule for the next release? CRM 2005 Date? Official?
I came across a tangential post that said the RTM date for CRM 2005 (or CRM 2.0 or whatever it will be called) is October 2005. I would like to see this post produce a trustworthy timeframe. If you have any clear timeframes from Microsoft, please share. (MBS Sales Support just replied to me saying, "Currently it is scheduled for release Calendar Q4 2005" but I want to see what else you all have heard.) That is about as official as it gets. I have heard the target of October which would mean GA sometime in december or January. Matt Parks MVP - Microsoft CRM ---------------...

Cube returns dimension from whole table, not from query?
Hello, in excel, I get data from sql server 2k. I create a query like this: select ... from tblCustomers, tblSelection where tblCustomers.customerid = tblSelection.id The table tblCustomers has thousands of customer id's; but the table "tblSelection" has only a few - those that need to be included in this particular Excel report. When I run the query in MS Query, it works correctly, i.e. it returns only the selected customers. However, then I create OLAP cube in MS Query, save it as file, and return the cube to Excel. In the resulting pivot table, Customers include not ...

Error running a link query in a query page in Business Portal
Hi, I am working on Business Portal 4.0 and created some custom entities and created a query page. Transactions and CodtCodes were two of my custom entities. When i run Transactions Query it will show CostCodes link in the links section along with other links.When i click cost codes link in the links section it was showing an error saying "Can't process query (Status=TraverseHyperlink)" when i click details it was showing Microsoft.BusinessFramework.Data.InvalidDataSourceException The key 'JobCost.CostCodes+key' has multiple segments. Some segments of the key ha...

can i change plot area colors for different date ranges
if i want to shade the plot area of a chart diferent colors, for example gray from Jan 1 through Fed 17, then white until March 10, is that possible? Will want to change the shadinging preiodically and fairly frequently. Thank you Hi, You should be able to use the technique explained on Jon's page http://peltiertech.com/Excel/Charts/ColoredVerticalBand.html Cheers Andy steve wrote: > if i want to shade the plot area of a chart diferent colors, for example gray > from Jan 1 through Fed 17, then white until March 10, is that possible? Will > want to change the shadinging pr...

Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a colleague's machine at work (Office 2003; VBA 6.5; XP). I've read Chip Pearson's "Missing References in VBA" which is a geat explanation about how to fix problems like this. Oddly the library didn't seem to be flagged as "missing" and was in fact present in the system32 directory but didn't show up in the list of available references. Now to the questions: I'm not clear on whether if I write code in a project that references a library which is not part of the &qu...

PowerShell Query Help, WMI, Select Statement
Here is where I have started: #Get Physical Memory function getwmiinfo ($svr) { gwmi -query "select * from Win32_PhysicalMemory" -computername $svr | select [$svr], DeviceLocator } $Servers = get-content -path "C:\test.txt" foreach($Servers in $Servers) { getwmiinfo $Servers } I get this: [risk] DeviceLocator ------ ------------- DIMM0 DIMM1 What I want is this: ServerName DeviceLocator ---------- ...

Update query
They scare me! I do have a copy of the db. Here's what I'm trying to say: Find Contacts who are Board Members and make them Gala Members as well. Here's my query to find Board Members: SELECT Contacts.LastName, Contacts.ContactID FROM Contacts INNER JOIN (Groups INNER JOIN GroupMembers ON Groups.GroupID = GroupMembers.GroupID) ON Contacts.ContactID = GroupMembers.ContactID WHERE (((Groups.GroupName)="Board of Directors") AND ((GroupMembers.GMemberEnd) Is Null)); The Groups.GroupID for "Gala" is 12. GroupMembers contains: GroupMemberID, GroupID, ContactID,G...

Date Formats for Cells Do not match System Date Formats
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have just loaded Office 2008 and then update to the latest update 12.2.4 <br> I have my system preferences set to Australia for date and time using the date format dd/mm/yyyy. But in Excel when I open the format cell dialogue and select date the only formats available for selection are in the mm/dd/yyyy formats. When I checked in &quot;Numbers&quot; the dd/mm/yyyy format is used so &quot;Numbers&quot; is picking up the system preference why not the excel. Did you by any chance have ...

How to create a calendar from date information in XL sheet.
I have an XL spreadsheet that contains multiple columns, one of which contains data in date & time format. Is there a way to export this data and associated information from the record to another application, for example Word, and create a calendar? Any suggestions how to manipulate this XL data are welcomed. Thanks! ...

Reference
Is it possible to create a reference to another object such as: int & x = i; but using an image list, and referencing different objects depending on a condition? such as : CImageList & imageList; if (thumbnail == 1) imageList = m_ImageListThumb; else imageList = m_ImageListFullSize; Hope this makes sense! Ben You're better off using pointers for this. I believe, depending on the class, that what you're trying to will make a copy of the object instead of referencing it. "Ben Williamson" <oakdaleclose@googlemail.com> wrote in message news:43ee4603...

Macro to Reference Column Next to Current Reference
Hi, I'd like to automate the following procedure using a macro. Please help. 1) i have 2 sheets: Sheet1 & Sheet2 2) In Sheet1, Cells B2,D2,F2 has the formulas "=Sheet2! F2", "=Sheet2!K2", "=Sheet2!P2" correspondingly. 3) Every month when i do my work, i'd have to manually rekey the formulas to reference the subsequent columns, ie, Cell B2,D2,F2 will change from above to "=Sheet2! G2", "=Sheet2!L2", "=Sheet2!Q2" and in the following month, I have to change manually again to "=Sheet2! H2", "=Sheet2!M2&qu...