SetValue from query

[gray8015]

My apologies up front for being a bit of a novice. I have a rugby database with matches, players, scoring. etc.
Based on the date (txtdate) of a game, I would like to retrieve the correct season (txtseason) from a table (tblseason).
(tblseason) has (txtstartdate), (txtenddate) and (txtseason).
When someone enters the game date on the entry form, I have an AfterUpdate macro that first runs a query and then SHOULD populate the form with the correct season using SetValue.
The query runs fine but I can't get (txtseason) into my form. I've tried making the values either numeric or text and neither one works.
Does anyone know what I'm doing wrong?
Again, I'm sorry if this is a simple question and I'm wasting your time.
Kind regards,
Kate

 

[pbaldy]

Welcome Kate. What are the arguments for the SetValue? You would have to be using a DLookup to get the value from your query (or directly from the table).

 

[gray8015]

I have
Item: [Forms]![frmFixtureList]![SeasonPlayed]
Expression: [qrySeason]![SeasonPlayed] (both with data type = text)

[SeasonPlayed] on my form has a control source which goes back to the table holding all of the data for each game.

I thought this would be more efficient because I only need the "date" looked up once and placed in the table/form. My limited knowledge of DLookup is that it looks up the data each time the form is opened??

I also struggled with the syntax of DLookup because the "date" I'm looking up is ">= startdate" and "<= enddate". It all got a bit beyond me!
Thanks for your help.

 

[pbaldy]

Well, a DLookup does lookup data, but so does your query, so you're making a trip to the data either way. You could think of a DLookup as a mini query. Your expression won't work, because you can't refer to a query directly like that. You can use a DLookup without criteria on your query (which I assume does have criteria, or eliminate the middleman and just use a DLookup that looks directly at the table. This might help on the syntax:

http://www.mvps.org/access/general/gen0018.htm

 

[gray8015]

Paul,
Well I have more than I had, but not quit there yet!
I have the following in the control source of "seasonplayed" on my form:

=DLookUp("[SeasonPlayed]","tblSeasonsandValues","[DateStart] < " & [Forms]![frmFixtureList]![Date]) & " AND [DateEnd] > '" & [Forms]![frmFixtureList]![Date] & "'"

It returns the following result:
1899 - 1900 AND [DateEnd] > '06/04/2010'

PS...The date of the actual game is: 06/04/2010

Any ideas what I'm doing wrong? Again, Thanks!

 

[pbaldy]

You haven't closed off the DLookup (which may be a cut/paste issue), but mainly you haven't surrounded the date values with #. Try this:

=DLookUp("[SeasonPlayed]","tblSeasonsandValues","[DateStart] <= #" & [Forms]![frmFixtureList]![Date]) & "# AND [DateEnd] >= #" & [Forms]![frmFixtureList]![Date] & "#")

Note I also added the equals sign, which is based on an assumption.

 

[gray8015]

Paul,
THANK YOU so much! I've been struggling with this for ages! There are a few games that don't show the correct season (even though the query shows the correct season), but I'll work on that tomorrow.

Is there a simple way to get the resulting "season" back into the fixture table? My issue is:
The point values have changed over the years and the only way to calculate the correct totals (scores * points) is to know which season the score took place. (Make sense?)

If I can get the correct "season" in each fixture record, I can then calculate season totals, etc.

Thanks again.
It's dinner time here, so I'll check in tomorrow. Thank you sooo much!

 

[pbaldy]

Dinner time?!? It's not even lunch time yet!

This is one of those values where it's debatable whether it should be saved at all, because it can be derived from another saved value. Some things I would absolutely say you shouldn't save, this one I don't feel as strongly about. If it wasn't saved, it could easily be gotten in a query. You'd use a join a lot of people don't know about, called a non-equi join. Here's an example from a test db:

SELECT tblActivity.OrderNum, tblActivity.TransDate, Rate
FROM tblActivity LEFT JOIN Rates ON tblActivity.TransDate Between Rates.StartDate and Rates.EndDate

If you want to save it, I'd do it in the after update event of the textbox where you enter the date. The code would look like:

Me.txtSeason = DLookup(...)

 

[gray8015]

I'll try to get my head around the last post. I'm a bit out of my depth, hopefully tomorrow it will all be a bit clearer.

I noticed that the dates that were returning the wrong value are being read as US dates - mm/dd/yyyy. I'm using UK dates - dd/mm/yyyy.

When the date is 28/12/1987 everything is fine. Reading December 28th.
When it is 05/12/1987 it's reading it as May 12th not December 5th and putting it in the wrong season.
Is there a setting I missed?

Thanks once again...

 

[pbaldy]

It's probably because of that improper date format.

These may help:

http://support.microsoft.com/default...b;en-us;210069
http://www.mvps.org/access/datetime/date0005.htm