Latest date in a query!


What would I need to enter to show the one record of each client by latest 
date?
Thanks for any Help..........Bob 


0
Bob
12/10/2007 6:29:10 PM
access 16762 articles. 3 followers. Follow

6 Replies
614 Views

Similar Articles

[PageSpeed] 51

In a Totals query select Client - Group By and action date - Maximum.
-- 
KARL DEWEY
Build a little - Test a little


"Bob V" wrote:

> 
> 
> What would I need to enter to show the one record of each client by latest 
> date?
> Thanks for any Help..........Bob 
> 
> 
> 
0
Utf
12/10/2007 6:42:11 PM
If you want to show the entire record for each client, but only the most 
recent one for each client then your query would look something like:

SELECT tblClients.*
FROM tblClients
WHERE tblClients.DateField = (SELECT MAX(DateField) 
                                             FROM tblClients C
                                             WHERE C.ClientID = 
tblClients.ClientID)

Another way of doing this would be:

SELECT tblClients.*
FROM tblClients
INNER JOIN (SELECT ClientID, MAX(DateField) as MaxDate
                   FROM tblClients
                   GROUP BY ClientID) as LatestDate
ON tblClients.ClientID = LatestDate.ClientID
AND tblClients.DateField = LatestDate.MaxDate

HTH
Dale

-- 
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Bob V" wrote:

> 
> 
> What would I need to enter to show the one record of each client by latest 
> date?
> Thanks for any Help..........Bob 
> 
> 
> 
0
Utf
12/10/2007 7:40:03 PM
Thanks Dale here is my SQL:
SELECT tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName, 
tblAccountStatus.BillDate, tblAccountStatus.PaidAmount, QryOverdue.Payable, 
IIf(IsNull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & ' ') & 
IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & ' 
') & IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName) 
AS ClientName, tblAccountStatus.OwnerID
FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID = 
tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON tblOwnerInfo.OwnerID = 
QryOverdue.OwnerID;

Can this be altered..............Regards Bob

"Dale Fye" <dale.fye@nospam.com> wrote in message 
news:AB79B906-E182-42D4-9DA1-E73E6C7E4D79@microsoft.com...
> If you want to show the entire record for each client, but only the most
> recent one for each client then your query would look something like:
>
> SELECT tblClients.*
> FROM tblClients
> WHERE tblClients.DateField = (SELECT MAX(DateField)
>                                             FROM tblClients C
>                                             WHERE C.ClientID =
> tblClients.ClientID)
>
> Another way of doing this would be:
>
> SELECT tblClients.*
> FROM tblClients
> INNER JOIN (SELECT ClientID, MAX(DateField) as MaxDate
>                   FROM tblClients
>                   GROUP BY ClientID) as LatestDate
> ON tblClients.ClientID = LatestDate.ClientID
> AND tblClients.DateField = LatestDate.MaxDate
>
> HTH
> Dale
>
> -- 
> Don''t forget to rate the post if it was helpful!
>
> email address is invalid
> Please reply to newsgroup only.
>
>
>
> "Bob V" wrote:
>
>>
>>
>> What would I need to enter to show the one record of each client by 
>> latest
>> date?
>> Thanks for any Help..........Bob
>>
>>
>> 


0
Bob
12/11/2007 12:06:44 AM
Got It :)
SELECT 
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle] & ' 
') & 
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName] 
& '  ') & 
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName]) 
AS ClientName, Max(tblAccountStatus.BillDate) AS LastDate, 
QryOverdue.Payable, tblAccountStatus.OwnerID, tblOwnerInfo.OwnerFirstName, 
tblOwnerInfo.OwnerLastName
FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID = 
tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON tblOwnerInfo.OwnerID = 
QryOverdue.OwnerID
GROUP BY 
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle] & ' 
') & 
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName] 
& '  ') & 
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName]), 
QryOverdue.Payable, tblAccountStatus.OwnerID, tblOwnerInfo.OwnerFirstName, 
tblOwnerInfo.OwnerLastName
ORDER BY QryOverdue.Payable DESC;

