Date comparison in SQL query problem 2

[RickBerem]

Hi,
I've build a form that enables a user to "create" a query by selecting and entering the data he wants to see.

My user can enter 2 dates (beginning and end) and my research query is supposed to return all the production data between this 2 dates.

I use this line of code.

strQuery = strQuery & &quot; Production.Date >= #&quot; & CDate(txtDateBegin) & &quot;# AND production.Date <= #&quot; & CDate(txtDateEnd) & &quot;#&quot;

the rest of the query is added else where in the code but it's functionnal since it works with all other features of my searh.

When I enter dates, The query returns nothing... whatever the dates I enter...

any Ideas?

Thanks

RickBerem

 

[llkhoutx]

Try building and running your query in the SQL Server enterprise Manager window. When it runs correctly there code it.


If your using SQL Server or pass through query utilize the single quote (') insstead of the pound (#) to enclose a date.

 

[RickBerem]

thanks llkhoutx,

But I only use Access...

sorry if it isn't clear in the subject...

RickBerem

Any other ideas?

 

[llkhoutx]

Use cvdate() instead of cdate()

 

[LWinfrey]

I don't know how to answer your questions using SQL, but maybe this will work for you... If you go into the design page of the query you are running type:

between [Enter Date to Begin Search, MM/DD/YY] and [Enter Date to End Search, MM/DD/YY]

on the &quot;Criteria&quot; line under the Field you are searching. Obviously you would adjust the wording to suit your needs and date format. Hope this helps.

~LWinfrey

 

[llkhoutx]

Trap your strSQL when it's finally built utilizing a breakpoint and put in the QBE SQL frame to see if it will execute.

Alternatively, build your query in the QBE frame, test until it executes properly, and then look at the SQL gerenated for that query. compare it to your original code and make the required modifications!

 

[MichaelRed]

I believe the CDate & &quot;#&quot; are redundant.

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over