Date Function/need Time Function
Jun 9, 2005
We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields.
160037
213137
224356
235716
235800
12341
21708
22732
Here is the date function we use:
Function f2Date(strDateOld As String)
Dim strDate As String, strMonth As String, strYear As String
strMonth = Mid(strDateOld, 5, 2)
strDate = Right(strDateOld, 2)
strYear = Left(strDateOld, 4)
f2Date = strMonth & "-" & strDate & "-" & strYear
f2Date = CDate(f2Date)
f2Date = Format(f2Date, "mmmm d yyyy")
End Function
View Replies
ADVERTISEMENT
May 25, 2006
Hi all,
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
Thanks in advance.
View 1 Replies
View Related
Dec 11, 2013
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies
View Related
Sep 10, 2005
need help regarding part of a database I am trying to get functioning. User scans a card with a barcode need to log the entry time and reduce a unit value by 1 in a table e.g user has 20 credits when they log in thier time is recorded and 1 credit is deducted from the 20 credits a warning is displayed when the credits are reduced to 1. I am a novice in this , am using ms access, thanks in advance
View 1 Replies
View Related
Jan 30, 2014
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
View 2 Replies
View Related
Feb 19, 2007
Hi all,
was wondering if there was a simple way out there, or if someone could give me some ideas on how to use an admin function to either approve or deny a users time and tasks... this wouldnt neccesarily have to a be a daily function... but i would like to have this done before the end of a pay period(2 weeks).
i feel like this could be as simple as using a check box to show approval, but i wouldnt know how to have the users submission go into a place where the admin could view to approve or deny.. has anyone seen something like this done before?
thanks in advance
View 8 Replies
View Related
Dec 2, 2013
Is there some kind of Time picker function , i can see the date picker however i am using a booking system and have a field named 'time of booking' and i need something that can dump the exact system/online time into the field.
View 5 Replies
View Related
Sep 13, 2014
In my DB that we use and a workflow tool, some of our work has and due date and time.If we get the complete_package our work time starts and we have X amount of time to complete our work. This is something i worked on but set it aside, now i am coming back to try and fine tune this so it returns a more accurate value.
So if we receive and [Date_Complete_Package_Received] at 09/13/2014 09:00:00 AM and based on the work being done we have 5 hours to complete the work, then the [Date_and_Time_Complete_Package_DUE] would be 09/13/2014 02:00:00 AM. That part is simple and i have coding that does that just fine. [SLA_Time] is where it gets the amount of time allotted, we have 5,10,12,14,and 16 hours depending on what is being done.
Code:
Me.Date_and_Time_Complete_Package_DUE = DateAdd("h", [SLA_Time], [Date_Complete_Package_Received])
This is done on AfterUpdate of a field on one of my forms and it works the way it is but what i need is to be able to run this through my Workhours Function so i am not getting values that our in off hours.The following doesn't work i know i can't use the Workhours function with the DateAdd but this is just to show what i am trying to do.
Code:
Me.Date_and_Time_Complete_Package_DUE = WorkHours(DateAdd("h", [SLA_Time], [Date_Complete_Package_Received]))
I have a Workhours module and it is used for a lot of things and it works perfectly.
View 1 Replies
View Related
Jul 26, 2005
Hi Folks,
I know this is possible, just not sure how to proceed or what function to use ... I have a normalized database of unique identifiers, admit data, and discharge date. I am attempted to count only the dates within a certain range between admit and discharge dates for each record. For example, I would like to compute the number of days someone was in admission between #07/01/03# and #06/30/04# only, not counting any days outside of that range.
So if a record has an admit date of 5/3/00 and discharge date of 12/7/03, the value returned would be 159 days, because that is the number of days between #07/01/03# and #06/30/04# the record was enrolled. If a records admit date is 01/01/01 and discharge is 07/26/05, the value returned would be 365 days.
Any ideas?
Thanks,
Joe
View 2 Replies
View Related
Aug 2, 2007
Here is what I'm trying to do.
I need a function which takes the current day and finds the date of that weeks Sunday.
Then I need to take that date (of the Sunday) and subtract 13 weeks from it.
This is for the criteria in a query so that the only data displayed will be the previous 13 weeks worth.
Not quite sure how to go about this. TIA for the help.
View 3 Replies
View Related
May 18, 2005
Should this work? If not is there a way I can achive it! I think its fairly self explanetry :confused:
Private Sub Form_Open(Cancel As Integer)
Me.Date2 = Now()
If Me.Date1 < Me.Date2 Then
Me.Check8 = False
End If
If Me.Date1 > Me.Date2 Then
Me.Check8 = True
End If
End Sub
View 10 Replies
View Related
May 23, 2006
Hi Guys,
Does anybody know the formula for validating a date in a DOB field.
I want it to only allow dates where the age is at least 18years old.
Something like '<Date()-18' (But a formula that works!)
Thanks in advance!
View 7 Replies
View Related
Jul 31, 2005
I made a program for litle hotel registration. It worked enoudh good for me but after changing a comuter function Date() isn't work
In Build-In-Functios Date() persist but every try to use it cose massage "The function you entered can't be used in this expression"
In some queries I change Date() with Now() but can't do this in all.
HELP
View 3 Replies
View Related
Dec 30, 2005
Since I use Access2000 my Date() function returns always the error "#name". Is there a fix for that?
I switched to using now() which is working, but it stores the date and time in the fields. Then the date searching is not accurate since the attached time increases the date by one day when I search for dates. I have to get the date() function back to work since I am also unable to format now() in a way that it kills the time portion of it. Any Ideas?
View 14 Replies
View Related
Dec 15, 2006
This function gets the last working day (Mon-Fri Are Deamed Working Days For Invoicing purposes) of a month adds it to a table then gets the next until it has done the Number indecated In "txtMonths", It works with a UK Date Format but not with a USA Date Format.
Is there A Better way of doing this or does anybody have an idear as to how I could correct it for UK and US Date Formats
Y = Year(Me![txtStartDate]) 'Get Added Year
M = Month(Me![txtStartDate]) 'Get Added month To Keep Track Of year Change
D = Weekday(Me![txtStartDate], vbMonday) 'Get Day of week and set to monday
On Error Resume Next
For C = 1 To Me![txtMonths]
DDt = CDate(1 & "/" & M & "/" & Y) 'Set to first of the month
Ld = LastOfMonth(DDt)
Nd = CDate(Ld & "/" & M & "/" & Y)
'StrSch = Ld & "/" & M & "/" & Y
CWd = Weekday(CDate(Ld & "/" & M & "/" & Y), vbMonday)
'Now If the date falls on a sat or sunday move the day back to friday
If CWd > 5 Then
If CWd = 6 Then D = 1
If CWd = 7 Then D = 2
Nd = CDate(Ld & "/" & M & "/" & Y) - D
End If
AddSchedule Nd
'Debug.Print C & " | " & StrSch & " | " & CWd & " | " & Nd & " | " & D
M = M + 1
If M > 12 Then
M = 1 'Only 12 Months in a year
Y = Y + 1 'Gotta be a new year Happy New Year
End If
Next C
Edit: Function used to get the last day of a given month
Function LastOfMonth(InputDate As Date) As Integer
' Return a date that is the last day of the month of the date passed
Dim D As Integer, M As Integer, Y As Integer
If IsNull(InputDate) Then
LastOfMonth = Null
Else
D = Day(InputDate)
M = Month(InputDate)
Y = Year(InputDate)
'find the first day of next month, then back up one day
LastOfMonth = Day(DateAdd("m", 1, DateSerial(Y, M, 1)) - 1)
End If
End Function
View 6 Replies
View Related
Aug 12, 2005
I don't get it!!!! I use it all the time. <=Date() should get all rows with no future dates...but I get this error on some of my queries...I've never got this error before... :mad: :mad: :mad: :mad:
Sorry about the poor image quality.
View 1 Replies
View Related
Mar 23, 2007
Does anyone know of a function that will change the date format from yyyymmdd to mmddyyyy? I have a linked table to a data repository, I'm using a date field in a query and would like to use the dateadd function to return a certain date range from 7 days prior. I can't get it to run, I thought this might be due to the date format.
here is what i have in the date field as the criteria: <DateAdd("d",-7,Now())
the date field is formated to yyyymmdd
Any suggestions?
View 11 Replies
View Related
Dec 7, 2004
On loading a form is it possible to Go To a record which is within 10 days of today's date?
DoCmd,GoToRecord,acDataForm,"frmClinics",?????????
View 1 Replies
View Related
Apr 2, 2008
Must admit I have always had problems getting my mind around the Nz function and where to stick it in an expression. have googled this one (as I know it is not a common problem) but to no avail
If i have a calculated field
=DateAdd("d",7,[DateSubmitted]) then how do I deal with the possibility that [DateSubmitted] may be blank??
View 1 Replies
View Related
Jun 23, 2006
Hello,
I would like to add a control on my form (textbox) that by default will always show the last day of the previous year with the format mm/dd/yyyy (ex. 12/31/2005). This should then update every new year always showing the last day of previous year (ex. next new year it should show 12/31/2006).
Any help? Thanks.
View 2 Replies
View Related
Aug 4, 2006
Whenever I try to do an insert using the Date() function the date always shows up in the field wrong. It shows up as a time (12:00:09 AM) instead of the date. I have the field setup as a Short Date and I have tried many other formats.
What am I doing wrong?
See script below.
"INSERT INTO tblOneTimeJobs ( CustomerID, OTJobDescription, CreateDate, CreatedBy ) " & _
"SELECT tblCustomers.CustomerID, '" & Me.txtOTJobDescription & "', " & Date & ", " & CurrentID() & " " & _
"FROM tblCustomers " & _
"WHERE (((tblCustomers.CustomerType)='2'))"
View 1 Replies
View Related
Aug 13, 2004
I have a date field (WhitesDue) that I want automatically filled in when information in another field (Conditions) in another table is entered. The field Conditions is a number field and depending on the number of conditions determines when the whites are due.
I saw the other thread, Automatic Date Function, and was wondering if it was possible to put an if then code in to have the date updated with different dates depending on the number of conditions and the date that the information is put in. For example if someone is putting information in today and they have 4 conditions then I want the date to be automatically filled in with 8/17/04 (is it possible to factor weekends in too?).
Does that make sense? Can anyone help me out here?
Okay I stepped through some code that I have in the Conditions after update event. It works, it puts the date in the code like it is supposed to. Now, how do I get it to put the date into a field on another form?
If [Conditions] > 3 And [Conditions] < 5 Then
[Whites] = Now + 2
ElseIf [Conditions] > 5 Then
[Whites] = Now + 4
Else
[Whites] = Now + 1
End If
learnasugo
View 9 Replies
View Related
Feb 14, 2008
Hi,
I'm Italian and I started playing (I don't work on it) with access to create an Airline Simulator for MS Flight Simulator. My "program" read data imported in an Access table by a secondary program, and work on it. :cool:
One or Two of my Beta testers found this problems:
1) function "date()" is not recognized in Access 2003 and 2007. in 2003 is read as "date"; in access 2007 is read "date(". The one with Access 2007 resolved the problem reinstalling the Office Suite 3 times. The third was working. nothing else changed. :mad:
2) Access request the OFFOWC.DLL, in an Access2003 version. But only one user... and that .dll is from Access 2007!! :confused:
I wrote the database in Access 2007 and in Access 2003, the file is obviosly Access 2003 format.
I attach it here, for someone that wanna try if he has the same problem. PS due to size of the file, it's a zip containing a .rar file, that contains the .mdb (zip only was 660 Kb)
Do you have some indications!? Thanks a LOT!! :)
Lorenzo.
View 1 Replies
View Related
Jun 12, 2014
How to amend the below, so that instead of doing 4 days, it does 4 working days?
<=DateAdd("d",4,Date())
View 5 Replies
View Related
Dec 8, 2012
I've been using between function with two dates in query and also have put event. There is no mater to display it's event at the start and end date, but if the different date more than one day, for example:
Query 1:
Start Date
End Date
Event
Amount Days
24/04/2012
27/04/2012
Leadership Course
4
the report only display the first and last date with it's event but not display date to 2, 3 and 4. While actually what I want to do in the query when I key in even at the start and end date, the result in a report should display every single date with it's event automatically.
View 1 Replies
View Related
Jun 13, 2007
Let's see if I can explain this situation effectively...
I've been handed a database that is set up to track tutoring sessions of students. The original creator designed the database for 1 semester. I am trying to make it functional for x number of semesters. So here's my dilemma: I have several reports that are based on queries. I would like for the user to be able to run the report and it prompt for them to enter a date range, which would then output desired results for that range (I thought this would be more efficient than setting up the db to run reports by semester, so that the user would be able to narrow their results).
So in corresponding queries, I am adding the following criteria to the "Date" field:
Between [Enter Beginning Date] And [Enter Ending Date]. The query produces the right results, just not exactly in the way I would like to see them. This is because the query tracks the number of hours per mentor (the person giving the tutoring) and does a sum.
So in my results, it is summing the hours for every change in date occurrence. For example, the results should look like this (on the report):
Student 1
Mentor A xtotal hours
Student 2
Mentor A xtotal hours
Mentor B xtotal hours
Instead what I'm getting is a sum of hours for every different date, as follows:
Student 1
Mentor A xtotal hours
Mentor A xtotal hours
Mentor A xtotal hours
Student 2
Mentor A xtotal hours
Mentor A xtotal hours
Mentor B xtotal hours
Mentor B xtotal hours
Mentor B xtotal hours
Mentor B xtotal hours
And so on...
Maybe I'm approaching this is the wrong fashion? I haven't had much luck searching google or access help... maybe I'm searching with the wrong strings. :)
Any advice would be greatly appreciated.
View 6 Replies
View Related
