DateDiff Function Giving Results With Numbers Way Too Large

Dec 28, 2007

I am finishing up a database that logs basic activities/tasks for my coworkers. As they go in to the database and log an activity in it stays open until they end their day or they start a new task. All this works fine, but the problem comes when I try to determine the total time spent (in hours) on each activity/task. I have written similar queries before and they worked just fine, but for some reason I am having trouble getting this one to work. Here is the expression I'm using...

Time Diff (in hrs): DateDiff("h",[Sample_TM_Table_1]![Time_In],[Sample_TM_Table_1]![Time_Out])

Unfortunately, say Time_In = 8:32:38 am and Time_Out = 8:33:03 am
The expression says that is equal to 946632.

For another Time_In = 8:33:00 am and Time_Out = 3:18:19 PM.
The expression says this equals 946639.

I have tried changing the format of the date/time in both Time_In and Time_Out fields to ensure they were the same and cross checked the expression but still get the same answers each time. Even when I try to use "n" for the interval or "s" it is still highly incorrect. The only thing that makes me think I still have a formatting issue is that the Time_In shows a long date on the results table while Time_Out shows it in a general date format. However, when I go into both the table and the queries to check the formatting they both show general date. Any ideas? I'm all out! Thanks in advance.

View Replies


ADVERTISEMENT

Two Fields Using OR In Both Not Giving Me Results

Nov 13, 2007

:o Okay. I have a frustrating one. This is embarassing to me, because it seems so easy. Anyways, I have a form, where a person could select up to 3 different months and up to 3 different paper types. These combo boxes are listed in the criteria of those fields in my query. If I use the form and try to run my query, it gives me blank results. If I run the query and fill in the pop ups that show up asking for the information that the form is referring to, and I type in the same information, I get the results I expected. What am I missing here? Please help before I go bald!!!

View 8 Replies View Related

Dcount Giving Wrong Results

Jun 14, 2006

Hi Folks,

I have answered my own question so I thought I would share as I couldn't find the solution in any posts. I confess that I don't understand why my results were wrong, but I managed to get them right. :rolleyes:

When using Dcount in a query, I was getting results which did not match the query results. For instance:

Phase_2: DCount("Project_Phase_ID","tbl_Prj_Details","Project_Phase_ID = 2")
gave an answer of 27 when there were in fact 41 projects in that phase.

Searching the forum I came accross this:

Count() always counts the entire domain.

So, Dcount is not counting the record set of my query but something else. I have 4 tables in the query and no idea what domain my dcount was looking at. I presume the various join types were messing with it somehow. :confused:

To get round this, I stripped out the Dcount expressions and changed the query to a make table. I then used the created table as the basis of a query in which I had my Dcounts. The dcount results now agree with the query recordset. :) :) :)

Any background on the bits I clearly don't understand will be gratefully received. I hope this helps someone else sometime.

Kind regards,

Keith.

View 3 Replies View Related

Query Giving Too Many Results (duplicates)

Mar 27, 2006

I have a query that selects from a table base on 3 entries (Name, BeginDate and EndDate) and should show me 12 other columns and their entries... I have 9 entries for a particular Name, but when i run the query i get 15 results. Some are duplicated but others are not and I don't know where to begin narrrowing it down. Any ideas where to start?

View 2 Replies View Related

Application Not Giving Desired Results

Jul 21, 2006

Hi There
we have a ms access application which pools data from AS400.
we have scheduled it for every week but there is a situation when its missing the data to feed the tables.
but when we run the queries manually it gives the desired results.
all kind of response are welcome
Best
danny

View 1 Replies View Related

SQL Query Giving Different Results In VBA/Access

Jul 11, 2007

Hi ,

I am using Access Application with Orcale Db as backend and this application is already in production.
The problem is.. USers Complained that the system is slow, this is because there is refresh (requery) for each Insert (When they click submit, that particular record is inserted and then a refresh has to be done to bring only unused member IDs on the User screen), i had used a Sql query using NOT IN , then i realized and now changed it to NOT EXISTS, but with this new query , if i test recordset. EOF , even though the query returns more than 13000 ..records, EOF sets to TRUE and the other part (not supposed to) gets executed. I really cant figure out why, i took the same query put it in query builder in Access and ran it , it gave 13000 records..but in VBA , this recordset is not giving expected results and so..i am totally confused..The part of the Code is shown below.

