I have a text box and currently this is my control source
="Testing " & [test]/3
test = 1000 so my text box reports:
Testing 333.333333333333
Is there a way to make it into a form like $333.33...Also is there a way to make [test]/3 come out in a money text form? like "Three hundred thirty three dollars and thirty three cents.
I have a Concatenate string of text and currency. When joined together, the currency format disappears. I need to reformat so the string stays together with new format. Below is what I have:
Public Function ConcatAgreementFundsCommitted(ID As String) As String
'/ Purpose: Generate a concatenated string of Tracks for selected line.
On Error GoTo Err_Handler
Dim lngLen As Long 'Length of string.
Dim strOut As String 'Output string to concatenate to.
[code]....
I need 'Funds_Committed' to display as currency.
Hi folks,
I have a table with the words "Church of (whatever)" in one of the records. I am attempting to do an update query to remove the "Church of" part but leave the remaining part of the string. I am using a combination of Format and left but am not having any look. Any suggestion are much appreciated.
Thanks
Colin.
I have a bunch of server names that have names like aaa.bbb, xxx.yyy. All i need from these server names is the name before the first "." so in the first example all I would need is aaa. I've been using this formula in excel MID(A1,1,FIND(".",A1) - 1). This works great.. Is there any way to do this in access?
View 1 Replies View RelatedI have a criteria in a parameter query that asks the user to enter a Subsystem number (such as 4596-666-001).
The source data for my database has Systems (e.g, 4596-666) and each system has several Subsystems associated with it (e.g, 4596-666-001, 4596-666-002, etc). The System and Subsystem numbers are stored in the same column of the source data spreadsheet, and hence the same field in my database.
The source data is structured in such a way that Systems and Subsystems have different information. I would like to set up a query that shows the System information as well as the Subsystem information.
For example, if the user enters 4596-666-001 into the parameter dialog box the query returns all the information for 4596-666-001 and 4596-666 (but NOT any other Subsystem).
As a work around I have set up an OR situation where the user first enters the Subsystem number and then another parameter query dialog opens and they enter the System number. This works but is not as nice as simply entering the Subsystem number and having all information returned.
Is there a way to set up my query so the when 4596-666-001 is entered an OR is generated using the first 8 characters of the Subsystem number?
I'm trying to find a function in access that could do the following:
ZAB103-3012
ZAF405-HD-0001
Turn those cells into:
AB103
AF405-HD
Pulling everything to the right of the Z in the last step is the easy part but I can't figure out what function would be able to find the last "-" in the cell and pull everything to the left of it.
I have an unbound calculated field [Txt1] that I would like to show the result as currency. The fields in the calculation are:
[CR] number, currency
[Commission1] general number, two decimal places
[Commission2] general number, two decimal places
The calculation is: CR15W * (1 - Commission1) * (1 - Commission2)
If I just set the field to currency it works fine. But I want the field to show the following:
"Some Text" & " " & Format(Calculation, "Currency")
When I tried to include the format it to currency I get type mismatch, run time error 13.
So I thought that perhaps I should try the following just to see where my error starts and I get the same error message when I use:
Dim Val1 As String
Val1 = CR15W * (1 - Commission1) * (1 - Commission2)
Txt1 = Format(Val1, "Currency")
Everything I have read says the formula is:
Format([Calculation], "Currency")
Is correct. So why won't it work for me?
I have a manual date format that looks something like:
02/16/2015 09:06:15 AM PST
I would like to truncate the text so that just the date is showing. For all that I have found, I can remove a string from the beginning? any tips on removing a string from the end?
I know there are many posts on this but still cannot find what I want ....
I have a string ....
... <surname>bloggs</surname> <fornames>Jane</fornames> etc.etc.
... <surname>williams</surname> <forenames>Jo</fornames> etc.etc.
In a query I know how to:
Find the Start and End Postions as follows:
StartSurnamePos: InStr([string],"<surname>") returns 19
EndSurnamePos: InStr([string],"</surname>") returns 34
I also know that by adding 8 to StartSurnamePos I can get Bloggs but how do I stop it there.
Using left, Right or Mid how do I pull out just
bloggs Jane
Williams Jo in seperate fields?
Thanks in advanced.
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
Thanks
S
Is there a way to make only part of the text in Default Value to be Bold or Italics? That would be cool so I dont half to make boxes throughout my paragraph for only bold text! :cool:
View 2 Replies View RelatedI'm trying to split up the filename into four separate fields. Here's an example of the filename:
123112 427900 55261 1156833.jpg
A 12312
B 427900
C 55261
D 1156833
The values A, B and C are always the same number of characters. The last value, D, changes in size. It can be anywhere from four characters to twenty. But it always starts at position "20" as with "1" in the case of the 1156833. The extension, jpg, is of no value.I might also add my programming ability is close to nonexistent.
Hello,
I need to create a field in an input form that is simply the concatenation of two other text fields. I have tried all sorts of things, but when I look at the data in the table that field.
I have a field called ID that I want to be created like this:
=Format([UniqueID],"00000") & "-" & [Mosque]
This works well in my output fields, but does not work the same way on the input form. It needs to be based on the currently input values from the current record. Anyone have any ideas?
Thanks in advance,
--Robert
I'm new to VBA - Access but just dangerous enough to get in trouble. Is there a way to make a variable with the code below? I've been scouring the net all day and can't find answer. What I would like to do is make all the "cirsize1" be a string. Particularly the "Me.cirsize1.ForeColor". if I use the "cs" string it works fine with the Me.cirsize1 = 0 (ie "If cs = 0") This is just a snippet of the code.
Private Sub cirqty1_AfterUpdate()
Dim cs As String
cs = Me.cirsize1
If Me.cirqty1 > 0 Then 'if there is a quantity greater than 0 in circle qty and
If Me.cirsize1 = 0 Then 'if there is 0 in circle size
Me.cirsize1.ForeColor = vbRed 'then make the circle size 0 red
Me.txtCirIPM1 = 0
End If
End If
End Sub
i want to make a string that contains all of the fields in a column.
I have a table called UserSelectedComponentT with a column called ComponentName.
I want a string that is essentially all the different component names seperated by a " + " .
how to change default currency in Access 2013 to a "foreign currency" (i.e. to Indonesia Rupiahs) without changing control panel (Region/Language>currency) - now setup as USA ($US)?
View 3 Replies View RelatedMy form has a button that has a calculated field on it (a dcount(...)). When the button is clicked, a couple of conditions are checked and if all is ok, it runs the vba code. If the tests fail, it returns to the form without running the code. All works correctly, except that the dcount number has disappeared. I finally figured out that it is now hidden behind the button. How do I bring it forward again programmatically?
View 11 Replies View RelatedIs it possible to make a calculated field in a table not allow duplicates?
There is obviously no option to select No Duplicates in the field settings?
This returns the correct value Debug.Print [myTable]![myField]
Why does this result in an error? Debug.Print Eval("[myTable]![myField]")
Is there a way to make just part of the text in a text box bold, or to use different font sizes in the same text box?
Something like this:
---------------------------------
Heading in bold: description in regular (not bold)
(a smaller height line used as a line space)
Another "normal" line
a larger height line
a normal line....all in one text box!
---------------------------------
One thing I'd like to be able to do is to specify the line height of a blank line in a text box. I'm using carriage returns created with: Chr(13) & Chr(10). I could see defining the font size of a hidden character, but I'd need to know how to assign a font size to a piece of the text in a font box.
I am trying to run a make table query that involves using a linked table in Ms from a SharePoint list. However, a few columns I wish to include in my Make table query are calculated columns from the SharePoint list.
The make table query will not run saying "Calculated columns are not allowed in SELECT INTO statements".
Can any think of a solution or a potential work around to this?
I need to create a table with these columns in it.
I have a calculated field which consists of a few other fields concatenated, i.e. [field1] = [field2] & [field3] & [field4].
I need field 1 to be unique when its concatenated, but fields2-4 don't need to be unique. Is there a way to do this?
Hi everyone,
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
Many thanks,
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
View 3 Replies View RelatedTable1 contain Two fields (3Months) and (6Months)
Table2 contain Two fields (3Months) and (6Months)
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
I have a field which is formatted as currency. When a button is clicked, it shows the value of the field. Because it is a currency field, if you enter "10", it is immedatly changed to "£10.00", although when i use txtAmount.Value, it gives me the "10" value. This is especally a problem if pennies are used - if someone enters "15.10", the .Value reterned is "15.1", which makes it seem like "15.01" has been entered. How can I get the currency-type value form the field?Thanks for any help.
View 4 Replies View Related