Funny DateDiff Parameter, Need Help
Apr 24, 2006
Hi:
I need to translate this VBA DateDiff into SAS and am not familiar with the "1>9,9"
I know "/" means divide but have never seen ""
Thanks for your help.
Steve
*Mth_1st_PNC: IIf(IsNull(DateDiff("d",DateDiff("d",[gest_age_days],[infantdob]),[calc_begin_pnc])/301),9, IIf(DateDiff("d",DateDiff("d",[gest_age_days],[infantdob]),[calc_begin_pnc])/301>9,9, IIf(DateDiff("d",DateDiff("d",[gest_age_days],[infantdob]),[calc_begin_pnc])/301<1,1,
DateDiff("d",DateDiff("d",[gest_age_days],[infantdob]),[calc_begin_pnc])/301)));
View Replies
ADVERTISEMENT
Dec 12, 2007
Problem:
I have a datediff function as a field in a query:
DateDiff("d",[ClientHistory].[Date],Now())
When I put in the criteria: >30
I get two results based on the datediff values equal to 37 and 110 for the two records.
Now problem: when I use >[Enter value] for the criteria and enter 30 at the prompt. I only get the record with 37, even though 110 is higher...
It seems very inconsistent based on the values I enter at the parameter. Have you ever experienced this or know what might be going on? All works fine if I hard enter the number in the criteria of the query, but not when I use a parameter.
I'm using Access 2000 on Vista and XP - both systems have the problem
View 5 Replies
View Related
Jun 16, 2005
Hi :) Just registered and was wondering if anyone could help me out.
I have a very simple table for which I am making a form. The table has two values:
'Week Number' (Number) and 'Period' (Number)
What I need is for the 'Period' field to increment with every new record created (easy enough) but when the week number changes I would like the 'Period' number to reset back to 1 in the following fashion:
Week||Period
...1.........1
...1.........2
...1.........3
...1.........4
...1.........5
...1.........6
...1.........7
...1.........8
...2.........1
...2.........2
...2.........3
...3.........1
...3.........2
...3.........3
...3.........4
etc...
If anyone can help me it would be greatly appreciated, thanks :)
Pob
View 2 Replies
View Related
Jan 24, 2007
Hello,
I just noticed something very strange - in a table of mine, when I create a new record with the form, the autonumber is an existing number. Say the last record had ID 400, then the "new" record is assigned an autonumber ID of 300.
Of course, record creation fails that way.
But with every attempt, the new autonumber moves one up, so eventually it'll start making unique numbers again.
Does anybody have an explanation for this odd behaviour?
View 7 Replies
View Related
Jan 5, 2005
Ok, so while working on my program, i was trying to integrate inventory control with job ordering. i finally got it to work, but encountered a 2hr speedbump along the way. here's what happened:
i had 2 numbers: varQuantity and varStock (quantity being the amount to be ordered, stock being the amount in stock). i wanted code to run telling the orderer that we didn't have enough in stock if varQuantity > varStock. so, after entering test data (100 for stock, 200 for quantity) to see if the code would run, it didn't. going back through, i tried to see if i did something wrong, entered a > or < sign backwards, whatever. nothing.
If varQuantity > varStock Then
do this
else
do this
end if
it totally skipped this bit of code. i placed msgboxes between the steps to see where the problem was occurring, and only the boxes before and after the code functioned. i then placed a msgbox directly before the If statement with this prompt:
msgbox "varQuantity > varStock =" & varQuantity > varStock
the response? varQuantity > varStock = False. ooookkkkaaayyyy.... tried this:
msgbox varQuantity & ">" & varstock & "=" & varQuantity > varStock
guess what i got? you'll laugh. 200 > 100 = False. Now tell me what i'm supposed to do when my computer can't tell when 1 number is bigger than another? i tried different test data, i made sure that my fields were of the same data type, etc. nothing. how do i proceed?
well, the solution i came across worked, but i'd like to know why. varStock = varStock * 1. why does this work? maybe i'll never know. Stupid computers.
View 13 Replies
View Related
Jan 16, 2006
Hi all
basically i have implemented something in form that says record 1 of 5 etc
the way this is implemented is done straight into the text box on teh form ...
="Application " & [CurrentRecord] & " of " & IIf([NewRecord],[CurrentRecord],Count(*))
The problem with above implementation is that when adding a new record sometimes i get say record 3 of 2 instead of 3 of 3
somettimes its 3 of 3 but when scrolling back i get 2 of 2
any ideas how i can implement this in a stable manner??
thanks
View 1 Replies
View Related
Jun 16, 2006
Is there a way in a query to write a datediff that looks at all the possibilities month, day, year? I have 10 different fields that I need to do a date diff on. I would like to have the fields checked in one place rather than 10 day checks then 10 month checks and 10 year checks. I don't need to use the data any where but I need it to let me know if there is a difference between a calculated date and a date that was input by a user. If there is then I know the user input the wrong date, and it shows as an error.
Thanks
View 4 Replies
View Related
Jan 2, 2008
I have heard of this datediff thing on access. How do you make it work? I also want to do a calculate between the date of birth and date of death? Bearing in mind they are all different. I am wanting if possible to list the age at death either as for example 70years and 85 days or 70.233 years
View 5 Replies
View Related
May 14, 2007
Hi,
I am trying to calculate the number of days between 'today' and a 'date of birth' field. In the field properties Default Value I am using =(DateDiff('y',Date(),[dob]) where Date() gives today's date and [dob] is my 'date of birth' field. However, this is not working. I get an error "(The database engine does not recognize either the field 'dob' in a validation expression, or the default value in the table 'Table1')"
I have set the data type for both fields to 'Date/Time'. What am I doing wrong? Is there any alternate method?
Also, how can I calculate height in feet & inches (5'6" or 5.6 or 5-6) from a given height in meters?
Thanks
Raj
View 5 Replies
View Related
Dec 2, 2005
I need to caculate the difference in days between to dates. I would like to include only Business days but would settle for any help at this point
Thanks in advance
-WM
View 5 Replies
View Related
Jul 16, 2007
Hi there.
I'm using the following code in my Access aplication:
DateDiff("h", IncStart, txtrts)
My problem is i need the exactly time difference and if the difference is 2hours and 15 mins it will display only 2 hours. Is there any way to do that? I've tryed in min but it display 135 mins.
View 4 Replies
View Related
Oct 3, 2007
hello all,
would really appreciate some extra help with this,
many thanks,, the story is as follows,,,,,,,
hello all,
i have a very simple databse with two table in it,, both table share a lot of common information, including a primary key called jobcard number,, i want to know if there is a simple query i can run that will find any differences in the start date fields that are found in both tables
any help would be much appreciated
cheers
wardy
---------------------------------------------------------------------
Look at DateDiff function in the help for the time differences.
Thus for example in a new column in your query you can type:
DifferenceInDays: DateDiff("d",[Table 1 Name].[DateField Name], [Table 2 Name].[DateField Name])
--------------------------------------------------------------------
thank you DrSnuggles,
could you explain in idiot proof instructions?
do i enter the diffdate code in the criteria row of the select query? also, what do i need in the field and table rows? is it the table and filed i'm comparing the data against?
once again
many thanks
View 1 Replies
View Related
Apr 9, 2008
I have a call tracker database.
What I'm trying to create a query to view all my calls' time average (opened date and resolved date). Basically, how long it took to fix a call or problem... whatever...
I tried with datediff(), but had problems getting the Total Avg.
I would need the query to look like this...
Total Calls | Avg Call Time
100 | 5.33 (min.sec)
Thank you
Lucas
View 11 Replies
View Related
Sep 20, 2004
I have a form that has a start time end time and total time. I am using date diff to calculate the total time and it is working fine, but i want the output to be placed into Total time field of a table. I am new to access and DB in general. Any help would be appriciated.
Greg
View 6 Replies
View Related
Aug 30, 2005
I am trying to calculate the total number of days between a date (i.e, csdate) and todays date in a Query for a Report.
I have tried the DateDiff command in the query line and in an unbound box on the report but it is not returning the right values.
Can Someone tell me what I have done wrong. I have checked that the date is in long format and tried it in short format as well with the same results. Below is what I have tried.
csdays:=DateDiff("d", [csdate], Date()) I have tried the Now() command as well, but it is not right either.
Please help!
View 6 Replies
View Related
Mar 21, 2006
My company recently switch us from Access 2000 to Access 2003. A query that previously worked, no longer works. I've searched the previous posts and Access help and updated the query based on the search results, but still receive the error message. "Undefined function 'Date' in expression."
Here's the query calculation:
AppOut: DateDiff("d",[AppMailed],Date())
Would someone please let me know where I am going wrong?
I've gotten it to work with this calcualtion, but the user has to manually enter today's date each time. But I know there has got to be an easier route.
AppOut: DateDiff("d",[AppMailed],[date])
Thanks =)
View 3 Replies
View Related
May 12, 2006
I am using Expr1: DateDiff("d";Date();[Expiry Date])
I am having a problem , the date diff uses the date in this format " mm,dd,yyyy"
but in my program I enter the date dd,mm,yyyy, is there is way to change it, I know that in sql you can do it, ms access is abit different, any solutions to that.
View 6 Replies
View Related
Jul 26, 2006
Have a table which has 4 fields PatientID FirstName,LastName,BalanceDue,Due Date
Trying to create a query that will show all fields of the accounts that have due dates that are under 30 days. I tried this Under30: DateDiff("d",[DueDate].Now())criteria >0 And <30. But can't put anything in the parameter dialog box which after clicking ok shows any data. If I can get this to work my hope is to have 3 more catagories. over 30 days, over 60 days and over 90 days. I'm new at access and could really use some help.
View 3 Replies
View Related
Mar 28, 2007
Morning
Pretty new to all this, so forgive my ignorance but i'm having problems with DateDiff. I have a table with 2 dates in it, and I want to work out how many working days (or working hours if possible) are between the 2 dates
SELECT DateDiff("d",[Date Opened]+[Date Closed])
FROM Dashboard;
I want it to print it the number of working days/ hours in a new column in the Dashboard table if possible.
Any ideas?
Thanks
View 4 Replies
View Related
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
Jan 24, 2008
Hello,
I create query that subtract “schedule date” (mm/dd/yyyy) and “deliver date” (mm/dd/yyyy) to calculate difference deliveries days. I used function DateDiff different ways but query doesn’t work.
Expr1: DateDiff("mm/dd/yyyy","SCHEDULE DATE","DELIVERY DATE »")
And I have error: “data type mismatch in criteria expression” or doesn't work.
Thanks for helping.
View 5 Replies
View Related
Mar 11, 2008
hey guys i have a query where i want to return all values with a difference greater than a value entered in the user.
Now DateDiff is working fine for this but the only problem i am having is if i set it to > FormTextBox nothing is being selected but if i actually put in a number e.g > 10 in the where clause it works fine. Any ideas?
View 3 Replies
View Related
Jul 31, 2005
:) I'm having problems with a simple piece of code using datadiff. all i want is to know how days from today is 3/10/2006, which obivously will countdown as everyday gets closer.
joe
View 6 Replies
View Related
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
Feb 28, 2008
Does anyone know if it is possible to round down a datediff functions calculation?
Thanks.
View 6 Replies
View Related
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