Code:newquery = " SELECT A.MEMBER_ID, A.MEMBER_NAME, A.ADDRESS_LINE1.................. FROM TABLE A WHERE NOT Exists ( SELECT '' FROM TABLE B where A.MEMBER_ID =B.MEMBER_ID)AND A.MATCH_LEVEL <> 0 ORDER BY A.MATCH_LEVEL DESC"rst1.Open newquery, Cnt, adOpenDynamic, adLockOptimisticIf rst1.EOF = False ThenForm_PHS_ASSIGN.RequeryForm_PHS_ASSIGN_SUB.RequeryElse MsgBox " No records to Process!" End If

EVEn though it should requery , it gives a message No records to process which is wrong. It works with NOT IN.... BUT VERY SLOW, NOT EXISTS IS FAST BUT DOES NOT GIVE ME WHAT I WANT..

Where am i going wrong..help pls!!!!

THANKS VERY MUCH!!

View 6 Replies View Related

Queries Codes For Giving Results As All If Criteri Is Left Blank

Sep 12, 2007

Hi

I have set up a form to run a query with the criteria linked to the options/drop down menus on the form. I am able to search for criteria set out in the combo boxes. However, what i would like to ensure is that if the drop down boxes are left blank the results will be to show 'all' results for that field.

help is greatly appreciated!

thanks in Advance!

Shapman

View 3 Replies View Related

Find And Replace Search Giving Either Ascending Or Descending Results

Dec 21, 2014

I have a DB and I have a button that triggers the find and replace. this will search over 100K entries and filter a specific client. The clients record can be 100's of entries.

This all works fine, but its always ascending and I have to scroll to the bottom for the latest record.

I can't set it to show Descending permanently as some employees can't cope with change. so...

I have a checkbox on my form for either Ascending or descending results. Is there any way I can get the find / replace to reference it, Or can I re-sort them after the search.

I've tried putting re-sort code on the 'find' button as lostfocus and mouseup but it doesn't work.

View 6 Replies View Related

Modules & VBA :: Public Function - Giving Control Name As Argument

Oct 22, 2013

I am designing a Public Function F()

I want this function to populate value from a any field selected from any table to any text box in any form ...... Lets say in a Database named TestDB we have a Table named tblTest , a Form named frmTest and in this form( frmTest ) we have one Bound Combo Box named cmbTest and one unbound TextBox named txtTest

We assume that the table tblTest has three fields : TestID , FName and LName .

We also assume that there are already some records in the Table tblTest .

If the function F() is already programmed it should take as arguments as it follows :

F(FormName as??? ,ControlName as ???, TableName as ???, FieldName as ???, ID_Field_Name_of_the_Table as ???, Combo_box_selected_ID as ???)

In result the function should (probably) DLookup (FieldName , TableName , ID_Field_Name_of_the_Table = Combo_box_selected_ID ) and then store the value in a variable ( probably Variant ???) , lets say called varSetValue.

The problem is IF this is the correct way to handle that issue , than what should i do next.... I really don't know how to pass a Control's name to a function from the Event module of the ComboBox -

I mean - how can I obtain the value so it is usable for the function .... What data type should be the function arguments so I can use them to set a value for a control in any form .

In example : If the Dlookup() is somehow successfull then I want to assign the varSetValue to the txtTest what should I do : FormName.ControlName.Value = varSetValue >??????

I have read a lot materials but couldn't find a good answer for that , and aswell what data type should be the arguments that the function accepts and how do I obtain Controls and Forms names so I could use them in the function as described above .

View 4 Replies View Related

DateDiff Function

Jan 18, 2008

Hello,
I am trying to create a query that subtracts "TimeOUT" from "TimeIN" fields to calculate the total time worked.

The "TotalTime" is a new field that is not in the table, only in the query.

I am attempting to use the DateDiff function to calculate this, but I need to return both hours and minutes.

Currently, it looks like this:
Total Time: DateDiff("h",[Time IN],[Time OUT])

It works this way to return the total time in hours only.

The DateDiff function works to return either hours (h) or minutes (n) but I cannot figure out how to get both!

Perhaps someone has an answer or possibly a better way to get the results in hour:minute format for me??!!

View 6 Replies View Related

DateDiff Function?

Sep 16, 2005

I'm trying to add a txtfield that the control source is 65 years prior the current date.

So the control source should come up with "1940". I've tried the datediff function, but not having any luck...any suggestions?

Thanks.

View 2 Replies View Related

DateDiff Function?

May 9, 2007

Hello,

