How To Add To A List (not Replace)?

Nov 8, 2004

I have a combobox called ‘SupplierList’ full of suppliers that the user can choose from. Then I have a listbox called “SupplierQuery’ which is empty. The user will double-click on a name in ‘SupplierList’ to add the suppliers that they want to include in some future query. I have this as the event procedure so far:



Private Sub SupplierList_DblClick(Cancel As Integer)

With Me.SupplierQuery.RowSource = _

"SELECT Supplier " & _

"FROM GS_List_Suppliers " & _

"WHERE Supplier = '" & Me.SupplierList.Value & "';"

End With

End Sub



This works insofar as it adds the double-clicked name to the ‘SupplierQuery’ listbox. But it replaces the previous double-clicked name with the newly double-clicked name each time.
How can I make so that it adds each newly dbl-clicked name - forming a list of names in the listbox - instead of replacing the previous entry? If possible, I would like it to
get the listed results directly from a table through a query.

View Replies


ADVERTISEMENT

Queries :: Make Replace Query To Look In Order To Find What To Replace

May 6, 2014

I have this working query:

Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Left([dbo_BACKUP_ACESSOS.LOGIN],255) AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>"ACTIVE DIRECTORY") AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));

But Iwant to be able to use a set of data to be used in the Replace Statement, so I create a table to add each string I would like to have replaced by "nothing", and trying to make the replace query to look there in order to find what to replace.I also created a table where I will list the systems that I dont want in the select, so I removed the "ACTIVE DIRECTORY" and replaced by the colum that have the list of system I dont want listed.This is the result:

Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Replace((Left([dbo_BACKUP_ACESSOS.LOGIN],255)),[PREFIXOS_E_SUFIXOS]![Valor],"") AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>[SISTEMAS_EXCLUIDOS]![Sistema]) AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));

The thin is that this keeps asking me to enter the parameter value for "PREFIXOS_E_SUFIXOS!Valor" and for "SISTEMAS_EXCLUIDOS!Sistema"

View 6 Replies View Related

Replace Vbcrlf

Dec 5, 2005

How do I replace vbcrlf in a string with "NN"

I have tried putting vbcrlf and "NN" as parameters into this but it doesn't work (maybe it can't see vbcrlf as a separate word!)

Function ReplaceWord(strText As String, _
strFind As String, _
strReplace As String) As String

' This function searches a string for a word and replaces it.
' You can use a wildcard mask to specify the search string.

Dim astrText() As String
Dim lngCount As Long

' Split the string at specified delimiter.
astrText = Split(strText)

' Loop through array, performing comparison
' against wildcard mask.
For lngCount = LBound(astrText) To UBound(astrText)
If astrText(lngCount) Like strFind Then
' If array element satisfies wildcard search,
' replace it.
astrText(lngCount) = strReplace
End If
Next
' Join string, using same delimiter.
ReplaceWord = Join(astrText)
End Function

View 1 Replies View Related

Find & Replace

Aug 21, 2007

I have some database, I need to replace empty cells one column with some data.
But Access does not do it.

Any suggestions?

View 1 Replies View Related

Help With Find And Replace

Jul 17, 2007

is there any simple way to find and replace wildcard character- I have a large data table with text as sample below- I would like to remove |00


2901-0206|00


Thanks

View 1 Replies View Related

Expression To Replace / With _

Jun 23, 2005

i have a table that has a column containing a letter reference a / and then another reference ( EG. AAA/A56457A, ght/6577654ghf, etc ) and i want to replace the / with a _

whats the easest and quickest way to do this as an expression

best regards

View 1 Replies View Related

Replace In Query

Dec 14, 2006

Hi Guys,

Need a little bit of help, I have a report I pull out of an inhouse system with sales, shortages etc. I want to use a query to alter the formatting slightly and append it in to a different table. Most of this i can do fine, except for one thing.

Two of the sales fields in my report I'm uploading come out in the following text format:
19.0PC
27.0PC
45.0PC
etc...

I need to change this before appending as its going in to a number field, all i need to do is lose the PC, e.g.
19.0
27.0
45.0
etc...

I can do this manually, it's a simple find and replace in excel before uploading, gowever I want to automate this as much as possible. Looking around I'm pretty sure it can be done in a query using the REPLACE function but I'm unsure on the formats to use.

The only other thing is once its converted even though it will visually be a numbe (e.g. 45.0) it will still technically be a text field, will this prove a problem on the appending?

Many Thanks

Paul

View 1 Replies View Related

Multiple Replace

May 18, 2007

Hi,

I am currently working on a project where i need to try and compare a list of organisation names held by my organisation and another organisation (around 20000 records each).

To take into account differences in how the organisation names may have been entered I'm wanting to strip out things like ltd, limited, company, co, &, and , and so on. I've tried using IIf and replace which works ok if the organisation name only contains one of the substrings I want to replace but not if contains more than one.

How do i go about doing multiple replaces in the same string?

View 4 Replies View Related

Replace All Query