"Bob V" <rjvance@ihug.co.nz> wrote in message 
news:ug4d1m4OIHA.4712@TK2MSFTNGP04.phx.gbl...
> Thanks Dale here is my SQL:
> SELECT tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName, 
> tblAccountStatus.BillDate, tblAccountStatus.PaidAmount, 
> QryOverdue.Payable, 
> IIf(IsNull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & ' ') & 
> IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & ' 
> ') & IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName) 
> AS ClientName, tblAccountStatus.OwnerID
> FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID 
> = tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON tblOwnerInfo.OwnerID = 
> QryOverdue.OwnerID;
>
> Can this be altered..............Regards Bob
>
> "Dale Fye" <dale.fye@nospam.com> wrote in message 
> news:AB79B906-E182-42D4-9DA1-E73E6C7E4D79@microsoft.com...
>> If you want to show the entire record for each client, but only the most
>> recent one for each client then your query would look something like:
>>
>> SELECT tblClients.*
>> FROM tblClients
>> WHERE tblClients.DateField = (SELECT MAX(DateField)
>>                                             FROM tblClients C
>>                                             WHERE C.ClientID =
>> tblClients.ClientID)
>>
>> Another way of doing this would be:
>>
>> SELECT tblClients.*
>> FROM tblClients
>> INNER JOIN (SELECT ClientID, MAX(DateField) as MaxDate
>>                   FROM tblClients
>>                   GROUP BY ClientID) as LatestDate
>> ON tblClients.ClientID = LatestDate.ClientID
>> AND tblClients.DateField = LatestDate.MaxDate
>>
>> HTH
>> Dale
>>
>> -- 
>> Don''t forget to rate the post if it was helpful!
>>
>> email address is invalid
>> Please reply to newsgroup only.
>>
>>
>>
>> "Bob V" wrote:
>>
>>>
>>>
>>> What would I need to enter to show the one record of each client by 
>>> latest
>>> date?
>>> Thanks for any Help..........Bob
>>>
>>>
>>>
>
> 


0
Bob
12/11/2007 1:17:51 AM
OOPS BUG:    If there has never been a payment the name will not show 
QryOverdue shows the name and amount but without a PayedAmount been put in 
tblAccountStatus the Owner will not show....Thanks if you can help..Bob

"Bob V" <rjvance@ihug.co.nz> wrote in message 
news:uAcukO5OIHA.4948@TK2MSFTNGP02.phx.gbl...
> Got It :)
> SELECT 
> IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle] & ' 
> ') & 
> IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName] 
> & '  ') & 
> IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName]) 
> AS ClientName, Max(tblAccountStatus.BillDate) AS LastDate, 
> QryOverdue.Payable, tblAccountStatus.OwnerID, tblOwnerInfo.OwnerFirstName, 
> tblOwnerInfo.OwnerLastName
> FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON tblAccountStatus.OwnerID 
> = tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON tblOwnerInfo.OwnerID = 
> QryOverdue.OwnerID
> GROUP BY 
> IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle] & ' 
> ') & 
> IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName] 
> & '  ') & 
> IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName]), 
> QryOverdue.Payable, tblAccountStatus.OwnerID, tblOwnerInfo.OwnerFirstName, 
> tblOwnerInfo.OwnerLastName
> ORDER BY QryOverdue.Payable DESC;
>
> "Bob V" <rjvance@ihug.co.nz> wrote in message 
> news:ug4d1m4OIHA.4712@TK2MSFTNGP04.phx.gbl...
>> Thanks Dale here is my SQL:
>> SELECT tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName, 
>> tblAccountStatus.BillDate, tblAccountStatus.PaidAmount, 
>> QryOverdue.Payable, 
>> IIf(IsNull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & ' ') & 
>> IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & 
>> ' ') & 
>> IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName) AS 
>> ClientName, tblAccountStatus.OwnerID
>> FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON 
>> tblAccountStatus.OwnerID = tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON 
>> tblOwnerInfo.OwnerID = QryOverdue.OwnerID;
>>
>> Can this be altered..............Regards Bob
>>
>> "Dale Fye" <dale.fye@nospam.com> wrote in message 
>> news:AB79B906-E182-42D4-9DA1-E73E6C7E4D79@microsoft.com...
>>> If you want to show the entire record for each client, but only the most
>>> recent one for each client then your query would look something like:
>>>
>>> SELECT tblClients.*
>>> FROM tblClients
>>> WHERE tblClients.DateField = (SELECT MAX(DateField)
>>>                                             FROM tblClients C
>>>                                             WHERE C.ClientID =
>>> tblClients.ClientID)
>>>
>>> Another way of doing this would be:
>>>
>>> SELECT tblClients.*
>>> FROM tblClients
>>> INNER JOIN (SELECT ClientID, MAX(DateField) as MaxDate
>>>                   FROM tblClients
>>>                   GROUP BY ClientID) as LatestDate
>>> ON tblClients.ClientID = LatestDate.ClientID
>>> AND tblClients.DateField = LatestDate.MaxDate
>>>
>>> HTH
>>> Dale
>>>
>>> -- 
>>> Don''t forget to rate the post if it was helpful!
>>>
>>> email address is invalid
>>> Please reply to newsgroup only.
>>>
>>>
>>>
>>> "Bob V" wrote:
>>>
>>>>
>>>>
>>>> What would I need to enter to show the one record of each client by 
>>>> latest
>>>> date?
>>>> Thanks for any Help..........Bob
>>>>
>>>>
>>>>
>>
>>
>
> 


