Between Expression - Include From and To Dates in Query 2

[gbobbie]

I currently have a simple parameter query based on a form with start and end dates. I am using a between expression:
Between [Forms]![qbf_e]![Begin_Date] and [Forms]![qbf_e]![End_Date]
And obviously this pulls data only between the two dates entered, not including data on the parameter dates.
I would like to modify the query to include the Start and End dates data, so I wrote an expression as follows:
>=[Forms]![qbf_e]![Begin_Date] And <=[Forms]![qbf_e]![End_Date]
But, it tells me the expression is too complex. Does anyone know a way around this? I'm not too knowledgable when it comes to SQL...

Thanks!

 

[PHV]

Seems you have time info in your data.
You may try this:
Between [Forms]![qbf_e]![Begin_Date] And [Forms]![qbf_e]![End_Date]+#23:59:59#

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

 

[gbobbie]

I tried your suggestion, but received an "expression to complicated or typed incorrectly" error.

 

[PHV]

Could you please post the actual SQL code ?

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

 

[Remou]

Perhaps you are experiencing a different problem.

The BETWEEN operator
The BETWEEN operator is used to determine if the value of an expression falls within a specified range of values. If the expression's value falls within the specified range, including both the beginning and ending range values, the BETWEEN operator returns True. If the expression's value does not fall within the range, the BETWEEN operator returns False. Let's suppose that we want to find all invoices that have an amount between $50 and $100 dollars. We'd use the BETWEEN operator in the WHERE clause with the AND keyword that specifies the range.

Intermediate Microsoft Jet SQL for Access 2000

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

(my bolding)

 

[gbobbie]

Following is the code:

SELECT call_sheet_details_e.[Contact Category], call_sheet_details_e.[Contact Type], call_sheet_details_e.[Contact Person Type], call_sheet_details_e.Date, call_sheet_details_e.[Coordinator Initials], call_sheet_details_e.[Site Name], call_sheet_details_e.[Site Contact Person], call_sheet_details_e.[Issue Category], call_sheet_details_e.[Issue Sub-category], call_sheet_details_e.[Issue Detail], call_sheet_details_e.Status, call_sheet_details_e.[Resolution Date], call_sheet_details_e.ID, call_sheet_details_e.[Site Phone Number], call_sheet_details_e.Covering, call_sheet_details_e.[Alternate Initials], call_sheet_details_e.[Coordinator Intials 2], call_sheet_details_e.[Issue Category 2]
FROM call_sheet_details_e
WHERE (((call_sheet_details_e.Date) Between [Forms]![qbf_e]![Begin_Date] And [Forms]![qbf_e]![End_Date]+#12/30/1899 23:59:59#) AND ((call_sheet_details_e.[Site Name]) Like "*" & [Forms]![qbf_e]![Site_Name] & "*" Or [Forms]![qbf_e]![Site_Name] Is Null) AND ((call_sheet_details_e.Status) Like "*" & [Forms]![qbf_e]![Status] & "*" Or [Forms]![qbf_e]![Status] Is Null) AND ((call_sheet_details_e.ID)=[Forms]![qbf_e]![LogID] Or [Forms]![qbf_e]![LogID] Is Null) AND ((call_sheet_details_e.[Coordinator Intials 2]) Like "*" & [Forms]![qbf_e]![Coordinator_Initials] & "*" Or [Forms]![qbf_e]![Coordinator_Initials] Is Null) AND ((call_sheet_details_e.[Issue Category 2]) Like "*" & [Forms]![qbf_e]![Issue Category] & "*" Or [Forms]![qbf_e]![Issue Category] Is Null));

 

[PHV]

Perhaps this modifications ?
[!]PARAMETERS [Forms]![qbf_e]![Begin_Date] DateTime, [Forms]![qbf_e]![End_Date] DateTime;[/!]
SELECT call_sheet_details_e.[Contact Category], call_sheet_details_e.[Contact Type], call_sheet_details_e.[Contact Person Type], call_sheet_details_e.Date, call_sheet_details_e.[Coordinator Initials], call_sheet_details_e.[Site Name], call_sheet_details_e.[Site Contact Person], call_sheet_details_e.[Issue Category], call_sheet_details_e.[Issue Sub-category], call_sheet_details_e.[Issue Detail], call_sheet_details_e.Status, call_sheet_details_e.[Resolution Date], call_sheet_details_e.ID, call_sheet_details_e.[Site Phone Number], call_sheet_details_e.Covering, call_sheet_details_e.[Alternate Initials], call_sheet_details_e.[Coordinator Intials 2], call_sheet_details_e.[Issue Category 2]
FROM call_sheet_details_e
WHERE (((call_sheet_details_e.Date) Between [Forms]![qbf_e]![Begin_Date] And [!]([/!][Forms]![qbf_e]![End_Date]+ [!]#23:59:59#)[/!]) AND ((call_sheet_details_e.[Site Name]) Like "*" & [Forms]![qbf_e]![Site_Name] & "*" Or [Forms]![qbf_e]![Site_Name] Is Null) AND ((call_sheet_details_e.Status) Like "*" & [Forms]![qbf_e]![Status] & "*" Or [Forms]![qbf_e]![Status] Is Null) AND ((call_sheet_details_e.ID)=[Forms]![qbf_e]![LogID] Or [Forms]![qbf_e]![LogID] Is Null) AND ((call_sheet_details_e.[Coordinator Intials 2]) Like "*" & [Forms]![qbf_e]![Coordinator_Initials] & "*" Or [Forms]![qbf_e]![Coordinator_Initials] Is Null) AND ((call_sheet_details_e.[Issue Category 2]) Like "*" & [Forms]![qbf_e]![Issue Category] & "*" Or [Forms]![qbf_e]![Issue Category] Is Null))

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

 

[gbobbie]

That worked! Thank you so much!!

 

[rosieb]

PHV - I was just having exactly the same problem. Many thanks.

Rosie
"Don't try to improve one thing by 100%, try to improve 100 things by 1%