Required Data Format

May 18, 2007

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

Thank you

View Replies

Queries :: Fixed Format Numbers Required From A List?

Jun 28, 2013

I have a table which contains list of data like,

10025.325
100.245
1245.8464
12546.356
etc.. etc.....

Now, I need a data which contains numbers 5 digits before decimal point & 3 digits after decimal point.

like xxxxx.xxx

View 5 Replies View Related

Forms :: Fields Are Required To Be Filled Out Even Though Not Set As Required

Nov 3, 2014

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?

View 3 Replies View Related

External Data Source Password Help Required

Jun 9, 2005

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).

(Access 97 by the way)

Thanks :confused:

View 3 Replies View Related

Advice Required With Access Data Pages

Jan 29, 2006

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.

Thanks in advance for your help.

View 5 Replies View Related

Queries :: Getting Required Data Using Query For Calculations To Be Used Later?

Nov 7, 2014

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 Tables are joined Left to Right as Follows:

UNIT_DATA.UNIT - PT_LEVEL.UNIT
UNIT_DATA.INF_MONTH - PT_LEVEL.INF_MONTH
UNIT_DATA.INF_YEAR - PT_LEVEL.INF_YEAR

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.

View 14 Replies View Related

Correct Join - Not Bring Required Data Back?

Jan 21, 2008

Hi ,

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.??

Hope some one could suggest something.

Thanks again.

View 9 Replies View Related

General :: Compare Data In A Column And Update If Required

Feb 28, 2013

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

View 1 Replies View Related

Queries :: Single Row Data Required As Column Header

Nov 4, 2013

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.

(Access 2007 query)

View 1 Replies View Related

General :: Make Mainform Field Required To Enter Data Into Subforms?

Aug 7, 2015

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.

View 4 Replies View Related

Data Format

Aug 8, 2005

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!!!!!!!!!!

View 4 Replies View Related

Data Format

Jun 28, 2006

hello, Not sure if this is right placeto post.

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).

Can anybody help me with it?

Thank you.

View 2 Replies View Related

Field Data Format

Oct 11, 2006

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?

View 3 Replies View Related

Extraxt And Format Data From Txt File

Jun 28, 2006

Does Access able to extract and format data from Access such as follow:

abc/d e4.5% 0216 010203 USD22.35 - extract 'abc', '010203', '22.35'.

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.

Please advise and thanks.

View 1 Replies View Related

How To Transform Data To Crosstab-like Format?

Apr 1, 2008

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

View 7 Replies View Related

Format Existing Columns Of Data

Sep 18, 2007

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.

Thanks for your help.

View 3 Replies View Related

Data Format For Query Input

Oct 17, 2005

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 ????

View 3 Replies View Related

Convert Query Data To Other Format

Nov 13, 2005

I am getting the following from a query

date|id1_|id2___|
1___|nick|james|
2___|john|nick_|

How can i convert this data this

id_____|date1|date2|
nick___|_1___|_2___|
john___|_1___|_____|
james_|_____|_2___|

Best Regards

View 4 Replies View Related

How To Format A Number Data Type

Dec 15, 2012

I am trying to format my Number data type to look like this: 0001 / 2013 where "0000" is the prefix, 1 is the number and " / 2013" is the suffix.

Tablename: tblclients
Field Name: DelNoteNo
Data type: Number

Form
Control Name: DelNoteNo
Control Source: DelNoteNo

I am searching on this forum but I can't get it working.On the Before Up date procedure i have:

DelNoteNo = Nz(DMax(DelNoteNo, "tblClients"), 0) +1

but its is giving "invalid use of Null "

View 4 Replies View Related

How To Set A Format On Strings Upon Data Import

Mar 8, 2012

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

View 3 Replies View Related

Date Format When Passing Data To An Email

Aug 31, 2005

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?

View 1 Replies View Related

Format Dollar Data In Access 2000/03

Jan 3, 2007

Hi,

How do I format data in my report to display with a dollar sign?

I am using the following format $#,##0.00;($#,##0.00), but somehow I get a Format$ paramater to enter value.

Thanks

df

View 10 Replies View Related

Format Text Field Data As Date

Oct 6, 2006

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.

Thanks!

View 1 Replies View Related

Append Query Pass ID Format And Data

Jul 20, 2007

Hello,

Is it possible to pass a AutoNumber Format variable and data to a new table using an Append Query? If not does anyone know what I can try?

Thanks!

View 1 Replies View Related

Export To Excel Seems To Format Data Incorrectly

Aug 29, 2006

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 ?

View 1 Replies View Related

Date Format Changing When Saving Data

Jan 19, 2004

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.

Thanks,
-Ethan

View 5 Replies View Related