I would like to ask if I would like to count the number of days between two date, but EXCLUDE Fridays, Saturdays, Sundays since the workers ONLY work Monday thru Thursday. I am using the function DateDiff as follow:

DateDiff("d",[Date1],[Date2]) with Date 2 is later than Date 1.

However, the results count calendar days. HOW do I exclude Fridays thru Sundays fall within Date 1 AND Date 2?

Please help!

Thanks so much,

View 1 Replies View Related

DateDiff Function Sample Database

Aug 29, 2005

Hey, its been a while since I've posted here, wow.

I had a database that contained samples on different date functions

(first of the month, last of the month, first weekday, etc...)

I can not find it, I have tried searching. I am pretty sure I got it from here.

Does anyone know where this is?

View 3 Replies View Related

Problem With Rounding With DateDiff Function

Dec 11, 2005

Hi all

l'm running a query that has a field with the following formula;

[Anniv_date]=01/03/2006
[NextEBDay]=01/07/2006
[LastPayToDate]= In this case is 25/11/2005

To_Anniv_wks: IIf([Anniv_date]<[Forms]![DateEB]![NextEBDay],(DateDiff("w",[Forms]![DateEB]![LastPayToDate],[Anniv_date])),0)

The result returns 13. However l know the answer should be 14. Coz when l enter the information in excel l get 13.71.

l've tried using the round function with no luck. Any suggestions would be appreciated.

View 1 Replies View Related

Forms :: Datediff And IIF Function In Form

Jan 21, 2014

Basically my database is for a library based on borrowing/returning books ect

Everything works except this one thing, in one of my forms I can ask the user to input a book number of the book they want to check if it has been returned or not, if it hasnt been returned within 30 days then it will come up with overdue.

The statement im trying to use i cant get it to work.

=IIf([DateDiff("d",[Borrow Date],Now()>30)

I want it to display overdue if the book isn't returned in 30 days of the borrow date.

View 3 Replies View Related

Forms :: DateDiff Function In A Calculated Field

Sep 15, 2013

I am struggling with the DateDiff fuction in a calculated field. After some study, I have used several variations of:

=(DateDiff("d",[DUED],Date())/7)

to calculate the time (in weeks) until a due date starting from today's date.

It continually turns up a specific date in the late 18 and early 1900's. It does not give me the number in weeks which was what I was hoping for.

Earlier I used a very simple: =DateDiff("m",[Birthdate],Date()) to calculate the age from a date of birth perfectly with no problems.

I do not understand why the formula is not working. Does it have to do with format or the fact that I am working with a future date and how can I rectify this problem?

View 4 Replies View Related

Forms :: DateDiff Function Not Working In A Form

Aug 14, 2014

I have a specific question on the datediff function.

My example is =DateDiff("d",[Policy Issue Date],Date()) where [Policy Issue Date] is the inception date of an Insurance Policy.

I need to determine how many days the policy has been inforce. The formula works great in a query and I can see the number of days in that field when the query is run.

However, my dilemma is I want the same function to run off of a text box using its control source property and have the number of days calculate and be visible for each record on the form. This part is not working and the field is returning the #Error? message.

View 5 Replies View Related

DateDiff Function - It Doesn't Work In Some Version Of Acces ?

Apr 11, 2008

Hi All

I've created a database where a query who'se result is loaded on a form uses following function --

DateDiff('d',Date(),[Target Date])-(DateDiff('ww',Date(),[Target Date])*2)-(Select Count(*) from [tblHolidays] where [HolidayDate] between Date() and [Target Date])


I've notice that 2 ppl get an error message when they try to open database, with thatt function coming on an error message
Does this mean they have older access version ?
How can I tell ? Any advise on what to do ?

View 2 Replies View Related

Queries :: DateDiff Function - Calculate Difference In Dates Between Two Fields

Apr 22, 2013

I am trying to calculate the difference in dates between two fields. How do I find the difference in days between field one which contains the date 04/12/2011 and field two which contains the date 04/12/2013? I have tried to use the datediff function, but it keeps telling me it doesn't recognize the field name, even though the spellings correct.

View 3 Replies View Related

DateDiff Function - Calculate How Many Days Left Till End Of Contract

May 18, 2013

I am trying to calculate how many days are left till end of contract...

whats wrong with this formula

=DateDiff("d",[Completion_Date], Date())

View 14 Replies View Related

Large Query Aggregate Function Error

Sep 15, 2006

Hi guys,

I am completely at a lose here. I have a LARGE sql query that was written before my time and how have the task of customizing it a little. It will run perfectly in Advisor Office (the program it was written for which uses access as the back end db). But if i try and create a query in access directly i get the following error:

You tried to execute a query that does not include the specified expression 'IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))),
IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type =' as part of an aggregate function

the full IIF statement that is causing it to fail is:

IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))),
IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type = 4, 'Renewal', IIF (CommnPremiums.Type = 5, 'Trail', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' ')))))) AS CommissionType


