I have a small issue, but cannot solve it. I have a form with a textbox bound to a time(7) on a SQL server linked table.
Even if I put the format as "Short time 24h" or "hh:mm" it will always come out with seconds, nanoseconds, etc as soon as you change record. I would like to avoid using the "current" event to keep the "hh:mm" at any record change.
I am having difficulties changing a date/time field like '22/04/2013 09:14' to make it look like '2013-04-22 09:14' using a query.
View 3 Replies View RelatedI wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
I have a continuous form that displays data from a table. What I am trying to accomplish is to change the format of the field if the string length is equal to 11, to "@@@-@@@-@@@@-@". Otherwise if it is greater or less than 11 then no format.
I have tried it using code:
If Len(Me.FormFieldName)=11 Then
Me.FormFieldName.Format = "@@@-@@@-@@@@-@"
End If
Is it even possible and if so is my syntax correct or am I way off base?
How to make a custom ID format that increment every time you add a new record the sample ID look like this "HCCR-SMA-CV-ST-000".
View 4 Replies View RelatedI have a date/time field on a form that has the default set to "=now()" which puts in the system date and time. I need to change it so that the date changes to the next day any time after 11:00 PM (instead of midnight). This is because our third shift starts their day at 11:00. If they start entering data right away, alot of their work shows up on the wrong (to them anyway) workday date. I'm struggling with the expression builder to get this to work.
One way is changing the time to be an hour ahead but that will affect the entire day when I just want to change the date for one hour per day.
I have set a field on my FORM with the General Date format. Every time I use the date picker it inserts the correct date with a time of 00:00? I even changed the format on my table to general date - still no luck.
View 4 Replies View RelatedI have recently upgraded my Access 2003 application to Access 2010. I am running into a problem when formatting a textbox to 'Long Time' that is linked to a Double type field. The error that I get is 'The value you entered isn't valid for this field. You may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.'
The textbox is linked to a field (double data type) and displays as the time in long format (4:06:17 PM). When I try to change to 4:07:17 PM I get the error. I tried removing the 'PM' and the colons but the error still occurs. The only way that I can get it to work is if I use a double value (0.735405092592593). In Access 2003 the time value would display properly, allow me to change the time and then save the time as a double to the database.
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?
Any ideas?
This screen shot may help:
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 short time but the format is in AM/PM or medium time.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
I want to be able to display the result of a difference between Date/Time values in "HH.MM" format (i.e. yes I want the result in decimals and I don't want Access to round up or down just because it feels like doing it!). I have used the following:
Example 1:
Dim ActualManHours as Long
ActualManHours = (txtEndTime.Value - txtStartTime.Value) * 86400
txtActualManHours.Value = ActualManHours
Example 2:
Dim ActualManHours as Long
ActualManHours = DateDiff("h",txtStartTime.Value, txtEndTime.Value) & "." & Format(DateDiff("n",txtStartTime.value,txtEndTime. value) Mod 60, "00")
Example 3:
Dim StartTime as Double
Dim EndTime as Double
Dim ActualManHours as Long
StartTime = CDbl(txtStartTime.Value)
EndTime = CDbl(txtEndTime.Value)
ActualManHours = EndTime - StartTime
txtActualManHours.Value = ActualManHours
Can I change the Records Source of a subform from within that subform and do a requery to have a different set of records displayed'
I want to be able to refine the records displayed in the subform
I have the following tables:
Supplier
Supplier# [Other columns]
Material
Supplier# Material# [Other columns]
DeliveryHeader
Delivery# Supplier# [Other columns]
DeliveryDetail
Delivery# Supplier# Material# [Other columns]
I've created a form based on table DeliveryHeader with a subform based on table DeliveryDetail. The two are linked by Delivery# Supplier#.
On the form, Supplier# is a combobox that lists all suppliers from table Supplier.
On the subform, Material# is a combobox that lists only the materials supplied by the supplier selected with form's Supplier#.
The problem is that this second list always shows the materials of the first listed supplier. For instance, when the form is loaded, it shows delivery #1 with all its details on the subform. Suppose supplier loaded to form is #1.
1) If I use subform combobox, it shows correctly supplier's #1 materials. But then, whatever delivery I navigate, it always shows supplier's #1 materials.
2) If I navigate to another delivery without using subform combobox, suppose I stop on a delivery where the supplier is #4, then subform combobox shows correctly supplier's #4 materials. But then, once again, whatever delivery I navigate, it always shows supplier's #4 materials.
I've spread Me.Requery here and there but without any success. What trick must I apply to force the execution of the query of the subform combobox Material# each time I navigate to a new record on the form?
My form has a Record Source of qry_Profile, it is a query that shows the user the records of the dogs that they currently own. I want to put a button in the form footer to allow the user to show the records of all the dogs that they have ever owned.
So my question is how do I change the record source with vb.
I have a list of patients that are seeing various clinicians in a clinic. Currently everything is on paper and when a patient arrives we highlight their name on the list. So now that I'm finally moving everything paperless, I want to have the record become "highlighted" when I select patient has arrived. The only problem is as it's a continuous form, the formatting changes on all records. Here is the screenshot before anyone arrives:
Those are two separate records that you see. Behind all of those fields is a box coloured #E7F442 (optic yellow-ish). What I want is when I select "Arrived" from the dropdown, for the fields on the left (name, number, appointment type, etc) to become transparent so the yellow shows through and looks highlighted. I've used this code:
Code:
Private Sub ATCST_AfterUpdate()
If Me.ATCST = "Arrived" Then
Me.RGBCHID.BackStyle = 0
Me.PATNAME.BackStyle = 0
Me.TCDESC.BackStyle = 0
Me.VISPURP.BackStyle = 0
Me.ATCMNT.BackStyle = 0
Me.ATTIME.BackStyle = 0
Me.ATCST.BackStyle = 0
[code]....
Making the whole code utterly useless, as it's changing the formatting on every record. Is there a way to program this so the formatting changes only affect the record I'm working on at that moment?
I am new to Access. I am using Access 2010. I have searched and found several articles on emailing the current record shown on a form but haven't been able to get any of them to work.
My goal is to have a user input information onto the form and then have one button the user will click that will save the current record, email the current record in pdf, and then close the form.
The form name is: frmCorrectiveActions. The unique identifier for a record is: Corrective Action ID.
The last coding I tried is below. I know something is wrong but have copied this code from another post about emailing forms so I'm not even sure what half of it means.
I am getting the message Run-time error '3075': Syntax error (missing operator) in query expression "Corrective Actions ID = 23' and when I hit debug this is highlighted: qry.SQL = strSQL
form Private Sub Email_Click()
Dim qry As DAO.QueryDef
Dim strSQL As String
Dim ReportQueryName As String
ReportQueryName = "qryCorrectiveActions"
Set qry = CurrentDb.QueryDefs(ReportQueryName)
[Code] ....
Hello,
I would like to format the currency values in a query. There values will either be Euro or Pound. Is it possible to change the currency format "on the fly" in this way in a query?
I would like to have a variable currency format. For example:
If [fldCurrency] = [Euro] Then format [fldAmount] As Euro
If [fldCurrency] = [Pound] or [GBP] Then format [fldAmount] As Sterling
How does one format data as Euro?
Many thanks,
Dj_T
I have a table populated with several thousand records-one field is a date field in the format of 5/5/2006. I have to change the data so that it is 5/05/2006 in order to import it into a SQL table.
I'm sure there has to be an easy way to do it, but I can't come up with it. Changing the imput mask on the field affects future data input but does not change what is currently there.
Any ideas or suggestions?
Thanks!
I have this problem, I have a field that updates by using the now() function. The problem is when I run a date query I have a problem due to the long date format. Is there a way to change all the records with a Long date format to a short date one?
Here is an example of what im working with:
Long Date: Januar 1, 2007 2:00 PM
Short Date: January 1, 2007
When I run a query I only get the records with the short date even though I have records with the Long Date format don't show up.
I want to be able to set text boxes so that if one enters a date and a time it displays in the format "dd/mm/yyyy hh:mm", but if one just enters a date is displays in the format "dd/mm/yyyy". Is this possible?
Stipulating "dd/mm/yyyy hh:mm" means that when you just enter a date it adds "0" values for the time e.g. entering "21/6/13" gives "21/06/2013 00:00".
General Date allows for an optional time, but it means that when you do enter a time it gives you seconds as well "dd/mm/yyyy hh:mm:ss" - and I don't want that.
Does anyone know how to change a field FORMAT default? I am creating lots of tables with lots of number format fields. I don't, however, want a zero default value (or any default value for that matter) for all of these fields. I can go in and delete them manually, but that seems like a backward way of doing things. Does anyone know how to elimate that default value from the format of a number field in general?
Thanks!
Carly
Hi All,
Is it possible to change the format of a date picker, for instance once i have selected a date, its is visible as 09/11/2006, can this be edited to just the month and the year 11/2006.
Hello again,
I think my subject heading explains it. How would you add or subtract two short time formatted values and produce a short time value? Also, how would you change a short time value into number minutes. (i.e. 5:30 into 330) Thanks!
G
Hi,
I'm using a web based form to store dates (and other records) in an access database. I use the dd/mm/yyyy date format (europe. go figure)
Anyway, the problem is that when submitting an ambiguous date, as in 11/01/2004, access sees it as 01-NOV-2004 rather then 11-JAN-2004, as it should.
Why does this happen, and how can I fix it. I have no problem doing the fixing in the access file itself or in the SQL command that populates it- whatever works best.
Thanks,
-Ethan
I am using an append query to move data into another database. One of the fields being imported is a date field in text form (20141201). I need it appear in the final database in text form (01/12/14) I have tried using several date conversions and cant get this work. Ideally i need the final value as a text rather than date.
View 8 Replies View RelatedI have a calculated field in a report, which is as follows:
=IIf(IsNull([POI_E1A]),(([TodaysDate]-[EADDate])/365)/3,[A1]+[T2]+[A3]+[T4]+[A5]+[T6]+[A7]+[A8])
The calculation works fine; however, I only want to show the whole number (not rounded up; not rounded down). In other words, if the result is 0.9967, then I want 0 to appear.
I cannot figure out how to make this happen. In the properties section I ensured the format was "general number" with 0 decimal places but this didn't change the result. I tried changing the format in the calculated field, again, I wasn't successful.