If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Adding a date parameter to an SQL statement
Hi all, I have the following SELECT statement as below but want to add a date
parameter, where in the statement do I add it ?? can anyone help - I keep trying different places but it returns errors - Thank You SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults], [Main Audit Data].[Boiler Type], [Main Audit Data].Status FROM [Main Audit Data] WHERE ((([Main Audit Data].[Boiler Type]) Like "*isar*")) AND ([Main Audit Data].Status="warranty") GROUP BY [Main Audit Data].[Boiler Type], [Main Audit Data].Status; Include this date parameter where ????? HAVING ((([Main Audit Data].[Audit Date]) Between #1/1/2008# And #31/01/2008#)) |
#2
|
|||
|
|||
Adding a date parameter to an SQL statement
Include that as part of the where clause.
SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults] , [Main Audit Data].[Boiler Type] , [Main Audit Data].Status FROM [Main Audit Data] WHERE [Main Audit Data].[Boiler Type] Like "*isar*" AND [Main Audit Data].Status="warranty" AND [Main Audit Data].[Audit Date] Between #1/1/2008# And #31/01/2008# GROUP BY [Main Audit Data].[Boiler Type] , [Main Audit Data].Status; By the way, I would enter the dates in yyyy-mm-dd format or in the US format of mm/dd/yyyy. See the following for an explanation of why. International Dates in Access at: http://allenbrowne.com/ser-36.html John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County blake7 wrote: Hi all, I have the following SELECT statement as below but want to add a date parameter, where in the statement do I add it ?? can anyone help - I keep trying different places but it returns errors - Thank You SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults], [Main Audit Data].[Boiler Type], [Main Audit Data].Status FROM [Main Audit Data] WHERE ((([Main Audit Data].[Boiler Type]) Like "*isar*")) AND ([Main Audit Data].Status="warranty") GROUP BY [Main Audit Data].[Boiler Type], [Main Audit Data].Status; Include this date parameter where ????? HAVING ((([Main Audit Data].[Audit Date]) Between #1/1/2008# And #31/01/2008#)) |
#3
|
|||
|
|||
Adding a date parameter to an SQL statement
Brilliant - Thanks John, I knew there was a way round it, but just could not
see it. "John Spencer" wrote: Include that as part of the where clause. SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults] , [Main Audit Data].[Boiler Type] , [Main Audit Data].Status FROM [Main Audit Data] WHERE [Main Audit Data].[Boiler Type] Like "*isar*" AND [Main Audit Data].Status="warranty" AND [Main Audit Data].[Audit Date] Between #1/1/2008# And #31/01/2008# GROUP BY [Main Audit Data].[Boiler Type] , [Main Audit Data].Status; By the way, I would enter the dates in yyyy-mm-dd format or in the US format of mm/dd/yyyy. See the following for an explanation of why. International Dates in Access at: http://allenbrowne.com/ser-36.html John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County blake7 wrote: Hi all, I have the following SELECT statement as below but want to add a date parameter, where in the statement do I add it ?? can anyone help - I keep trying different places but it returns errors - Thank You SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults], [Main Audit Data].[Boiler Type], [Main Audit Data].Status FROM [Main Audit Data] WHERE ((([Main Audit Data].[Boiler Type]) Like "*isar*")) AND ([Main Audit Data].Status="warranty") GROUP BY [Main Audit Data].[Boiler Type], [Main Audit Data].Status; Include this date parameter where ????? HAVING ((([Main Audit Data].[Audit Date]) Between #1/1/2008# And #31/01/2008#)) |
#4
|
|||
|
|||
Adding a date parameter to an SQL statement
Just one more question John, when you say enter the dates in yyyy-mm-dd
format or the US style, do you mean in the design view or SQL view of the query ? Thanks "John Spencer" wrote: Include that as part of the where clause. SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults] , [Main Audit Data].[Boiler Type] , [Main Audit Data].Status FROM [Main Audit Data] WHERE [Main Audit Data].[Boiler Type] Like "*isar*" AND [Main Audit Data].Status="warranty" AND [Main Audit Data].[Audit Date] Between #1/1/2008# And #31/01/2008# GROUP BY [Main Audit Data].[Boiler Type] , [Main Audit Data].Status; By the way, I would enter the dates in yyyy-mm-dd format or in the US format of mm/dd/yyyy. See the following for an explanation of why. International Dates in Access at: http://allenbrowne.com/ser-36.html John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County blake7 wrote: Hi all, I have the following SELECT statement as below but want to add a date parameter, where in the statement do I add it ?? can anyone help - I keep trying different places but it returns errors - Thank You SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults], [Main Audit Data].[Boiler Type], [Main Audit Data].Status FROM [Main Audit Data] WHERE ((([Main Audit Data].[Boiler Type]) Like "*isar*")) AND ([Main Audit Data].Status="warranty") GROUP BY [Main Audit Data].[Boiler Type], [Main Audit Data].Status; Include this date parameter where ????? HAVING ((([Main Audit Data].[Audit Date]) Between #1/1/2008# And #31/01/2008#)) |
#5
|
|||
|
|||
Adding a date parameter to an SQL statement
Definitely use yyyy-mm-dd in the SQL view. If you enter a date in the grid
in the design view, I believe Access will interpret it correctly, but it won't in the SQL view. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "blake7" wrote in message ... Just one more question John, when you say enter the dates in yyyy-mm-dd format or the US style, do you mean in the design view or SQL view of the query ? Thanks "John Spencer" wrote: Include that as part of the where clause. SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults] , [Main Audit Data].[Boiler Type] , [Main Audit Data].Status FROM [Main Audit Data] WHERE [Main Audit Data].[Boiler Type] Like "*isar*" AND [Main Audit Data].Status="warranty" AND [Main Audit Data].[Audit Date] Between #1/1/2008# And #31/01/2008# GROUP BY [Main Audit Data].[Boiler Type] , [Main Audit Data].Status; By the way, I would enter the dates in yyyy-mm-dd format or in the US format of mm/dd/yyyy. See the following for an explanation of why. International Dates in Access at: http://allenbrowne.com/ser-36.html John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County blake7 wrote: Hi all, I have the following SELECT statement as below but want to add a date parameter, where in the statement do I add it ?? can anyone help - I keep trying different places but it returns errors - Thank You SELECT Count([Main Audit Data].[Boiler Type]) AS [Number of faults], [Main Audit Data].[Boiler Type], [Main Audit Data].Status FROM [Main Audit Data] WHERE ((([Main Audit Data].[Boiler Type]) Like "*isar*")) AND ([Main Audit Data].Status="warranty") GROUP BY [Main Audit Data].[Boiler Type], [Main Audit Data].Status; Include this date parameter where ????? HAVING ((([Main Audit Data].[Audit Date]) Between #1/1/2008# And #31/01/2008#)) |
Thread Tools | |
Display Modes | |
|
|