0
Bob
12/11/2007 2:14:33 AM
Bob,

You are going to have to use a somewhere in here, and may actually need a 
subquery.

Assuming that tblOwnerInfo contains info on all owners, then that is the 
table you want to start your query with.  Because you have indicated that if 
they have never made a payment, there will not be an entry in 
tblAccountStatus, I recommend that you create outer joins by linking the 
OwnerID field from tblOwnerInfo to each of the other two tables.  Then edit 
the join type to select all from tblOwnerInfo and only the matching records 
from each of the other tables.  Because tblOwnerInfo is your base table in 
this query, you should never have Nulls in the OwnerTitle, OwnerFirstName, 
OwnerLastName.
But to use the Max( ) aggregate operator, you will also need to use a 
GroupBy

What does qryOverdue look like?  I would expect that you would need the 
OwnerID, LastPaymentDate, and AmountDue in that query to determine who is 
overdue.  If so, why do you even need tblAccountStatus in this query?

At any rate, try it like:

SELECT [tblOwnerInfo].[OwnerTitle], _
               [tblOwnerInfo].[OwnerFirstName], _
               [tblOwnerInfo].[OwnerLastName], _
               Max(tblAccountStatus.BillDate) AS LastPaymentDate,
               QryOverdue.Payable
 FROM (tblOwnerInfo LEFT JOIN tblAccountStatus _
              ON tblOwnerInfo.OwnerID = tblAccountStatus.OwnerID)
LEFT JOIN QryOverdue
            ON tblOwnerInfo.OwnerID = qryOverDue.OwnerID
GROUP BY [tblOwnerInfo].[OwnerTitle], _
                    [tblOwnerInfo].[OwnerFirstName], _
                    [tblOwnerInfo].[OwnerLastName], _
                    QryOverdue.Payable

HTH
Dale

