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?
View RepliesI 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 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
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!!
Thanks much,
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??
Thanks so much,
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
[Code] ....
Hi,
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
Regards
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?
Thanks.
Hi,
and again I have a problem :)
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.
Any non VBA ideas?
tnx
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.
Hi Folks,
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.
Thanks all!
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?
View 2 Replies View RelatedHi
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 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!
View 6 Replies View RelatedI 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.
View 1 Replies View RelatedI've been trying to convert a date format like dd/mm/yy to a SAP used format like dd.mm.yyyy .
a simple string conversion like
Code:
pp = day(datefield) & "." & month(datefield) & "." & format(year(datefield),"YYYY")
is not working, the year is converted wrong.
thus 17-07-62 should be converted to 17.7.1962 ( European date format )
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".
View 4 Replies View RelatedI 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?
HELP!
Thanks so much for any ideas.
Beth
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 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 have a date entry textbox.
The user is copying a date from an old form.
Sometimes the form will have the date entered as 00/00/97
How can I change the month and day to 01/01 and of course leave the year as it was.
I've tried using split with no success.
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine:
Option Compare Database
Option Explicit
Private Sub Command1_Click()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
[Code] .....
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Excel workbooks would take names from the "Div" field and the tab names would come from the "Tab" field in the Access table. First need to find workbook name (Div - Field) then the look for each sheet name (Tab - Field) to create 1st Excel workbook with all the sheets (Tab) and repeat the process. I think you need to approach of read the Access table one record at a time keying on the "Div" and "Tab" fields in creating each Excel workbook with the associated multiple tabs (sheets) that are written to a common folder.
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine.
Option Compare Database
Option Explicit
Private Sub Command1_Click()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
[code]....
I would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.
For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...
I have tried using this:
Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"
Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.
Any other ways to import the data?
How move to next record (without enter) after fill mask in first record in access form?
View 4 Replies View RelatedHi
I've built a contacts database which consists of one table containing contacts and services provided and a table with a list of categories, each has a PK autonumber id field. My problem is that individual contacts could fall into multiple categories, could somebody tell me how to do this.
Many Thanks
Anita