The WHOLE SQL query spans over 4 pages of A4 so i will only put it at the bottom (hoping its not needed to see the error. There are a few unions so i will put the first one in here to show what it is doing.

SELECT IIF (Policies.Owner = 0, clients.partnersurname + ", " + clients.partnerforenames, clients.surname + ", " + clients.forenames) AS Name,
Policies.PolicyNumber,
Schemes.SchemeName,
Managers.FullName AS Manager,
CommnPremiums.CommnPremiumDate,
IIF (CommnPremiums.Type = 1, 'Initial Premium', IIF (CommnPremiums.Type = 2, 'Increment', IIF (CommnPremiums.Type = 3, 'Single Premium', IIF (CommnPremiums.Type = 4, 'Annual Renewal', IIF (CommnPremiums.Type = 5, 'Value Based', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' '))))))) +
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type = 1, ' - Non Indemnity', IIF (CommnEntries.Type = 2, ' - Indemnity', IIF (CommnEntries.Type = 3, ' - Renewal', IIF (CommnEntries.Type = 4, ' - Level', IIF (CommnEntries.Type = 5, ' - Fund Based Trail', IIF (CommnEntries.Type = 6, ' - Level Indemnity', ' ')))))), ' ') AS PremiumType,
CommnEntries.DueFrom,
CommnEntries.DueTo,
SUM (Matches.Amount) AS TotalMatched,
CommissionCredits.CreditPC AS Credit,
Consultants.FullName AS Consultant,
ROUND ((CommissionCredits.CreditPC * SUM (Matches.Amount) / 100), 2) AS CommissionMatched,
Cheques.Amount AS ChequeAmount,
Cheques.ChequeDate,
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))),
IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type = 4, 'Renewal', IIF (CommnPremiums.Type = 5, 'Trail', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' ')))))) AS CommissionType
FROM ((((((((Clients LEFT JOIN Policies ON Clients.ClientRef = Policies.ClientRef)
LEFT JOIN Schemes ON Policies.SchemeRef = Schemes.SchemeRef)
LEFT JOIN CommnPremiums ON Policies.PolicyRef = CommnPremiums.PolicyRef)
LEFT JOIN CommnEntries ON CommnPremiums.CommnPremRef = CommnEntries.CommnPremRef)
LEFT JOIN Matches ON CommnEntries.CommnEntryRef = Matches.FKeyRef)
LEFT JOIN Cheques ON Matches.ChequeRef = Cheques.ChequeRef)
LEFT JOIN Managers ON Cheques.FKeyRef = Managers.ManagerRef)
LEFT JOIN CommissionCredits ON CommnEntries.CommnEntryRef = CommissionCredits.FKeyRef)
LEFT JOIN Consultants ON CommissionCredits.ConsultantRef = Consultants.ConsultantRef
WHERE Matches.FKeyType = 1
AND Cheques.FKeyType = 1
AND Cheques.ChequeDate BETWEEN #01-Jan-2004# AND #31-Dec-2004#
AND CommissionCredits.FKeyType = 1
AND CommissionCredits.Type = 1
GROUP BY IIF (Policies.Owner = 0, clients.partnersurname + ", " + clients.partnerforenames, clients.surname + ", " + clients.forenames),
Policies.PolicyNumber,
Schemes.SchemeName,
Managers.FullName,
CommnPremiums.CommnPremiumDate,
IIF (CommnPremiums.Type = 1, 'Initial Premium', IIF (CommnPremiums.Type = 2, 'Increment', IIF (CommnPremiums.Type = 3, 'Single Premium', IIF (CommnPremiums.Type = 4, 'Annual Renewal', IIF (CommnPremiums.Type = 5, 'Value Based', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' '))))))) +
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type = 1, ' - Non Indemnity', IIF (CommnEntries.Type = 2, ' - Indemnity', IIF (CommnEntries.Type = 3, ' - Renewal', IIF (CommnEntries.Type = 4, ' - Level', IIF (CommnEntries.Type = 5, ' - Fund Based Trail', IIF (CommnEntries.Type = 6, ' - Level Indemnity', ' ')))))), ' '),
CommnEntries.DueFrom,
CommnEntries.DueTo,
Cheques.Amount,
Cheques.ChequeDate,
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))),
IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type = 4, 'Renewal', IIF (CommnPremiums.Type = 5, 'Trail', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, ‘Transfer’, ' ')))))), Consultants.FullName, CommissionCredits.CreditPC

