I can't seem to set the format and decimal places to do what I want in my table. I've tried several different combinations of Field Size being either Integer or Long Integer, Format being either General Number or Fixed, and Decimal Places being either Auto or 1.
What I want is to display the number I've input as I've typed it. For example, I type 10.6 and thats what I want.
But instead I'm getting either 11 or 11.0 or 11.00 for all the formatting options I've tried.
Can someone please tell me why its doing this rounding to my number, and what do I need to change?
Is it possible to change the resulting format of a new field (expression) within a query? I.e. Instead of the resulting when running query being displayed results in decimal places but instead being displayed in percentages
I am having trouble applying conditional formats to my access db they work in excel but cannot seem to put them in to access I have one table that my forms and queries look at
These are the conditions i had used in excel
formula is =AND($H18>=TODAY(),$H18<TODAY()+14)
this turns red if within two weeks of today
formula is =AND($H18>TODAY()+15,$H18<TODAY()+43)
this turns amber if between two and six weeks of today
less than =<TODAY()
text is struckthrough and turned red
Any help with implementing these in access either similar or new method
so, In Form I have two calendar controls, and two text boxses. Based on selected date, text boxes are showing day of the year (format(Date(),"y")).
Query is based on table with records of days in year (numbers), so, when Im trying to sort Between two numbers from Form (in this case nubers of days in year) query is reciving (imo) full date, not the number and im not getting any results.
I'm tring to build a queary based on a field called "PLAN_CODE" and a policy issue date field. There are 3 plan codes ...select1, select 2 and select 3. I want to pull information as follows Select1 has an issue date 11 months ago (day the queary is ran) select2 has an issue date 23 months ago select3 has an issue date 35 months ago
I built a field to calculate the duration of the policy based on the issue date Duration: (Round((POLICY_ISSUE_DATE]-Date())/365,1)). I think the format is wrong? need help with that!
I built a field to create a nested "IF" statement but the first "IF" is not pulling correctly, so I cant built the remaining "IF" statements. Expr2: IIf([PLAN_CODE]="SELECT01N" [Duration]=-0.9,0).
Anyone have a better approach? Any input how to handle this? I'm not real good with VB code but have a general understanding of it. It's easier for me to build in the QBE.
I have a table of dates and a "frm Dates" based upon it. I have copied a very useful calendar control which I found on a sample datebase elsewhere on this excellent site. I want to have an [OK] button which jumps to the correct date in my frm Dates when I choose that date on my calendar, but it's getting the months and days the wrong way round. Here in the UK our favoured approach is dd/mm/yy, but to try and figure this thing out I've formatted everything as Long Date.
Further details are on the form of the attached database, I hope you can help. Non-technical answers most appreciated as I'm pretty much a greenhorn with this stuff.
I have built several reports and I have set the layout to Landscape. When I go into the reports after a few days, it puts the format back to portrait. How can I stop this from returning to portrait?
I have been battling to get a mail merge to work correctly with a access database, the data isn't coming across correctly.
I have the date set up in access as a long date format, when it merges into word it changes to 2005-10-05 00:00:00, can anyone tell me what I need to do to get the date to appear correct.
thanks in advance
Di
ps I have read and re read the help pages on this but still can't get it to work
I need to build a output text file (edi) with 500 byte records and up to 6 different record formats. Can anyone point me to any information/links that would help me understand how to do this?
I spent a pretty significant amount of time working with Access in college, but that was years ago. Now that I'm actually trying to do something practical with it in the office, I can't remember how to do anything! This board has been helpful to read so far, and hopefully you can help me with my specific question.In our system, shipping charges by unit vary between customers. I have a table set up with a field associating a customer's charge with the customer's name. The problem is, the two most common charge amounts are $0.25/unit and $0.305/unit (dealing in fractions of a cent here). Right now the field is set to show 3 decimal places, so the numbers are showing up as $0.250 and $0.305 respectively. While this works and all, I want to format the field so that when a currency figure with 2 decimal places is input, it will display only 2 decimal places, but in the event you enter one with three decimal places, all 3 places are shown (and the number is not rounded off).Can anyone help me? My database is in its beginning stages, and I'm still somewhat of a beginner, so the less complicated, the better.Thanks!
I have a query based on two tables that calculates days elapsed between two dates. Problem is one date field is date/time and the other is short date, so the result is always infinite decimal places. I don't want to change the date format for the date/time on the one table. I tried the format function in the query, but still ended up with decimal places.I just want to make that one long date to act like a short date for calculating "days", not hours, etc.
I have a query that is drawing info from a union query. There is a time column that is bringing over time in long form (ex 10/17/2013 4:00:00 pm) and just the time (4:00:00 pm). This actually works for me b/c it lets me see which table each piece of data came from but I need a helper column w/ a formula in it to identify it for me. Basically, I need a calculated column that will bring back the word "scheduled" and if it's in short form "actual".
I have a report that looks like a letter on company letterhead. I wrote a macro that changes the report to .rtf, attaches it to an email so that we can send it to customers. The problem I'm having is that the .jpg logo graphic doesn't show up on the .rtf version of the report.
I've tried using .html and .doc formats and it doesn't transfer to those either. Is there a way to make the graphic stay on the letter?
I have one more problem about the version 2000 since it was not happen in version 97. I have changed several report formats margin lots of times (for example .25" for left and right margin to fit on one page) and when I open the reports next time or other users access them from the server (since the application residing on the Novel server) the format keep changing back to the default (1" for all margins: top, bottom, left, right)!!
Please let me know if it is a "bug" in Access 2000 and HOW to fix it!!
I am having problem to keep the desired format for reports' margin (left, right, top, bottom) and paper size (letter, legal), and orientation (portrait, landscape) stay!!
Currently, I have one MS Access 2000 database application resides on Novel Netware server version 6.0, [B]although I open the Access database exclusively to change all my reports design to the way I like, and SAVED them properly; however, later when I and other users view those reports, the formats change back to default view, which are "letter size, portrait and all margin set to 1")??
It is very STRANGE and FRUSTRATING and incovenient since all users have to manually go to "Page Setup" of the report's design everytime to change margins to make the data display nicely on one page!! It has never happened like this in version '97!
Plllease let me know if ANYBODY has any SOLUTIONS to this bug of version 2000 of Access?? Or any UPDATE file released to help get rid of it??
FoodStamps This is a YES/NO field (printed in the first or second columns).FSAmount This is a numeric field(always printed in the third column).I have a preprint form with three columns.The first column is for a YES answer and I want to print YES if FoodStamps is TRUE.The second column is for a NO answer and I want to print NO if the FoodStamps is False.The third column is either blank if the first column is YES or the actual value if the second column is a NO
Two cases:
If FoodStamps is TRUE then print
YES Blanks I have a preprinted form and I want the YES in the first column and blanks in the third column on the form regardless of what is in the fieldFSAmount
If FoodStamps is False then print NO value in FSAmount
the NO in the second column and the value of what is in the FSAmount field printed in the third column.I tried to figure it out with conditional formatting with no luck.
I am having an issue with a database I'm creating. It is a root cause analysis database. One of the fields is Target_Date. I need to set up queries that alert us if the target date is coming up, (in a week or so), or if it is past due. The issue is that some target dates are simply, "Next Run", which is to say that the corrective action must happen on the next run of the particular item which, at that point, has not been scheduled.
In order to do the above query, the dates must be formatted as Date/Time as <date() will bring up all dates regardless of whether they are in fact less than today if they are in text format. However, this restricts the ability to put in the target_date of "Next Run". Next Run will be required for another query in which we will specify the products coming up and it will let us know which "Next Run" items associated with those products we will do.
Essentially, I have taken the long route to ask, is it possible to create a field format that measures dates as dates and text as text?
I am printing statements for clients. Depending on certain criteria about the client, the format of the statement changes completely. This is just how the boss wants it, I have no control. I still want to generate all statements at one time (not separate reports).
I currently have this working using multiple subreports, and changing their Visibility property, only one being visible at any one time. This works, but it is very slow, I assume because I am basically making Access do triple the work (I have 3 subreports).
I pretty confident this is not the intended use of subreports. Is there a "correct" way to do what I am doing? I know I could just manipulate everything with VBA, but having subreports makes editing the different formats very easy.
I am pushing some data to Excel from an Access query. When the data is in Excel I reformat the sheet by changing the fonts, applying borders and cell formats - I have got all of this to work fine.
The one thing I am struggling with is applying conditional formats. I am pretty sure it is something to do with incorrectly referencing the applcation/sheet. An extract of what i think to be the key parts of the code are below.
.... Dim ApXL As Object Dim xlWBk As Object Dim xlWSh As Object ...
Set ApXL = CreateObject("Excel.Application") Set xlWBk = ApXL.Workbooks.Add ApXL.Visible = True
I have an Access2007/SQL Server 2012 system with 20 users for an insurance company. The company does most of its business via a network of vehicle dealers around the country. If someone comes in to buy a motorcycle, boat, or recreational vehicle at a dealership they need insurance to take it home, and our dealers send the quotes to us.
The dealers, in turn, receive payment from us each month for their efforts. Some are paid a % commission on the premium, some are paid for each quote they send regardless of whether the policy actually sells or not, and some are paid a set amount per sold policy. (Yes, that is relevant information!)
We already have reports that tally the amounts due each dealer based on their payment scheme, but last month our bookkeeper had to write about 650 checks manually because the check writing is not automated. She'd look at the report, and then enter name, address, and amount (in digits and words) into Quick Books and print the checks from there, a horribly tedious process. I've been asked to print the checks from Access. Basically one click would print all 650 checks.
I've opted to use a Make Table query to move the commissioned dealers amounts to a single location, and then to run two append queries to add the records from those paid per quote and those paid per policy. At the end of the day, one table contains all the information necessary to print the checks...except one.
The check number.
I need a way to sequentially number each record in the new table with a user generated starting point, the first check number.
By the way, the check blanks are on standard letter sized paper, three to a page, with tear-off perforations to separate them, in case that information has any relevance.
I think the best way to accomplish this is from the report itself. I've created a blank field on each record for the check number, and what seems most logical is that the sequential number is generated on print and written back to the table, rather than just generating all the numbers at once. That way, should print ever be interrupted, it will be easy to take up where we left off.
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
I have a form where we fill in information for supply of equipment to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows in my report detail for each signature of the items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
