using Dates as a Criteria/Lookup in Query

[Kevin]

I have been searching past posts on using dates as query criteria but did not
find something similar to my issue.

I have two tables I am working with. The first lists a starting and ending
date when a specific value should be used for calculations in a second table.
The value used in calculations is "Weight" and the table looks like this:

STATION_ID START_DATE END_DATE GAGE_ID WEIGHT
MOGC0296 1/1/1970 3/15/1970 MOGC0112 0.34
MOGC0296 1/1/1970 3/15/1970 MOGC0124 0.66
MOGC0296 3/16/1970 11/7/1970 MOGC0123 0.206
MOGC0296 3/16/1970 11/7/1970 MOGC0127 0.321
MOGC0296 3/16/1970 11/7/1970 MOGC0129 0.083
MOGC0296 3/16/1970 11/7/1970 MOGC0130 0.39

The second table is joined to the first by the "Gage_ID" field and contains
data that is continously collected and needs to be used in a calculation with
"Weight" but the "Weight" value changes based on the time period when it was
collected. The second table contains the "Gage_ID", "DATE", and
"COLLECTED_DATA". How can I set up this query to only use the specific
"WEIGHT" dependent on the time period when it was collected.

Can I use the BETWEEN in the query criteria somehow?

Any help is greatly appreciated. Thank you.

 

[Klatuu]

The first thing you want to do is to rename the field named DATE. That is an
Access reserved word and reserved words can confuse Access. It may think it
is your field name or it may think you are using the Date function. Usually
enclosing a reserved word in brackets as in [DATE] will resolve the issue,
but I have seen Date used as a field name where someone was trying to do
WHERE [DATE] < Date()
And it still got it wrong. So, best practice is don't use reserved words.
Change it to something meaningful like COLLECTED_DATE

And yes, if you join the two tables, you can use

WHERE [COLLECTED_DATE] BETWEEN [START_DATE] AND [END_DATE]

Now, another point when comparing dates. If your dates contain time values,
you can loose records on either end of the range because Date doesn't = Now

That is to say if END_DATE is today, anything after midnight might be left
out.
Note that
Date will be:
06/04/2008 12:00:00 AM

And at this writing, now would be:
06/04/2008 2:07:37 PM

Which is greater than the date and would be filtered out.
So you have to be sure what is in your date fields and what you are using to
compare against.

 

[Kevin]

Thank you very much for help with renaming the Date fields and the query
criteria.
--
Kevin

The first thing you want to do is to rename the field named DATE. That is an
Access reserved word and reserved words can confuse Access. It may think it
is your field name or it may think you are using the Date function. Usually
enclosing a reserved word in brackets as in [DATE] will resolve the issue,
but I have seen Date used as a field name where someone was trying to do
WHERE [DATE] < Date()
And it still got it wrong. So, best practice is don't use reserved words.
Change it to something meaningful like COLLECTED_DATE

And yes, if you join the two tables, you can use

WHERE [COLLECTED_DATE] BETWEEN [START_DATE] AND [END_DATE]

Now, another point when comparing dates. If your dates contain time values,
you can loose records on either end of the range because Date doesn't = Now

That is to say if END_DATE is today, anything after midnight might be left
out.
Note that
Date will be:
06/04/2008 12:00:00 AM

And at this writing, now would be:
06/04/2008 2:07:37 PM

Which is greater than the date and would be filtered out.
So you have to be sure what is in your date fields and what you are using to
compare against.


--
Dave Hargis, Microsoft Access MVP

I have been searching past posts on using dates as query criteria but did not
find something similar to my issue.

I have two tables I am working with. The first lists a starting and ending
date when a specific value should be used for calculations in a second table.
The value used in calculations is "Weight" and the table looks like this:

STATION_ID START_DATE END_DATE GAGE_ID WEIGHT
MOGC0296 1/1/1970 3/15/1970 MOGC0112 0.34
MOGC0296 1/1/1970 3/15/1970 MOGC0124 0.66
MOGC0296 3/16/1970 11/7/1970 MOGC0123 0.206
MOGC0296 3/16/1970 11/7/1970 MOGC0127 0.321
MOGC0296 3/16/1970 11/7/1970 MOGC0129 0.083
MOGC0296 3/16/1970 11/7/1970 MOGC0130 0.39

The second table is joined to the first by the "Gage_ID" field and contains
data that is continously collected and needs to be used in a calculation with
"Weight" but the "Weight" value changes based on the time period when it was
collected. The second table contains the "Gage_ID", "DATE", and
"COLLECTED_DATA". How can I set up this query to only use the specific
"WEIGHT" dependent on the time period when it was collected.

Can I use the BETWEEN in the query criteria somehow?

Any help is greatly appreciated. Thank you.