Calendar Controls and Query

[EBGreen]

I am trying to create a query that uses the values of two calendar controls as between criteria. If I hard code a date for the between criteria, the query runs fine even from the form. If I try to use this for the criteria:
Between [Forms]![MonthlyReport]![txt_StartDate] And [Forms]![MonthlyReport]![txt_StopDate]
Then the sub that is actually running the query (I want to get the results into a recordset) throws an invalid SQL statement error. Any help would be great.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[randy700]

Try changing
Between [Forms]![MonthlyReport]![txt_StartDate] And [Forms]![MonthlyReport]![txt_StopDate]
to
Between # & Forms!MonthlyReport!txt_StartDate & # And # & Forms!MonthlyReport!txt_StopDate & #


Randy

 

[EBGreen]

When I try that, it won't even let me enter it. I get the
'The expression you entered has an invalid date expression'
error.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[randy700]

Can you post the SQL code for the query?

Randy

 

[EBGreen]

Thanks for the help. Here is the SQL:

SELECT dbo_PRODUCT.PRODUCT_ID, dbo_PRODUCT.MANUFACTURE_ID, dbo_PRODUCT.PRODUCT, dbo_PRODUCT.VERSION, dbo_EMPLOYEE_INFORMATION.LAST_NAME, dbo_EMPLOYEE_INFORMATION.FIRST_NAME, dbo_AB_RELEASE_STATUS.SEQUENCE, dbo_AB_RELEASE_STATUS.DESCRIPTION, dbo_AB_LIFE_CYCLE.DESCRIPTION, dbo_AB_LIFE_CYCLE.LIFE_CYCLE_ID
FROM (((dbo_PRODUCT INNER JOIN dbo_RELEASE_PROCESS_STATUS ON dbo_PRODUCT.PRODUCT_ID = dbo_RELEASE_PROCESS_STATUS.PRODUCT_ID) INNER JOIN dbo_AB_RELEASE_STATUS ON dbo_RELEASE_PROCESS_STATUS.RELEASE_STATUS_CODE = dbo_AB_RELEASE_STATUS.RELEASE_STATUS_CODE) INNER JOIN dbo_EMPLOYEE_INFORMATION ON dbo_RELEASE_PROCESS_STATUS.LOGIN_ID = dbo_EMPLOYEE_INFORMATION.LOGIN_ID) INNER JOIN dbo_AB_LIFE_CYCLE ON dbo_PRODUCT.LIFE_CYCLE_ID = dbo_AB_LIFE_CYCLE.LIFE_CYCLE_ID
WHERE (((dbo_AB_LIFE_CYCLE.LIFE_CYCLE_ID)=1 Or (dbo_AB_LIFE_CYCLE.LIFE_CYCLE_ID)=2 Or (dbo_AB_LIFE_CYCLE.LIFE_CYCLE_ID)=4) AND (([dbo_AB_RELEASE_STATUS]![SEQUENCE])=2.1 Or ([dbo_AB_RELEASE_STATUS]![SEQUENCE])=3.5) AND ((dbo_RELEASE_PROCESS_STATUS.CREATION_DATE) Between [Forms]![MonthlyReport]![txt_StartDate] And [Forms]![MonthlyReport]![txt_StopDate]));

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[EBGreen]

OK, I think the problem is in the way I call the query. Here is the code that calls it:

Dim myRs As New ADODB.Recordset
myRs.Open "Metrics_Test", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Where "Metrics_Test" is the name of the query.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[RoyVidar]

I've found opening stored queries in ADO with parameters to be a bit challenging. Two methods I use:
1 - concatinate an sql string - or
2 - use the parameters collection of the command object. Now, there might be some challenges there too in Access, have a look at thread709-819033.

Roy-Vidar

 

[EBGreen]

I found a solution. I pulled the SQL out of the stored query and concat it into a string with my params. Thanks for the help.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]