Update on the fly

[HV_L]

Hi,

Got this attempt to update a field in table "registration" on the fly..

myperiodeveld = DLookup("[Periode_naam]", "Periode", "[registratie].[datum]" Between "[periode].[periode_startdatum]" AND "[periode].[periode_einddatum]")

Me.txtImport = myperiodeveld
Me.txtImport.Requery

Keeps giving me errors about syntax.
Just want to put a value from table Periode in Table registration based in the date set on a form, and as soon I change the date field, the Periode filed also recalculates it's value.
How should I do this?

Thanks

 

[Khalid_Afridi]

Hi,

Got this attempt to update a field in table "registration" on the fly..

myperiodeveld = DLookup("[Periode_naam]", "Periode", "[registratie].[datum]" Between "[periode].[periode_startdatum]" AND "[periode].[periode_einddatum]")

Me.txtImport = myperiodeveld
Me.txtImport.Requery

Keeps giving me errors about syntax.
Just want to put a value from table Periode in Table registration based in the date set on a form, and as soon I change the date field, the Periode filed also recalculates it's value.
How should I do this?

Thanks

I think you should put dot(.) before and after the .AND. and also the brackets after the WHERE clause ( )

Dim myperiodeveld as Variant

myperiodeveld = DLookup("[Periode_naam]", "Periode", "[registratie].[datum]" Between ("[periode].[periode_startdatum]" .AND. "[periode].[periode_einddatum]"))

 

[DCrake]

A lookup will only find the first match based on the where condition if the range of dates is large then you cannot be sure you are going to retreive the correct answer.

Also the syntax is wrong

"Between #" & Date1 & "# And #" & Date2 & "#"

 

[HV_L]

myperiodeveld = DLookup("[Periode_naam]", "Periode", "[registratie].[datum]" Between (#" & [periode].[periode_startdatum] &"# .AND. #" & [periode].[periode_einddatum] & "#))

Isn't woking either.
Let me try to clearify what I'm doing...
Table Periode has 4 columns
Id | Periode_naam | periode_startdatum | periode_einddatum

Id is the Autonumber
Periode_naam is P1 to P13
Periode_startdatum is the date a certain period starts
Periode_einddatum when it ends

In Table registratie I enter all kind of stuff
One of them is the date some event accurred
I want to fill a field on the form [registratie].[periodevak]
the Periode_naam when the entered date is between startdate and enddate as define in Table Periode

Would be nice if the moment I change the date on the form, this [periodevak] field would be updated immediately.

And what is the best way? Through Dlookup? VBA?