Problem with code

[DevilDog1978]

If [PC QUERY CORRECTED t1]![part_nbr] And [PC QUERY CORRECTED t1]![mfr_desc]
= IsNull Then
Replace([part_nbr],â€â€, [pcinventry]![model]) And Replace([mfr_desc],â€â€,
[pcinventry]![fscm])
End If

Above is a code I am trying to get to work. Basically If Access detects a
null value in [PC QUERY CORRECTED t1]![part_nbr] And [PC QUERY CORRECTED
t1]![mfr_desc] I want it to go look in the pcinventry table to fill in the
null values. Where am I going wrong?

 

[Armen Stein]

If [PC QUERY CORRECTED t1]![part_nbr] And [PC QUERY CORRECTED t1]![mfr_desc]
= IsNull Then
Replace([part_nbr],””, [pcinventry]![model]) And Replace([mfr_desc],””,
[pcinventry]![fscm])
End If

Above is a code I am trying to get to work. Basically If Access detects a
null value in [PC QUERY CORRECTED t1]![part_nbr] And [PC QUERY CORRECTED
t1]![mfr_desc] I want it to go look in the pcinventry table to fill in the
null values. Where am I going wrong?

You need to do each IsNull comparison separately:

If IsNull([PC QUERY CORRECTED t1]![part_nbr]) And IsNull([PC QUERY
CORRECTED t1]![mfr_desc]) Then

And Replace is for finding and replacing strings inside of other
strings, not for whole fields.

Are you trying to run this code in VBA or in a Query? It will be
structured differently in each case.


Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[DevilDog1978]

I would like to run it in VBA. Here is the Query I want to add it to:

SELECT [PC QUERY CORRECTED t1].lab_svc, [PC QUERY CORRECTED t1].customer,
[PC QUERY CORRECTED t1].part_nbr, [PC QUERY CORRECTED t1].serial, [PC QUERY
CORRECTED t1].ecn, [PC QUERY CORRECTED t1].mfr_desc, [PC QUERY CORRECTED
t1].sub_cust, [PC QUERY CORRECTED t1].int, [PC QUERY CORRECTED t1].fscm, [PC
QUERY CORRECTED t1].uic, [PC QUERY CORRECTED t1].lab_sched, [PC QUERY
CORRECTED t1].part_of, [PC QUERY CORRECTED t1].on_site, [PC QUERY CORRECTED
t1].std_cal, [PC QUERY CORRECTED t1].std_class, [PC QUERY CORRECTED
t1].std_type, [PC QUERY CORRECTED t1].status, [PC QUERY CORRECTED
t1].dt_rcvd, [PC QUERY CORRECTED t1].dt_reptd, [PC QUERY CORRECTED
t1].dt_lstcal
FROM [PC QUERY CORRECTED t1]
WITH OWNERACCESS OPTION;

Now if the part_nbr and the mfr_desc fields are populated, I would like for
Access to just leave the information in those records alone. Any clue how to
do this?

If [PC QUERY CORRECTED t1]![part_nbr] And [PC QUERY CORRECTED t1]![mfr_desc]
= IsNull Then
Replace([part_nbr],â€â€, [pcinventry]![model]) And Replace([mfr_desc],â€â€,
[pcinventry]![fscm])
End If

Above is a code I am trying to get to work. Basically If Access detects a
null value in [PC QUERY CORRECTED t1]![part_nbr] And [PC QUERY CORRECTED
t1]![mfr_desc] I want it to go look in the pcinventry table to fill in the
null values. Where am I going wrong?

You need to do each IsNull comparison separately:

If IsNull([PC QUERY CORRECTED t1]![part_nbr]) And IsNull([PC QUERY
CORRECTED t1]![mfr_desc]) Then

And Replace is for finding and replacing strings inside of other
strings, not for whole fields.

Are you trying to run this code in VBA or in a Query? It will be
structured differently in each case.


Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

Now if the part_nbr and the mfr_desc fields are populated, I would like for
Access to just leave the information in those records alone. Any clue how to
do this?

MAKE A BACKUP before you run any update queries!

In the query designer, change your query to an Update query. Join in
the tables from which you want to get your new values. Use Where to
select only the records where both target fields are Null, then use
the Update To to update them to the fields from the joined tables.

Records that already have a value in either of those two fields will
not be updated.

From VBA, you can run that update query using the Execute method.

Hope that gets you started,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com