If I changed the IIF statement to:

'X' as commisiontype

it all works great but i need this information and its driving me mad. I can not see why it doesnt work within Access.

Any and All help would be great.

thx

Twiggy


I have attached the FULL sql query if you want to look at it all.. it hurts my head to read it all ;)

View 1 Replies View Related

Union Queries - Results Showing As Text & Not Numbers

Dec 13, 2007

To cut a long story short, i've got a union query that is a full outer join of 2 tables.

Anyway, the results i have got back needed formatting to no decimal places.

How do i do that?

I've tried leaving it as it is and formatting it in the report, but it still doesn't format and i can't total it up.

It's like it is showing as a text and not number.

Is there a way of converting the result to a number, particularly in a sql query.

View 2 Replies View Related

Reports :: TRIM Function For Numbers

Jun 13, 2014

I have a report that contains several numeric fields for Blood tests. I am currently using the "TRIM" function to great success in the following format

=Trim("Last Bloods: " & [dtmBloodsLast] & " Hb " & [intHb] etc.....

However When I try to Bracket out any fields that do not have a value in the table I get a #Type! Error

e.g: Trim("Last Bloods: " & [dtmBloodsLast] & (" Hb "+[intHb]))

Where intHb is a Number, field Size: Double, Format: General numberSimilarly I have one or two fields which have + - characters in their name e.g. intCa2+ (Calcium) which seem to throw a type error (inconsistently).how I can achieve "skip this field if no value in the table - but display with Indicator (e.g. Hb for Haemoglobin) if value present".

View 4 Replies View Related

Queries :: Function To Output Line Numbers In A Query - YTD

Apr 18, 2014

Is there a built in function which can be used to create line numbers in a query?

I've written a query to calculate year to date (YTD) points for yachts in a series of races and sorted it in descending order - so yacht 1 is coming first, yacht 2 is coming second etc. I'm looking for a way to add sequential numbering (starting at one and increasing by one for each line) into the query to represent their YTD places. Or this this something that should be left to the report which uses the query?

View 1 Replies View Related

Search Function Returning Zero Results On 2nd Use

May 15, 2007

Item: Access app front end for an SQL database (production tracking/job logging utility)

Problem: The app has a main search page that allows job entries to be located and opened by varies criteria (job#, job name, etc). I can currently run multiple searches on different criteria successfully but if I open a job window(which has been returned as result of my search) and then close that job window to do a new search my search function will no longer return any results(other then “Calculating….”) unless I close out the access app and reload.

Thanks in advance for any ideas that can point me in the right direction to hunt down my bug.

View 1 Replies View Related

Modules & VBA :: Like Function Returning Odd Results

Jan 13, 2014

I'm trying to do a string compare between two variables. One string variable is part of an array (which I'm looping through), the other is passed to the function as an argument.the function should return the position of a field in the OrderBy string of a subform.Here's the VBA :

Code:

Private Function SortPosition(strOrderBy As String, strField As String) As StringDim arrSortedFields() As String
Dim i As Long
If Len(strOrderBy) > 0 And InStr(strOrderBy, strField) > 0 Then
arrSortedFields = Split(strOrderBy, ",")

[code]....

The weird thing is, the line in blue returns True when, by watching the variables, it appears that it should not, and returns False when it appears that it should?

arrSortedFields(i) = "[RandomField]"
strField = "RandomField"
arrSortedFields(i) Like "[" & strField & "]*" = False
arrSortedFields(i) = "[SomeFieldName]"
strField = "ADifferentField"
arrSortedFields(i) Like "[" & strField & "]*" = True

I would have thought the expression "[RandomField]" Like "[RandomField]*" should return True?And similarly the expression "[SomeFieldName]" Like "[ADifferentField]*" should return False?Have been using 'Like' for donkey's years and never seen it throw results like this before?

(P.S. The reason I need to use 'Like' rather than a straight = is to account for the possibility that a field may be sorted descending, and therefore to nullify the DESC keyword which may follow any given field...)

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved