Using external query (mdb) for dynamic date range
I'm trying to set up an Excel workbook with a dynamic link to an Access
database containing quality data, involving dates. I want the workbook to
always import data for the last 90 days. When I try to set this up using MS
Query, to try to filter data I've tried several different variations where
time is greater than or equal to:
And every time, it comes back as an error. It seems to be setting the query
so that the entire statement is a date variable, eg "Syntax error in query
expression '((Table1.Time>=#date()-30#))'...Date/Time Question
I have a start date and time which is inputed to a form in the format of
I also have a total time which is in decimal hours. Like 25.61.
How would I create an expression in a query to take the start date/time, add
the total time to it, and get the result in the date/time format.
If possible I would also like to exclude the hours between midnight and 5 AM.
Thanks in advance,
You are, to some extent, mixing apples and oranges -- both of your items are
somewhat related to dates, but not related directly to each other.
If you have a Date/Time value...returning a date 7 business day excluding holidays in the future
How would I write a formula that would do this. If I input a date in cell A1
and I have holidays in cells R1:R11. Can I write a formula that would give
me a date in cell B2 that is 7 business days excluding holiday from the date
I am having a hard time getting the networkdays and holidays working if I am
not inputing 2 dates.
>I am having a hard time getting the networkdays
>and holidays working
Use the WORKDAY function:
Format as Date
Microsoft Excel MVP
"excelrookie" <excelrookie@discussio...Date Time Picker lost
I created a form using the DateTimePicker Control to write in a field.
Then I removed the DateTimePicker Control.
When I publish the form to another computer where the DateTimePicker Control
is not installed, the form does not work.
If I install the DateTimePicker Control the form is working without
I want to sent this contact form to other people who does not have this
In the fact I am not using the DateTimePicker Control but it must be
somewhere in the form, but I cannot find it.
If I put in the form another DTP Control in the list of the fields it is
shown as DTP2 b...stop end time of appointment from printing
I print out the month view of the calendar for my boss constantly and he
carries it with him. How can I prevent the end time of an appointment from
printing? He only want to see the start time of his appointments.
If it shows in the onscreen calendar, it will print if the cells are wide
Diane Poremsky [MVP - Outlook]
Outlook Tips: http://www.outlook-tips.net/
Outlook & Exchange Solutions Center: http://www.slipstick.com/
Outlook Tips by email:
EMO - a weekly newsletter about Outlook and Exchange:
m...e-mail owner, when many recipients ?
Today I received e-mail adressed to many recipients and I didn't owners of
this one, but last person who was on the recipients list To: was...
The last person didn't registered contact in CRM, I try it with registered
recipients it was the same. Any time I didn't owner of e-mail but always last
person on the recipients list.
Who know why ?
And why when I want assign to me this e-mail at first I must assign to
present owner and after that one more timy try assign to me
What version of CRM are you using?
How is this email being generated? (workflow rule, callout,
crmservice, ...Running a query in SSIS thwn importing Excel spreadsheets
I've just been learning SSIS to import Excel 2003 spreadsheets into SQL
Server 2005, and so far, so good.
I want to take my use of SSIS to the next level, so to speak, and could
use some help.
Here's the scenario:
I've got spreadsheet data representing names that must be consolidated.
As can happen with so many
businesses, at my workplace, there's a big effort underway to correct
and consolidate names that may
have been entered many different ways over the years, in several
different systems. Now that the systems
are being unified, we must have corrected, ...Subtracting times..confused
How do I properly enter times to produce a "correct" result?
For example, trying to figure out the hours worked by various employees.
Finish time - start time
For example, 11 Am -8 am results in 3 AM, I want this to result in 3 hours.
Is this a formatting issue?
Any assistance would be appreciated.
Thanks in advance.
you can change the format of the cell to just an hour with out the AM
so - 8 AM - 5 AM will give you just 3:00
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet...Using excel as a time clock
How can I make excel work as a time clock. I want to have
a spreadsheet showing, for example, someone works from
10:32A.M. until 7:32P.M. I need a formula column saying
how many hours this totaled.
Help and thank you.
XL stores times as fractional days (e.g., 3:00 = 0.125), so you can
add and subtract, with the caveat that at midnight 0.999..
(23:59:59.. or 11:59:59..PM) becomes 0, so you need to account for
A1: 10:32 AM
A2: 7:32 PM
A3: =A2-A1 ==> 8:00
when A3 is formatted as h:mm
If the times may span midnight, use:
A3: =MOD(A2-A1,1)...Date/Time Macro Puzzle
Here is a macro that deposits the date and time into a cell, but which =
supposed to pop up a warning box when the time is after 5:00PM:
Dim mPrompt As String
Dim mBoxStyle As Long
Dim mTitle As String
Dim mMsg As Variant
mPrompt =3D "It's after 5:00 PM! Click OK to enter time, but please =
to enter TOTAL HOURS WORKED TONIGHT in the yellow box at right.=20
mBoxStyle =3D 64
mTitle =3D "AFTER-HOURS ENTRY"
.Value =3D Now
.NumberFormat =3D "mm/dd/yy h:mm AM/PM"
If Now...Text manipulation in update query
I want to compare two tables, in order to update fields in either table. The
fields that I am matching on are text.
Table 1 field key_num
Table 2 field key_num
How can I parse out the hyphens and the _nn portion, so I can match on the
Mid([table1],1,13) Start at the 1st character and show the next 13.
State of Arkansas
"Tony in Michigan" wrote:
> I want to compare two tables, in order to update fields in either table. The
> fields that I am matching on are text.
> Table 1 field key_...windows 2007 cannot find queries or forms
I used to go to windows unhide to find my list of queries to modify.
Now we are updated to 2007 and I cannot find this on the ribbon bar to check
items in an updated database
You can show the database window by clicking F11 and then F11 to sort of hide
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
> I used to go to windows unhide to find my list of queries to modify.
> Now we are updated to ...Help with SQL Query Update Prior Transaction item Cost
Hi can any one tell me how to update past transactions item cost so my
historical P&L reports are correct. Currently any items with 0 cost show up
as 100% profit it looks like my profit is much higher then it really is there
a way to export all 0 cost items into excel fix cost prices then update data
base with SQL query imported from excel please explain how to do this Thanks
What cost are you going to use?
Are you just going to use the current cost?
Do you need the date of the transaction to use to look up manual records?
Here is a select query that will show you how many re...online time out
Is there some way to change the time out value Mny03 used to determine
when to stop waiting for an institution to reply? It is an annoyingly
long time. Quiting / cancel never works (Win98SE). I could plow
through ini's and the registry but maybe someone here knows.
In microsoft.public.money, Arthur wrote:
>Is there some way to change the time out value Mny03 used to determine
>when to stop waiting for an institution to reply? It is an annoyingly
>long time. Quiting / cancel never works (Win98SE). I could plow
>through ini's and the registry but maybe s...Excel 2000 Web query imports incomplete hyperlink info
Would anyone know why Excel 2000 chops off the end of
certain hyperlink addresses imported by web query? In this
I was surprised to see this behaviour as the same thing
works fine in Excel 2003.
Thanks in advance,
Could you give a wee example so we know what you
are working with.
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Peter ...need help with msquery query
i am using access 2000 database and an excel 2000 spreadsheet. the access
data base also has linked tables to a SQLAnywhere database. the data that i
need to pull comes from both a native access table and a linked table to the
SQLAnywhere database. when just pulling data from the access table, using
parameters to limit the data, i get the data that i selected. when i create
a new query using both the data from the access table and the SQLAnywhere
table, i am getting way too much data. The parameters from the msquery seem
to not be working. can someone help me with this?
Well i am quite a novice to Xquery/Xpath.I have my data in XML which i load
into the XMLDocument and Dataset and subsequently bind the dataset to the
The data with hierarchy is well displayed in the grid.Now i need to
for better analysis.I tried using Xpath query / XpathExpression / Xpath
Navigator but however i am not able bind the filter data as a datasource to
Could some one suggest a better way to go about so that i could query on the
XMLDocument & subsequetly use the queried result as a datasource to the
...Day of the month on which accounts will be closed
Specify 2 or more date of the month on which accounts will be closed.
Customer closing day 10
Customer closing Weekly (15)
Customer closing End OF The Month (30)
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...edit of oqy
I'm trying to edit an olap query to add more data to the
query, but I go into pivot table wizard and the edit
button is greyed-out so I cannot edit. Anyone any ideas?
is this a feature?
...Using time functions to give a warning
In cell I14 I need to put a formula which says 'Open' when the PC clock
is between 8 am and 4.30 pm , and 'Closed' when the PC clock is later
than 4.30 pm.
Can someone help with the code , please?
This will not auto update. You will have to press F9 to update
Microsoft Excel MVP
"Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message
news:656VvXAJZ6SLFwD9@ch...Hours Disappearing in Previous Time Periods in My Tasks
I am having an issue where submitted hours do not appear in the My
Task web part for previous time periods. We are using MOPS 2007
(fully patched EXCEPT for Feb. 2010 CU) with timesheets turned OFF.
The issue does not affect every user.
From what I have seen, when it is working properly the previously
submitted hours that live in the work plan should populate when a user
clicks the "Previous" button. Many of my users do not see any values
when they click "Previous".
I haven't been able to dig anything on this up and I would like to
figure this o...Multiple domains in Query-based Distribution List
I would like to find out if/how it is possible to setup a query-based DL
with the following:
Include: All users with email address @domain1.com **OR** all users
I know you can add multiple domains/email addresses but what it effectivley
does is look for All users with email address @domain1.com **AND** all users
i.e The object has to satisfy both the criteria. I need a DL that picks up
the users if they satisfy either/or.
Your assistance is greatly appreciated
Here's a response I gave to a similar issue that was posted a few days
...Macros and Queries
I have someone who has queries and macros on a spreadsheet
and they would like to get those on someone else's
computer for that person to use as well. How do you
transfer those to another computer to use in another
Thanks in Advance,
...Run-time Error 6 when Calculating Physical Inventory #2
Any ideas? tried reindexing and checking for gross large quantities.
...Dates Stored with incorrect Date&Time
When inserted/updated a date field in the contact form the date displayed
(correct) is different from the date stored in the database (that is
BirthDate (default crm field) : value introduced in the form: 18/04/2005
BirthDate (default crm field) : value stored in the contactbase table:
We have MS CRM installed in a SBS2003 Server.
All clents Clients and the SBS2003 Server have the Date&Time = (GMT)
Greenwich Mean Time (Lisbon, London,...)
Could anyone help me?
All datetime values are stored in GMT, so there will always be...