Aug 14, 2007

Hi, i have a table of data, with a user name, and a task.

Each user is assigned many tasks, i and i would like to create a query that replaces all instances of a certain name with another name. How would i do that?

Thanks

Sean

View 1 Replies View Related

REPLACE Function

Oct 23, 2007

I am trying to use an update query to modify some text in a specific field using the REPLACE function without much luck!!!

I have a field called "Option" and I want to replace the word "Metallic" with "Paint" and the word "Electric" with "Windows".

Could someone please point me in the right direction.

Thanks in anticipation.

View 7 Replies View Related

Replace Characters

May 1, 2008

Hi, i am looking to replace characters in a field using a query, I can use the replace to replace them all but that aint what I want e.g.

I want this: ~~test~test~~~~~test~~

To become this: test~test~test

I can use replace and get this testtesttest, but I need the ~ placed back between each word.

Any help appreciated, thanks:confused:

View 7 Replies View Related

Find And Replace

Jan 22, 2005

We do alot of find and replace in our main form (CTRL+F). The glitch is that once the pop-up box for "Find and Replace" appears, we almost always have to change the search setting from "Whole Field" to "Any Part of Field". Is there a way to set the default to "Any Part of Field" instead so that we don't have to change the setting every time?

View 3 Replies View Related

Find And Replace

May 27, 2005

Is it possible to change the background color on the Find and Replace dialog box that access provides?

I open it with this code:

DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

View 1 Replies View Related

Replace #Error

Feb 20, 2006

I have a query where I make a calculation. In some cases this calculation results in a division by zero. The field will then show #Fout (#Error in english?). I want to replace this message with something else, like a dash for example.

Can someone tell me how?

Thanks.

View 4 Replies View Related

Replace Last Digit

Oct 7, 2004

How would I replace the last digit of all the numbers on a table field from a 1 to a 2? Please help. Thank you.

View 2 Replies View Related

Replace Chars

Feb 14, 2006

Hey guys,

having a problem with a query, its used to export data to a txt file for another program.

the problem is i have a field that i format with end of line chars between entries in the field, so in access it looks like a few entries rather than one field (there were reasons for this).

when it comes to export this data, i need to remove the end of line chars with spaces.

heres the part of the query i need to fix:
Description: IIf(InStr([Order_data].[Stock2],Chr(13)),Replace([Order_data].[Stock2],Chr(13),Chr(32)) & Replace([Order_data].[Stock2],Chr(10),Chr(32)),[Order_data].[Stock2] & " " & IIf(nz([Order_data].[Stock3])<>"",IIf(InStr([Order_data].[Stock3],Chr(13)),Replace([Order_data].[Stock3],Chr(13),Chr(32)) & Replace([Order_data].[Stock3],Chr(10),Chr(32)),[Order_data].[stock3]),""))

yes that is nasty.. but simple to understand. 2 fields get combined for output (stock2 and stock3), i check first if they need to have chars replaced, then replace if necessary. the output i get though doesnt seem to be replacing the chars, it finds them there with the if however.

any help?

thanks guys

View 3 Replies View Related

Find And Replace Programatically

May 3, 2005

Hello,

I have a list of dates of births in a table, but the way they are always imported (from some piece of crappy software somewhere) is in the format of: 01.01.2005 (using full stops).

Access doesn't like this, and insists on slashes, or dashes. Now, if I do a find/replace, it works perfectly, just replacing all the full stops with slashes. However, I want to do this quite regularly...any ideas how to do it programatically in VBA?

Thanks!

Any help much appreciated!

View 4 Replies View Related

Big Problem With Replace Function

Jun 23, 2006

Hi All,

I'm currently migrating a lot of Excel processing to Access and really enjoying the transition. I am, however, having big problems with the Replace function in Access.....


In Excel I use a macro to relace anything in a cell contents that follows a space with nothing......i.e. replace " *" with ""

The fields that i need to do this on do vary in length- they are product descriptors.....e.g


abcdefg 123456
xzy 987


The replace feature (ctrl + h) in table view will do this correctly if "any part of cell" is specified in "match" selection but I cannot seem to replicate this in a query.

I'd be grateful for any suggestions here. I thought about exporting the fields in the columns to Excel and doing the replace there, but I can't get it to work. Similarly I tried writing some VBA for this, but again no joy!

Thanks,

N

View 2 Replies View Related

Find And Replace Question

Jul 6, 2006

Anyone know how to search for a line of text and replace it with 2 lines?

i.e.

Search for:

Set cnn = CurrentProject.Connection

and replace it with:

Set cnn = CurrentProject.Connection
cnn.CursorLocation = adUseServer

View 2 Replies View Related

Find And Replace Text

Aug 3, 2006

Hi,

I have my main table and one column is a lookup field to another table.

One of the options that was in the dropdown needed to be changed. So I changed the dropdown, but now I need to change all the old entries to reflect this change.

