How can I do this? There doesn't seem to be an member of Field called format. Why is this? I want to make the display on a Boolean field, in a Table made from a Make Table Query, to Yes/No, rather than default 0/-1.
View RepliesHi
I have a field on a Form that displays values in Sterling (The Default on the machine) ie with the pound sign before the numbers.
I need to change the sign before the numbers to Dollar at a click of a button. I'm not interested in actually converting the values from Sterling to Dollar but only to change the sign. So I attached the code below to a Button's On Click Event, but I get an error = Expected.
Format([ControlName] , "$#,##.00")
ViRi
I am attempting to manipulate the Format Property of a Textbox. As an example, entering in &" lbs" causes your text to end with " lbs".... only I am trying to add a " mark, for example saying something is 6" away...
How do you tell the format property to add this?
I have a date/time field. I would like to:
Enter time this way and have it show in the form as:
Enter 5 - show 5 PM
Enter 515 - show 5:15 PM
etc.
I would like it to default to PM and not have to select or enter the PM. How do I enter this format in the table?
I have several tables that I would like to be seperate from the forms. Bottom line is that I want the program stuff to be seperate from the tables seperate as in two different files.
I have not messed around with this yet and am wondering how big of a pain this will be to do with the code that I have already written?
Any thoughts ideas etc.?
We have an access program that unlinks a table after certain functions, and relinks it in others. Is there a need for this? For one user, the table does not link, so we have to manually go over and link it for her everyday.
I am wondering why it was built to unlink and relink - shouldn't the link be "real time" and get up-to-date data?
I am also not sure why the TableDefs.RefreshLink is necessary (though it is not in our code - what situation would warrant refreshing the link?)
I want to add a column to an existing table using the sql statement Alter table. The new column needs to be a yes/no type. The following code almost works but it only sets the column to a general logical type not specifically the yes/no check box .
DoCmd.RunSQL "Alter Table TblIssues Add [" & Issue & "] logical;"
Any ideas?
I am having trouble with a make-table query. I run a query that populates my website with data daily. I have a bunch of fields in the db that I wanted to be displayed as one field on the website, so in my query I concatenated them. My website is able to distinguish which fields are text and which are memos so I can display multiple lines. Everytime I run the query, it creates the concatenated field as a text field. Is there a way I can default this to a memo type?
Thanks.
I have a linked table to a SQL server table called AllAttendanceEvents.
the AllAttendanceEvents table has index called "AttEvents". I wrote the following code:
set AllAttendanceEvents=currentdb.openrecordset("AllAttendanceEvents")
AllAttendanceEvents.movefirst
AllAttendanceEvents.index="AttEvents"
When it executes the read line statement, it generates the following error:
Operation is not supported for this type of object
Is there any way to get around of this problem with the linked tables?
I am using Access 2010. How to change the property of a field in a table programmatically.
I have a table in which one field has Required property set to "Yes". I would like to set this property by using VBA code to "No", then add data into a table using a query and re-set the Required property to "Yes".
Is this possible and if so, how can I do it?
I cant change the table property to number only so is there an afterupdate code I could run on a field and check for alphanumartic charters ?
View 1 Replies View RelatedWhen I try to transfer (ctrl c + v) data from a table in Excel to a table in Access it loses format.
Exemple: $ 1.000,00 (Excel) become 1000 (Access).
I need to keep the format to make sure that the code works properly.
I have a numeric field (long integer) in a table. I've set the format to fixed. No matter what I set the decimal places to, auto, 0 or 2, when I enter a value of 0.71, it displays as 1.00
In my form, I've also set the field property to fixed, but it displays the value as 1.
Hi All,
The following code is supposed to insert a date formatted as dd/mm/yyyy into a table, when i view the mySQL string in the debug window it shows the correct format, however when i open the table it displays the date as mm/dd/yyyy.
The table's date field userdate is formatted as a short date field with an input mask of dd/mm/yyyy.
My computers date settings are set up as dd/mm/yyyy as well
Any ideas on hows to correct this?
Thanks in advance. :)
Public Function Test()
Dim mySQL As String
Dim dtDate As Date
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()
dtDate = Format(Now(), "dd/mm/yy")
mySQL = "INSERT INTO tblCurrentUser( userdate ) values (" & "#" & dtDate & "#" & ");"
Debug.Print mySQL
'*The debugger shows* INSERT INTO tblCurrentUser( userdate ) values (#04/05/2005#);
' However the value in my table tblCurrentUser is05/04/2005 is in mm/dd/yyyy format
Set qdf = db.CreateQueryDef("", mySQL)
qdf.Execute
End Function
Here is my problem:
Let's say that I have a table with a field name of "Phone Number". I input about 200 #'s in the following format: xxx-xxx-xxxx. I decide that I want to use the an input mask to create (xxx) xxx-xxxx. I input 50 more #'s. The previous 200 have the old format while the 50 recent #'s have the new format. How can I update the entire field from the old format to the new without reentering the data? Or can I? If I can, will the change also apply to any forms created off of this table?
Thanks
The transfer spreadsheet function only allows one to export a table from the database to an excel file format. I want to output the table to a file in csv format using a simple function. Is this possible ?
View 1 Replies View RelatedGreetings all,
I am probably just being dense.
But how do I go about entering a percentage of say 5% in a numer field formated to percentage in a table?
If I enter 5 in translates to 500%, .05 as 0!
Thanks,
Brian.
Zimbabwe.
I know in creating columns in a table where I have date/time you can select long short, etc. But, I want my column name to be YR and the values of 2014, 2015, 2016, 2017 all the way to 2020. So I end up with 7 rows. Then another date related table that houses the months. It will have a column name of MTH and the values Jan through Dec so a total of 12 rows. I have tried in format putting YYYY and MMM but when entering the data, it gives me an error saying I have to convert, click the calendar and choose a date. I don't want to choose month, day and year. I just want the year.
View 2 Replies View RelatedHi guys, hope you can help.
I have two fields (that I'm interested in) in my table: Insurance Policy End Date and Reminder Date.
I would like the default value in the reminder date to be exactly six weeks before the value entered in Policy End date. I'm not at all sure how to do this with the expression builder. Could you guys nudge me in the right direction?
Eternally grateful.
I guess this is pretty fundamental but I cant get it right! I want to search for a member by post code so I need to have validation for my table field post code so that when my parameter query accepts the post code input records will be returned covering all input types e.g. Users can put in CF72 (space)9AD or CF729AD and still get the same records or alternately how do I make users enter Post Codes with the correct UK spacing i.e 4 characters a space then the remaining characters?
View 7 Replies View RelatedA lady at work created an Access database that imports a dBase 5 file and when I go to import this file on User #1 PC it gives the error;
"External table is not in the expected format."
I can use the same Access database and same dBase file to import (stored on a shared network drive) from User #2 & #3's PC's and all works well. The only problem is that it is User #1 job to do this.
I unloaded Access 2003 and reloaded it and still the same results. All 3 PC's are identical via hardware and software.
It use to work until this past Tuesday when it started to give the error.
My steps are:
open the "Sales" Access database
select the prices table
Get External Data / Import and then select Prices.dbf
Then the error message comes up.
I am not an Access database guy and could use some help!
Thanks
WhooTAZ
I have set the Left([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],8) AS [DISTRIBUTION DT] field property to Short Date, 99/99/0000;0;_ . When I run the query I get that field formated as a short date, but when I try to create a table from the same query the data returned is no longer in the date format. Is there something I can do to have the make table results look like what I get when I just run the query without creating a table?
SELECT
DSERDBA_RECEIPTS.RCTH_ID_CASE AS [CASE],
DSERDBA_RECEIPTS.RCTH_DT_RECEIPT AS [DT RCTH],
Left([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],8) AS [DISTRIBUTION DT], DSERDBA_DISBURSEMENTS.DSB_DT_DISBURSE INTO [TABLE 1]
FROM [UNIVERSE OF CASES] INNER JOIN (DSERDBA_RECEIPTS INNER JOIN DSERDBA_DISBURSEMENTS ON DSERDBA_RECEIPTS.RCTH_RECEIPT_NO = DSERDBA_DISBURSEMENTS.DSB_RECEIPT_NO) ON [UNIVERSE OF CASES].STAT_CASE_ID = DSERDBA_RECEIPTS.RCTH_ID_CASE
WHERE (((DSERDBA_RECEIPTS.RCTH_ID_CASE)="20028570P"));
My Access DB has two tables linked to excel spreadsheets. Excel is the only way I can acquire this data from another source. The Excel spreadsheets update themselves on a regular basis.
My problem is that one of the fields in the spreadsheet needs to be formatted as a date and I have been unable to get Excel to handle this automatically.
Is there some way that I can date format a field in a table that is linked to an Excel spreadsheet?
Unless I can figure this out, I have to manually be involved with each Excel Update.
Thanks in advance.
I have an issue with queries not showing sufficient decimal points, even though the underlying table does have the formatting I desire.
If I open Table 1 directly, for Field 1 I have a value of 0.1439254445378153860918842318 using a format of decimal / fixed / Precision: 18 / Scale: 0 / Decimal: 2 and it shows in the table as 0.14 until I click on the cell in which case I get the full value above.
This is how I want it to show (0.14). When I run a query on this table, not setting any formats in the query, I get 0.00, and clicking on the cell only provides a result of 0. I've tried a ton of different underlying formats on Table 1, and been unable to get a different result...
1. I am trying to extract table from Access in XML format. On completing the extraction, the XML disappears from the folder where it was stored.....
2. Also, How do i see SQL query for table in Access... the SQL view option is not seen in the ACCESS dropdown.
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?