"Bob V" <rjvance@ihug.co.nz> wrote in message 
news:%23tIZQu5OIHA.4752@TK2MSFTNGP05.phx.gbl...
> OOPS BUG:    If there has never been a payment the name will not show 
> QryOverdue shows the name and amount but without a PayedAmount been put in 
> tblAccountStatus the Owner will not show....Thanks if you can help..Bob
>
> "Bob V" <rjvance@ihug.co.nz> wrote in message 
> news:uAcukO5OIHA.4948@TK2MSFTNGP02.phx.gbl...
>> Got It :)
>> SELECT 
>> IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle] & 
>> ' ') & 
>> IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName] 
>> & '  ') & 
>> IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName]) 
>> AS ClientName, Max(tblAccountStatus.BillDate) AS LastDate, 
>> QryOverdue.Payable, tblAccountStatus.OwnerID, 
>> tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName
>> FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON 
>> tblAccountStatus.OwnerID = tblOwnerInfo.OwnerID) INNER JOIN QryOverdue ON 
>> tblOwnerInfo.OwnerID = QryOverdue.OwnerID
>> GROUP BY 
>> IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle] & 
>> ' ') & 
>> IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName] 
>> & '  ') & 
>> IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName]), 
>> QryOverdue.Payable, tblAccountStatus.OwnerID, 
>> tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName
>> ORDER BY QryOverdue.Payable DESC;
>>
>> "Bob V" <rjvance@ihug.co.nz> wrote in message 
>> news:ug4d1m4OIHA.4712@TK2MSFTNGP04.phx.gbl...
>>> Thanks Dale here is my SQL:
>>> SELECT tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerLastName, 
>>> tblAccountStatus.BillDate, tblAccountStatus.PaidAmount, 
>>> QryOverdue.Payable, 
>>> IIf(IsNull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & ' ') & 
>>> IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & 
>>> ' ') & 
>>> IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName) AS 
>>> ClientName, tblAccountStatus.OwnerID
>>> FROM (tblAccountStatus INNER JOIN tblOwnerInfo ON 
>>> tblAccountStatus.OwnerID = tblOwnerInfo.OwnerID) INNER JOIN QryOverdue 
>>> ON tblOwnerInfo.OwnerID = QryOverdue.OwnerID;
>>>
>>> Can this be altered..............Regards Bob
>>>
>>> "Dale Fye" <dale.fye@nospam.com> wrote in message 
>>> news:AB79B906-E182-42D4-9DA1-E73E6C7E4D79@microsoft.com...
>>>> If you want to show the entire record for each client, but only the 
>>>> most
>>>> recent one for each client then your query would look something like:
>>>>
>>>> SELECT tblClients.*
>>>> FROM tblClients
>>>> WHERE tblClients.DateField = (SELECT MAX(DateField)
>>>>                                             FROM tblClients C
>>>>                                             WHERE C.ClientID =
>>>> tblClients.ClientID)
>>>>
>>>> Another way of doing this would be:
>>>>
>>>> SELECT tblClients.*
>>>> FROM tblClients
>>>> INNER JOIN (SELECT ClientID, MAX(DateField) as MaxDate
>>>>                   FROM tblClients
>>>>                   GROUP BY ClientID) as LatestDate
>>>> ON tblClients.ClientID = LatestDate.ClientID
>>>> AND tblClients.DateField = LatestDate.MaxDate
>>>>
>>>> HTH
>>>> Dale
>>>>
>>>> -- 
>>>> Don''t forget to rate the post if it was helpful!
>>>>
>>>> email address is invalid
>>>> Please reply to newsgroup only.
>>>>
>>>>
>>>>
>>>> "Bob V" wrote:
>>>>
>>>>>
>>>>>
>>>>> What would I need to enter to show the one record of each client by 
>>>>> latest
>>>>> date?
>>>>> Thanks for any Help..........Bob
>>>>>
>>>>>
>>>>>
>>>
>>>
>>
>>
>
> 


0
Dale
12/11/2007 3:03:06 AM
Reply:

Similar Artilces:

Chronological Date Orders
Hi I have a worksheet column (L6:L34) into which users are asked to enter dates that payments are made. The columns are formatted as 'Date' and Data Validation is set to ensure a date is entered. A blank entry is allowed. Initially all the cells are blank and the user is expected to populate the cells, starting with the earliest date in L6 and moving sequentially down the column. Of course, there's no guarantee it will happen that way. It doesn't matter if a cell is skipped (left blank), but I would like to ensure that any dates that are entered, are entered in chron...

