SQL Query Date Variables 1

[Enkrypted]

Fairly new to Access/SQL and have come across an issue I need assistance with.

I'm trying to get a query built upon user input. I have a form with 2 text input boxes. One box is for start date and the other for end date.

What I need for my report is assistance in getting that information to be reported on when the submit button is clicked. Below is the current working SQL query I have without the date query

SELECT OptionsValues.AgentAssisted, OptionsValues.CurrentDate, OptionsValues.Duration
FROM OptionsValues
WHERE (((OptionsValues.Cable) Is Not Null))
ORDER BY OptionsValues.AgentAssisted;

Somehow I need to get it to query my report to reflect the CurrentDate to span whatever the Start Date(frmStartDate) and the End Date (frmEndDate) is. So if start date is 4/21 and end date is 4/22, it needs to pull information from those 2 days.

Any help on this is appreciated. TIA!

Enkrypted
A+

 

[bborissov]

If the fields are DATETIME type:
Add one day to your EndDate and convert both dates to so called ISO format "yyyymmdd' and send them as a string.

SELECT OptionsValues.AgentAssisted,
       OptionsValues.CurrentDate,
       OptionsValues.Duration
FROM OptionsValues
WHERE OptionsValues.Cable Is Not Null

      -- Bigger or equal of startdate 
      AND OptionsValues.CurrentDate >= '20140421'

      -- Smaller than your end date + 1 day. That way you will get right result if you have time in your datetime fields. 
      AND OptionsValues.CurrentDate <  '20140423' 
ORDER BY OptionsValues.AgentAssisted;

If the fields are DATE type:
Convert both dates to so called ISO format "yyyymmdd' and send them as a string.

SELECT OptionsValues.AgentAssisted,
       OptionsValues.CurrentDate,
       OptionsValues.Duration
FROM OptionsValues
WHERE OptionsValues.Cable Is Not Null
      AND OptionsValues.CurrentDate BETWEEN '20140421' AND '20140422' 
ORDER BY OptionsValues.AgentAssisted;

Other way is to use PARAMETERS in your query. Then you didn't have to convert anything. But you should ask Access forum for this.


Borislav Borissov
VFP9 SP2, SQL Server

 

[Enkrypted]

Thanks for the reply. One last question

AND OptionsValues.CurrentDate >= '20140421'

Can the date of 20140421 be replaced with a variable of some kind so that it will input whatever is put into that field? For example, the input box is labeled as frmStartDate, so how would I get it to work like

AND OptionsValues.CurrentDate >= 'frmStartDate'

so that whatever date is put in there will take the place of the original date in the following

AND OptionsValues.CurrentDate >= '20140421'

Enkrypted
A+

 

[gmmastros]

Passing parameters from Access to a query is really an Access question. There are people in this forum that may know the answer to this specific question, but you will likely get a quicker response if you post your question in an Access specific forum.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom