SQL date parameter syntax 1

[RonnieDobbs]

I have a select/where query with a few different conditions. I would like to have this query pull info between two entered dates. In normal design view in a query you would add Between [Enter Start Date] And [Enter Ending Date]. How would you put this as SQL?

thanks,

ab

 

[KenReay]

Hi

"In normal design view in a query you would add Between [Enter Start Date] And [Enter Ending Date]. How would you put this as SQL?"

Assuming the form containing the textbox controls StartDate and EndDate is open, and is the form in which this code is running

"SELECT...blah WHERE MyDateCol BETWEEN #" & Format(StartDate,"yyyy/mm/dd") & "# AND #" & Format(EndDate,"yyyy/mm/dd") & "#;"


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RonnieDobbs]

This is the last bit of the query, am I putting this in wrong?


UNION SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office]
FROM Confirm
WHERE ((([Confirm].[Fees])='Standard') And (([Confirm].[Offlimits]) In ('non std','none')))

SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office], [Confirmation date].[Office]
FROM Confirm
WHERE [confirmation date] BETWEEN #" & Format(StartDate,"yyyy/mm/dd") & "# AND #" & Format(EndDate,"yyyy/mm/dd") & "#;

 

[KenReay]

Hi

Not sure about the UNION part, you have UNION with no previous SELECT and then two SELECT's with no UNION

think it should be

SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office]
FROM Confirm
WHERE ((([Confirm].[Fees])='Standard') And (([Confirm].[Offlimits]) In ('non std','none')))
UNION
SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office], [Confirmation date].[Office]
FROM Confirm
WHERE [confirmation date] BETWEEN #" & Format(StartDate,"yyyy/mm/dd") & "# AND #" & Format(EndDate,"yyyy/mm/dd") & "#;"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

Ken, you must have the same number of columns of same type in the select clauses of an union query.

 

[KenReay]

Yes I know, I did not check every thing about his query, he was only asking about the BETWEEN dates part anyway

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

Also

[Confirmation date].[Office]

the table [Confirmation date] is not in the SELECT


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RonnieDobbs]

I have the first and last date promting now, however it does not effect the results. Does this have to do with this being a union query???

I have more code than above, I will paste it in full.

SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office], [Confirm].[confirmation date]
FROM Confirm
WHERE (((Confirm.[Confirmation Date]) Between [Enter Start Date] And [Enter Ending Date]))

UNION SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office], [Confirm].[confirmation date]
FROM Confirm
WHERE ((([Confirm].[Fees])='Non-Standard') And (([Confirm].[Offlimits]) In ('std','none')))

UNION SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office], [Confirm].[confirmation date]
FROM Confirm
WHERE ((([Confirm].[Fees])='Non-Standard') And (([Confirm].[Offlimits]) In ('non std')))

UNION SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office], [Confirm].[confirmation date]
FROM Confirm
WHERE ((([Confirm].[Fees])='Standard') And (([Confirm].[Offlimits]) In ('std')))

UNION SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office], [Confirm].[confirmation date]
FROM Confirm
WHERE ((([Confirm].[Fees])='Standard') And (([Confirm].[Offlimits]) In ('non std','none')));

 

[KenReay]

Could do, if the other SELECT's are selecting rows which do not match your BETWEEN, these rows will included in the result set

why not try the single SELECT in isolation first and get it working, then put it into the UNION query

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RonnieDobbs]

I have tried but to no prevail!!! Why can't this work? I am asking to gather information in this query and group by office in a report. From here I am making a report with 4 unbound text boxes that sum each different condition in the above query. I hide/delete all other feilds from the report and just the totals are showing. Now all I need is a Date parameter!

 

[KenReay]

Hi

Can we take one step at a time, forget the report for now

"SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office], [Confirm].[confirmation date]
FROM Confirm
WHERE (((Confirm.[Confirmation Date]) Between [Enter Start Date] And [Enter Ending Date]))"

does that select the rows you expect?

Yes or No?


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RonnieDobbs]

sorry over the pond here in London, yes.

 

[RonnieDobbs]

As are you I see....not very observant in the morning.

 

[KenReay]

Hi

Is that a yes it does seelct the data you would expect?

If yes, build up the query adding ONE Select clause at a time via the UNION key word, after each run the query and verify that it selects what you would expect.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RonnieDobbs]

I have added my first SELECT ROW below the parameter above with the column Confirmation Date. The results displayed are the correct criteria asked, but the date are not what I selected.

 

[KenReay]

Lets see the SQL

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RonnieDobbs]

I don't need to put the parameter in each UNION SELECT do I??

SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office], [Confirm].[Confirmation Date]
FROM Confirm
WHERE ((([Confirm].[Confirmation Date]) Between [Enter Start Date] And [Enter Ending Date]))

UNION SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office], [Confirm].[Confirmation Date]
FROM Confirm
WHERE ((([Confirm].[Fees])='Non-Standard') And (([Confirm].[Offlimits]) In ('std','none')));

 

[KenReay]

OK

So your first SELECT selects ONLY those rows with [Confirmation Date] in the date range you sepcified, but the second SELECT selects rows depending on the columns [Fees] and [OffLimits]

So it is perfectly correct to end up with a result set with [Confirmation Date](s) which are not in the specified date range



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Sorry pressed send too early

if you want only rows which satisfy all criteria you need to use a WHERE clause with AND's not a UNION query

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RonnieDobbs]

I see, but I have 4 different criteria. This will work for 1 criteria, but not for all 4 at the same time. Not quite sure how this is going to work.