Dlookup and update question about criteria.

  • Thread starter Thread starter Christian
  • Start date Start date
C

Christian

I am new to the forum. I hope I can resolve this.

Ok my database is for a Shrimp farm company and it handles a los o
numbers and calculations. So here it goes:

'piscina = pool = table
'peso_W_total = total_weight = field in my table
'número_de_dias = number of days = field in my table.
'Number of days always increments by 7. They gather data
' every tuesday.

Ok I have this from another post and it works great. It grabs las
weeks total_weight and subtracts it from this weeks. Which then give
me an increment.

UPDATE piscina INNER JOIN piscina AS piscina_1 O
piscina.dias=piscina_1.dias+7 SET piscina.incremento_total
piscina.peso_W_total-piscina_1.peso_W_total

However, the company has 16 pools and the problem is that this updat
will always pick up 7 days before even if the pool is 15 and not 16.
was trying to put like a where statement and make the criteri
something like this:

'número_de_piscina = pool number = field in my table

where piscina.número_de_piscina
[forms]![muestreo_semanal]![número_de_piscina];

So, I was just thinking that when the user inputed into the form th
pool number, lets say 16 it will only do that operation with the pool
labeled 16. However, because the user is inputing at the moment I thin
that the table is still empty and it doesn't work. Any ideas?

I have the same problem with this:

=DLookUp("[peso_W_total]","piscina","[dias] ="
Forms!muestreo_semanal!dias-7)

I want to put like a criteria AND pool number equals the one they hav
inputed. Same as above where it only picks up the value of the specifi
pool number.

Thanks ahead of time
 
I want to put like a criteria AND pool number equals the one they have
inputed. Same as above where it only picks up the value of the specific
pool number.
Click to expand...

Use the pool number directly in the query, and use a LEFT JOIN to
allow for the fact that piscina_1 may have no data:

UPDATE piscina LEFT JOIN piscina AS piscina_1
ON piscina.dias=piscina_1.dias+7
AND piscina.[número_de_piscina]=piscina_1.[número_de_piscina]
SET piscina.incremento_total =
piscina.peso_W_total-NZ(piscina_1.peso_W_total);

No DLookUp is needed.
 
Back
Top