HI, I have a table called cust_order, with fields: orderNo, product, ...
I have a form which updates these values.
On the 'enter' event of a button to 'update changes' I collect the stored values cust_order and compare these old values with the new values. I do it using the dlookup command like this;
Private Sub UpCust_OrderDetBut_Enter()
Dim old_product as string
old_product = DLookup("product", "cust_order", "orderNo = '" & Forms![update_order].orderNo + "'")
Dim old_product as variant
old_qty = DLookup("qty", "cust_order", "orderNo = '" & Forms![update_order].orderNo + "'")
' THERE ARE OTHER FIELDS
'I then compare these values with the ones in the form. Where they are different I write them along with info into another table to keep control of the changes.
End Sub
I would rather return all the fields in a select query but I do not know how to get the selected data into local variables. Assuming I want the two fields above, can someone show me how to achieve it through a query - something like:
docmd.runsql("select product into " &old_product + ", qty into " &old_qty +" from cust_order where orderNo = " & forms![update_order].orderNo )
I have a date/time text field on a form with the General Date format and a combo box next to it that has sequencial times as the row source (IE. 12:45 AM, 1:00 AM, 1:15 AM, 1:30 AM, ETC.) When the user chooses a time in the combo box, I want the time portion of the text box to be updated with the chosen time in the combo. I have tried a few things but cant seem to get it right.
Wonder if someone can help please. I'm quite new to Access so please bear with me.
I have a data field in my database consisting of both a date and time.
I then have a form containing two fields where the user can type a 'To' and 'From' date to extract the records that they are interested in. The query behind this uses the 'Between[Enter The Date] And [Enter The Date]' coding.
The problem is that because the field contains a time it doesn't return any records when I run the query.
Can anyone offer a bit of guidance on how I could ignore the time part of the field perhaps by adapting the above.
I have a column/field named [DateTaken] which contains test dates and times in the same cell. I am needing to find those with a test time less than 2:30 pm or <14:30pm.
Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.
When I enter data, Album ID increases every time I jump to the next field. For example, when I enter: AlbumTitle, Song, Rating, Album ID is already at 4 and this is just the first record.
I need to convert a text field containing a total amount of time (example: 128:15:52 -- 128 hour, 15 minutes, 52 seconds) as a time field. The data is imported into a database as a text field.
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Can someone help me to put the date and time field as one so that it shows as DD/MM/YY HH:NN in one field. I have the date and Time in two fields currently.
I'm creating a adp from a mdb. In the old mdb i had a form that held sessions that lecturers worked and in the form footer i had a text box which had a control source =Sum([hrs])*24 and this worked fine. now in the adp this does not work the field just returns error. Can anyone give me any advice on this please.
I have a form that has two text fields "Time From" and "Time To".
I can get the current time to show up with either =Date() or = Time(). Althought when I use Date() the time shows up correctly in the field until I click on the field to edit it and then it shows the Date instead of the time until I tab out o the field. But I want the user to be able to pick another time without having to type it in. Kind of like a list box but with a scroll that with either increase or decrease the time.
Also I am trying to get the "Time To" field to be 30 minute higher than the "Time From"
I have tried different things like DateAdd("n", 30, [TimeFrom]) and although it does add 30 minutes when I click in the field it shows the Date and time (3/6/2006 12:30:00 AM) and not just the Medium Time (12:30 AM) even though I have the Conrol and the table field formated to Medium Time.
what is the best way to record durations, not time?I want to have a table that records charging data such as below:
startDate
startTime Duration (hh:mm:ss)
15/04/2012 12:00:00 13:00:00
In this example you can see we started charging the device at 12pm on 15/04/2012 for 13 hours, from this I will be working out the endDate/time using dateAdd etc.Do I set the duration field up as a dateTime and if so how do I avoid the DB thinking that the field is a time and not a duration, meaning that the endDate would be 1pm on the 15/04/2012 instead of the correct 1am on the 16/04/2012?
I have nearly achieved this from an example on the Microsoft site but am having problems with the subforms. Can anyone take a look and if possible provide me with guidance as to what I am doing wrong. The instructions are included.Any help with this would be very much appreciated. Kind regards. Bernard
I have atable with fields time in and time out (short time type), in aquery i did a diffrence between that to fields, so I can know to evry employee every day how many hours work.
but, my problem is how do summation to the work hours to the month (it's a short time type).
example, an employee work from 8:30 to 15:00 in the first day, second day work from 9:10 to 14:20 ....etc at the end of the month i want to have report from query which give me sum. of hours the employee workes.
I have a query with data grouped by time, with the goal being to combine values for each 5-minute time interval over the course of several months into a single 24-hour period (resulting in 288 records). Most of the time values, when converted to decimals, are between 0 and 1. However, some of the times are actually greater than 1. They still display correctly in time format, but are unable to group with the rest of the times since they have different values.
I need to construct a statement to a.) Keep the given value if SensorTime<=1 b.) If SensorTime>1, subtract an integer value (between 1 and 4) so it is between 0 and 1 and will then group with the rest of the times
I experimented with a SWITCH function but I'm not sure how to specify "1<SensorTime<=2." I think the current language will subtract up to 10 from numbers less than 1 (-1 if less than 2, -2 if less than 3, etc.). Here's what I have so far:
SELECT Sensor3.SensorDate, Switch(Sensor3.SensorTime<=1,Sensor3.SensorTime,Sensor3.SensorTime<=2,(Sensor3.SensorTime-1),Sensor3.SensorTime<=3,(Sensor3.SensorTime-2),Sensor3.SensorTime<=4,(Sensor3.SensorTime-3),Sensor3.SensorTime<=5,(Sensor3.SensorTime-4)) AS Expr1, Sensor3.SensorTime, Sensor3.Volume, Sensor3.Trucks FROM Sensor3 WHERE (((Sensor3.LaneName)="NB1" Or (Sensor3.LaneName)="NB2" Or (Sensor3.LaneName)="NB3"));
Can anybody suggest a way to subtract the correct integer value from each time to get a number between 0 and 1?
I have a form with date/time field. I have the default value at =Now(). My problem is I would like for this particular field to stay with the current date/time all the time. I mean if I created a form last week with the date/time of 3/14/2005 12:54:34 PM and go back to the same form to edit something this week I would like for that particular date/time to be the current date/time and not the date/time from the week and time the original form was created.
I am running some code to work on a table and I want to blank a date field. It is a stock control system where when a vessel is emptied I would like the updated field to become blank as it is empty and therefore has no history (this is stored in another table.)
The code I have tried to us is; (where updated is a date/time field)
Else rst![Updated] = " " rst![Volume] = "0" End If rst.Update rst.Close
The message I am getting is 'Data type conversion error'.
I am new here, so any help would be greatly appreciated.
I am running MS Access 2000 with MS Office update pack 3
I have written a custom application that uses time fields in Medium Time Format to do some time calculation.
In every other part of this application, the Medium Time format shows correctly as Medium Time Format. In the Report, the field shows as 12:00:00 PM. Is there any way to remove the seconds?
Please note that this data is being used in a string that contains other data fields and quoted text as well.
I have a table with several fields including the following 3 fields:
dtmAppoint Date = stores appointment date dtmAppointTime = stores appointment time lngAppointDoctorID = stores doctor's id for specific appointment
When I enter a new appointment, I want to know whether a specific combination of these 3 fields already exists. The doctor is actually a dentist and maybe another appointment has already been placed for the same dentist on a different chair. Creating a recordset as indicated below works but I am having some problems with comparing Time (dtmAppointTime). The following works but I need to extract Hour, Minute and Second and put it into string variable strTimeDum.
Say we have a table like the following, with hundreds of symbols:
SYMBOL DATE OPEN HIGH LOW CLOSE VOLUME ALLTIMEMAX
[code]....
and we need to add the ALLTIMEMAX column. This is the MAX CLOSE price for the symbol on SYMBOL till the date on DATE.I think that if we can take the first CLOSE for each symbol, then for the following date wed just need to check if the new CLOSE is bigger than the previous. If it is, use it and if not, just keep the old one.
I am currently in the process of creating an Employee Database. I have a 'staff information' table with several fields, one of which is a 'contracted hours field'. I have a spreadsheet linked to this database and therefore require this field to be formatted correctly in hours and minutes. For example...I wish to store a decimal time of 37.5 as 37:30.
