The annoying "Enter Parameter Value"

[JosefPrado]

Hi,

I´m an access Newbie, so sorry if I´m asking anything stupid

I have this working query:

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 don´t want listed.

This is the result:

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"

I just can get rid of this, I cant find where I´m doing wrong :banghead:

Can someone help me with that?

 

[CJ_London]

try using a dot rather than a bang

[PREFIXOS_E_SUFIXOS].[Valor]

 

[pbaldy]

Those tables are not in the FROM clause, so aren't available. If they only have one record, you can add them without a join.

 

[JosefPrado]

Those tables are not in the FROM clause, so aren't available. If they only have one record, you can add them without a join.

Thanks pbaldy, it seems that is the issue.

When I added the tables in the FROM clause, it worked, but just because they actually only have one record, as soon as I added a second record to one of the tables, it just ignored and all the records came on the SELECT result.

I believe that its because I would need a JOIN, right?

What would be an intelligent way of doing a join here? Because with my knowledge, what I would do here is would be a series of steps, doing join after join, comparing to tables, the comparing the results against another table, then the results against another table, and although I believe this will work, I don't think this is the most efficient way of doing that, am I correct?

Regards,

Josef

 

[pbaldy]

Yes, without a join you get a Cartesian product. I don't really understand the data; I take it the sistema field isn't a candidate?

 

[JosefPrado]

Yes, without a join you get a Cartesian product. I don't really understand the data; I take it the sistema field isn't a candidate?

Hi Paul, with your hints I managed to be able to filter everything I needed.

The idea was to have a table with:

Users, System, Profile, Date

Then filter and remove all users that I found in AD
Get this results and filter against the systems I don't want in the results
And get the results and filter again removing the users I don't want in the result.

With you help I managed to do that, its working perfectly, I made a join, and another join, and another join and its working.

Now I juts need to adjust a little thing more.

On the accounts original that (where I made the first select) some user have a prefix before the username, like "NT\" or "COMPANY\", and I need to replace this for "nothing", so this is what I did:

INSERT INTO Contas_Localizadas ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Replace((Left([dbo_BACKUP_ACESSOS.LOGIN],255)),[Prefixos_Sufixos]![Valor],"") AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS, Prefixos_Sufixos
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>"ACTIVE DIRECTORY") And ((dbo_BACKUP_ACESSOS.DATA)=Forms!Home!Selecione_DATA));

The thing is that it works only when [Prefixos_Sufixos]![Valor] has only one record. If I add a second record there, it just ignores it... :banghead:

Do you think you can throw some light here?

Just for you to know, I´m doing a Left in the Login field because the original table in SQL server has this field as VARCHAR, so this is the way I found to convert it to text, so I could compare the data.

Regards from Brazil!

 

[pbaldy]

So the [Prefixos_Sufixos] table is supposed to contain any number of items that are to be removed from text in the first table? Maybe I'm just having afternoon brain cramps, but I don't think it's going to work the way you're trying to do it.

Two ways come to mind, both involving VBA. First is creating a function that you use in the insert query. Basically it would accept the original value as an input, use a recordset on that table to remove each item, and pass back the result. The second way would be to insert the data as-is, then run a process that updated the field, again using a recordset on that table.

Greetings from Nevada, USA!