how do i calculate 10% of a total in an access query
I am working in access 2007 and have a goal amount that i need to calculate a 10% increase.. I cant figure out how to do this!! Increased Price: ([YourField] * .1) + [YourField] -- KARL DEWEY Build a little - Test a little "confussed student" wrote: > I am working in access 2007 and have a goal amount that i need to calculate a > 10% increase.. I cant figure out how to do this!! On Mon, 25 Feb 2008 09:13:10 -0800, confussed student wrote: > I am working in access 2007 and have a goal amount that i need to calculate a > 10% increase.. I cant figure out how to do...

Crosstab query question 01-07-10
I have a question about using crosstab query of course maybe there is another way and any help would be great appreciated. The problem is that the crosstab query is that it uses some type of function(sum, first, min) etc. I need it to just show all the data. An example would be: Instrument Date Result Machine1 12/01/2009 20.1 Machine1 12/01/2009 22.5 Machine1 12/02/2009 23 Machine2 12/01/2009 25 Machine2 12/02/2009 ...

Report based on query w/subquery won't give a Report Footer total
I have a report that runs off a query which pulls item entry times per user. Each record in the table has an EntryStartTime field which is updated as soon as a user updates the first field in the entry form. (Technically the current time is stored in a global variable which is updated once the record is committed.) What I want to do is run a report showing how long it takes for a user to begin a new entry (i.e. the time between the start of record 1 and the start of record 2), and the average of those times. However, in my report, whenever I try to add a field in the Report Footer that...

start date & end date
I have a date table & a leave table which leave table contains startdate & end_date. How i do show all date in between startdate & end date . For example : Leave startdate = 1/5/2010, end date =3/5/2010. i want the data show all date - 1/5/2010 NANCY AL 2/5/2010 NANCY AL 3/5/2010 NANCY AL You can use a Cartesian Product query to generate a record for every date in the period. 1. Create a table with just one field of type Number, named (say) CountID, and mark it as primary key. Save the table as (say) tblCount. 2. Enter records from zero to the highest number of ...

program to make Tables and Queries becomes READ ONLY
Anyone out there knows how to write a code to make the Tables and Queries to be "READ ONLY" in a database, so when we convert that database to a "MDE" mode, then everthing is secured? Your help is much appreciated. No, there isn't. If you use only queries, and have DISTINCT in each of the queries, you won't be able to update the data through the forms. However, you'll still be able to get to the tables by linking to the database. Note that your application should be split into a front-end (containing the queries, forms, reports, macros and modules), li...

Excel date format convert to string format
I have this problem I would like to change a column format from (m.d.yyyy <== date format) to (m.d.yyyy <== string format) Any method I can use ? thanks in advance Enter this macro: Sub gsnu() Dim s As String For Each r In Selection s = r.Value r.Clear r.NumberFormat = "@" r.Value = s Next End Sub Then select the cells to be converted and run the macro. -- Gary's Student "man" wrote: > I have this problem > I would like to change a column format from (m.d.yyyy <== date format) to > (m.d.yyyy <== string format) > > A...

add date picker
I would like to add a date picker to an excell sheet is there an easy way? thanks for your help tab Ron de Bruin has some notes: http://www.rondebruin.nl/calendar.htm tab wrote: > > I would like to add a date picker to an excell sheet is there > an easy way? > thanks for your help > tab -- Dave Peterson ...

Access2000: Suppressing messages for Append/Delete queries
Hi Is there a way to suppress all messages like "You are about to delete ...", "You are about to ran an append query ...", etc. invoked by DoCmd.OpenQuery/DoCmd.RunSQL, until VBA code is processed. Application.DisplayAlerts=False doesn't affect those messages. -- Arvi Laanemets ( My real mail address: arvi.laanemets<at>tarkon.ee ) Thanks DoComd.SetWarnings will do! -- Arvi Laanemets ( My real mail address: arvi.laanemets<at>tarkon.ee ) "Ofer Cohen" <OferCohen@discussions.microsoft.com> wrote in message news:D8F32627-71C7-4...

