SQL Help (Please)

[Ross Winter]

Hello to all the SQLers on the board,

I am looking for some assistance with what will no doubt prove to be a
terrible piece of code.

SELECT *
FROM ((((Assets AS a LEFT JOIN AssetTypes AS e ON a.AssetTypeNumber =
e.AssetTypeNumber)
LEFT JOIN Contacts AS c ON a.ContactNumber = c.ContactNumber)
LEFT JOIN Contacts AS d ON c.ContactParent = d.ContactNumber)
LEFT JOIN PATResults AS pr ON a.AssetNumber = pr.AssetNumber)
LEFT JOIN PATVisualResults AS pv ON pr.ResultNumber = pv.ResultNumber

(FIELDS)
{CONTACTSELECTION(d)}=[Contact Selection]
{Assets.AssetDescription}LIKE[Description]
{Assets.AssetManufacturer}LIKE[Manufacturer]
{Assets.AssetRoom}LIKE[Room]
{PATResults(pr).TestDate}>=[Tested From Date]
{PATResults(pr).TestDate}<=[Tested To Date]

I need to add a between condition to refine the result to a specified
date range that the end user will choose by means of a VB interface.

I was thinking of adding a line like

WHERE Date BETWEEN 'from date' AND 'to date'

to the bottom of the statement but no matter what I try I don't seem to
be able to get it to work, all I want is for the [Tested From Date] and
the [Tested To Date] to populate the BETWEEN condition.

Any help will be GREATFULLY received as at the moment I am pulling my
hair out.

Thanks in advance

Ross :)

[Larry Linson]

"Ross Winter" wrote

Hello to all the SQLers on the board,
WHERE Date BETWEEN 'from date' AND 'to date'

Date is the built-in function that returns the current date. Is that what
you want to test against the two Fields in your Record? Dates are not
strings... and are set off with # not single or double quotes, if at all.
Date/time type Fields in the Record most likely don't need to be set off at
all. Try

WHERE Date BETWEEN [Tested From Date] AND [Tested To Date]

if that does not work, try

WHERE Date BETWEEN #[Tested From Date]# AND #[Tested To Date]#

If you are building the statement in code, and need see where to put the
quote marks in the VBA expression, post back and someone may be able to
help.

Unless an SQL statement is trivially simple, I start with the Query
Builder -- I may simplify its output if I use that as a model for building
SQL in code.

Larry Linson
Microsoft Access MVP