Hello,
I have two fields: Ordered and Delivered formatted as general date. I want to know the difference in hh:nn. I tried to build an expression, but it is coming up blank. The fields are named Ordered and Delv_Date. I am looking to have the Time_Calc field calculate once the Delv_Date is entered. Can someone please help me??? I have minimal knowledge in VB.
Any help will be appreciated. Thank you in advance.
JYMALY
I am creating an online post, similar to a guestbook. When a user submits an entry, I am storing the date and time in a column titled "Timestamp", which is formatted 8/4/2005 9:16:58 ("General Date"). I am running the table through a query which is then posted on the web.
What I would like to do is this: I would like to use Timestamp to display the date that the entry was submitted, but not the time (basically, "Short Date"). Can I exclude the time using a query without changing the actual data stored in each record of the table? I understand that the time stored is completely different than the time displayed, even in the table. How can I do this?
I have attached a sample of a database.
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
I have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).
I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.
Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.
How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?
In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.
View 13 Replies View RelatedIn a date field is it possible for a date that is in the future to be hi-lighted in a different colour.
E.g. todays date is 25/03/14
So anything with todays date or previous would stay in black
24/03/14
25/03/14
but anything from 26/03/14 forward would be in red
26/03/14
Then will change to black when the 26th comes.
How can I convert a Date/Time field into a Date field?
View 4 Replies View RelatedI'm putting in a date and I need to have the small "st" beside the number 1.
I'm not sure how to do this or if there is a chr() call for it.
The date variable in VBA can store date and time together. So to make my table smaller and easier to modifiy I have decided to store date and time values in the same feild, using textboxes to 'extract' the required part when needed.I have a form connected to a table with my date/time field which will be hidden, and there are then 2 seperate boxes for Date and Time input. On load, both textboxes are populated with their respective values from the hidden textbox which works fine. But when the user modifieds either box, and clicks a button, VBA should add both time and date into the hidden connected field together, the issue is no matter what i try I keep getting miss-match errors. Have tried different, and no, formats on all/each textboxes and adding formats to VBA ect.
Code:
Me.Arrival1.Value = Me.arivaldate.Value + Me.arrivaltime.Value
And for reference - onload code that works fine looks like this;
Code:
Me.arivaldate.Value = Format(Me.Arrival1.Value, "dd/mm/yyyy")
Me.arritime.Value = Format(Me.Arrival1.Value, "hh:mm")
I'm working on a bound form and I'm trying to set the where condition of a DSum to Month(ReviewDate) = Month(me.txtReviewDate), but having trouble. I have tried several different ways. I am trying to sum all of the records from the recordset where the month is the same. So if I am on a July record, for example, I want to see a sum of all July records only, no other months. When I navigate to an August record, then the DSum should provide the sum of only August records, and so forth.
Code:
Me.txtRecordsforCurrentIndividualforCurrentMonth = DSum("NumberofPresentations", "tblCCMECPresentation", "ReviewDate = #8/1/2013#")
But as soon as I try to replace the actual number (#8/1/2013#) with a variable, I run into trouble.
i have a tbl that stores items that have an expiry. in a child table i want to store the expiry. but as it is different for each item i need a way of doing this.
i thought that by having
DateAdd('m',3,Date()) in one of the fields this would work.
on click event i would like the date add to come into play but the way i am doing it comes up with an error. too few arguments.
this is what i have so far
Code:
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
[Code].....
how does a user enter an appointment time without having to type the date and time in full? e.g. if i'm adding appointments for today at 11am and 1pm do i have to type year and minutes/seconds?
View 8 Replies View RelatedI'm having trouble with DLookup() using dates as part of a multiple criteria. I'm using the following;
Code:
If IsNull(DLookup("ExchRate", "TBL_DDPExchRates", "CurID = " & Me.Combo4 & " AND ExchDate = #" & Me.ShipOBDate & "#")) And Me.Combo4 <> 2 Then
It seems that if ShipOBDate is any date between the first and tenth of the month the DLookup fails to locate the appropriate record. I can't understand why as ShipOBDate and ExchDate are both formatted as Short Date and ExchDate is being populated via an OpenArgs which is derived form the field ShipOBDate..Is the fact the the date is getting converted from a date to string and back to a date some how upsetting things
In column D I have a date (different on each row) and in Column E I have another date (again different dates on different rows)
I need Column D conditional formatted to that
If the date in column D is less than Column E then it turns red.
I tried doing one row and it was giving erroneous formatting
Need a lot of rows done.
Is there a way to use the date entered in one field in a table as the default date on another field in the same table?
View 1 Replies View RelatedI have a database that is for a manufacturing process and there is a field for date/time to be recorded when a product at each step in the process is started and completed. I would like to automate the date/time stamp (maybe when the field is double-clicked on a query or form). BTW, I have seen the auto update option when a record is updated, but a record will be updated multiple times at every step in the process.
View 3 Replies View RelatedI am trying to create an access database to allocate people to projects and show the total time spent on projects. As peoples time can be allocated to different projects I want to add up the proportions of their times per day and show the results in a graph.The data I am entering is the date they start working on a project (Start date), the date they stop (End date) and their time allocation (e.g. 50%). How can I use this data to sum for date ranges?
I have attached an example database showing the problem. In the report the graph shows the type of thing I want for the output, clearly showing the total time spent on a project is more than 100% on one day and less on another day. Do you know how I can do this?
I have a database with two tables: taskTable and volumeTable. On the task table I have the list of task performed in my team and the time it takes to perform the task related to a category, and on the volume tasks I have the volumes of workload based on the category moth after month. Then I have a query that multiplies the time it takes to perform a task with the volume in the category and returns me results for each month.
Now, if a task changes on march and the time to perform it its reduced or increase I change it on the table, howerver, ALL results for all months. I would like to know if there is a way for the change to be applied from the month it is changed and not retroactively.
Example Task Table:
Task Time to Perform Task Category
Buy ticket 5 min Business Visitor
Create profile 10 min Business Visitor
Example Volumes Table:
Month Year Category Volume
March 2014 Business Visitor 39
I have a form in which I have four yes/no boxes to indicate in which part of the process is the product.
What I want is that when the last box is checked, automatically the field that I have for Closing Date set the date.
Is this possible? and if it is, how and where can I do it?
I have a lot of dates in one column (dd/mm/yy), and i somehow need to duplicate them into another column where they are shown without the date separator (ddmmyy), so that i can use that figure in a future calculation where a variety of fields are combined to show one unique product number.
View 7 Replies View RelatedI want a field in my table (called 'ContactTotal') that tracks the total number of times the date was changed in the field 'LastContacted' - through either table or a form, but it should exclude any changes made on the same day.
I'd also like a second field (called 'Popularity') that does the same thing, but multiplies any changes made in the last 3 months by 3, the last 3-6 months by 2, the last 12 months by 1 and anything before that by 0.
I would like a query to run, depending on a date..If the date in [Txt_Date_Account_Opened] is between todays date, and within the last 30 days.do CurrentDb.Execute ("Qry_Append_Units")else a msg box stating "account is not yet open"
View 12 Replies View RelatedI've been trying to convert a date format like dd/mm/yy to a SAP used format like dd.mm.yyyy .
a simple string conversion like
Code:
pp = day(datefield) & "." & month(datefield) & "." & format(year(datefield),"YYYY")
is not working, the year is converted wrong.
thus 17-07-62 should be converted to 17.7.1962 ( European date format )
I am operating in MS Access 2007 and unfortunately have to stick with the "out of the box" controls. I have data entries of notes in my database for "most workdays" - but not holidays or weekends. I want to allow the user to be able to edit notes for "most workdays" - but not let them pick a day to edit that doesn't have notes - by selecting them from a calendar date picker. Essentially, I want to gray-out/lock the non-workdays from editing.Is there a smart enough Active X or other control as a Date Picker that uses the results of a query to show valid/invalid dates?
View 2 Replies View Relatedhave a table with a list of employees with their badge numbers and the expiry date of their badges. I want to be able to run a query that will list/alert any badges that are due to expire with one months notice. How do I do this?
View 4 Replies View RelatedWhat do I use in VB to set a date combo box to nothing. In other words I want the combo box to be blank after an update event.
This does not work
Me![Combo2] = " "
I get an error "the value you entered isn't valid for this field" ....