SQL date parameter

[RonnieDobbs]

Would anyone know how to put a date parameter in this???



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

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

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

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

thanks,

ab.

 

[misscrf]

I am not sure, but you should be able to (in design view) go to query, parameters, and list your parameters. Then you will need to put those same parameters in the criteria level under the cooresponding fields.

misscrf

Management is doing things right, leadership is doing the right things

 

[wemeier]

Are you talking about being able to enter a date parameter once and using that date in all four SELECT statements in the UNION query? If so, just use the same parameter name in all four. It will only ask for the parameter value once and use it for all 4 SELECT statements. For example:

SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office]
FROM Confirm
WHERE ((([Confirm].[Fees])='Non-Standard') And (([Confirm].[Offlimits]) In ('std','none'))[b][COLOR=red] And (([Confirm].[Date]) = [ParameterDate])[/color][/b])

UNION SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office]
FROM Confirm
WHERE ((([Confirm].[Fees])='Non-Standard') And (([Confirm].[Offlimits]) In ('non std'))[b][COLOR=red] And (([Confirm].[Date]) = [ParameterDate])[/color][/b])

UNION SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office]
FROM Confirm
WHERE ((([Confirm].[Fees])='Standard') And (([Confirm].[Offlimits]) In ('std'))[b][COLOR=red] And (([Confirm].[Date]) = [ParameterDate])[/color][/b])

UNION SELECT [Confirm].[ID], [Confirm].[Fees], [Confirm].[Offlimits], [Confirm].[Office]
FROM Confirm
WHERE ((([Confirm].[Fees])='Standard') And (([Confirm].[Offlimits]) In ('non std','none'))[b][COLOR=red] And (([Confirm].[Date]) = [ParameterDate])[/color][/b]);

You can go into the query properties, select Parameters and enter ParameterDate as a parameter with a datatype of Date/Time. This makes sure the date is compared as a date and not evaluated as a division statement!

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[RonnieDobbs]

Thanks for the answer, However I have tried to find where to select parameters in query properties. I just have SQL view for this query.

ab

 

[wemeier]

You may be able to get away without defining the datatype for the parameter. Try adding the date parameter to the SQL as I've done in my example and run it. Enter a date in the format m/d/yy and see if you get the results that you want.

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[PHV]

I just have SQL view for this query
PARAMETERS [ParameterDate] DateTime;
SELECT ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244