I'm using an input mask of:
AAA-AAAaaa
This allows three Characters, followed by a "-" followed by three more characters, followed by optional three more characters.
My problem is that the "-" is not actually part of the data. It is only there for viewing. I would like (need) it part of the actual data becuase I use this information else where (VBA, exports to excel, etc...).
How can I define a field that required the hyphen as part of the real data.
I'm posting this in both VBA and Tables because I don't know if the answer is via a mask or code. Either way is fine
I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?
I have a database sitting on my server for which individual users login to over the network with their own User name and password. Within my database there are links to data from external sources (SQL based).
My query is, if the database remains open at all times, once the link to the data has been established after keying the SQL database password, how do I stop it from asking each user that logs in to enter the password again each time they dip into the database? (If Possible).
Hi, I'm currently putting together a database for a medical NGO in Cambodia (http://www.medicorps.com/updates/cambodia.html) and am looking for some advice. The simple database is for logging client referral cases by Cambodian doctors to a team of international doctors. I haven't used access in 10 months and despite programming in access for 5 years progress is very slow. At the moment I'm designing the input and search forms. I was thinking that a more logical approach would be to convert the forms to access data pages and put the database online. I haven't used data access pages but from what i know their fairly limited? The goal would be the ability to log/search the data with auto updated pull downlist based on the actual data. Ultimately I want the data compiled and emailed to a email list from withing the website. The trouble is I have no idea how to do it.
I've been trying unsuccessfully for weeks to try and query a usable data set that would later be used for calculated values in a report.
I'm building a Hospital Acquired Infections Database for my facility to record data and process reports automatically. Previously, I built the system in Excel using VBA and userforms, but the size of the file has become too cumbersome, and now takes too long to process my reports.
So here's the issue.
UNIT_DATA TABLE: UNIT INF_MONTH INF_YEAR PT_DAYS CVC_DAYS IUC_DAYS VENT_DAYS APV_DAYS
PT_LEVEL TABLE (There are many fields in this table for recording purposes but I will only list the ones I need for reporting purposes):
UNIT INF_STE_MAJ SPECIF_SITE INF_MONTH INF_YEAR
So the important numbers I can't seem to collect are the total number infections per month from the PT_LEVEL Table for only select SPECIF_SITE infections.
The UNIT_DATA Table is a monthly collection for the 5 metrics (PT_DAYS, CVC_DAYS, IUC_DAYS, VENT_DAYS, APV_DAYS) regardless of whether or not the number for each is zero. Every unit in the database will have a record for each month and year.
The PT_LEVEL Table will only have records if and when a patient develops an infection. This means there is no cumulative monthly data for the PT_LEVEL Table Data based on SPECIF_SITE infection type.
I need to first do a roll up count for every month and year in the database, for which I was initially trying to use the UNIT_DATA table for since it contains every month and year. The problem is when I try to query the SPECIF_SITE from PT_LEVEL, I can get the number of Infections for months where infections where present for each unit and null values, for each month, and each year in UNIT_DATA, but when I include a where condition to narrow the view to only selected SPECIF_SITE's it only shows data for that SPECIF_SITE for months where they occured.
I need a cummulative monthly aggregation of the totals for a selection of SPECIF_SITE infections, for every unit, for every month, and every year, since July 2010.
recently posted a thread about a union query. I have got over that hurdle now but face a new problem which is:
When I have created a right join to bring back data from a table, if there is no data it will leave the fields blank. Is there any way I could bring back the row data of the same table.??
I have a table "Customer Plans" with 100s of rows of data with 3 columns
'Name', 'Company', 'Plan Type'
I want to create a new column or table "Customer Details" with the the column name 'Customer' under the following criteria:
If the data in 'Customer Plans.Company' Column meets a certain criteria as in = "Corp&Gov" or "Head Office" or is "blank", I want it to populate the new column/table "Customer Details.Company" with the corresponding data in the 'Customer Plans.Name' column
e.g. if Name = Anna B Company = Corp&Gov then Customer = Anna B
I want to populate 'Customer' with Anna B for that record and so on down the list of data in 'Company' column.
If however the 'Company' column doesn't meet the criteria, I want to populate the new column/table 'Customer' with the current data in 'Company'
if Name = Anna B Company = Dealer then Customer = Dealer
Is there any way I can use data in the row as a column header ? I only have 1 row of data which i want to use as a header to I can link it to other tables/queries.
I need to make my mainform fields required before any data can be entered into subforms. Mainform and Subforms are linked with LinkMaster Child ID. This should only be applied in this form.
I have a string "HICX106-StandardTop-Venderedstest", I only want the first 30 letters or numbers ie "HICX106-StandardTop-Vendereds". Can anyone help, guess this is really easy!!!!!!!!!!
In the subreport, I calculated a difference between budget and actual. I want to present this difference in a way that if it is positive, then just show it the way it is, but if it is a negative number, I want to show it in a (). FOr example, if it is -123, I want to show it as (123).
I want my PRODID (PK, AutoNumber) field to have "PROD-" before the number, i thought it could be done by seting format to "PROD-"# but that only displays it inside MS A, when i use sql queries through php it only retreives the number. How can i avheve this?
abc - it's always in front, it's either 'abc' or 'wxyz'. If 'abc' is not shown, just input 'wxyz'.
'010203' - it will be in the middle, it may appear as abc010203 or abc222010203, always in 6 digit.
22.35 - always at the back, after three alphabet, if it's not shown, just input 0.
I have been able to do it in Excel, but I am wondering if it can be done in Access so that I can eliminate the process to import the text file into Excel.
May i reach following function by Grid Query design or by SQL
Original: ============================================== Factory 1-----------Product A Factory 1-----------Product B Factory 1-----------Product C Factory 1-----------Product D
Factory 2-----------Product D Factory 2-----------Product E Factory 2-----------Product F
Factory 3-----------Product D Factory 3-----------Product E Factory 3-----------Product F
Result: ============================================== Factory 1-----------Product A,Product B,Product C,Product D Factory 2-----------Product D,Product E,Product F Factory 3-----------Product D,Product E,Product F
I have an old table that has many, many records. One of the columns lists the Date of Birth. Here's my problem:
Users have entered data into this column as "71462" This representing July 14, 1962. Is there a way to automatically format all data in this column so that it is more palatable - e.g. 07/14/1962?
Changing the column Data Type to "Short Date" in Design View would delete all existing data in that column, so this is not an option.
I have a query which is pulling its data from a form, which in turn is pulling data from a table. When I select 0.82 on my form, my query runs fine. But when I select 0.826856 from my form the query does not return any result.
0.826856 is available in the table and the pull down list in the form. The data format for all my table, form and query is General and decimal places is set to 8.
Could you plz tell me why I am not able to run the query using 0.826856 ????
I have a database where I need to import data from an excel file.
What my employer needs me to do is upon appending the data to a table which I named properties, under the field which I called owner, all the names should follow the correct format. Most of the inputted data does not follow the correct format.
The correct format of the names should be: K. CURTIZ -> Where K is the initial of the First Name, it should have the '.' and a space before the Family Name
Here's an example of a data that is inputted incorrectly: R OTTESON -> it should be R. OTTESON
another example where there are 2 names of 2 different persons, it should be inputted this way: A. & J. PANAG
Mostly inputted incorrectly this way: J&T BOTIFAN -> it should be J. & T. BOTIFAN
I have a button on a form that creates an email and inserts fields from my database. I have a field that is set to Long Date format type, but when it populates the email, it shows as a Short Date format type. Is there anyway to retain the Long Date format?
Well I have been searching but have not exactly found the answer I need so:
I have a linked table in my database. One of the fields on the source table is a date field but the data type is Text. So there is data in the field that represents dates but they are formatted as text like this: 090506 101106 120506
I need the field data to behave as a date, any ideas? I can't change the source data type.
Then...at some point I want to produce a query that shows all records with a date earlier than the current days date...in pointers in the right direction on that will be appreciated also.
I have a standard form with header, detail, and footer sections. The detail section has a range of calculated boxes and the footer section also has a range of calculated control boxes.
There is a button on the form, which runs the following code: DoCmd.OutputTo acOutputForm, "Divisions", acFormatXLS, "C: ester.xls", False
This works as far as exporting the Detail section of data. However, as soon as it trys to export the footer section, it just appends them onto the end of the rows. For example, the report lists: ABCD 10 20 DEFG 20 30 Totals 30 50
The excel spreadsheet after export lists Controlbox name Controlbox name Controlbox name cbn cbn ABCD 10 20 30 50 DEFG 20 30 30 50
Is there a way to: (a) correct this in the export; (b) format the spreadsheet; (c) alternative, e.g. drag the data in from a sppreadsheet ?
Hi, I'm using a web based form to store dates (and other records) in an access database. I use the dd/mm/yyyy date format (europe. go figure) Anyway, the problem is that when submitting an ambiguous date, as in 11/01/2004, access sees it as 01-NOV-2004 rather then 11-JAN-2004, as it should.
Why does this happen, and how can I fix it. I have no problem doing the fixing in the access file itself or in the SQL command that populates it- whatever works best.