Dlookup and update question about criteria. 1

[cblock1025]

I have one more question for you guys, can't figure it out or do not know if it can be done. Althouhg you guys seem to amaze me all the time.

Ok my database is for a Shrimp farm company and it handles a los of 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 last weeks total_weight and subtracts it from this weeks. Which then gives me an increment.

UPDATE piscina INNER JOIN piscina AS piscina_1 ON 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 update will always pick up 7 days before even if the pool is 15 and not 16. I was trying to put like a where statement and make the criteria 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 the pool number, lets say 16 it will only do that operation with the pools labeled 16. However, because the user is inputing at the moment I think 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 have inputed. Same as above where it only picks up the value of the specific pool number.

Thanks, I promise this will be my last question, hope so!!

 

[gol4]

Do an additional join on the poolid so the join is on the date and the poolid then it will only update the pool id where they are equal. In addition you can add a where clause if desired

UPDATE Pool INNER JOIN Pool AS Pool_1 ON (Pool.readdate = Pool_1.readdate+7) and (pool.poolid = pool_1.poolid) SET Pool.increment = [pool].[total_median_weight]-[pool_1].[total_median_weight] where pool.poolid = 16;

UPDATE piscina INNER 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-piscina_1.peso_W_total where piscina.número_de_piscina = 16

Not certain but perhaps the problem with your dlookup is dates need to have # sign

=DLookUp("[peso_W_total]","piscina","[dias] = #" & Forms!muestreo_semanal!dias-7 &"#&quot

hope that helps.

 

[cblock1025]

Hey, I really appreciate your help. As you know I am very rusty at access. I never used access before, but I can code in php, shell, c++ etc. However, for my senior project here in college, I have to work with Access. It is nice I will admit but I am still getting the sql down.
However, the first question you answered perfectly it works like a charm. I just copy and pasted it and it works great, I only modified it a little and changed the =16 to =[forms]![my_form]![number_days] and it works great every time the user enters the value it picks it up and only does by pool ID. Thanks so much!!!. So answer one is perfect.

Now the other Dlookup stuff is basically the same concept. What it does is looks up the total_median_weight from last weeks and just displays it on the form field. However the code I have works but I can't incoporate the pool_number = 16 in it or [forms]![my_form]![number_days].

Here is what I want to write:

Dlookup("[peso_W_total]","piscina","[dias]="& Forms!muestreo_semanal!dias-7 And [pool_number] = 16]

This way it will only pick up the field that is from a certain pool in this case 16.

With that you should get a concept of exactly what I want to do. I seen various examples online to try to figure it out. However, none have the quotation on the equal.
This does not work because I guess the quotes are on the equal, and truly I have no Idea why. I just know the code works, but I cannot ad an And expression to it. I bet you it is easy, I have been trying everything adding quotes and stuff, but it does not work. So, I just decided to leave it to the professionals.

Once again, I really appreciate your time. I am sorry to probably waste it with very easy questions, but I just can't get it to do what it has to do. Anything I can do for you don't doubt to e-mail me.

Christian

 

[cblock1025]

Sorry, I forgot to mention. The field dias is actually just days it is not a date. Because the company gets data every tuesday the days increment in by 7 so last week was day 140 and this week 147.

 

[cblock1025]

Finally I got it to work!
=DLookUp("[peso_W_total]","piscina","[dias] =" & Forms!muestreo_semanal!dias-7 & "And [número_de_piscina] =" & Forms!muestreo_semanal!número_de_piscina)

It works now. I finally figured how to mix them up with the quotations.

Once again thanks, if I can do anything just e-mail me. This is the second time you help me. Thanks.

Christian