Date query 1

[krets]

I'm trying to run a date query and having no luck. Can anyone tell me what's wrong with this:

SELECT tblAITInfo.*, tblTechs.*
FROM tblAITInfo INNER JOIN tblTechs ON tblAITInfo.TechRxEInit=tblTechs.TechRxEInit
WHERE tblAITInfo.AITDate >= Forms!frmAITTotals!StartDate And tblAITInfo.AITDate <= Forms!frmAITTotals!EndDate;

When I open the form that uses this query there are no results. If I open the query and fill in the popups with an appropriate date it also returns nothing.

AITDate is a column in tblAITInfo. It has a Data Type of Date/Time and a format of Short Date.

 

[AlexCuse]

You need to put the date delimiter (#) around your parameters.

Hope his helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[krets]

So:

SELECT tblAITInfo.*, tblTechs.*
FROM tblAITInfo INNER JOIN tblTechs ON tblAITInfo.TechRxEInit=tblTechs.TechRxEInit
WHERE tblAITInfo.AITDate >= #Forms!frmAITTotals!StartDate# And tblAITInfo.AITDate <= #Forms!frmAITTotals!EndDate#;

???

 

[lespaul]

yep, that should do it....

 

[krets]

That's giving me a syntax error message.

"Syntax error in date query expression 'tblAITInfo.AITDate >= #Forms!frmAITTotals!StartDate# And tblAITInfo.AITDate <= #Forms!frmAITTotals!EndDate#'

 

[PHV]

First, at the time the query is executed the frmAITTotals form should be open and populated.
Next, you may define the parameters in your query:
PARAMETERS Forms!frmAITTotals!StartDate Datetime, Forms!frmAITTotals!EndDate DateTime;
SELECT ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[krets]

So my query should look like:

PARAMETERS Forms!frmAITTotals!StartDate DateTime, Forms!frmAITTotals!EndDate DateTime;
SELECT tblAITInfo.*, tblTechs.*
FROM tblAITInfo INNER JOIN tblTechs ON tblAITInfo.TechRxEInit=tblTechs.TechRxEInit
WHERE tblAITInfo.AITDate>=Forms!frmAITTotals!StartDate And tblAITinfo.AITDate<=Forms!frmAITTotals!EndDate;

When I open the form and execute the query it is still displaying no results. The default values for the query dates on the form are for today's date. That shouldn't cause it to be blank should it?

 

[PHV]

If AITDate contains time info:
WHERE tblAITInfo.AITDate>=Forms!frmAITTotals!StartDate And tblAITinfo.AITDate[!]<1+[/!]Forms!frmAITTotals!EndDate
or:
WHERE Int(tblAITInfo.AITDate) Between Forms!frmAITTotals!StartDate And Forms!frmAITTotals!EndDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886