How do you make the amount in a currency text box appear red if its negative? Right now it shows in parenthesis but I would like it to be red as well. Is this possible? It's also a calculated text box if that makes a difference.
View RepliesIs there a way to show negative numbers in brackets without selecting the Currency format?
I want to show (75,000.00) and not -75,000.00
I have several reports where the currency was always reported in dollars so for the fields in question I had a format of $#,###.00 which printed the dollar sign in front of the amount.
As the system has now become more sophisticated I need to print out a currency symbol depending upon the client. I have a currency table that links into the client so my query 'behind' the report holds the right symbol but how do I get it ito the report. I Know I can define a second field that will hold the symbol but to me this is a clumsy way of doing it. Is there anyway I can put the symbol in the control source for the data or into the format of the field or some other way?
I was wondering if there is a way I can store a formatted text in a field in Ms Access. I know memo type can contain a very big string upto approximately 65k but formatting is not possible in that type. Regarding the OLE object, I am not sure if I have to create a word document and then store it as a OLE object or is there a way to do that without creating the word doc.?
I do not want to create a word document for each record in the table. Is there a better way?
Please advise. Thanks
I have a VBA code to import mail from Lotus Notes into Access. The database user is able to read his email inside Access, similar to any email program.
Unfortunately, when I use the GetItemValue (lotusscript) method to read the body of the email message, Notes will render the Rich Text information as Plain Text, and I end up with no formatting information (bold letters, underline, text color, etc.)
How to import messages from Notes to Access and keep the text formatting? I am storing the data in a Rich Text Memo field in a table. I have found code to write HTML (MIME) messages, but not to import them into Access.
Hi.
I really need to convert fields with currency data into text values AND still keep the decimal point and the numbers after it.
But how?
Russ
I am sure it must be very simple but I can't find the command!
I need to convert a currency value to a text value for use in a combined string, say £123.00, to the text string "£123.00".
Basically I want one of the reports to say "£123.00 each less x% discount".
Currently I am using :
[Door Price] & " each less " & [Discount] & "% discount"
which will display "123 each less x% discount"
I could add the £ sign in code but if I add the .00 as well I will then have problems with a price of say £123.50!
Surely there must be a simple command that does this for me?
Cheers!
John
I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
I am importing a spreadsheet from Excel - the problem is that one of the columns in excel has a currency and text format (ex. $123,000 in B5 and 3M in B6). I know in Excel you can have both formats in one column since each cell can be formatted individually - how do I replicate this in Access?
View 9 Replies View RelatedI am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:
NameNormalPayRateVacationPayRateSickPayRateOvertimePayRateStandbyPayRate
John Smith$15$10$15$30$8
Ken Jonson$10$8$10$20$8
I am doing this in a query. So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.
The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.
By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.
Results that I am getting: I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.
I am using Access 2013 ...
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 RelatedHi folks,
I have a query that returns about 3500 records (and runs very well I have to say). the issue comes when I attempt to export that queries results to a comma seperated text file. It gives me the message that the field is too small to accept the amount of data..bla bla bla... I've looked this up and it mentions stuff about memo fields and issues with that, but I don't have any memo fields in any of the tables that this is pulling information from. Does anyone have a clue why this would be happening...please help..this is urgent.
Thanks - J
Good evening,
I am a little confused. I have one table with the Autonumber field and another table with a field that looks it up.
Tha problem is that I am using a Format for my Autonumber, e.g.
"O0045". And the field with the foreign key refuses to accept it! It needs a number to be entered, i.e. 45, and then it can format it. So on the whole, the LookUp Feature does not work, because it suggests FormattedAutonumbers, which do not fit in (obviously the foreign key accepts numbers only).
Is there a solution? Thank you in advance.
I am using this formula to format my date field
Convert this:A601030730 to 03-Jan-06
A6 = Year
01 = Month
03 = Day
0730 = Time (not worried about that part)
ApptDate: IIf([abap_030] Is Null,"",Format(DateSerial(Val(Mid(Left([abap_030],6),2,1)),Val(Mid(Left([abap_030],6),3,2)),Val(Right(Left([abap_030],6),2))),"dd-mmm-yy"))
I get the date to "Look" the way I want, but Access does not seem to recognize the formatting as it will not sort properly?
Any suggestion would be welcome, thanks
Ziggy
If the data in the spreadsheet is not in a standard row/column datasheet format how can I import it to Access, does anyone know, please help if you do. I have a formatted excel form and i need to import it to access!!!!!!
View 1 Replies View RelatedOn my Form I'd like to have a textbox that I could type something like the following...
Hi, My Name is Adam
Here is my favorite sport: Soccer
I'd like to find out what other people's favorite sports
Please send me an email back with your answer
Keep in mind the above could be one line, or it could be 20.
How can I transfer all the hard/soft returns etc. into a text box when I view and print the report?
Hope that made sense.
Thanks!
I have a DB with tables where the primary key fields are autoincremented, but with formatting applied.
Formatting is for example "CC"000000 so record 1 will be CC000001.
(each table has a different alpha prefix)
This works fine, except the database content is being read by another application, and it sees only the 1, 2, 3 etc.
Exporting the data to Excel format proves that this is due to Access not the other application.
However, if I export to Excel format, and tick the "Save Formatted" option, the data exported is as I require, CC000001, CC000002 etc.
Is there a setting or way to make Access present the data when it is read by another application to be the 'formatted' version of the data?
I'm trying to create an auto generating ID number field that contains a date followed by a sequential number for that date. In example...
YearMonthDay###
070719001
070719002
070719003
tomorrows numbering would be
070720001
070720002 and so on.
Each new day would start a new sequence of 001, 002, 003, etc.
I have searched these forums and haven't really found any answers. Could someone direct me in the correct direction. Also I need this number to be saved in the table and not just displayed or printed.
I've successfully created a DateDiff calculation that is generated with a d:h:nn format. Now the question:
How can i select or identify specific values, such as <= 2 days using those outcomes generated by the expression? Is it possible?
Example: a value is generated as 3:14:35. I would want to indicate this more than two days by putting a "N" in a separate column.
How is this done?
I have a table with sales by day. I want to display the data in a graph summarised by week but the period spans several years. If I format the date thus Format(MyField,"yyyy ww") then Access sorts the results thus 2003 1, 2003 10, 2003 11 but it should be 2003 1, 2003 2, 2003 4 etc.
How can I get Access to sort in ascending order correctly on the formatted date?
Thanks
Report preview no longer formatted on Windows 8. Report prints correctly but when viewed as a report it is 1" x 1" in the left hand corner of the screen instead of filling up the page.
View 1 Replies View RelatedI have a database with a table of employees, and that has a column named "DaysOff", into which I type a couple of days like this: "SUNDAY AND MONDAY"
Next, I have a form onto which I will display a query of those employees. The form contains a field called DATE in which I display the mm/dd/yyyy date. What I want to do is this:
I only want those employees that have a DaysOff field that DOES NOT CONTAIN the WEEKDAY NAME of the date in the DATE field. So, for instance, if the DATE field read 2/22/2014, and I have three employees as such:
Employee 1 DaysOff "Monday and Tuesday"
Employee 2 DaysOff "Friday and Saturday"
Employee 3 DaysOff "Sunday and Monday"
In that case, ONLY EMPLOYEES 1 and 3 should show up. Employee 2 will NOT show up, because his DaysOff field contains the word "Saturday", and the WEEKDAY FORMATTED value of the DATE field is "Saturday"
I hope that's not too confusing. Again, this needs to be the filter criteria for the query. That query will only return a list of employees that are NOT off on the day in question. If one of their off days is the day in question, the query won't return that record.
I've tried a few things, but I can't get it to work. If you want, I can list the various things I've tried, but I imagine one of you knows exactly how to do what I need to do, and you won't need my feeble attempts as a springboard.
I am trying to show the Current Repair Estimate total in currency form on my report. The attachment shows my criteria, which works on the query, but does not show in the report. The second attachment shows what pops up when I try to view the report. What can I do to get this current rep estimate to show correctly in currency form on the report?
View 8 Replies View RelatedIn order to perform conflict searches in my Access 2007 database, you enter a bunch of information and then receive a report of cases with similar information in order to avoid any conflicts of interest.One of the fields is "Date of Accident". Currently I have its format set as yyyy/mm/dd. However, my expression
Code:
Date Searched: [Forms]![frmConflictSearch]![Date of Accident]
returns nothing (if I look at the query in Datasheet the column is blank).I found a fix for getting it to appear by removing the format on Date of Accident, however, my query doesn't retrieve cases with matching dates (the dates are formatted to dddd, mmmm dd, yyyy as a Date/Time Data Type).Is there any way I can get the query to both return the date searched, AND retrieve matching cases?
After I have run all my access queries is it possible to send a conditionally formatted excel worksheet all in access using vba?
View 1 Replies View RelatedI suspect my design is flawed
Code:
SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.MyYear)<1990))
ORDER BY tblData2.LineNum;
The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?