Delete Extra Fields
May 28, 2006
Hi
Does anyone know how to delete the extra blank fields you get at the end of a query. I am converting the query to a handheld database with a field limit and the extra fields push my field count over the limit. i have tried just 'deleting' them but they reapear when I reopen the query. they definitely count as fields because if I delete some real fields i can do the conversion. i dont want to alter the structure of the original database as it has been in use some time and works well. any ideas advice
Thanks
supateach
Hi again
I am still having problems with too many fields in my query so it will not convert to a handheld database. I have tried making a test query (with the wizard)from the same table and sure enough there are extra fields at the end, which do not come from the table, which reappear after they have been deleted, when you reopen the query. I have attatched the query to illustrate that I have deleted the fields and then they reappear. It does appear to be impossible to get rid of them, in which case I shall have to decrease the number of 'real' fields to get below the max the handheld can convert. Any advice gratefully received
Thanks
supateach
View Replies
ADVERTISEMENT
Jul 15, 2015
I have an Access table with data fields however at after the last field column, I have about 15 blank and unselectable or uneditable columns that I don't want there. How I delete these? Reason I want to delete them is because table is a subform on a main form and these extra columns are visible on it and they also cause a scroll bar to shown
View 4 Replies
View Related
Dec 3, 2007
Is it possible to have an extra field that is added to the calculated weeks left? Such as what if I want 2 extra weeks instead of the 12?
I know this is probally an advance question! Thanks in advance!!!
This is the awesome example from sbenj69:
Expr1: 12-(datediff("ww",[join date], date())
What I want to do is add another column which will have an additional number. The additional number would just be added to the 12- part of the code. So if I have a field with 2 in it, the 12- would become: 14-(datediff("ww",[join date], date())
Is this truly possible or is it something that can't be done in access
View 5 Replies
View Related
Nov 30, 2011
I'm using Access 2003 (with Windows XP).
I'm intending to make two extra fields open up if a particular value is selected. Just as a simple example:
Field: Type of Item
Possible Values: Alcohol, Beverage, Cutlery
So if either a beverage or alcohol is selected, I would like a new field to open up. For both Alcohol and Beverage, I would like "Content of bottle in mLs" to open up. For Alcohol, I would also like "Percentage Alcohol content" to also open up.
So basically, if a specific value is selected, I would like extra fields to come up. I'm not sure if there's any simple way to do it, or whether it requires VBA coding, but either way if it is possible I would like it to be done.
1) How do you make an input mask enter from right to left? For example, if post codes can either be 4 or 5 digits long, I put 90000 (requiring at LEAST 4 digits) but if you start entering data, it starts from the 9. Would it work if I did 00009?
2) Is there any way to make a form go through a progression of screens rather than all the input fields being on the one page?
View 2 Replies
View Related
Jul 3, 2013
Using DAO recordset, table enters unbound fields data properly for desired records, but adds an extra record which is a duplicate of the first record entered....any known access quirks for adding unwanted records?
View 3 Replies
View Related
Aug 30, 2005
Hi
Is there a automatic way i can delete Fields from a Table? This table gets input into the database very frequently as updates come in and i'd like something like a Query or Macro, where i can define which fields i dont want, to delete the 30+ columns of useless data.
Can't see how to do it with a Query or macro though
Anyone have any ideas?
Thanks
View 5 Replies
View Related
May 23, 2005
I have a table in my Database that uses the feilds Location and partnum. I use a barcode scanner to generate each of these fields. The problem I am having is that when our part number is scanned it contains data such as "des--. 123456 --aa". I have been doing a find/replace on this data to remove the "des--." and "--aa" but I was wondering if access has a function to do this for me so that I can get a macro to check automatically for this type of problem. Any ideas?
View 1 Replies
View Related
Nov 14, 2012
I need to delete data in a table that named Cap One Table. I can not use a delete query since database located on company server and it is being used by many people. So, I have created a form and put on there two text boxes - txtStartDate and txtEndDate and command button cmdDelete. An idea behind this is that a user types date in txtStartDate and txtEndDate and click command button and delition happend in table Cap One based on that criteria. I put the below code in command button click event , but it doesn't work.
Dim startDate As String
Dim EndDate As String
Dim strSql As String
startDate = txtStartDate.Value
EndDate = txtEndtDate.Value
[code]....
View 4 Replies
View Related
Jun 20, 2012
How can I create a criteria to delete the entire record in a query if the LnPreApprConvDt and LnPreApprFg are both blank?
View 1 Replies
View Related
Aug 5, 2006
I need to trim a lot of tables. Is it possible to delete table fields with reference to the field number instead of field name?
The command 'tblMyTable.Fields.Delete ("FieldName")' takes a string (field name) as input. I rather like to - in a loop - delete say,
Sub DeleteFields()
For i = 40 To 60
tblMyTable.Fields.Delete Field(i) ' Not correct syntax
Next i
End Sub
thh
View 2 Replies
View Related
Sep 5, 2007
Hi
I need to know if it is possible to create a delete query which will target particular fields in a row based on certain criteria in a seperate combo box(on same table).
The few ways that I have tried to do this have been unsuccesful so if anyone can shed some light on the subject it would be greatly appreciated.
View 5 Replies
View Related
Apr 13, 2007
I have recently gotten a handle on ASP and found it to be a very powerful tool.
I am wondering if there is a way to design a page where I can list my database tables, and by using a command, I can use asp to do the following.
1. Add or delete table
2. Add, delete, or update fields within the tables
for instance: rename a field 'PersonName' to 'MyName' without change the original values in the field 'PersonName'
I have searched all over the net and could not find information on this subject.
Thanks in advance.
View 3 Replies
View Related
Jan 1, 2006
Hi All,
For some strange reason, a replica db is being created along side the original. I don't believe it's a back up since the name back_up usually appears in the file name. I've seen it happen once when the proper mdb file was opened and then noticed the new db was created (always called db1.mdb). It's a permanent file mdb file and not the record locking icon. I remember over the summer someone posted a similar problem but I haven't been able to locate that thread. Any suggestions?
Thanks
View 2 Replies
View Related
Mar 8, 2008
Although I have been using access for a year or two (self taught) I have only recently noticed that the form toolbox (the one with combo boxes and buttons etc on it) appears to have a library of procedure calls for calendars and all sorts of things sitting there waiting to be used. Is there a list somewhere of what all this lot does? I have been playing with a couple of date pickers but I don't particularly want to go through all of the rest to see what they do or if they would be useful.
Thanks
View 2 Replies
View Related
Jan 9, 2007
I have a table t1 which contain many records, the field look like:
id f1 f2 f3 ---fn
Id is the primary key
I have some records which have different id but exact the same other fields.
like
id f1 f2 f3 ---fn
87 1 4 6 ---9
12 1 4 6 ---9
18 1 4 6 ---9
116 1 4 6 ---9
1287 1 4 6 ---9
98787 1 4 6 ---9
for those records, I only want to keep one record (any one) and remove all others. How can I do that?
Thanks.
Jeff
View 1 Replies
View Related
Aug 29, 2006
I have the following expression, which needs to be wrapped by some extra logic:IIf([NetTotal]<>0,IIf([NetTotal]-[InvoicesRaised]>0,30,[InvoicesRaised]/[NetTotal]*30),0)What I need to do is, if the above expression comes to less than 0, then the control box needs to display 0, otherwise it should display the value of the expression.Now, I know I can do this by IIf(expression<0, 0, expression). However, this adds a lot of code in there and the expression is put in twice. Is there an alternative way of doing this ?
View 1 Replies
View Related
Aug 31, 2006
Is it possible to adde extra filters to an expression, i.e. the same as an AND clause in a SQL query.
For example,
=DSum("[1To90]","tableA","ID=2")
I need something like :
=DSum("[1To90]","tableA","ID=2 AND Region='A'")
Will that work ?
View 1 Replies
View Related
May 25, 2006
Hello,
I made an database with all addresses.
When persons live in an appartment, then the field "busnummer" will be filled in with a number. When they live not in an appartment, the field "busnummer" is empty.
In a query, I made an expression and filled the expressien in field (in the query):
Expr3: IIf([Busnummer]>0;"bus [Busnummer]";"")
When "Busnummer" is greater then 0 THEN write the text "bus" and the value of the field right behind, else leave the field empty.
The result of the expression above is:
"bus [Busnummer]"
I have already tried the following:
Expr3: IIf([Busnummer]>0;[Busnummer];"")
The result of the expression is the number I filled in in the database. Now I want to have the result: bus and the number I filled in... :)
Who can give me a tip to solve this problem?
Kindly regards
View 3 Replies
View Related
Mar 5, 2007
Hi
I have 120 tables, each with the same name except 2 identifying characters at the end eg pc_dist_ab, pc_dist_al
Each table currentnly has 3 columns.
I would like to be able to add 2 additonal columns to each table with one query and was hoping an alter table query where the table name matches pattern would have worked but evidently not.
Is there a way to build some sort of dynamic query to add extra columns to these different tables at the same time?
To save another post I guess Once this is done I would then like to create 1 main table by creating a new table and appending all the files together- again I would prefer to be able to run this once.
I'd appreciate any help/thoughts as to whether this can be done?:confused:
Thanks
View 1 Replies
View Related
May 23, 2006
I have a pop-up form in Continuous Forms mode used to enter notes tied to the id field of my main form.
Two fields - notedate and status. Everytime I click in the blank field "status" another blank record always shows up underneath.
I have "notedate" set to auto fill when the focus is set to "status" and this is creating extra records in my table.
Is there a way to stop this from happening?
"Cycle" set to All Records - changed to Current Record with no difference.
Private Sub status_Click()
If IsNull([notedate]) Then
Me.notedate = date
Me.status.SetFocus
End If
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.negid = Forms!OpenContracts!negid
End Sub
Thanks,
Toni
View 4 Replies
View Related
Mar 23, 2015
We recently converted to Access 2010 after using 2000. This problem has suddenly appeared. It doesn't happen on every report but there is one in particular right now and I can't figure it out.The first page of the report is complete but it prints a second page containing only the page header and footer, the rest blank. There are only 2 detail records on the first page and plenty of blank space.
It's not the issue of the page overflowing onto extra pages because the page size bleeds beyond the margins. Everything is safely inside the set margins, and in fact as a test I brought the page width down to 4 inches with .5" margins and it still prints a second page.
View 3 Replies
View Related
Aug 16, 2014
Copying a column from one record to another. Code is:
Code:
Set fld = Records.Fields("violationLocalOrdinanceNumber")
If Len(fld & "") > 0 Then
rst!LOR_NB = Nz(Records!violationLocalOrdinanceNumber, "")
Debug.Print ("LOR NB:" & rst!LOR_NB & "." & " len:" & Len(Nz(Records!violationLocalOrdinanceNumber, "")) & " len new:" & Len(rst!LOR_NB))
End If
Some of the output I'm getting is:
LOR NB:8.08(5) . len:7 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:7.08(5)(A) . len:10 len new:25
LOR NB:7.08(5)(A) . len:10 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:8.08(5) . len:7 len new:25
No matter what the original string length is, something is adding extra spaces and forcing it to 25 in the new record. This is the only field I've been able to identify with this issue. Column definition is char(25), no indexes or anything special that I can tell.
Tried adding a left() function call after the assignment but that didn't work either.
View 4 Replies
View Related
May 15, 2015
I need to add HolDte and make it also use HolidayDate as it's criteria.
PHP Code:
strSQL = "INSERT INTO tblHour (WorkDate,Hours,HolDay,EmployeeID) " _
& " VALUES (#" & Me.HolidayDate & "#," & Me.txtHrs & ",True," & Me.EmployeeID & ")"
View 7 Replies
View Related
Aug 31, 2011
When I open my access 2007 database from the switchboard, the form that it opens up to is a parent form with a subform embedded in it. The subform is linked by the 'org name' and by 'year'.
I am finding that when I open the parent form,there is always an additional empty record in the subform, alongside the record which has data in it.
I am not sure why it is doing this, especially as when you enter another record, this empty form vanishes. Its almost acting as the default form?
I have also noticed that when I go to another form which is displaying specific data linked to the 'org name' and 'year' it also has an extra record
However, the weird thing is it does not show up in the table. And again, once you go to a new organisation and input some data, and then go back to the other organisation,the additional record has gone?
Is there a simple property setting whereas each time you open the form, it always opens up to where you essentially left off, no empty record?
View 2 Replies
View Related
May 6, 2014
All I am trying to do is insert to have a form with a "Delete Record" button on it. The problem is I don't want anyone to be able to delete a record, I would like someone to have to insert a password to confirm the delete.
View 13 Replies
View Related
Mar 9, 2006
Hi,
I am a novice with Access and would like a steer with what I am sure is a simple issue but I can't find an answer. :confused:
I currently have 2 queries based on 2 separate tables.
Ops_Log_996_Query
SELECT [996_Table].Unit, [996_Table].Location, [996_Table].hiredate
FROM 996_Table
WHERE ((([996_Table].hiredate)=Date()));
Ops_Log_SQTU_Query
SELECT SQTU_Table.Unit, SQTU_Table.Location, SQTU_Table.hiredate
FROM SQTU_Table
WHERE (((SQTU_Table.hiredate)=Date()));
When run separately the first query returns 2 results and the other 1 result - fine so far.
I am now trying to combine the results for display in a report so I have a third query which takes its info from the first two -
SELECT DISTINCTROW Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate
FROM Ops_Log_996_Query, Ops_Log_SQTU_Query
GROUP BY Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate;
This displays the 2 separate records in the first 3 columns ok but in the last 3 columns the info in record 2 is a repeat of record 1. :confused:
Can anyone help me out please ?
Thanks, Oscar
View 2 Replies
View Related