What is the latest version of CRM 3.0?
This may seem like a mediocre question however I have been trying to determine what the latest version of CRM 3.0 is and have not found any definitive answer. With the updates that are available, what should the version number be? I have users with varied version numbers and I'm trying to determine if they are out of date or not. Currently, the most predominant version is 3.0.5300.0. Can someone let me know if this is an older version or not? Microsoft CRM V3.0 was released January 2006 Microsoft CRM V3.0 Rollup 1 was released December 2006/Jan 2007 For the Outlook Client Microsoft...

Inputting Dates Formula?
Is there anyway to input a date in a cell when data first appears in another cell? For instance, if I entered data in cell A1, is there a way that B1 will record that date, but not change it the next day? Thanks! You may be able to use a macro to enter the time/date so that it doesn't change. Take a look at JE McGimpsey's site: http://www.mcgimpsey.com/excel/timestamp.html Scott wrote: > > Is there anyway to input a date in a cell when data first appears in another > cell? For instance, if I entered data in cell A1, is there a way that B1 > will recor...

Confounded by date differences
Hi all, I have 24,000+ records in my spreadsheet, all originating from the same database. It would seem that this database has a mind of its' own when it comes to producing date and time fields. Background, I am trying to calculate the working days and hours between two date cells. The formula I am using was provided by good people on this group. It is: =((NETWORKDAYS(H2,I2)-1)*("17:00"-"08:00")+MOD(I2,1)-MOD(H2,1))*24 (answer 1.5 where H2 = 13-09-2009 2:00 PM and I2 = 13-09-2009 3:30 PM ) However, and no matter how I try and work this out, t...

Problem keeping data synchronised with database query.
Hi all, I'm using Excel 2003. I have a spreadsheet containing a database query (inserted using Data->Import External Data->New Database Query) with 5 columns. The 6th column is a notes column, in which the user can type little comments to help them. When clicking "Refresh Data" on the data, I have a problem if a row is deleted, because the Notes cell that was on that line is now inline with the next row. I would prefer it if the notes cell was deleted entirely (or, more specifically, if the entire row was deleted). Is this possible in Excel 2003? I've been th...

How to add the date after the "A1"
Hi, I type "The information gathered as of March 9, 2008" on cell "A1" Is there a way to make a date default like "The information gathered as of " & Date (default). Your help would be much apprecated. ="The informations gathered as of "&TEXT(TODAY(),"mmmm d, yyyy") -- Kind regards, Niek Otten Microsoft MVP - Excel <learning_codes@hotmail.com> wrote in message news:c11a1bfc-65ba-4cf3-bdef-c28bdcc19c1d@8g2000hse.googlegroups.com... | Hi, | | I type "The information gathered as of March 9, 2008" on cell "A1&q...

FRx Report Date won't change
We have a customer that has a GP Fiscal Year of 9073. Apparently, an AP transaction had accidentally been posted with a document date of 9073. So, in order to void that transaction, they created the fiscal year of 9073. Now, in FRx when they try to change the Report Date to December 2008, FRx changes the report date to December 9072. We've tried deleting the .g32 file and rebuilding the FRx GL Index. We've also tried deleting the SY40100 and SY40101 tables, rebuilding the Fiscal Periods in GP then rebuilding the FRx GL index but we still can use the 12/2008 date in FRx. Any...

Simple dues expiration date flagger
I do membership for a 100 member plus club, I need an equation that will take today's date and compare with the dues expiration date and highlight past due memberships so I can generate a past due report. I'm not very familiar with creating equations. I have a "Today's Date" and "Expiration Date" column in a Delinquincy Query but haven't figured out how to show the date in the "Today's Date" column On Tue, 1 Jan 2008 19:06:00 -0800, ArthurRW <ArthurRW@discussions.microsoft.com> wrote: >I do membership for a 100 member plus clu...

