I have two questionaire forms in a database. There are no tables or queries involved.
The answers to each questionaire are compiled into text boxes, one on each Form. On the main form I have another text box that puts all the answers together so that this can be transferred to another program. An example of what is in each text box is as follows:
These are just small extractions. My problem is, if an answer is not necessary in the first lot of text, but an answer has been supplied in the second lot, how do I remove the spacing that has resulted from my Formula in Text1. -
e.g: Normal answers would show perhaps
Yes Twice 16 25 Red Peter Ford Football 1965 - Whereby the first 4 answers are from Text1 and the remaining answers from Text2.
If some answers are not necessary from Text1 I would get the following result:
SPACE SPACE SPACE Twice Red Peter Ford Football 1965
Access 2000. I have table of many hundreds of records. One of th fields of text looks like this: XXXXXXXXXXXX and I want it to look like this: XXX XXX XXX XXX by adding the spaces. Would someone please show me how to write the function or code to add the spaces to the text as shown : Thanks :o
We have a table with approx 2m records. Just three fields: Key (primary) Postcode Postcode reference
The problem is with the postcodes. Many of these have a double space in them such as this DE13 7EL but we need DE13 7EL.
Find and Replace runs into a problem because of the number of records.
An update query seems obvious but we can't figure out what to put in Update to as this appears to return the literal value. We are not VBA programmers so that isn't really an option for us. How can we strip out that extra space?
I have a table with postcodes (1.6M rows) that have had all the spaces removed. I want to match another table to it and ordinarily would have removed the spaces from the postcodes in that but I think because of the vol (2.5M rows) Access is reluctant (it either crashes or only updates a few thousand). A better way would be to reformat the postcode using some SQL or VBA code. The trouble is the postcodes are different numbers of characters and the space would either need to be after the 3rd when there are 6 characters or after the 4th when there are 7 characters. eg PO121DL becomes PO12 1DL PO91UL becomes PO9 1UL
I can concatenate fields and count characters but am unsure as to the best way to approach this in terms of coding and structure? Using Access 2002.
Hi members, I have two empty columns in my access database whose total width is supposed to be 385 chars (255+130). I am trying to find a way to be able to export this blank field with a total of 385 blank spaces onto an ASCII text file. A sample row looks like this: L ***** 385spaces needed here ***** 888888 EXAMPLE DEALER NAME 1234 EXAMPLE LANE EXAMPLE ADDRESS LINE 2 EXAMPLE CITY CA88888 80088812348008881234FORD JOE DEALER NAME 8008881234
After the first letter L in position 1, I need to have 385 blank spaces and then at 387th place I have some other data to follow. I tried a lot to use the export wizard, but it doesn't help. Is there a way to do this? I would really appreciate any help. Thanks,
I have large sets of data that has labels on them. For example "25.56 lbs". I just want the raw numbers. Can someone help with what to type in the update to field of an update query to just keep the numbers? Thanks
I have a form with a unbound text field which when a user inserts text and then removes the focus from that field, the text gets inserted into a memo field. By using:
However, I have noticed an issue with this, in respect that when the user enter text then moves to the next record, the previous entered text is still there, This could then cause this old text to get inserted into the wrong record. How can I set this field to blank once it I have moved to the next record or closed the form?
Thank you for taking the time to look at this Question.
I am using a form in which i only need the last sections.
example:- G/001 - Ceiling - Tiles - Perforated. this is what i get at the second, but i would like to drop the "G/001 - " and just have "Ceiling - Tiles - Perforated.", has anybody got any ideas on how to do this please.
Hi, I got this code from a member here (thank you, dbickin!), and I've altered it several times to fit my needs. This time, though, it's not working. My goal is to delete all of the text to the left of "C-W." My previous goals were to delete text to the left of /, -, and (. Maybe it's not working now because C-W is three characters rather than one.
Here's the code... 3 samples of what WORKS, and the last one doesn't.
Function RemoveNote(TextIn As String) As String Dim TextOut As String ' buffer for result Dim i As Integer ' index into string Dim InNote As Integer ' flag showing if we are inside a note
InNote = 0 For i = 1 To Len(TextIn) If Mid$(TextIn, i, 1) = "/" Then InNote = Abs(InNote - 1) ' toggle flag Else If InNote = 0 Then ' not in note, so copy character TextOut = TextOut & Mid$(TextIn, i, 1) End If End If Next RemoveNote = TextOut End Function
Function RemoveNote2(TextIn As String) As String Dim TextOut As String ' buffer for result Dim i As Integer ' index into string Dim InNote As Integer ' flag showing if we are inside a note
InNote = 0 For i = 1 To Len(TextIn) If Mid$(TextIn, i, 1) = "-" Then InNote = Abs(InNote - 1) ' toggle flag Else If InNote = 0 Then ' not in note, so copy character TextOut = TextOut & Mid$(TextIn, i, 1) End If End If Next RemoveNote2 = TextOut End Function
Function RemoveNote3(TextIn As String) As String Dim TextOut As String ' buffer for result Dim i As Integer ' index into string Dim InNote As Integer ' flag showing if we are inside a note
InNote = 0 For i = 1 To Len(TextIn) If Mid$(TextIn, i, 1) = "(" Then InNote = Abs(InNote - 1) ' toggle flag Else If InNote = 0 Then ' not in note, so copy character TextOut = TextOut & Mid$(TextIn, i, 1) End If End If Next RemoveNote3 = TextOut End Function
Function RemoveNote4(TextIn As String) As String Dim TextOut As String ' buffer for result Dim i As Integer ' index into string Dim InNote As Integer ' flag showing if we are inside a note
InNote = 0 For i = 1 To Len(TextIn) If Mid$(TextIn, i, 1) = "C-W" Then InNote = Abs(InNote - 1) ' toggle flag Else If InNote = 0 Then ' not in note, so copy character TextOut = TextOut & Mid$(TextIn, i, 1) End If End If Next RemoveNote4 = TextOut End Function
Hi, I need to remove the automatic (empty) new row that access produces under the last record in the table, because i would like to remove it when i view a query. Could any of you kind people suggest a way that i could do this?? thanks for you help.
I have an inventory checklist being done up now. After the item info has been typed in (price, part number.. ), below i put in the transaction info (4 sold today, 2 recieved yesterday..)
I need by report to show the total number of all stocks and how much they are all worth. The report does that, no problem. Only thing is that instead of the end product, Eg: Product X, 5 pieces, $10
it also prints the transactions in the report. Eg: Product X 4 pieces, $8 Product X -1 piece ($2) Product X 2 pieces, $4.
What should I do to make my report -not- print all this useless junk, but just the end product? Thanks
Hi. Hoping someone can help me. Or at least tell me if this can or can't be done. I'm designing a new database at work and want to know if it's possible to blank out fields if they don't need to be filled in.
Basically in simple terms I want it so that :
For field A you have 2 options. If option 1 is selected - fields B, C, D and E are applicable and should be filled out. If option 2 is selected - fields B, C, D and E are unapplicable and so I'd like them to be shaded/blanked out.
I have a query with several different columns. One Column, CodeNum is built based on the values in Expr4. When I try to set a criteria for CodeNum (Like "8*"), and I run the query, I get a popup for Expr4 ("Enter Parameter Value | Expr4). I want to get all values of Expr4 when CodeNum starts with an 8.
CodeNum is built as follows: JCC: Left([Expr4],InStr([Expr4],"/")-1)
I have an Excel file that I want to import into an Access db table. In that Excel file is a date field formatted mm/yy. When I import that file, Access converts the date to m/d/yyyy. Is there anyway to re-format that data after it's imported into Access (like a global change?)? I even tried setting up an input mask for that date field, but that only seems to work when you are actually typing data into the field.
Hi, Im trying to import a spreadsheet from Excel. I use the wizard and I get the sheet imported. The only problem is that I get additional blank fields in my table in Access. How can I make sure that this does not happen? I want to keep on importing into the same table, so these useless empty fields keep on accumulating. Any help? Thanx, Stacey
I'm trying to create a database project for college and have run into a problem with a query. I am trying to find all bookings that have taken place in the last month, but with added details from other tables.
I have taken the job details and date (with validation for the last month only) from my Jobs table, and this works perfectly. However, when i try to match customer IDs to their names (stored in a seperate table), Access returns the same job multiple times with every customer name possible.
Any help you can give me to return just the one result needed would be greatly appreciated.
This is the code that I used to build a query. But some of the data that I thought I removed is still showing up. Any guidance on why? As always all feedback is welcome.
SELECT final.[SSN P ], final.[SSN S ], final.[SP'S SEC Y97], final.[SP'S SEC Y98], final.[SP'S SEC Y99], final.[SP'S SEC Y00], final.NCCD, final.[TC-530], final.[ DOB ], final.[ DOD ], final.[ PRIMARY NAME (ENMOD) ], final.[ PRIMARY NAME CONT'D (ENMOD) ], final.[ STREET (ENMOD) ], final.[ CITY (ENMOD) ], final.ST, final.[ZIP 1], final.ZIP2, final.ZP3, final.ZP3, final.CNLY, final.C, final.YRLR, final.[ PRIOR YEAR NAME (ENMOD) ], final.PNLY, final.P, final.[XREF SSN ], final.[INOLEX XREF CD V], final.[ INOLEX XREF TIN V ], final.[INOLEX XREF CD I ], final.[ INOLEX XREF TIN I], final.[ PRIMARY NAME (IRPTR) ], final.[ PRIMARY NAME CONT'D (IRPTR) ], final.[ STREET (IRPTR) ], final.[ CITY (IRPTR) ], final.ST1, final.[ZIP 11], final.ZIP21, final.ZP31, final.TXPD, final.[#DOCS], final.[#_SUM], final.[WAGES ], final.TX_WTHLD, final.ALLC_TPS, final.INTEREST, final.MTGINTPD, final.POINTSPD, final.PRYRRFND, final.SAV_BOND, final.DIVIDEND, final.PENS_ANN, final.[IRA_CTB ], final.NONEMPCM, final.CPTLGAIN, final.RL_ES_SL, final.GRSSDIST, final.TXBL_AMT, final.FICATXWH, final.TFICAWGS, final.MEDPYMNT, final.TFICATIP, final.FICAMISC, final.DFRDCOMP, final.UNEMPCOM, final.[RENTS ], final.ROYALTES, final.MEDCREWH, final.MEDCR_WG, final.[OR_K1 ], final.GRWINING, final.STDLNAMT, final.[ORD_DIV ], final.ROIRACTB, final.SMPL_CTB, final.[FMV ], final.CD_S_SMP, final.STK_BOND, final.[ADV_EIC ], final.FISH_INC, final.OTHERINC, final.SUB_PMTS, final.GOLD_PAR, final.CROP_INS, final.TXSTTUIT, final.TX_GRANT, final.[AG_SUBS ], final.INTFORFT, final.ORISSDSC, final.BARTERNG, final.PROFLOSS, final.AMTDBTCN, final.PATRONAG, final.MSAGRDIS, final.[TX-PRD], final.[LFRZ-RFRZ], final.MODULE_BALANCE, final.[TC-150], final.TC150_DT, final.[TC150_DLN ], final.RETRCDDT, final.[TC-290], final.[TC-291], final.[TC-300], final.DC, final.[TC-301], final.DC1, final.[TC-420], final.[TC-421], final.DC2, final.[TC-424], final.PRJ, final.[TC-425], final.PRJ1, final.[TC-460], final.TC460_DT, final.[TC-462], final.TC462_DT, final.[COLL-ASG], final.CC, final.[TC-540], final.[TC-590], final.CC1, final.[TC-591], final.CC2, final.[TC-594], final.CC3, final.[TC-598], final.[TC598 DT], final.[TC-599], final.CC4, final.[TC-610], final.[TC610 DT], final.[ TC610 AMT ], final.[TC-611], final.[TC611 DT], final.[ TC611 AMT ], final.[TC-612], final.[TC612 DT], final.[ TC612 AMT ], final.[TC-670], final.[TC670 DT], final.[TC670 AMT ], final.[TC-671], final.[TC671 DT], final.[TC671 AMT ], final.[TC672 DT], final.[TC-672], final.[TC672 AMT ], final.[TC-673], final.[TC673 DT], final.[TC673 AMT ], final.[TC-678], final.[TC678 DT], final.[TC678 AMT ], final.[TC-679], final.[TC679 DT], final.[TC679 AMT ], final.[TC-922], final.[PROCESS CDS], final.[TC-960], final.[TC-961], final.[TC-962], final.[TC-976], final.[TC-977], final.TAXPRD, final.ADJRSN FROM final WHERE (((final.[TC-530])<>"TC-530") AND ((final.[ DOB ])>1929) AND ((final.[ DOD ]) Is Not Null) AND ((final.[TC-150])<>"TC-150") AND ((final.[TC-290])<>"TC-290") AND ((final.[TC-291])<>"TC-291") AND ((final.[TC-300])<>"TC-300") AND ((final.[TC-301])<>"TC-301") AND ((final.[TC-420])<>"TC-420" And (final.[TC-420])<>"TC-420") AND ((final.[TC-421])<>"TC-421" And (final.[TC-421])<>"TC-421") AND ((final.[TC-424])<>"TC-424" And (final.[TC-424])<>"TC-424") AND ((final.[TC-540])<>"TC-540") AND ((final.[TC-590])<>"TC-590") AND ((final.[TC-591])<>"TC-591") AND ((final.[TC-594])<>"TC-594") AND ((final.[TC-599])<>"TC-599") AND ((final.[TC-976])<>"TC-976") AND ((final.[TC-977])<>"TC-977")) OR (((final.[ DOB ])<1987));
I am getting unwanted duplicates due to a joining error, I think.
The goal is to extract a list of employees and their accosiated departments showing a field called [CTD] from qry_employee but there are employees associated with more than one department.. I have a qry_employee liked to another qry (“qry_employee_ctd”) containing the desired field [CTD] joined by [employee_name] but there are employees associated with more than one department. NOTE: Both tables contain the department.
Please let me know if more detail is needs to assist.
Any ideas? I am up for any suggestions and appreciate your help greatly Thanks T
How to search an Access table for unwanted <cr> characters
Occasionally a stray carriage return <cr> Ascii 013 character finds its way into an Access table. These destroy the database when the table is processed by an outside utility for data cleansing.
To prevent this from happening, we have been told to clean the table before submitting it, i.e. remove all of the following:
carriage return, comma, double quotes, equals, greater than, smaller than
Is there any utility available which will remove all these characters when being run only once? (i.e. not find/replace which has to be started separately for each of these characters)
How do I search for a <cr>, even with find/replace?
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:
Hi! Solution is maybe simple, but I don't know it! When I put some value in a control (textbox) on a form, and after that if I close a form (by x button) that recordset is added to a table, but I don't want to do that- I just want to exit (close) the form! How to avoid adding that recordset to a table? Same things happens when I, by VBA, set focus to control, assign some value to it, and just want to exit the form.
I have a query which contains about 19 fields in it and some of the fields contain parameters. What I'm trying to do is to run the query using the parameters I've set, but at the same time remove fields that are empty (therefore only fields that are populated will be shown). I've tried using the 'is not null' parameter but it seems to interfere with the other parameters that I've set and as a result the query doesn't show any information. Also the field containing the 'is not null' parameter is still being shown. I'm not really sure what else to try!! Any suggestions would be most appreciated!!
Ok, this is a seemingly VERY simple problem gone haywire!
I have a very simple database that gathers data from 50 questions. It stores these in fields as numbers (1 - 10). no problem.
However, I've had to split the questions over 3 forms and this is where the problem begins. When I reach the end of the first lot and I click the button to open the next form, it jumps to the next record. So, on Form 1 we were on record 5 and then form 2 continues as record 6 (form 3 would be record 7).
All I want is for them all to appear in the same record.
I have a continuous subform with allow additions set to false. To make a new record I have used some update vba to create the record direct in the underlying query, then requery the form and the partly created record appears. The user then adds a quantity and some text. The subform still appears without the new record line.....However if I click the button again to create a second new record I end up getting an extra 2 lines.
One is a duplicate of my previous one and a new blank record. These do not actually appear in the underlying table and the subform looks ok. However this extra record confuses the end user and I want to avoid it. Refresh or shift f9 does not eleviate the problem. Sometimes I even get two "current record" pointers.
I've put the following Function together to clear out unwanted bits before exporting as csv. I was just wondering if there's a way of holding the table names together with a total count of each item replaced? I wasn't sure if these were stored and could be returned?
Code: Public Function ClearCommas() On Error GoTo ClearErr Dim db As DAO.Database Dim tb As DAO.TableDef Dim fld As DAO.Field
