Use Query on unbound field?

[HV_L]

Hi,
I have this Query:

SELECT periode.Periode
FROM periode
WHERE ((([Forms]![Urenregistratie]![datum]) Between [periode].[periode_startdatum] And [periode].[periode_einddatum]));

This will give me the correct value if the Form Urenregistratie is open in the background.
What I want is to present this value on a form, so when the [datum] field on the Form is filled, automatically this periode value is displayed.
I'm strugling how to do this..
Can anyone help?

 

[HV_L]

Tried Dlookup too, but don't know if this is possible.. can't get the syntax right
Anyone?

 

[vbaInet]

You've got things the wrong way round:

SELECT Periode
FROM periode
WHERE ((([datum]) Between [Forms]![Urenregistratie]![periode_startdatum] And [Forms]![Urenregistratie]![periode_einddatum]));

You will need to reopen the query everytime you change the value on the form.

 

[HV_L]

Oke, thanks.
But how do I set this query to be the control of a textbox on the form?

 

[vbaInet]

DLookup()

http://www.mvps.org/access/general/gen0018.htm
http://www.techonthenet.com/access/functions/domain/dlookup.php

In which case you don't need the query.

 

[HV_L]

Yes, I already read those links, but still struggling with the syntax.
But what is the best way to set the control? Use a query or use Dlookup?
And for both I'm not sure how to accomplish it..
If I wanted the query for control, where in the properties of the field do I define that?
I can't select row source (just isnt'there)

 

[vbaInet]

Let's see the DLookup() code you've written that isn't working.

Where do you put it? Control Source property of a textbox.

 

[HV_L]

I removed the Dlookup string.. (didn't work so tried other things)
I am work now, still have the string somewhere at home..
sorry...

 

[HV_L]

Tried to create this Dlookup line.. (don't laugh):


DLookup("Periode", "periode", "datum = #" & forms!Urenregistratie!datum & "#")

 

[vbaInet]

=DLookup("Periode", "periode", "datum BETWEEN #" & [periode_startdatum] & "# AND #" & [periode_einddatum] & "#")

This is assuming that [periode_startdatum] and [periode_einddatum] are the names of the controls on your form.

 

[HV_L]

[periode_startdatum] and [periode_einddatum] are in the periode table, but not on the form.
On the Form is just a field [datum] with calendar picker..
Is this still possible then?

 

[HV_L]

Changed to this:

=DLookup("Periode", "periode", "datum BETWEEN #" & [periode].[periode_startdatum] & "# AND #" & [periode].[periode_einddatum] & "#")

But syntax is not correct, errormessage is saying that a operator is missing, or a comma is missing or a ) is missing..

 

[vbaInet]

Why did you have period_start and period_end in your original query anyway?

Try one of these:

=DLookup("Periode", "periode", "datum = " & datum) 		

=DLookup("Periode", "periode", "datum = #" & datum & "#")

If this is all you want to do then I don't see the point of using a DLookup because the periode already exists in the current record.

 

[HV_L]

This is the content of table periode:
periode Id Periode Periode_startdatum Periode_einddatum
1 1 3-1-2011 30-1-2011
2 2 31-1-2011 27-2-2011
3 3 28-2-2011 27-3-2011
4 4 28-3-2011 24-4-2011
5 5 25-4-2011 22-5-2011
6 6 23-5-2011 19-6-2011
7 7 20-6-2011 17-7-2011
8 8 18-7-2011 14-8-2011
9 9 15-8-2011 11-9-2011
10 10 12-9-2011 9-10-2011
11 11 10-10-2011 6-11-2011
12 12 7-11-2011 4-12-2011
13 13 5-12-2011 31-12-2011
I need to display is which periode the registered action on form has occured.
Hope it makes more sense now..

 

[vbaInet]

Can you attach a spreadsheet or take a screenshot of the records.

Also tell me, if you enter a date, what records you would want to see. A screenshot of this or the records on the same spreadhseet will do.

 

[HV_L]

I'll attach the database.

 

[missinglinq]

I'll let you and vbaInet hash this out, but just wanted to add two things as an aside.

It hasn't been said outright here, but you cannot use a Query as the Control Source for Control on a Form! Period! Can't be done! You have to use DLookup().

Second thing relate to periode.Periode. Access is Case Insensitive, which means that you have a Table and one of its Fields with the same, exact name! And I suspect that, sooner or later, this is going to confuse the Access Gnomes and cause you major grief. It may well be what's wrong right now. I'd have to change that Field name to something else!

Linq ;0)>

 

[HV_L]

Very clearly spoken!
Thanks, and I think I will chnge the name of the periode field in the table.
Indeed is a little confusing, I also keep in mind about the Case Insensitivity.
Thanks for making this clear.
And you're welcome to join the party as far as I'm concerned..

 

[HV_L]

And then there was silence...

 

[vbaInet]

I gave you a format I would like to have it in to better understand your problem but you chose to attach your database. If I felt I needed to see your database I would have mentioned so. All I want to see is:

1. These are the full records - as a spreadsheet or in code tags
2. This is the date I enter - some date
3. These are the records I should get - as a spreadsheet or in code tags.