Need A Refresher Course... (variable Data Formats)
Sep 27, 2006
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.
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 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?
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 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?
I am working on a database for my boss that stores the results of monthly file audits and generates reports. Each month about fifty files are checked against a check list and the results are to be entered into the database. Reports are generated quarterly. I have a date field that holds the date of the review. I would like the default value of this field to be what ever was entered in the previous record. That way this field can be tabbed through until the next month. When the data in the field is changed that will become the new default for the following records. I don't have any formal VB or VBA training. I just learn what I need for each project and save it for later use.
Hy,i have this problem: I've got a table that has column called car_id which is primary key and autonumber. How can i get last record from that column and assign it to variable which i defined as integer. Is there something like: myvariable=max("car_id","mytable") ???? Thankls for any help
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 am trying to write an append query which is based on a table (PA0041)having muliple columns. The columns are DAR01, DAT01, DAR02, DAT02, etc etc.
The data for DARnn will contain values such as S1, S2, S3 etc. For each of these values, there will be a corresponding date in the corresponding field DATnn.
There may be up to 10 columns for each - i.e. DAR01 up to DAR10.
I can append them into 1 append table if I write 10 queries. What I would like to know whether it is possible to do this using 1 query. I need to write both the date type and the date into the table.
In addition I need to know whether any employee is missing any date types. If I have a table called tblDateTypes which holds the date types that I would expect the employee to have as a minimum, how can I check for any employees who do not have a required date type.
If the data contained in tblDateTypes is as follows:
DateType S1 S2 S3
In the above example I would expect the end result to flag the fact that employee 4 does not have date type S1.
Let us assume in the above example that the employees all exist in the table PA0041 - even if they don't have all the required date types.
I am trying to deny users from logging on from multiple pc's. Here is how i've got it so far,created table tblLoginLock with fields EmpID and LoggedIn both numberical data type created a form to set the Empid with tempvar!TempEmpId and LoggedIn as 1 when a user logs in.When he clicks the logOff button he is signed out and a form opens up and closes to set the LoggedIn field to 0.Now whenever a user is signed the LoggedIn Field is checked with this expression
if there is a one then the user is logged in the condtion is met and a message box pops up to warn the user that he is already logged in.
The above code does not check the specific TempVar to the LoggedIn field, the LoggedIn field is randomly checked for a '1' meaning if there is a '1' anywhere in the table then the condition is met and the user denied. how can i set the condition to validate the logged in user to the LoggedIn field.
I am creating a 2 level report to confirm an order. Main report already created, runs successfully called as subform/subreport under "OrderDetails" form. Linked to master using Order.ID. There are two versions of the confirmation report that have different layouts for different program types.
The hangup comes when I try to add a "Class Dates" subreport. It lists dates of individual classes and Skip dates. I have created the subreport as "srClassDates". When I add it to the main report, it lists the records. However, when I try to link it to the Main report, an error message box appears with the "object variable or With block variable not set".
I have tried rebuilding both the main and subreports, rebuilt the query, have not found anything that changes the result.
Linker has been working successfully on other subforms. Report with groupings works fine, but I need data from 2 tables both linked to order.id.
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 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 am getting this error 'You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)' I know why I am getting the error, I found out that Form and Subform should be implemented only with one-to-many relationships where the Subform is the many end. However, I have the opposite of that for a reason.
Is this bad practice? Is there anyway around it or to fix the error please?
I have now a report that span around 2 pages. It has subreports in the report that depening how many rows there are they can grow or shrink.I have the problem that the second page the top margin is to close to the top of the pager. The paper has a logo on the right top side. I can make the first page look nice under the logo, but the second page prints to close to the edge that prints over the logo. I did add a page break, but when the page 1 has more data, it flushes this to the next page. The page 2 is now page 3. How can I avoid that, or make it so that the margin of the second page/next page is on the correct lenght of the top. I tried it with the page setup, but it does not work.
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??