Queries returning different formats
Hello, I have cut and paste the same expression from a query to another, however when i do this it changes the format of the information returned. In the inital query it returns (Hours:Minutes) perfectly, expression is "LOS (Hours:Mins)": (EDMain!EDDischargeDate+EDMain!EDDischargeTime)-(EDMain!EDPresentationDate+EDMain!EDPresentationTime). When I paste it, it returns a decimal value (which I presume is the way access formats time), and adds '[]' to the expression i.e. "LOS (Hours:Mins)": ([EDMain]![EDDischargeDate]+[EDMain]![EDDischargeTime])-([E...

Query/Report based on Employee Hire Date
This is probably a very basic thing that I just don't know how to do. I need to pull a report on people hired in December, regardless of year, for performance appraisals. The hire date that I currently have in the database is in the mm/dd/yyyy format. How do I sort on just the Month portion of this. I can then build my reports off of the query. Thank you in advance for any help or suggestions. Steve Add a calculated field to the query. Field: Month([Hire Date]) Criteria: 12 That gets everyone ever hired in the month of December. John Spencer Access MVP 2002-2005,...

sort by date #2
I am trying to sort by date where formatting is 'yyyymmdd' and the months are December, January, February, and March. The months need to show in the above order. Thanks for helping with this.. Create a helper column with a ranking as you see fit such as =IF(MONTH(G1)+1=13,1,MONTH(G1)+1) and sort by the helper column. -- HTH Bob Phillips "Red" <Red@discussions.microsoft.com> wrote in message news:DFDAB427-51F1-4B73-9591-09DDEE8B2E43@microsoft.com... > I am trying to sort by date where formatting is 'yyyymmdd' > and the months are December, January,...

Default sort in inquiry screens should be by date
In the Inquiry screens for Purchasing transactions by vendor and Sales transactions by customer, the default sort is by document number. I ALWAYS want it by date, so I have to change it every time I get into this window. The vendor who does our support tells me there is no way to change this default. Is this true? ---------------- 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 sugges...

Date range
I have a Date field for each event in my table. Each company will have a range if they have more than one event--e.g., 7/7/90 - 1/5/94. How do I ask for the first event date and the last event date, so my header would read 7/7/90 - 1/5/94. How would I go about doing this? I can't think of anything at the moment? Any quick answers? Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201005/1 Hi If I understand what you want it is to filter a form or report to only show a selectable date range. Set up a f...

SmartList>MDA shows wrong trx date #2
for posted some trx, in SmartList>MDA the date is 00-00-0000 but in (MDA) Inquiry windows, it shows correct date. also, table DTA10100 also shows correct date. have run financial checklink, no errror reported. any advice? thks. ...

is it possible to set a persistant variable from a query?
I am trying to set a specific value to a variable to be retrieved on another line in the query where a specific value changes then the value from the variable will be returned, but so far am having no luck. here is the code for the module that contains the variable and the sql that I am using any help would be greatly appreciated. Thanks, Nate Option Compare Database Global GBV As String Public Function init_globals() GBV = Null End Function Public Function globalvar(ivalue) As String Function init_globals() GBV = ivalue End Function SELECT prm5051.level, prm5051.part, prm5051.desc, prm5051...

dates in VLOOKUP
Trying to use Vlookup to copy dates for a series of records (rows) from one file to another. The result in the placed file (where the formula resides) is 4 years and 1 day later than in the original/source file (or rather, 4 years with the leap day in included?). Have tried using text version of the date and it appears in the placed file the same as the source file, but when I change the format to date, the date increases as above. Both files are (now) same version of Excel, though source file started out as 97-2003. Any ideas? Make sure the settings on both computers for ...

Syntax Error in Excel Query for DATE field
I have created an excel query using ODBC, and linking to a financial software products tables. I am attempting to enter in a criteria to reduce the number of records returned. I am trying to put in a criteria against a DATE field. However when in enter in a date and run the query I get the following error "Syntax Error". Can someone help?? hi. post your code. I'm not sure i can help but unless i see the code, all i know for sure is that you have a syntax problem. Regards FSt1 "James T" wrote: > I have created an excel query using ODBC, and linking to a ...