I opened the main table, and thought I could do a find/replace. But it doesn't work. If I do just a 'find' it finds the old text just fine. But if I do the Find/Replace option, it says the 'look for' text was not found. Even tho, I just found it using the find option. If I click on the tab that says find, it will find it again. If I click on the find/replace it can't find it.

any ideas on a way to quickly replace my text in these fileds, without having to go 1 by 1 through each record.

Thanks

View 1 Replies View Related

Global Search And Replace

Dec 13, 2006

Hi,
My company has renamed a product. I want to find all the records in all the tables in my database that might have the old name, and replace with the new name. What's the easiest way to do this?

I don't really want to have to manually open each table and do a find.

I can write code to go through all the tabledefs and do a find on each one, but the only way I know to do that is by telling it the field name, and field names where the product name might exist vary.

I suppose I can tell it something like
for each TD in tabledefs
for each F in td.fields
{search and replace}
next f
next t

Is this really the best way? I have a program called SpeedFerret, which I thought would do this, but it apparently only searches the table names, not the actual records in the table.

View 4 Replies View Related

Replace And Data Types

Jan 26, 2006

Hi all!
I'm experiencing an apparently simple problem, but it's taking me too long to solve :confused:
Two tables with a "text" (100) column on each. I want to select those who are identical. ¡So easy! thats the where clause:

AND a.sDesEntidad = b.sNombreLargo

That works fine. But... i want to "relax" the clause, by not considering dots ( . ) when comparing, and i do like this:

AND (Replace([a]![sDesEntidad],'.','') = Replace([b]![sNombreLargo],'.',''));

and i get a run-time Error 3464, Data type mismatch in criteria expression. Oooops!
My access version is 2003, spanish. I would really appreciate ur help, since this matter shouldn't be keeping me busy for so long :(
Regards and thx in advance!
Alejandro

View 1 Replies View Related

Replace Function In Access

Aug 3, 2006

I'm trying to assign a numeric value to different existing text values in a field in Access. Does anyone know the syntax of this?? :) thanks

View 3 Replies View Related

Trying To Replace DSums By Subquery

Oct 25, 2007

Hi there,

THE GIVENS:

The following query works fine:
SELECT tblProjectSteps.ProjectID, tblProjectSteps.StepID,...,
NZ(DSum("Total","tblPayments","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0)+
NZ(DSum("Total","tblReceivables","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0)+
NZ(DSum("Total","tblBankPayments","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0) AS TotalCosts
FROM tblProjectSteps INNER JOIN tblSteps ON tblProjectSteps.StepID = tblSteps.StepID;

The 3 summed tables (tblPayments,tblReceivables,tblBankPayments) do all of course have a foreign key for ProjectID (from tblProjects) and StepID (from tblSteps) and the output would be something like

STEPID.....ROJECTID....TOTALCOSTS
1.............100.............1100
2.............100.............1200
3.............100...............900
4.............100.............1300
1.............200...............700
2.............200.............1400

and so on...

THE PROBLEM:

For performance issues I would want to eliminate the DSum part, i.e get me a TotalCosts field by other means . I tried several constructions with multiple queries and also subqueries but to no avail. Any suggestions from the query Cracks her would be greatly appreciated.

Thanks
Jaime,

View 9 Replies View Related

Replace Function In A Query

Jan 19, 2008

Can the replace function be used in a query?

I have used this

=Replace(Format([SITELATSEC],"00.0"),".",",") in a report and it works fine.

I have the following expression in a query that is output to a report.

LAT: "Latitude: " & [SITELATDEG] & "º " & [SITELATMIN] & "' " & [SITELATSEC] & """ "

I need to be able to display [SITELATSEC] with a comma instead of a decimal point. I tried

LAT: "Latitude: " & [SITELATDEG] & "º " & [SITELATMIN] & "' " & =Replace(Format([SITELATSEC]00.0"),".",",") & """ "

and got an error message about too many commas.

On a more general note: Has anyone ever actually finished a database - ie got to the point where no changes needed to be made? 18 months after this started, and I'm still tryng to accomodate what my manager wants!

View 3 Replies View Related

Find A Box, Replace With Line

Jan 26, 2005

Hello hello

I have several boxes instead of line breaks throughout my table. I would like to do a find and replace. Is this possible???

I can't seem to select the box to cut and paste it into the "find" line, which is too bad, because I might be able to get by with just replacing it with a semi-colon or something.

But, since I'm asking, I might as well go for the gold and ask how to make it show as a new line too, right??

I found in other threads how to use chr(10) and chr(13)... which I did, which created the boxes in some of the fields. (Some of the fields already had boxes). I also read where I should use vbcrlf, but I can't get that to work. In THIS thread: http://forums.aspfree.com/t38413/s.html&highlight=chr%2810%29 someone had the same problem as me, and was told to do an OnFormat Event.. and well, I'm just not advanced enough yet to figure that out. I've done modules, and sql view queries... and a lot of tweaking with the help I've already gotten here.. so I AM learning...

So... is there a way to do this? Preferably cheap and easy.

-Siena

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved