Selecting values from diff dates in one query

  • Thread starter Thread starter KERRY J
  • Start date Start date
K

KERRY J

I have a db that contains the tables tblTank-Daily (daily measurements
for a series of physical tanks that hold fluids) and tblTankTable
(which essentially is a lookup table containing volume data for a given
gauge measurement for a given tank). The Tanks are gauged daily (in
feet and inches) to determine the volume of fluid (in barrels).

I am trying to build a query that will show the volume for two
different dates; (selected date and selected date - 1). Ultimately, I
want to build an expression which will subtract the (selected date - 1)
volume from the (selected date) volume to determine a daily production
value.

I built the query to show the volume for one day (sql shown below) but
haven't been able to figure out how to pull two days.

SELECT [tblTank-Daily].tankID, [tblTank-Daily].date,
[tblTank-Daily].[gauge-Feet], [tblTank-Daily].[gauge-Inches],
tblTankTable.volumeBBL
FROM tblTankTable INNER JOIN [tblTank-Daily] ON tblTankTable.tankID =
[tblTank-Daily].tankID
WHERE ((([tblTank-Daily].date)=[Enter Date (mm/dd/yy):]) AND
(([tblTankTable]![gauge-Feet])=[tblTank-Daily]![gauge-Feet]) AND
(([tblTankTable]![gauge-Inches])=[tblTank-Daily]![gauge-Inches]));

Thanks in advance for any help!
 
Kerry,

This should give you the entered date and the date before...

WHERE ((([tblTank-Daily].date) BETWEEN [Enter Date
(mm/dd/yy):] AND ([Enter Date (mm/dd/yy):]-1))

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
KERRY J said:
I have a db that contains the tables tblTank-Daily (daily measurements
for a series of physical tanks that hold fluids) and tblTankTable
(which essentially is a lookup table containing volume data for a given
gauge measurement for a given tank). The Tanks are gauged daily (in
feet and inches) to determine the volume of fluid (in barrels).

I am trying to build a query that will show the volume for two
different dates; (selected date and selected date - 1). Ultimately, I
want to build an expression which will subtract the (selected date - 1)
volume from the (selected date) volume to determine a daily production
value.

I built the query to show the volume for one day (sql shown below) but
haven't been able to figure out how to pull two days.

SELECT [tblTank-Daily].tankID, [tblTank-Daily].date,
[tblTank-Daily].[gauge-Feet], [tblTank-Daily].[gauge-Inches],
tblTankTable.volumeBBL
FROM tblTankTable INNER JOIN [tblTank-Daily] ON tblTankTable.tankID =
[tblTank-Daily].tankID
WHERE ((([tblTank-Daily].date)=[Enter Date (mm/dd/yy):]) AND(([tblTankTable]![gauge-Feet])=[tblTank-Daily]![gauge-Feet])
AND(([tblTankTable]![gauge-Inches])=[tblTank-Daily]![gauge-Inch
es]));

Thanks in advance for any help!
Click to expand...
 
Gary,

Thanks for your response. I tried your suggested change to the query
(as shown);

SELECT [tblTank-Daily].tankID, [tblTank-Daily].date,
[tblTank-Daily].[gauge-Feet], [tblTank-Daily].[gauge-Inches],
tblTankTable.volumeBBL
FROM tblTankTable INNER JOIN [tblTank-Daily] ON tblTankTable.tankID =
[tblTank-Daily].tankID
WHERE ((([tblTank-Daily].date) BETWEEN [Enter Date (mm/dd/yy):] AND
([Enter Date (mm/dd/yy):]-1)) AND
(([tblTankTable]![gauge-Feet])=[tblTank-Daily]![gauge-Feet]) AND
(([tblTankTable]![gauge-Inches])=[tblTank-Daily]![gauge-Inches]));

However, I got an error message that read:
"This expresion is typed incorrectly or is too complex to be evaluated.
For example, a numeric expression may contain too many complicated
elements. Try simplifying the expression by assigning parts of the
expression to variables."
 
Kerry,

My apologies, I sure thought that would work, but it is
definitely the '- 1' that is causing the error. I had never
actually tried that.

I guess the best that I can offer at the moment would be
either prompt them for a Start date and End date seperately
or to launch the query from a form that allows them to input
the date desired and then populate another control, hidden
maybe, that would take the value of that date and subtract a
day. Then you could reference the controls like....

BETWEEN Forms!YourFormName!StartDateControlName AND
Forms!YourFormName!EndDateControlName
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
KERRY J said:
Gary,

Thanks for your response. I tried your suggested change to the query
(as shown);

SELECT [tblTank-Daily].tankID, [tblTank-Daily].date,
[tblTank-Daily].[gauge-Feet], [tblTank-Daily].[gauge-Inches],
tblTankTable.volumeBBL
FROM tblTankTable INNER JOIN [tblTank-Daily] ON tblTankTable.tankID =
[tblTank-Daily].tankID
WHERE ((([tblTank-Daily].date) BETWEEN [Enter Date (mm/dd/yy):] AND
([Enter Date (mm/dd/yy):]-1)) AND
(([tblTankTable]![gauge-Feet])=[tblTank-Daily]![gauge-Feet])
AND(([tblTankTable]![gauge-Inches])=[tblTank-Daily]![gauge-Inch
es]));

However, I got an error message that read:
"This expresion is typed incorrectly or is too complex to be evaluated.
For example, a numeric expression may contain too many complicated
elements. Try simplifying the expression by assigning parts of the
expression to variables."
Click to expand...
 
Back
Top